August 22, 2011 at 5:22 am
The challenges of being an involuntary 'DBA'...
During a routine check I discovered that the procedure cache only contained procedures that were executed less than 10 minutes. (by querying the sys.dm_exec_procedure_stats - code see below).
Windows Server 2007 SP2
SQL Server 2008 R2 SP2
CPU quite low, the system is not heavily used. No linked servers.
Started up PERFMON to collect some data:
Total Server Memory : 2.56 Gb
SQL Cache Memory : 1.672 Gb
Optimizer Memory : 1.5 Gb
Page life Expectancy : 107k
Buffer cache hit ratio : above 99%
Cache Pages : 43k
I started up a trace to catch the SP:Cache* events. And I executed DBCC ProcCache.
num proc buffs /num proc buffs used / num proc buffs active / proc cache size
44592 / 1013 / 1013 / 687
After a few minutes the trace showed suddenly lots of CacheInserts. I executed DBCC ProcCache again.
num proc buffsnum proc buffs usednum proc buffs activeproc cache size
29656 / 13 / 13 / 213
And
SELECT
p.name AS [SP Name],
qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads],
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_elapsed_time/qs.execution_count DESC;
showed again procedures with a very recent cached_time.
So I think that something must have triggered a cache flush somehow. But I haven't got the faintest idea what that could have been or if that is the right conclusion.
Can someone please give me a hint to point me in the right direction?
August 22, 2011 at 5:46 am
Check the SQL error log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 5:57 am
Nothing in the SQL Server Error Log, or other logs.
One 'cache flush' coincided with a Transaction Log backup (done every 15 minutes). But the two subsequent 'flushes' did not.
August 22, 2011 at 9:23 am
Do you have lots of Ad-hoc queries running? It could put pressure on the cache since Ad-Hoc queries plan are not reused much unless you optimize for Ad hoc queries.
-Roy
August 22, 2011 at 11:50 pm
There is another program from another company on another database which runs just after midnight to create to some files. It uses dynamic SQL - and as far as I can judge quite badly.
But that does not explain the 'cache flushes' during the day. (?)
August 22, 2011 at 11:52 pm
Besides that (almost) no ad-hoc queries.
August 23, 2011 at 3:32 am
My last remark about Ad-hoc code was apparently plain wrong.
The Windows Server is a SBS (Small Business Server) which apparently has some kind of Operations Manager running, continually checking various issues.
A trace shows prior to the 'cache flush' more than 100 SP:CacheInsert events caused by Window Script Host executing code like:
SET NOCOUNT ON
SELECT size / 128.0 as fileSize,
file_id as fileId,
FILEPROPERTY(name, 'SpaceUsed') / 128.0 as fileUsed,
CASE WHEN max_size = -1 OR max_size = 268435456 THEN -1 ELSE max_size / 128 END as fileMaxSize,
CASE WHEN growth = 0 THEN 0 ELSE 1 END as IsAutoGrow,
is_percent_growth as isPercentGrowth,
growth as fileGrowth,
SUBSTRING(physical_name,1,1) as drive
FROM sys.database_files where type IN (0,1) and is_read_only = 0
Must be a step in the right direction. Looking further...
August 23, 2011 at 6:56 am
If you cannot change those ad hocs into stored procs, the other option is to set "optimize for ad hoc workloads". It is always better to make it as SPs.
-Roy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply