May 29, 2019 at 3:07 pm
I have a store procedure which is running 40 secs longer in production Vs test. However, prod has other user databases and also has more memory but activity is also high when compared to test. I did not see any blocking or locks while running the procedure on both environments.Do you think the time difference is very normal? Please advise?
May 29, 2019 at 6:46 pm
There can be lots of differences. How much data does the procedure work on in each environment? Are the execution plans the same? If not, why not? Have you looked at wait stats for the execution in each environment?
May 30, 2019 at 12:29 pm
So, memory is different. What else? Number of processors? Max Degree of Parallelism? Cost Threshold for Parallelism? ANSI connection settings? These are just the top server settings that lead to query execution differences between environments. Without knowing anything about the query, memory alone could be the issue. Is it processing HUGE amounts of data or just a row (and a small row)? Versions of SQL Server? Number of disks? Speed of disks? Speed of processors?
Let's talk about the database. Are they identical? I mean identical. Number of rows, exact indexes, statistics (including when last updated and how they were updated). If not, there's a VERY strong indicator. In fact, more often than not, this is the issue. Compatibility level of the databases? Database scoped configuration differences?
As Tim says, start with the execution plan. However, as you've already said, there are differences between the machines. It's the differences that are resulting in different behavior. Identify ALL the differences.
Now, you can ignore all that if you want to. Another option is to set up Extended Events. Capture the statement executions for the queries in question. Also, capture the wait statistics. Ensure that causality tracking is on. You can then see the exact waits for both queries. That will tell you, at least at a system level, why the performance is different. You'll still need to then look at the system based on the waits as indicated. Here's a blog post I wrote on how to do this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply