15 May 2006

SQL Server Performance Testing

When faced with two viable solutions to a badly compromised database design, one using clustered indexes and the other compound primary keys, Grant Fritchey took the only sensible route: he gathered hard performance data...

Clustered indexes and primary keys

By Grant Fritchey

The company I work is for is implementing a custom system, built in Visual Studio and running against SQL Server 2000, to replace some old mainframe applications. It’s an all new database design that will meet all of the old application data requirements and a whole slew of new ones. I was responsible for the initial design, which I completed and handed over to a development team. I had been working quite closely with them for some time on the application, but I got pulled in other directions and the app and database had proceeded without me. One day, the development team raised a concern that the database was far too slow to support the anticipated transaction load. I began my investigation.

The problem

The application in question has a few data requirements that are different than the average application. Basically, at its heart, it is a document management system. However, every change to large sections of the data has to be stored historically, so that we have multiple versions of pieces of a given document. Not all of the data stored is recreated for each version. We needed to know that change ‘A’ was included in version ‘1’, but that change ‘B’ was in version ‘3’ and, on a completely different piece of data, read another table, and know that change ‘Q’ was included in version ‘2’. The data looks something like this:

Table 1

Version

Value

1

‘A’

2

3

‘B’

Table 2

Version

Value

1

‘Some other value’

2

‘Q’

3

When queries are run, the latest data in Table 1 is ‘B’ from version three and the latest data in Table 2 is ‘Q’ from version 2.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 56224 times – thanks for reading.

Tags: , , , , ,

  • Rate
    [Total: 42    Average: 4.1/5]
  • Share

Grant Fritchey is a SQL Server MVP with over 20 years’ experience in IT including time spent in support and development. Grant has worked with SQL Server since version 6.0 back in 1995. He has developed in VB, VB.Net, C# and Java. Grant has authored books for Apress and Simple-Talk, and joined Red Gate as a Product Evangelist in January 2011. Find Grant on Twitter @GFritchey or on his blog.

View all articles by Grant Fritchey

  • Finalist

    my co-worker
    I work with Grant and YES he does look that scary……

  • Anonymous

    great
    Hi Grant
    thats a very good article and you explained very well.
    am just wondering what happens if a particular Child table has more than one foreign key do we still make a compound primary pk including all those FKs ?

  • Granted

    RE: Great
    Thanks. I sure tried. It was such a fun process that I had a blast writing the article.

    Anyway, depending on the exact situation, yeah, I’d make all the FK’s be the clustered PK.

  • Anonymous

    statistics
    What commands you using to get the statistics

  • Granted

    re: statistics
    The transaction counts came out of the Quest Benchmark Factory tool. The rest were either from Performance Monitor or Profiler.

  • Anonymous

    Index Fragmentation
    re the MS design
    How is it working 6 months in ?
    My concern would be the levels of index fragmentation of the compound clustered primary keys when inserting new data

  • Granted

    6 mo in
    It’s working very well. We do index maintenance, rebuilds & reindex, on a nightly basis only hitting those indexes that are actually fragmented. It’s only hitting a sub-set of the indexes, running in about 90 seconds each night. So far, knock on wood, it’s all good. Thanks for asking.

  • pai

    good one
    I found this is good
    http://www.vsys.co.in/info

  • Mark D

    Bravo
    This is the most informative, interesting, and easily followable articles on SQL Server tuning that I’ve come across ever. I’m about to start my first true DBA role after over 8 years in database and app development, and greatly appreciate your insights not only on this article but your others on Simple Talk as well. Keep it up! 🙂

  • Granted

    re: Bravo
    Wow. Thanks.

    BTW, 1.5 years in and it’s scaling just fine. Not only no issues, but we’ve found some other areas where the data is of the same type and we’re using this approach. We also still test our designs to ensure they will behave the way we expect.

  • jt

    still scaling?
    A very helpful article, thx!

    still scaling well with the compound PK design?
    fragmentation not an issue?

    Did any of the tables have varchar columns as the part of the compound PK?

  • Granted

    re: still scaling?
    Yes it is still scaling very well. It has worked out quite nicely and we are using the method in other designs now.

    Occasionally we do see some statistics issues after a particularly large set of inserts, but it’s not in any way a show-stopper.

    No, none of the keys were using varchar columns. We’ve been very careful to use artificial keys in most of the root tables so that the compound keys consist almost completely of integers. We have had to put a datetime or smalldatetime into the key in some instances, but these continue to perform very well.

    I’ve done some testing with varchars (I don’t have all the numbers stored or I’d post them) and they just don’t perform as well as integers, especially if the varchar fields are really long or have a high percentage of null values. The nulls really kill performance. I’ll have to do new testing with SQL Server 2008 and the filtered indexes.

  • AbeMiessler

    Great post
    Only thing I might recommend is clearing your cache often in your “Verifying the Stored Procedures” step. It’s pretty simple, take a look at <a href=”http://www.abemiester.com/post/Setting-up-your-testing-environment-when-performance-tuning-SQL-Scripts.aspx”>this post</a>.

    Keep up the good posts. Your articles on execution plans have been very helpful to me with a project I’ve been working on lately.