Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Proc Cache Question Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 2:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 25, 2012 12:13 AM
Points: 20, Visits: 70
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
Post #1372944
Posted Tuesday, October 16, 2012 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 25, 2012 12:13 AM
Points: 20, Visits: 70
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

Post #1373076
Posted Tuesday, October 16, 2012 2:37 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
Freeing procedure cache in production systems should be considered only as last resort.
Did you look at the statistics. ?


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1373079
Posted Tuesday, October 16, 2012 4:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 25, 2012 12:13 AM
Points: 20, Visits: 70
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
Post #1373113
Posted Tuesday, October 16, 2012 5:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1373162
Posted Tuesday, October 16, 2012 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 25, 2012 12:13 AM
Points: 20, Visits: 70
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
Post #1373321
Posted Tuesday, October 16, 2012 4:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1373567
Posted Wednesday, October 17, 2012 2:22 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
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


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1373669
Posted Wednesday, October 17, 2012 6:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 259, Visits: 1,086
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.
Post #1374091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse