August 11, 2017 at 8:34 am
Hello!
Could someone please help me to understand why a query would run faster after running “DBCC FREEPROCCACHE” ?
I am simplifying here but the application is sending queries to the database in the following format.
SELECT *
FROM dbo.Employee
WHERE FistName = 'John'
SELECT *
FROM dbo.Employee
WHERE FistName = 'Jim'
Basically, the same query with the first name changing. The value for FistName is not a parameter.
Today one of these queries was taking approx. 10 seconds to complete.
After running “DBCC FREEPROCCACHE” it is now running in approx. 1 second.
I can see different plans being created for the ad-hoc queries in the plan cache and due to the absence of parameters I am not concluding it is parameter sniffing (Happy to be wrong)
Could it be because the data has significantly changed in the table to cause the original cached plan to be inefficient?
Thank you for any help!
August 11, 2017 at 8:42 am
The easiest conclusion to draw is that you had a poor plan for the query you were running. Once you cleared the cache, the query recompiled with a better plan and the query ran faster. Parameter sniffing could certainly have been a factor, especially if there are significantly more employees called John than Jim, or vice versa.
John
August 11, 2017 at 8:56 am
Are your statistics up to date?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 11, 2017 at 9:08 am
Hi Phil/John
Thank you for your help!
Phil: Yes, when I first started looking at this I updated all stats for the table with full scan.
John: If I am following you correctly, going forwards because the data in the table will grow, the cached plan that is now working after clearing the cache, will eventually become inefficient?
If this is the case how would I manage this without scheduling DBCC FREEPROCCACHE?
As I have no control over what is being sent to the database, should I be looking into Optimising For Ad-Hoc Queries?
Or am I thinking about this incorrectly?
August 11, 2017 at 9:16 am
It may, or it may not. But if the query covers data that isn't uniformly distributed (lots more Johns than Jims), you're likely to see parameter sniffing. Setting optimize for ad hoc may help especially if you jump about from John to Jim quite a lot. This is too much to cover in a forum post, really. I recommend you look up some of Kimberly Tripp's articles on optimizing ad hoc statement performance.
John
August 16, 2017 at 2:56 am
Phil Parkin - Friday, August 11, 2017 8:56 AMAre your statistics up to date?
Hi Phil,
It turns out you're correct.
Hidden in another job, the statistics were being updated with a sample only causing the statistics to be wildly incorrect.
Thank you for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply