Feature Requests for V2.0

Coordinator
Nov 23, 2009 at 10:19 AM

Verison 2.0 is only in the "idea" stage at the moment, but I amware that what started off a quite a small project has exploded into what you see today.  As such there are elements of the application that would have been coded differently had they been planned in from the start, and there are a few other things I would have added in, but to do them now to version 1 would mean to much re-work.

Since Version 2.0 will be a fresh start, but still incorporating all the functionallity we have today, now is the time to chip in with those ideas.

Dec 5, 2009 at 2:20 PM

Nice app.

It would be nice to put some status bars on the forms that are popped up.  for example, on User Connections.  The status bar could display the number of connections

 

 

Dec 7, 2009 at 7:36 PM

if you run sp_who2 you will see a column called Program Name.

If people build connections strings like this:

User ID=xxxxx;Password=xxxxxxxxxxxx;Initial Catalog=CLP_Admin;Data Source=10.74.x.xx;Application Name=LoadSLAB

The value in the ApplicationName parameter is displayed in the Program Name column. 

This would be a good column to include in your current User Connections popup

 

 

 

 

 

 

 

Coordinator
Dec 7, 2009 at 10:34 PM
jimpar wrote:

if you run sp_who2 you will see a column called Program Name.

If people build connections strings like this:

User ID=xxxxx;Password=xxxxxxxxxxxx;Initial Catalog=CLP_Admin;Data Source=10.74.x.xx;Application Name=LoadSLAB

The value in the ApplicationName parameter is displayed in the Program Name column. 

This would be a good column to include in your current User Connections popup

 

 That's actually information I'm gathering in another view (processes) although not via SP_who2.  I see you point though, and yes it would be worthwhile adding it into the UserConnections form.  I'll probably add that into version 1.29

 

 

 

 

 

 

Dec 8, 2009 at 1:33 PM

great.  i was just using sp_who2 to point out how to get at it.

I like this application.  i will probably use it as a basis for a dashboard.  Collect the data in one application and allow multiple people to view it through a different application.  This is great as is if one person is monitoring. 

 

Sep 24, 2010 at 1:29 PM

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
-- 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 

 

Coordinator
Sep 27, 2010 at 8:36 PM

Thanks Theo.

Some interesting stuff there for sure.  There are a few problems with them though.  Some of the column referenced are no longer present in 2008 from 2005, so unless they can be converted to something forward compatable, then I wouldn't be able to use the query as it will be too limiting. 

I'll need to do some performance tuning, and fix the syntax errors in the queries before I can see where they would fit in.  Some great stuff there, but not sure they will all work given that this is a low overhead monitoring tool, and some of these have quite an overhead.  Running them on a resource constrained system could be enough to make it unresponsive.

I'll have a good play with them though to see which ones I can make work.

Jan 20, 2012 at 1:39 PM
glensmall wrote:

Verison 2.0 is only in the "idea" stage at the moment, but I amware that what started off a quite a small project has exploded into what you see today.  As such there are elements of the application that would have been coded differently had they been planned in from the start, and there are a few other things I would have added in, but to do them now to version 1 would mean to much re-work.

Since Version 2.0 will be a fresh start, but still incorporating all the functionallity we have today, now is the time to chip in with those ideas.

I have always wished for a performance monitor tool that wasn't designed "backwards". The reason I monitor is to find out if I need to change something.  The perfect UI for this would one organized around the "changes" that could be made. The main menu list would be something like this 

   1) Do I need to increase Disk space

   2) Do I need to add memory

   3) Do I need to increase network band with

   4) Do I need to change the run time of a job

   5) Do I need to ask the developers to change a querry

   6) Do I need to ...

When a selection is made. the information needed to answer the question would be collected and displayed. Perhaps even with a small note on how each metric influences the answer ... Robin HC

Jul 26, 2012 at 8:58 PM

maybe the ability to store the data in a SQL Database rather than a csv file for Historical purposes?

Coordinator
Jul 27, 2012 at 10:13 AM

:-)  I put this in V2 already as I thought it would be a good option.  Nice to know someone else what's it.

Apr 29, 2016 at 8:41 AM
change the title of the app in the taskbar to have the instance connected instead of SQLMonitor, e.g. "srv2301\sql2012 - SqlMonitor" so when we have multiple copies running you can flip between them easier - thanks for the great app!
Coordinator
Apr 29, 2016 at 9:09 AM
Great idea, shouldn't too much to add that into the current version.

Let me see if I can do that today and will publish a version up.
Apr 29, 2016 at 9:12 AM
In your next version you could have the option of opening multiple instances to connect to different servers or have a tabbed ui

if there was some full screen option that could cycle through the tabs of connected servers it would make a great sql monitor dashboard to stick up on a large screen
Coordinator
Apr 29, 2016 at 12:22 PM
The Tabbed UI is exactly what's planned for the next major release. Going to be doing some major re-vamps of the UI plus a re-write of the internals so will definitely consider all suggestions.

Just Uploaded 1.34 with the title change as requested. Nothing too fancy but should achieve what you wanted until I get a proper V2 release out.

Hope it helps.
Apr 29, 2016 at 2:18 PM
Thats great thanks

alternative to a tabbed ui would be a grid option basically like a live spreadsheet view with multiple servers in colums
Sep 16, 2016 at 3:33 PM
Anglrzac: I was looking exactly for that: some kind of dashboard where I can see my 5-10 servers atop columns, and some basic health/performance items as rows under each one (page life expectancy, CPU%, locks longuer than 5 seconds, etc.)

I just downloaded this project, and was about to look into modifying it to do exactly this.

Get this to also archive the activity for later perusing, and you have a champion app on your hands here.