Cross DB stored procedures performance in SQL Server 2008

Question

Let's have:

• $DB an SQL Server database
• $DBSP1 an SQL Server database containing stored procedures referencing $DB
• $DBSP2 is exactly like $DBSP1
• $SP is a stored procedure

Running $SP on $DBSP1 from C# code takes around 1.5s.

Running $SP on $DBSP2 from C# code takes around 0.5s.

The C# code is very simple and use SqlClient with default parameters.

When I execute $SP in an SQL console on both $DBSP1 and $DBSP2, it takes 0.4s.

The only difference between the two code databases is $DBSP1 is in production and is a bit loaded, while $DBSP2 is idle. There is no data in code databases, only stored procedures and views over $DB.

Can someone suggest reasons why this happen? Since all the work happens in $DB which is equally loaded in both cases I would expect performances to be similar.

Answer 1

I think you have your answer. $DBSP1 is busy. It still has to task switch and prioritize work that comes in even though most of the work is happening in $DB.

Further, depending on the amount of data being transferred, the network activity comes into play. If $DBSP1 is sending / receiving a fair amount of data, that lessens the amount of available bandwidth for it to communicate with $DB and will cause result sets to transfer slower.

Answer 2

1) Check that both servers have the same indexes defined on each table

2) Rebuild Indexes (or at a minimum, update Statistics)

Do you have regular index maintenance jobs scheduled? Sounds like out of date statistics or fragmented indexes are causing the discrepancy..

Answer 3

Suggest you run profiler to see what is happening, but often the workload on a production database makes everything run slower. Also statistics may be out of date. Performance is rarely the same between two servers in my experience.

Answer 4

Measure.

Add SET STATISTICS TIME ON and SET STATISTICS IO ON to your C# and on the sql console call as well. In C#, hook up the SqlConneciton.InfoMessage event to capture the informational messages and display them. also separate the time measured for the SqlConnection.Open() call and the SqlCommand.ExecuteNonQuery call.

Then you'll be able to tell the difference.

• different conenction Open() times?
• different compilation/execution times ?
• different logical reads/physical reads ratio?
• different logical reads count?

Answer 5

The difference in speed came from different query plans. $SP is a complicated stored procedure and its initial query plan may vary vastly depending on the first query hitting it. And at this point we were not controlling the first request since right after deploying the $SP, it was published in production.

We tried providing hints about $SP parameters both at $SP levels and query levels, without success. So instead, we warm up the $SP before deploying with well crafted queries known to generate the correct query plan.