共用方式為

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

image

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)

image

6)  Click OK and the two databases will be compared

Here’s a snippet of the differences:

image

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

  • Anonymous
    April 11, 2010
    The comment has been removed

  • Anonymous
    April 12, 2010
    So what happens to the run time on the reports that probably drove the need for the indexes?

  • Anonymous
    April 12, 2010
    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.

  • Anonymous
    April 12, 2010
    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.

  • Anonymous
    April 29, 2010
    The comment has been removed

  • Anonymous
    May 25, 2010
    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?

  • Anonymous
    May 27, 2010
    The comment has been removed

  • Anonymous
    May 30, 2010
    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.