The case of the additional indexes

I was assisting with a SQL Server performance issue the other day.  The issue was transactional replication was unable to keep up while trying to replicate data from a transactional database to a reporting database.  This was causing the customer to miss their data latency SLAs.  The oddest part of the problem was that replication to a test reporting database was perfectly able to keep up.  Since the CPU, I/O, and memory capabilities of the two servers were similar, we began to suspect that there were differences in the schemas of the two databases (test and production) even though they were ostensibly supposed to be the same.

Unfortunately, detecting schema differences between two supposedly identical databases can be fairly difficult.  You can go through the databases by hand looking for differences or you can script out the entire schema and then compare them.  However, both of these approaches are subject to error since a human being has to actually identify the differences.

The good news is that there is a version of Visual Studio that can help solve the problem – Visual Studio Database Edition.  This edition of Visual Studio has a really neat feature called Schema Compare.  Let me walk you through the steps involved in making a comparison:

1)  Open up Visual Studio and then go to File->New->Project

2)  From there, browse to Database Projects and then select the appropriate variant of SQL Server and then Database Projectimage

3)  Give your project a name and a location

4)  Once the project has been created, go to Data->Schema Compare->New Schema Comparison


5)  At this point, you are able to select both a source database and a target database.  In this case, I am going to select the development database (BlackAdept) and the production database (DSDB)


6)  Click OK and the two databases will be compared

Here’s a snippet of the differences:


As you can see above, I apparently have a stored procedure in my production database that differs in definition from my development database (see the red highlight above).  I guess I need to go back and see why they are different before I have problems.  🙂

Looping back around to the original problem, we used the Schema Compare capability and found 8 (!!!) additional indexes in the reporting database.  The overhead of keeping these indexes updated was enough to keep the transactional replication process far enough behind that the data latency SLAs were being missed.  Removing these indexes allowed transactional replication to keep up, thus allowing the customer to meet their SLAs.

Evan Basalik | Senior Support Escalation Engineer | Microsoft SQL Server Escalation Services

Comments (8)

  1. SDC says:

    RedGate has a nice ‘SQL Compare’ tool we’ve used to sync up our test and prod db schemas for some time.

    Super easy to use and a lifesaver at times. Not a Microsoft Product, but it gets the job done.

  2. ray herring says:

    So what happens to the run time on the reports that probably drove the need for the indexes?

  3. Evan Basalik says:

    Ahh – that’s the balancing act.  It is certainly possible to see a decline in reporting performance if you remove necessary indexes.

    The key in this case was that the schema in test generated reports that were "fast enough", so we went with that schema.

  4. Evan Basalik says:

    Having used RedGate in a previous life, it certainly has some nice features.

    I couldn’t say it yesterday since the product had not released, but Visual Studio 2010 Premium includes the Database Project functionality that was a separate release in VS 2008.

  5. bwunder says:

    And if you want a compare tool that is high quality, speedy, highly configurable (but works great right out of the box), works between 200, 2005, and 2008 SQL Servers and even existing files, and is absolutely free check out the SQLClue Workstation download from my web site. You get all the SMO scripting options and you can apply regular expressions to your compares.

  6. znaika says:

    What was the problem with additional indexes?

    What was a version of SQL Server?

    Could you provide more detailed description of that problem and how it was fixed, please?

  7. Gustavo Ayala says:

    And suppose you really needed those extra custom indexes as are usually needed for read only reporting replicas and totally reasonable ?

    Maybe replication was over the head and mirroring (and read only snapshots) is better for such scenario ?

  8. EvanBa says:

    The problem with the additional indexes was that they were killing the replication process because of the overhead maintaining them.  Regarding the necessity of the indexes, it is a business decision.  You need to decide if replication keeping up is more important that reports running fast or vice versa.  In this particular case, a even better solution might have been to have a totally separate reporting database that was 12-24 hours out of date.

    If I remember right, this was against SQL Server 2008.

Skip to main content