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

query plan cache gets cleared every hour Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 3:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:42 AM
Points: 56, Visits: 160
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
Post #1566659
Posted Thursday, May 1, 2014 4:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566666
Posted Thursday, May 1, 2014 5:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:42 AM
Points: 56, Visits: 160
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?
Post #1566678
Posted Thursday, May 1, 2014 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566684
Posted Thursday, May 1, 2014 6:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:42 AM
Points: 56, Visits: 160
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
Post #1566691
Posted Thursday, May 1, 2014 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566707
Posted Thursday, May 1, 2014 12:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:03 AM
Points: 457, Visits: 1,463
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?
Post #1566832
Posted Thursday, May 1, 2014 2:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
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.
Post #1566848
Posted Thursday, May 1, 2014 2:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:42 AM
Points: 56, Visits: 160
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.
Post #1566851
Posted Thursday, May 1, 2014 5:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse