February 10, 2021 at 3:03 pm
Just wanted to know what this could be
I have a simple query that takes 1 min for the initial run and then it takes less than 10 secs after the second run in test environment
and I can see the query in the cached plans
I run the same thing in prod it takes 1 min and how many times I run it takes the same time
I run the dmv the plan is not there..
What could be the reason of this behavior?
Thanks
February 10, 2021 at 7:52 pm
Most likely this is caused by physical reads - loading the data from disk into the buffer cache. Once the data has been loaded into the buffer cache the query no longer has to wait for the physical reads.
On production - the data is already loaded so you don't see any differences in performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2021 at 2:09 pm
Without seeing both plans and the code, I can only make a guess as to why you're not seeing the plan in cache. It could be, production is busy and the plan is simply aging out (yeah, not likely, but a possibility). Maybe there's a recompile hint or the code is called with recompile in production, so it won't be in cache. Maybe, you're trying to look for it using the query hash or plan hash from your test instance, and it may not match production, so you're not seeing it. There could be other possibilities, but it's all just speculation without more details.
"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