May 1, 2014 at 3:29 am
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
May 1, 2014 at 4:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2014 at 5:43 am
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?
May 1, 2014 at 6:16 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2014 at 6:45 am
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
May 1, 2014 at 7:33 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2014 at 12:56 pm
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?
May 1, 2014 at 2:05 pm
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.
May 1, 2014 at 2:17 pm
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.
May 1, 2014 at 5:21 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply