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