Sql-server – MS SQL Server unexplained differences in query speed

sql serversql-server-2019

I have a production database and staging database, they contain the exact same data, the production database was backed up and restored as the staging database.

A particular operation (API call) always runs in about 200ms when connected to the Staging database but in production it always takes around 1700ms.

I thought the indexes might need reorganizing in production and in staging they could be in better shape because it was restored from a backup but when I ran

sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) ddips

the index fragmentation is pretty much the same between both databases.

I can try restarting the database server late at night and see if that brings up the performance but i'm really puzzled as to why one database would be so much different to the other in query performance when they contain the same data.

Best Answer

You want to look at the execution plan (actual execution plan would be better then the estimated one) on both server and see if there is a difference. I guess it would be my first step.

If they are different, then you'll need to understand why but at least, it will explain the performance difference.

If the plan is the same, you can also identify which part took more time (as we now have the time for each operator with SSMS 18).

Finally, you could look at the server waits time. This may help you identify where your bottleneck is. There are some tools like sp_blitzfirst that can help you with this.