October 15, 2012 at 2:46 pm
Hi,
Background...
SQL Server 2005 sp2. Mirrored with witness
Problem
Every few days queries start getting slower
The only way to speed them up again is to use dbcc freeproccache
The database is using lots of ad-hoc queries
Attempts to resolve so far
I have switched to forced parameterization which had no significant effect
I have parameterized lots of queries whose plan is only used once. This has not fixed it.
Checked the size of the proc cache - it gets to 4gb very quickly and stays around there.
My Question...
My understanding is that this means that a query or maybe lots of queries are getting bad plans because of parameter sniffing - where it works ok when the plan is created, but that plan does not suit subsequent queries using the same plan which run slower.
My huge problem though - is how do I identify the queries/plans which are causing the problem? When I look in profiler, all queries are running slower. Do I concentrate on the ones which are taking longest?
The system is running a large number of queries every second (1000+)
It was slowing earlier today.
I tried setting OPTION (RECOMPILE) on a load of in the hope that I would randomly find the problem however it did not. query, In the end I ran dbcc freeproccache
If I can't get to the bottom of this I will have to run freeproccache as a maintenance plan every day or something, but clearly I would much rather be able to identify the actual problem.
Any thoughts welcome,
Thanks
Simon
October 16, 2012 at 2:29 am
Hi,
After some research my next step is to try
DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’)
And see if that has the same effect as DBCC FREEPROCCACHE
Right now the tokenandpermstore cache is 791mb/1874000 entries and running ok.
I will see what it is when it next runs slower.
There is a well documented issue with TokenAndPermUserStore which I have so far overlooked as we are on sp2 however some posts suggests it could still be the cause.
Thanks
Simon
October 16, 2012 at 2:37 am
Freeing procedure cache in production systems should be considered only as last resort.
Did you look at the statistics. ?
October 16, 2012 at 4:20 am
Hi,
I totally agree - I am now at the last resort phase!
We simply cannot have a database which slows down after 3-10 days.
I have looked at the statistics, but i'm not sure what they are supposed to tell me. There are 2 large tables (10m rows) and the statistics reflect this on those tables.
However i'm not sure how statistics relate to a slowing down database which speeds up by freeing the proc cache?
Thanks
Simon
October 16, 2012 at 5:38 am
simon4132-806507 (10/16/2012)
Hi,I totally agree - I am now at the last resort phase!
We simply cannot have a database which slows down after 3-10 days.
I have looked at the statistics, but i'm not sure what they are supposed to tell me. There are 2 large tables (10m rows) and the statistics reflect this on those tables.
However i'm not sure how statistics relate to a slowing down database which speeds up by freeing the proc cache?
Thanks
Simon
have you checked the memory ? this could be issue too here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2012 at 8:48 am
Hi,
I'm not sure how the memory could be a problem.
When I clear the cache it is back up to 4gb again within a short period (e.g. an hour or less) but the system does not slow down for days.
It has 32gb memory for a 40gb database.
Thanks
Simon
October 16, 2012 at 4:12 pm
Check for single use plans.
Plan cache, adhoc workloads and clearing the single-use plan cache bloat by Kim Tripp
I had to implement a variation of one of the queries in the post to clear ad hoc plans on a 2005 instance because the proc cache was using a very high % in the buffer pool and it started to affect performance after the instance was up for a while and usage upticked.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 17, 2012 at 2:22 am
simon4132-806507 (10/16/2012)
Hi,I totally agree - I am now at the last resort phase!
We simply cannot have a database which slows down after 3-10 days.
I have looked at the statistics, but i'm not sure what they are supposed to tell me. There are 2 large tables (10m rows) and the statistics reflect this on those tables.
However i'm not sure how statistics relate to a slowing down database which speeds up by freeing the proc cache?
Thanks
Simon
If the statistics are wrong you have bad plan and your query is slow.
Freeing proc cache is kind of restart - the - server solution. You may get back the performance by doing that.
But how do you confirm that your queries are waiting for memory. What waits do you have on your server?
I think yoou should thoroughly check cached plans and execution statistics before you look for resource bottleneck
October 17, 2012 at 6:10 pm
I have seen a cache plan bloat problem like this before.
It was caused by hundreds of stored procedures implemented with passed in arguments converted to local variables, the developer's theory being that parameter sniffing was a problem, and by confounding the parameter sniffing, he was able to force recompilation of the stored procedures' query plans with every execution of the stored procedures.
His technique did improve stored procedure execution times. But the old plans remained unused in memory and the plan cache grew and grew, with many thousands of unused query plans for the same stored procedures.
To offset this problem, the developer created a SQL Agent job to execute the DBCC FREEPROCCACHE command every 4 hours, which cleared the plan cache but didn't help server performance.
I eliminated the SQL Agent job and added "WITH RECOMPILE" to all of the stored procedures. This eliminated the plan cache bloat problem.
This was not an ideal solution. The "WITH RECOMPILE" clause forced no query plan reuse and added a small amount of time to every stored procedure execution. But, the stored procedures' executions times were still shorter than without the shuffling of arguments into local variables and the plan cache bloat problem disappeared.
For this particular set of stored procedures, there was a wide variance in passed in arguments, especially for date ranges, so the developer's theory about parameter sniffing may have had some validity. Empirical results from testing seemed to confirm it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply