free the cache - SQL Server Server

Question

free the cache - SQL Server Server

river1

SSC-Insane

Points: 24315
More actions
April 27, 2016 at 12:28 pm

#326047

Dear all,
We will try in Production environment to clean the cache in order to get new query plans (stored procédures will need to create new plans)
This is because everytime we have the server for more than 1 month online we start to get slow. After a restart we are ok for another month.
We think that this is realted with bad query plans on SPs as memory , processor and disk are normal.
Can you please advice if the following commands can make any warm?
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO

Viewing 13 posts - 1 through 13 (of 13 total)

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

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 1

The cache drops old plans and add new ones as needed.

If you find certain items getting slow, I'd address those queries/procedures/functions themselves rather than clearing the cache. If you do this, then every plan, including good ones, gets flushed and all queries must be recompiled. I'd think that would slow your system down when this happens.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Don't guess as to the cause of a problem. You need to do enough analysis and investigation to be sure of the cause before you decide on a solution. Otherwise you could end up making the problems worse.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

river1 (4/27/2016)
Dear all,
We will try in Production environment to clean the cache in order to get new query plans (stored procédures will need to create new plans)
This is because everytime we have the server for more than 1 month online we start to get slow. After a restart we are ok for another month.
We think that this is realted with bad query plans on SPs as memory , processor and disk are normal.
Can you please advice if the following commands can make any warm?
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO

How often do you rebuild STATISTICS? Based on what you stated, it sounds like "never" is the answer and you need to change that sooner than later.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61142 More actions · Answer 4

I'm with Jeff - sounds like your stats are not getting updated. You should check your Auto Update Statistics setting for your databases (right click DB, go to options).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

river1 SSC-Insane Points: 24315 More actions · Answer 5

I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats

river1 SSC-Insane Points: 24315 More actions · Answer 6

I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

river1 (4/27/2016)
I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats

It still sounds like the stats are getting stale. If the statistics get updated, you will automatically get a plan rebuild, no need to nuke the entire cache to make that happen. I'm with everyone else, focus on gathering some more metrics so you know precisely where and why the issues are occurring. Running FREEPROCCACHE over & over on a scheduled basis is a poor choice for performance tuning.

"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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

river1 (4/27/2016)
I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats

Ah, careful now. Specifically, REORG does NOT rebuild stats. Only a REBUILD will. You can verify that in "Books Online".

Even then, relying on REBUILDs to take care of stats isn't the way to go because any given index may not become fragmented enough to trip the REBUILD into action. This is especially true if you have tables with a clustered index with ever increasing keys, which frequently never get fragmented enough to trigger a REBUILD in most folks maintenance procs.

Autostats are also woefully inadequate in 2008 because roughly 20% of the table has to change before autostats will kick in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (4/28/2016)
river1 (4/27/2016)
I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats
Ah, careful now. Specifically, REORG does NOT rebuild stats. Only a REBUILD will. You can verify that in "Books Online".
Even then, relying on REBUILDs to take care of stats isn't the way to go because any given index may not become fragmented enough to trip the REBUILD into action. This is especially true if you have tables with a clustered index with ever increasing keys, which frequently never get fragmented enough to trigger a REBUILD in most folks maintenance procs.
Autostats are also woefully inadequate in 2008 because roughly 20% of the table has to change before autostats will kick in.

I read that as "There's an additional step that updates statistics" but I could be very wrong (as usual).

"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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

Jeff Moden (4/28/2016)
river1 (4/27/2016)
I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats
Ah, careful now. Specifically, REORG does NOT rebuild stats. Only a REBUILD will. You can verify that in "Books Online".

While that's true, the OP explicitly says that if it was a reorg, then they update stats.

Besides, there's not enough evidence (no evidence in fact) that the problem is related to execution plans or stats. Restarts don't update stats and so uptime should not be a factor if it is stale stats

Last time I saw a problem like this (overall slowdown that got worse the longer the server had been up), it was a memory leak in a linked server driver, on SQL 2005 IA64.

The first step should be to ensure there's sufficient monitoring on the server to track query performance and common performance-related characteristics over time (I'd start with wait stats and latch stats, overall CPU, memory use and IO stats) and analyse them until you can determine a possible cause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

GilaMonster (4/28/2016)
Jeff Moden (4/28/2016)
river1 (4/27/2016)
I have a proc running every day that reorg/rebuild indexes. If reorg then also updates the stats
Ah, careful now. Specifically, REORG does NOT rebuild stats. Only a REBUILD will. You can verify that in "Books Online".
While that's true, the OP explicitly says that if it was a reorg, then they update stats.
Besides, there's not enough evidence (no evidence in fact) that the problem is related to execution plans or stats. Restarts don't update stats and so uptime should not be a factor if it is stale stats
Last time I saw a problem like this (overall slowdown that got worse the longer the server had been up), it was a memory leak in a linked server driver, on SQL 2005 IA64.
The first step should be to ensure there's sufficient monitoring on the server to track query performance and common performance-related characteristics over time (I'd start with wait stats and latch stats, overall CPU, memory use and IO stats) and analyse them until you can determine a possible cause.

Apologies and the heads up. Misread what the op stated as him thinking that REORG rebuilt stats.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24315 More actions · Answer 12

Thank you for the reply. How can we see if we have the same situation on our linked server drivers? Is there any special command to do this?