October 23, 2020 at 12:50 pm
Is it possible to have a same execution plan for query but different run time?
We have an ongoing issue with a stored procedure that runs in 1 second sometimes and sometimes takes 2 minutes.
I was able to capture the actual execution plan for both the run times.
on comparing the execution plans both look the same.
Am I missing something?
October 23, 2020 at 1:29 pm
Nah, the plan can be the same for lots of different executions. If it doesn't recompile, regardless of parameters passed, you'll see the same plan used over & over. It's pretty common. So, this means, that in a scenario where, just for example, an index scan is the better choice, it's using a seek and running slowly. This is very common behavior.
Plus, you could see blocking one time and not another. Resource contention one time and not another. All sorts of things could affect the exact runtime, but leave the plan in place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 23, 2020 at 2:01 pm
If that is the case..how do I know the exact reason for the slowness?
Thank you
October 23, 2020 at 2:47 pm
You have to look to multiple metrics. The plan is one, certainly, but after that you look to I/O, CPU, memory, blocked processes, waits.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 26, 2020 at 2:56 am
If that is the case..how do I know the exact reason for the slowness?
Thank you
How many records does the procedure return during both executions? Is the count same?
Are you changing the parameters values for each run of the procedure ? If yes this can be due to parameter sniffing.
What exactly the procedure does?
As Grant mentioned check whether you facing any blocking at that moment, resource contention (CPU & Memory)
December 9, 2020 at 1:57 am
Hi, I am running into similar situation, except that my procedure consistently runs slow compared to other environment, when in comparison both environments have similar execution plan and statistics, attached are snapshots(slowstatistics and faststatistics). Any suggestion on what other areas to look at?
December 9, 2020 at 4:56 am
Hi, I am running into similar situation, except that my procedure consistently runs slow compared to other environment, when in comparison both environments have similar execution plan and statistics, attached are snapshots(slowstatistics and faststatistics). Any suggestion on what other areas to look at?
There's just not enough information in your post or your pictures to really know. For example, do the two machines have the same number and type of CPUs at the same clock speed? Same question for memory and disk (especially since your slow one hit the disk for read aheads). Have statistics been updated? Are the tables very close to the same size and have roughly the same page density due to possible fragmentation? Are they under the same workload? And "Similar execution plan and "Same execution plan" could be a world of difference. There's just to many unanswered areas to help here.
I recommend you attach both ACTUAL execution plans (and not the pictures of them) as actual saved execution plans as a starting point and only after you've answered the question I asked about configuration and workload.
--Jeff Moden
December 9, 2020 at 12:50 pm
In addition to what Jeff says, be carefule when you say things like "similar execution plans". Similar is not the same as identical. Small differences in an execution plan can absolutely point to the differences in performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply