-1

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.

Improve this question
5
  • It's possible you're running into parameter sniffing: dba.stackexchange.com/questions/204565/…
    – Josh Darnell
    Oct 6, 2020 at 16:09
  • I'm working on this slowly between other jobs, so i'll get back to you when I know more. I should have said both the staging and production databases are on the same database server, not 2 separate ones.
    – Paul
    Oct 7, 2020 at 16:09
  • I narrowed the problem down to 1 stored proc and ran : EXEC sp_recompile 'spCleanseDataLookupRecords' and the problem has been solved. Before I recompiled the stored proc it ran really quickly in Sql Server Management Studio but ran a lot slower using my c# code, and after it was recompiled it was really quick in both places. It's as if the cached query plan is per client and not global?
    – Paul
    Oct 7, 2020 at 17:12
  • @Paul if recompiling your procedure (sp_recompile) instantly fixed the issue, then I would definitely agree with Josh on this likely being due to a parameter sniffing issue. Basically the execution plan for your production copy of the procedure had bad cardinality estimates from when it was initially generated with the original parameters that were passed into the procedure. The staging copy was either supplied different parameters or was generally more able to accurately estimate the cardinality at the time it's execution plan was generated and therefore had a more accurate plan...
    – J.D.
    Oct 17, 2020 at 13:47
  • ...Recompiling forces a new plan to be generated which probably fixed the cardinality estimates on your production copy of the procedure. If the procedure typically receives parameter values that result in similar cardinalities (similar number of rows returned from the tables that filter on those parameters) then there's a pretty good chance you won't run into this issue again. But if you do run into this issue again, updating table statistics are a preferred way to fix parameter sniffing issues. See: brentozar.com/archive/2016/08/…
    – J.D.
    Oct 17, 2020 at 13:58

1 Answer 1

Reset to default
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.