Solved

MS SQL Cache

Posted on 2011-09-09
14
414 Views
Last Modified: 2012-05-12
We purchased an application that has a Microsoft SQL 2005 database.  Our in-house application has an API to interrogate the SQL database on the purchased application.  Each morning the first transaction against the SQL database runs about 10 minutes.  Each subsequent transaction runs between 0 and 4 seconds.  Also if we reboot the server with the SQL database and try to hit it for the first time it also runs for about 10 minutes.  The database is only about 100 MB in size.
My thought was that there may be some caching that occurs when the database is opened and that is why it runs very quickly after the first time.  If this is the case, does it have some kind of lease, that expires overnight and must re-cache each morning?  Is this something that can be set to re-cache only on server reboot.  
This first database hit each morning is a pain!
0
Comment
Question by:sfletcher1959
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
14 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36511733
Like any other database SQL has data cache and object cache where data respectively query plans are kept and they are 100% flushed by a SQL server restart.
For more details about caching please see:

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966425.aspx

Understanding Query Plan Cache in SQL 2005 SP2 - why it's changed from SQL 2005 RTM/SP1?
http://blogs.technet.com/b/vipulshah/archive/2007/09/24/understanding-query-plan-cache-in-sql-2005-sp2-why-it-s-changed-from-sql-2005-rtm-sp1.aspx

0
 
LVL 40

Expert Comment

by:lcohan
ID: 36511737
Sorry I hit submit too soon without the link below:
Caching in ASP.NET with the SqlCacheDependency Class
http://msdn.microsoft.com/en-us/library/ms178604.aspx
0
 

Author Comment

by:sfletcher1959
ID: 36511777
I completely understand the cache flush on SQL Server restart, but why it it happening every day without a server restart?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Accepted Solution

by:
DrewKjell earned 500 total points
ID: 36511874
The cache in SQL I believe only stores a query plan for a certain amount of time or up to a certain number of cached plans.  

Drew
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36511905
There must be a process either in SQL on .NET that is causing the cache to be flushed - either data or plan or both due to for example a sp_recompile on the table(s) from where you get the data. By the size of your DB the cache should not be cleared unless explicitely asked for but..is this the only database on that SQL Server? May sound silly but ask yourself if there's enough memory available for SQL (and how it is configured) if this is not the only DB and app on that box.
BTW - how is your SQL code built/executed from .NET app? Is it a SQL Stored Procedure? If it's not than I suggest you can try put your code in a SP and use that SP instead.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36511923
The cach in SQL is not cleared based on any schedule if sufficient memeory is available however you can force it to clear - plan and data - by issuing a sp_recompile on the table(s) used by the query.
Other than that if data cardinality is changing a lot (not sure what the treshhold is) but if you have a masive import/delete on the table(s) than the data/plans cache need to be rebuild obviously.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512013
here are a few other articles that may be useful to identifiy your caching issue:

Analyzing the SQL Server Plan Cache
http://www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/

Check adhoc sql cache is bigger than procedure cache and clear only adhoc sql cache
http://sqlserverpedia.com/blog/sql-server-bloggers/check-adhoc-sql-cache-is-bigger-than-procedure-cache-and-clear-only-adhoc-sql-cache/

Execution Plan Caching and Reuse
http://msdn.microsoft.com/en-us/library/ms181055(v=SQL.90).aspx
0
 

Author Comment

by:sfletcher1959
ID: 36512376
Any way to tell where the cache is being stored.  I am not a DBA so not sure where I would look.
0
 

Author Comment

by:sfletcher1959
ID: 36512441
Also how to increase the cache size.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512457
What do you mean by "where the cache is being stored" this is not a flder on your server or anything like that - this is SQL Server internal cache.

How to Interact with SQL Server’s Data and Procedure Cache
http://www.sql-server-performance.com/2004/data-cache/
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512469
"......its already been answered on this thread a number of times.  You can not set, change, or otherwise manipulate the plan cache memory size in SQL Server.  If you choose to reject that simple truth and care to waste time looking for another answer, I can't stop you, but there is no magic behind this, and no button you can press to make any kind of change. "



http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/e8b07ee5-a715-4ed4-92a2-84757defae6e
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512505
0
 

Author Comment

by:sfletcher1959
ID: 36512921
No need to get nasty lcohan.  Obviously, I didn't understand.  Which means either I didn't pay attention or you did a lousy job of explaining.  
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36513032
Pardon?
What do you consider nasty in all my postings above?
All I tried was to help and make you understand how SQL caching works including the fact that in one of the links I gave the solution (not the code!) of how to workaround the problem you are having each morning!
[...]
" that expires overnight and must re-cache each morning?  Is this something that can be set to re-cache only on server reboot.  This first database hit each morning is a pain!"
[...]

if you have good eyes and ears - you will find it.
Good luck - without any intention to be "nasty"...
 
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question