June 12, 2008 at 2:40 am
Hi,
I'm running a simulation of an application that reprocesses a large portion of the database on a daily basis. In the simulation I repeatedly increment the date of the machine and then run the app. The problem is that each iteration of the process takes longer than the previous. If I stop the simulation and restart the server the times drop and then start to increase again which indicates that the increase in time is not the result of an increase in the amount of data being processed.
My guess is that I'm using up resources and by restarting the server I recover them. I know that we use dynamic sql for almost everything so from what I've read I should be looking at the hit ratio of the cache buffer which if I'm reading correctly is > 90% which looks ok. On the other hand the number of SQL Query plans grows to what seems to me to be a huge number, on the order of 9000. From my understanding this contradicts the hit ratio information.
Any ideas or direction will be much appreciated
June 12, 2008 at 5:50 am
It sounds like you're using up resources. I'd suggest using perfmon & trace to see what's happening with the server & the queries respectively.
90% cache hit ratio isn't all that high. Most of our servers operate at 98%+ most of the time, but then we use stored procedures.
"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
June 12, 2008 at 12:24 pm
Which properties would you watch and what kind of values would you expect to see?
June 12, 2008 at 12:34 pm
Not knowing your system, your code or any special circumstances that you're laboring under, I'd go with my standard set. I've got them documented here[/url].
"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
June 13, 2008 at 1:15 pm
If you are not on SP2+, get there. There are SIGNIFICANT proc cache issues with earlier versions, including the ridiculous situation where proc cache can take up SEVENTY FIVE PERCENT of your buffer pool!! And with dynamic sql, you may well be just blowing out your proc cache and preventing useful data from being cached.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply