May 21, 2009 at 8:18 am
I see Buffer Cache page life expectancy is 66 seconds in red alert on my quest spot light.
Could some one tell me what is the cause for it and how can i get rid of this performance issue.
May 21, 2009 at 1:35 pm
:w00t:
May 24, 2009 at 1:20 pm
>> Buffer Cache page life expectancy is 66 seconds
That is very low and there can be multiple reasons for this.
1) Low memory available on the server.
2) Queries returning large sets of data.
3) Missing indexes.
-- Cached SP's By Logical Reads
SELECT TOP (25) qt.text AS 'SP Name', total_logical_reads,
qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY total_logical_reads DESC;
-- Missing Indexes for entire instance by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek,
mid.statement AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;
You may want to bookmark this page for reference.
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1340.entry
http://technet.microsoft.com/en-us/library/cc966540.aspx
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply