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 55973 times – thanks for reading.

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

Grant Fritchey

View all articles by Grant Fritchey

Related articles

Also in Database

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in Database Design

Five Online Database Modelling Services

To design, or redesign, a database of any complexity, the Entity-Relationship modelling tool becomes essential. The specialized tools that have dominated the industry for a long while are expensive and are installed on a workstation. Now that browser technology has progressed so rapidly, the online database modelling tools have become viable and are starting to attract the attention of database designers. Are they good enough to use now? Robert Sheldon finds out.… Read more

Also in Performance

T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem

Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more

Also in SQL

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more
  • 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.

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening