Clearing Plan Cache

Question

Post reply

Clearing Plan Cache

navtec

Hall of Fame

Points: 3206
More actions
November 13, 2014 at 6:14 am

#291960

Hi,
I am experiencing slow performance in SQL Server.
This is resolved by executing DBCC FREEPROCCACHE
The problem occurs every few days. Is there anything obvious which could be causing this that I am missing.
Are there any steps I can take to pinpoint the cause of this issue?
Any help much appreciated, Thanks.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

Bad parameter sniffing probably.

Are your stats updated regularly?

-- Gianluca Sartori

navtec Hall of Fame Points: 3206 More actions · Answer 2

Yes statistics are updated via a maintenance plan every night (full scan).

When the slowdown occurred previously, running update stats manually would restore performance.

Now it doesn't. Only clearing the cache restores performance.

There are no slow running queries. Auto create stats and auto update stats are both on.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

zedtec (11/13/2014)
There are no slow running queries.

So if there are no slow running queries what is the definition of slow performance?

Are you collecting any metrics to determine what might be the issue? Things like wait stats, perfmon counters (compiles, ple, io stats).

Typically if clearing the plan cache fixes performance issues it is related to bad parameter sniffing or out of date statistics and you can usually identify specific queries that are causing the problems and either re-write them, add the OPTION(RECOMPILE) or OPTIMIZE FOR hints, or imporve your indexing strategies to solve the issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

navtec Hall of Fame Points: 3206 More actions · Answer 4

The interaction between application and database becomes slow all of a sudden.

I have run a trace but no slow queries found (all running under 5 seconds).

Checked CPU, memory, disks on server itself and all is fine.

Will try your suggestions.

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 5

zedtec (11/13/2014)
The interaction between application and database becomes slow all of a sudden.
I have run a trace but no slow queries found (all running under 5 seconds).
Checked CPU, memory, disks on server itself and all is fine.
Will try your suggestions.

That all means that SQL Server is not slow (as long as "under 5 seconds" is acceptable in your shop). Check application server and network connections.

navtec Hall of Fame Points: 3206 More actions · Answer 6

Already checked application server, logs, the server itself and cannot spot anything.

Will check network connections.

Thanks.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 7

SQL Guy 1 (11/13/2014)
zedtec (11/13/2014)
The interaction between application and database becomes slow all of a sudden.
I have run a trace but no slow queries found (all running under 5 seconds).
Checked CPU, memory, disks on server itself and all is fine.
Will try your suggestions.
That all means that SQL Server is not slow (as long as "under 5 seconds" is acceptable in your shop). Check application server and network connections.

+1 to this. You should check wait stats. It could be ASYNC_NETWORK_IO waits because the application is doing something incorrectly when receiving and processing results. I see this all the time with one application I support.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

navtec Hall of Fame Points: 3206 More actions · Answer 8

Yes I think it might be that.

I'll check it out thanks.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

Based on the fact that you're seeing performance increase after FREE_PROCCACHE, while it may be other things, it's more than likely first and foremost, bad parameter sniffing. You need to identify the problem query or queries. Figure out why the execution plans are going pear shaped and then modify their behavior to fix it. Also, identifying the individual queries that are running slow will allow you to use targeted FREE_PROCCACHE by passing the plan_handle rather than nuking the entire cache every time.

----------------------------------------------------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

spaghettidba SSC Guru Points: 105712 More actions · Answer 10

Some more things to look at:

1) Since DBCC FREEPROCCACHE fixes the issue and you don't see slow performing queries, it might be that your cache is bloated by single-use plans and SQL Server is struggling to allocate memory for the other clerks. Check your plan cache when the issue is happening.

2) If it's a memory issue, you should see some indicators of it. Big drops in Page Life Expectancy are a good place to start looking.

3) If you suspect this is a network issue, take a look at the connectivity ring buffer. It might reveal interesting information on this regard. See this post for reference: http://www.johnsansom.com/using-sys-dm_os_ring_buffers-troubleshoot-connectivity-issues/

-- Gianluca Sartori

navtec Hall of Fame Points: 3206 More actions · Answer 11

Will check the execution plans for the queries I have identified as running slower than usual.

How would I check for ASYNC_NETWORK_IO? would this be from Perfmon?

spaghettidba SSC Guru Points: 105712 More actions · Answer 12

It's a wait class. It tells you where the instance is spending its time waiting.

See this post by Paul Randal for more information: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

-- Gianluca Sartori

navtec Hall of Fame Points: 3206 More actions · Answer 13

Thanks for that.

For the Plan Cache in perfmon, what should the counters be to consider performance to be good and what should they not be over in order to consider performance to be bad?

for example Cache Pages and Cache Objects for SQL Plans.

Is there anywhere I can reference for this information? I just want to know what to measure these counters against.

spaghettidba SSC Guru Points: 105712 More actions · Answer 14

I would monitor the memory clerk usage instead of a perform counter:

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb]

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(single_pages_kb) DESC;

Look for high values of the following clerks:

CACHESTORE_SQLCP SQL Plans

These are cached SQL statements or batches that aren't in stored procedures, functions and triggers

CACHESTORE_OBJCP Object Plans

These are compiled plans for stored procedures, functions and triggers

-- Gianluca Sartori