|
|
Hi Glen,
A few idea's i have been kicking around for a while:
a better formatted query cache view:
SELECT top 25 db_name(dbid) as DbName, coalesce(object_name(objectid,dbid), 'Dynamic SQL') as ObjectName,
t.[text] AS [Adhoc Batch or Object Call], SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
, qs.[execution_count] AS [Counts] , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time]
, qs.[total_physical_reads] AS [Total Physical Reads] ,(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
, qs.[total_logical_writes] AS [Total Logical Writes] ,(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
, qs.[total_logical_reads] AS [Total Logical Reads] ,(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
, qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] / qs.[execution_count]) AS [Avg CLR Time]
, qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time] / qs.[execution_count]) AS [Avg Elapsed Time]
, qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tWHERE qs.[execution_count] > 100
ORDER BY [Total Worker Time] DESC
the buffer cache brakdown per db i sent you per email
select @@servername as servername ,db_name(database_id) as 'DbName'
,sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) as 'MBClean'
,sum(case dirty when 1 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) as 'MBDirty'from
( select database_id --,FILE_NAME ( file_id ) as 'FileName'
,[is_modified] as 'Dirty' ,count(page_id) as 'pages'
--,page_level --,allocation_unit_id
--,page_type
--,row_count --,free_space_in_bytes
from sys.dm_os_buffer_descriptors group by database_id
,[is_modified] ) as tgroup by database_idorder by sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) desc
stalled IO per database
select DB_NAME(database_id) as 'DbName' ,[file_id]
,io_stall_read_ms ,num_of_reads ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms ,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'from
sys.dm_io_virtual_file_stats(null,null)order by avg_io_stall_ms desc
A little CPU history derived from the ring buffers for a drilldown @ CPU
select dateadd(ms, -1 * (cpu_ticks /( cpu_ticks / ms_ticks ) - [timestamp]), GetDate()) as DT
,cast((1.0 * ms_ticks / (24 * 60 * 60 * 1000)) as decimal(10,2)) as system_uptime_days
,cpu_count ,cpu_ticks_in_ms /1000 as cpu_MHz
,hyperthread_ratio ,SQLProcessUtilization
,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
,SystemIdlefrom ( select record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
timestamp from (select --top 1
timestamp ,convert(xml, record) as record from
sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%' --order by timestamp desc
) as x ) as y , sys.dm_os_sys_infoorder by timestamp desc
a usage histogram for the query cache
;--Vertical with percentageWITH Agg_IO_Stats AS( SELECT
2 as 'Row', '1 time' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 1
union all SELECT 3 as 'Row', '2 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 2
union all SELECT 4 as 'Row', '3 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 3
union all SELECT 5 as 'Row', '4 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 4
union all SELECT 6 as 'Row', '6 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 6
union all SELECT 7 as 'Row', '7 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 7
union all SELECT 8 as 'Row', '8 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 8
union all SELECT 9 as 'Row', '9 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count = 9
union all SELECT 10 as 'Row', '10 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where
qs.execution_count = 10 union all SELECT 11 as 'Row', '11 and 100 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count)
as 'TotalExecutionCount' FROM sys.dm_exec_query_stats qs where
qs.execution_count between 11 and 100 union all
SELECT 12 as 'Row', '101 and 200 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count between 101 and 200
union all SELECT 13 as 'Row', '201 and 300 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as
'TotalExecutionCount' FROM sys.dm_exec_query_stats qs where
qs.execution_count between 201 and 300 union all
SELECT 14 as 'Row', '301 and 400 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count between 301 and 400
union all SELECT 15 as 'Row', '401 and 500 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as
'TotalExecutionCount' FROM sys.dm_exec_query_stats qs where
qs.execution_count between 401 and 500 union all
SELECT 16 as 'Row', '501 and 600 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count between 501 and 600
union all SELECT 17 as 'Row', '601 and 700 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as
'TotalExecutionCount' FROM sys.dm_exec_query_stats qs where
qs.execution_count between 601 and 700 union all
SELECT 18 as 'Row', '701 and 800 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count between 701 and 800
union all SELECT 19 as 'Row', '801 and 900 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as
'TotalExecutionCount' FROM sys.dm_exec_query_stats qs where
qs.execution_count between 801 and 900 union all
SELECT 20 as 'Row', '901 and 1000 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count between 901 and 1000 union all
SELECT 21 as 'Row', '>1000 times' as 'Re-UsageCount', count(qs.execution_count) as 'Aantal', sum(qs.execution_count) / count(qs.execution_count) as 'AvgExecutionCount', sum(qs.execution_count) as 'TotalExecutionCount'
FROM sys.dm_exec_query_stats qs where qs.execution_count > 1000
)SELECT --ROW_NUMBER() OVER(ORDER BY Row) AS row_num,
[Re-UsageCount], Aantal, AvgExecutionCount,
TotalExecutionCount, CAST((TotalExecutionCount * 1.0) / SUM((TotalExecutionCount *1.0)) OVER() * 100 AS DECIMAL(5, 2)) AS pctFROM
Agg_IO_StatsORDER BY row;
IO in bytes per driveletter pivot
IF OBJECT_ID('tempdb..#tmpThEk') IS NOT NULL DROP TABLE #tmpThEk;
create table #tmpThEk (Dname sysname null, DrvName sysname null, RW Bigint null);
With g as ( select db_name(mf.database_id) as database_name,
left(mf.physical_name, 1) as drive_letter, coalesce(vfs.num_of_bytes_read + vfs.num_of_bytes_written, 0) as BYTES
from sys.master_files mf inner join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
where mf.database_id > 4 -- With or withoiut the system DB's
)insert into #tmpThEk (Dname,DrvName,RW)select database_name,
drive_letter, BYTES-- Percentage = BYTES*100.0/(SELECT SUM(BYTES) FROM g) -- If you so desite you can use overall percentagefrom
g
-- Find all permutations of DrvName DECLARE @DrvNames AS TABLE(DrvName sysname NOT NULL PRIMARY KEY)INSERT INTO @DrvNames SELECT DISTINCT DrvName FROM #tmpThEk
-- Build a dynamic IN clause based on @DrvNames DECLARE @SelCols AS nvarchar(MAX),
@InCols AS nvarchar(MAX), @DrvName AS nvarchar(10)SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames)SET @SelCols = N''SET @InCols = N''WHILE @DrvName IS NOT NULL
BEGIN SET @SelCols = @SelCols + N',coalesce(['+@DrvName+N'], 0) as '+@DrvName+N' '
SET @InCols = @InCols + N',['+@DrvName+N']' SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames WHERE DrvName > @DrvName)
ENDSET @SelCols = SUBSTRING(@SelCols, 2, LEN(@SelCols))SET @InCols = SUBSTRING(@InCols, 2, LEN(@InCols))
-- Dynamic build of the pivot statement.DECLARE @sql AS nvarchar(MAX)SET @sql = N'
SELECT Dname, ' + @SelCols + N' FROM (SELECT Dname, DrvName, RW FROM #tmpThEk) AS D
PIVOT(sum(RW) FOR DrvName IN(' + @InCols + N')) AS P'
-- Show me the good stuff :) EXEC sp_executesql @sql
a general overview
declare @dt as datetimedeclare @Clean as numeric (17,2)declare @Dirty as numeric (17,2)set @dt = getdate()
-- Get Buffer usage, in Clean and Dirty MB'sselect @Clean = sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) --as 'MBClean'
,@Dirty = sum(case dirty when 1 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) --as 'MBDirty'from
( select [is_modified] as 'Dirty'
,count(page_id) as 'pages' from sys.dm_os_buffer_descriptors
group by [is_modified] ) as t
SELECT @dt as DT ,'Memory Manager' as 'Group'
,'Total Server Memory (MB)' as 'Variable' ,pc.cntr_value/1024.0 as 'RawValue'
,CONVERT(varchar(10), pc.cntr_value/1024) as 'Value' ,'As is' AS 'Indicates'
,'Total Server Memory (Mostly Cache store + Buffer pool)' as 'Explanation'FROM
sys.dm_os_performance_counters pcWHERE [object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Memory Manager'AND
pc.[counter_name] = 'Total Server Memory (KB)'
union all
SELECT @dt as DT ,'Buffer Manager' as 'Group'
,'Page Life Expectancy' As 'Variable' ,pc.cntr_value as 'RawValue'
, CONVERT(varchar(10), pc.cntr_value/86400) + ' Days '
+ CONVERT(varchar(10), (pc.cntr_value % 86400) /3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (pc.cntr_value % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), pc.cntr_value % 60), 2) as 'Value'
,CASE WHEN pc.[cntr_value] IS NULL THEN 'UNKNOWN memory pressure'
WHEN pc.[cntr_value] > 2000 THEN 'Light memory pressure'
WHEN pc.[cntr_value] > 1000 THEN 'Moderate memory pressure'
WHEN pc.[cntr_value] > 600 THEN 'Heavy memory pressure' WHEN pc.[cntr_value] > 300 THEN 'Severe memory pressure'
ELSE 'Extreme memory pressure' END AS 'Indicates' ,'Memory allocated by the Buffer pool' as 'Explanation'FROM sys.dm_os_performance_counters pcWHERE [object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Buffer
Manager'AND pc.[counter_name] = 'Page life expectancy'
union all
SELECT @dt as DT ,'Buffer Manager' as 'Group'
,'BufferCacheHitRatioPct' ,(V * 1.0 / B) as 'RawValue'
,cast(CAST((V * 1.0 / B) * 100.0 AS DECIMAL(10,2)) as varchar(13))
,CASE WHEN (V * 1.0 / B) * 100.0 IS NULL THEN 'UNKNOWN'
WHEN (V * 1.0 / B) * 100.0 > 99.5 THEN 'Good'
WHEN (V * 1.0 / B) * 100.0 > 99 THEN 'Normal'
WHEN (V * 1.0 / B) * 100.0 > 95 THEN 'Moderate'
WHEN (V * 1.0 / B) * 100.0 > 90 THEN 'Bad' ELSE 'Extremely low' END AS 'Indicates'
,'Cache hit percentage for the buffer bool' as 'Explanation'from
( select sum(case counter_name when 'Buffer cache hit ratio' then cntr_value else 0 end) as V
,sum(case counter_name when 'Buffer cache hit ratio base' then cntr_value else 0 end) as B
from sys.dm_os_performance_counters where
[object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Buffer Manager'
AND counter_name like 'Buffer cache hit ratio%'
group by [object_name] ) a union all
SELECT @dt as DT ,'Plan Cache' as 'Group'
,'PlanCacheHitRatioPctTotal' ,(V * 1.0 / B) as 'RawValue'
,cast(CAST((V * 1.0 / B) * 100.0 AS DECIMAL(10,2)) as varchar(13))
,CASE WHEN (V * 1.0 / B) * 100.0 IS NULL THEN 'UNKNOWN'
WHEN (V * 1.0 / B) * 100.0 > 99.5 THEN 'Good'
WHEN (V * 1.0 / B) * 100.0 > 99 THEN 'Normal'
WHEN (V * 1.0 / B) * 100.0 > 95 THEN 'Moderate'
WHEN (V * 1.0 / B) * 100.0 > 90 THEN 'Bad' ELSE 'Extremely low' END AS 'Indicates'
,'Hit percentage on all query plans' as 'Explanation'from
( select sum(case counter_name when 'Cache Hit Ratio' then cntr_value else 0 end) as V
,sum(case counter_name when 'Cache Hit Ratio base' then cntr_value else 0 end) as B
from sys.dm_os_performance_counters where
[object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Plan Cache'
AND counter_name like 'Cache Hit Ratio%' and
instance_name = '_Total' group by [object_name], instance_name
) a union all
SELECT @dt as DT ,'Plan Cache' as 'Group'
,'PlanCacheHitRatioPctAdHoc' ,(V * 1.0 / B) as 'RawValue'
,cast(CAST((V * 1.0 / B) * 100.0 AS DECIMAL(10,2)) as varchar(13))
,CASE WHEN (V * 1.0 / B) * 100.0 IS NULL THEN 'UNKNOWN'
WHEN (V * 1.0 / B) * 100.0 > 99.5 THEN 'Good'
WHEN (V * 1.0 / B) * 100.0 > 99 THEN 'Normal'
WHEN (V * 1.0 / B) * 100.0 > 95 THEN 'Moderate'
WHEN (V * 1.0 / B) * 100.0 > 90 THEN 'Bad' ELSE 'Extremely low' END AS 'Indicates'
,'Hit percentage on Dynamic query plans' as 'Explanation'from
( select sum(case counter_name when 'Cache Hit Ratio' then cntr_value else 0 end) as V
,sum(case counter_name when 'Cache Hit Ratio base' then cntr_value else 0 end) as B
from sys.dm_os_performance_counters where
[object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Plan Cache'
AND counter_name like 'Cache Hit Ratio%' and
instance_name = 'SQL Plans' group by [object_name], instance_name
) a
union all
SELECT @dt as DT ,'Plan Cache' as 'Group'
,'PlanCacheHitRatioPctStoredProcedures' ,(V * 1.0 / B) as 'RawValue'
,cast(CAST((V * 1.0 / B) * 100.0 AS DECIMAL(10,2)) as varchar(13))
,CASE WHEN (V * 1.0 / B) * 100.0 IS NULL THEN 'UNKNOWN'
WHEN (V * 1.0 / B) * 100.0 > 99.5 THEN 'Good'
WHEN (V * 1.0 / B) * 100.0 > 99 THEN 'Normal'
WHEN (V * 1.0 / B) * 100.0 > 95 THEN 'Moderate'
WHEN (V * 1.0 / B) * 100.0 > 90 THEN 'Bad' ELSE 'Extremely low' END AS 'Indicates'
,'Hit percentage on Stored procedure query plans' as 'Explanation'from
( select sum(case counter_name when 'Cache Hit Ratio' then cntr_value else 0 end) as V
,sum(case counter_name when 'Cache Hit Ratio base' then cntr_value else 0 end) as B
from sys.dm_os_performance_counters where
[object_name] = substring([object_name] , 1, CHARINDEX( ':', [object_name] )) + 'Plan Cache'
AND counter_name like 'Cache Hit Ratio%' and
instance_name = 'Object Plans' group by [object_name], instance_name
) a
union all
select @DT ,'Cache Store' as 'Group' ,'Total Cache Store(MB)'
,sum((single_pages_kb + multi_pages_kb) /1024) as 'RawValue'
,CONVERT(varchar(10), sum((single_pages_kb + multi_pages_kb) /1024)) as 'Value'
,'As is' ,'The sum of all entres in the cache store'from
sys.dm_os_memory_cache_counters
union all
select @DT ,'Cache Store' as 'Group' ,'SQL Plans (MB)'
,(single_pages_kb + multi_pages_kb) /1024 as 'RawValue' ,CONVERT(varchar(10), (single_pages_kb + multi_pages_kb) /1024) as 'Value'
,'As is' ,'Query plans produced from an ad hoc Transact-SQL query'from
sys.dm_os_memory_cache_counterswhere name = 'SQL Plans'and
type = 'CACHESTORE_SQLCP'
union all
select @DT ,'Cache Store' as 'Group' ,'SQL Plans (Count)'
,entries_count as 'RawValue' ,CONVERT(varchar(10), entries_count) as 'Value'
,'As is' ,'Count of Query plans produced from an ad hoc Transact-SQL query'from
sys.dm_os_memory_cache_counterswhere name = 'SQL Plans'and
type = 'CACHESTORE_SQLCP'
union all
select @DT ,'Cache Store' as 'Group' ,'Object Plans (MB)'
,(single_pages_kb + multi_pages_kb) /1024 as 'RawValue' ,CONVERT(varchar(10), (single_pages_kb + multi_pages_kb) /1024) as 'Value'
,'As is' ,'Query plans generated by creating a stored procedure, function, or trigger.'from
sys.dm_os_memory_cache_counterswhere name = 'Object Plans'and
type = 'CACHESTORE_OBJCP'
union all
select @DT ,'Cache Store' as 'Group' ,'Object Plans (Count)'
,entries_count as 'RawValue' ,CONVERT(varchar(10), entries_count) as 'Value'
,'As is' ,'Count of Query plans generated by creating a stored procedure, function, or trigger.'from
sys.dm_os_memory_cache_counterswhere name = 'Object Plans'and
type = 'CACHESTORE_OBJCP'
union all
select @DT ,'Cache Store' as 'Group' ,'Security cache (MB)'
,(single_pages_kb + multi_pages_kb) /1024 as 'RawValue' ,CONVERT(varchar(10), (single_pages_kb + multi_pages_kb) /1024) as 'Value'
,'As is' ,'If Cache Store is large and system is sluggish, run DBCC FREESYSTEMCACHE(TokenAndPermUserStore) and check http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx'from
sys.dm_os_memory_cache_counterswhere name = 'TokenAndPermUserStore'and
type = 'USERSTORE_TOKENPERM'
union all
select @DT ,'Cache Store' as 'Group' ,'Security cache (Count)'
,entries_count as 'RawValue' ,CONVERT(varchar(10), entries_count) as 'Value'
,'As is' ,'If Cache Store is large and system is sluggish, run DBCC FREESYSTEMCACHE(TokenAndPermUserStore) and check http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx'from
sys.dm_os_memory_cache_counterswhere name = 'TokenAndPermUserStore'and
type = 'USERSTORE_TOKENPERM'
union all
SELECT @dt as DT ,'Buffer pool' as 'Group'
,'Total Buffer pool (MB)' ,@Clean + @Dirty as 'RawValue'
,CONVERT(varchar(10), @Clean + @Dirty) ,'As is' as 'Explanation'
,'Total Memory used as buffer' AS 'Indicates'
union all
SELECT @dt as DT ,'Buffer pool' as 'Group'
,'Clean Buffer pool (MB)' ,@Clean as 'RawValue' ,CONVERT(varchar(10), @Clean)
,'As is' as 'Indicates' ,'Memory in buffer pool that is clean' AS 'Explanation'
union all
SELECT @dt as DT ,'Buffer pool' as 'Group'
,'Dirty Buffer pool (MB)' ,@Dirty as 'RawValue' ,CONVERT(varchar(10), @Dirty)
,'As is' as 'Indicates' ,'Memory in buffer pool that is dirty (High dirty to clean ratios point to too little disk I/O capacity' AS 'Explanation'
union all
SELECT @dt as DT ,'Buffer pool' as 'Group'
,'Dirty Buffer pool (pct)' ,@Dirty / (@Clean + @Dirty) * 100.0 as 'RawValue'
,CONVERT(varchar(10), cast((@Dirty / (@Clean + @Dirty) * 100.0) as numeric(10,2)))
,'As is' as 'Indicates' ,'Memory percentage in buffer pool that is dirty (High dirty to clean ratios point to too little disk I/O capacity' AS 'Explanation'
Get the server boot date time
select create_date as SqlStartDateTimefrom sys.databaseswhere name = 'tempdb'
Maybe some of them might have any use for you in SQL monitor :)
Theo Ekelmans
NL
|
|