Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query plan cache gets cleared every hour


query plan cache gets cleared every hour

Author
Message
OldCursor
OldCursor
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 162
I'm working for a client who creates a reporting database from a production system by shipping and restoring log files. For some reason (I'm a developer not a dba!) the restore process clears the query plan cache. So complex report queries run very slowly all the time - a test proved that if a plan is available the query takes a few seconds, if not it could take 30 minutes. And the database is growing rapidly.

Is there a way to save query plans and then restore them for reuse?

Is there a way to avoid clearing the cache every hour?

Can Grant Fitchey drop by and sort me out please?

Help
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
I'm not sure Grant Fritchey knows the answer to this. Is it a home-grown shipping process? I don't think the normal one flushes the cache.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
OldCursor
OldCursor
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 162
Grant - I'm not aware of the detailed process but I'm told it involves switching the receiving database into simple recovery mode. Does that clear the cache and is there a (good) reason for doing it?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
Scroll down in the documentation here (down to Remarks). It shows all the causes for the clearing of the cache and procedure cache. I'll bet they're doing one (or more) of these.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
OldCursor
OldCursor
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 162
Grant
First - I think it is awesome that the best dba in the whole world should respond so rapidly to my call! I am almost overwhelmed.

Second - I think they set the offline / online options and I will question whether that is really necessary.

Thanks for your help
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
Erin Stellato is here? Where is she?

Ha! Thanks. It's not true, but it's nice to hear.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQL Guy 1
SQL Guy  1
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 2443
1. Did you check SQL Server log file? Every time Cachestore is flushed it records it to SQL Server Log along the reason why it was flushed.

2. From your post it looks that SQL Server spends 30 min to create a plan that executes for 1 sec. Did you check the plan itself? Does it make a sence? How about statistics? Is it up to date? Are you using Linked Server by any chance?
Matt Crowley
Matt Crowley
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 469
I have never seen SQL Server take 30 minutes to compile a plan. My guess is that you do not have an issue with the plan cache being wiped out, but the index statistics being declared invalid. Just after a restore of a log, run an update stats command on a few tables in a big report, and see if performance improves.

Alternatively, it could be that the restore wipes out the data cache. Have a look at some of the disk counters to see how badly you are hitting the disks while that report runs.
OldCursor
OldCursor
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 162
Thank you all for your comments. I think Grant has put me on the right track though because it's not just one report query that's slow - it's everything. And if I run a stored procedure after the database has been updated it grinds through - then when I run it again it zips off in no time.

I believe the database is being set offline before the logs are applied and then online again afterwards. I might be wrong. The database is also set to read-only. And both those actions will clear the plan cache according to Microsoft.

I don't think either action is necessary. And I have a friendly dba to talk to about it!

Again - thanks for your comments.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
But you can check the compile time of the plan by looking at the SELECT operator properties (or the DELETE operator, etc.). 30 minute compiles are abnormal as hell, but not unheard of.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search