November 9, 2012 at 1:15 am
Comments posted to this topic are about the item Top 14 KPI
November 9, 2012 at 7:23 am
Thanks! I hope this helps with a problem we're currently having.
Script requires BIGINT, not INT, here:
@iBatchStart AS BIGINT,
@iBatchEnd AS BIGINT,
November 9, 2012 at 7:58 am
So far this is really nice and I can see how this can be helpful.
One suggestion possibly changing how the delay works as I found doing something like 60 seconds causes a conversion error.
Msg 241, Level 16, State 1, Procedure spGetKpi_Top14, Line 105
Conversion failed when converting date and/or time from character string.
change to
SET @sDelayDuration = CONVERT(varchar, DATEADD(ms, @iDelay * 1000, 0), 114)
November 9, 2012 at 10:47 am
Thanks Monte. This proc was just what I needed today.
November 9, 2012 at 11:08 am
I made some modifications, like a bigint in some variables and put in on one line to run it in openrecorset.
declare @ts_now bigint, @iUserCon INT, @iLogInStart INT, @iLogInEnd INT, @fSeconds FLOAT, @dtStart DATETIME, @iDelay INT, @sDelayDuration CHAR(8), @iBatchStart BigINT, @iBatchEnd BigINT,@iLogOutStart INT, @iLogOutEnd INT, @iPageSplitStr INT, @iPageSplitEnd INT, @nBuffCachHit Numeric(10,2), @iPageLife INT, @iLockWaitStart INT, @iLockWaitEnd INT, @biDeadLock BIGINT, @iLatchStart BigINT, @iLatchEnd BigINT, @iLatchTmStart BigINT, @iLatchTmEnd BigINT SET @iDelay = 5 SET @iUserCon = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='User Connections') SET @iPageLife = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Buffer Manager%' AND counter_name = 'Page life expectancy') SET @biDeadLock = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Number of Deadlocks/sec' AND instance_name = 'Database') SET @dtStart = GETDATE() SET @sDelayDuration ='00:00:' + CAST(@iDelay AS VARCHAR) SET @nBuffCachHit = (SELECT cast(cntr_value as float) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio') / (SELECT cast(cntr_value AS FLOAT) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base') * 100 SET @iBatchStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Batch Requests/sec') SET @iLogInStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Logins/sec') SET @iLogOutStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Logouts/sec') SET @iPageSplitStr = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='page splits/sec') SET @iLockWaitStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Lock Waits/sec' And Instance_Name = '_Total') SET @iLatchStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Latch Waits/sec') SET @iLatchTmStart = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Total Latch Wait Time (ms)') WAITFOR DELAY @sDelayDuration SET @fSeconds = DATEDIFF(ss, @dtStart, GETDATE()) SET @iBatchEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Batch Requests/sec') SET @iLogInEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Logins/sec') SET @iLogOutEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Logouts/sec') SET @iPageSplitEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='page splits/sec') SET @iLockWaitEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Lock Waits/sec' And Instance_Name = '_Total') SET @iLatchEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Latch Waits/sec') SET @iLatchTmEnd = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Total Latch Wait Time (ms)') select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info select top 1 record_id, convert(varchar(20), dateadd(ms, -1 * (@ts_now - timestamp), GetDate()), 120) as EventTime, SQLProcessUtilization, SystemIdle, 100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization, (select COUNT(*) from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L') as Locks, @@SERVERNAME ServerName, @iUserCon as TotalConnections, getdate() Fecha,65 ServerID , CAST((@iLogInEnd - @iLogInStart)/ @fSeconds AS Numeric(10,2)) LoginsPerSec, CAST((@iLogOutEnd - @iLogOutStart)/ @fSeconds AS Numeric(10,2)) LogoutsPerSec, CAST((@ibatchEnd - @iBatchStart)/ @fSeconds AS Numeric(10,2)) BatchRequestsPerSec, CASE WHEN (@ibatchEnd - @iBatchStart) = 0 THEN CAST(0.00 AS Numeric(10,2)) ELSE CAST((CAST((@iPageSplitEnd - @iPageSplitStr) AS FLOAT)/ CAST((@ibatchEnd - @iBatchStart) AS FLOAT) * 100) AS Numeric(10,2)) END PorPageSplitsPerBatch, CAST(@nBuffCachHit AS VARCHAR) BufferCacheHitRatioPor, @iPageLife PageLifeExpectancy, CAST((@iLockWaitEnd - @iLockWaitStart)/ @fSeconds AS Numeric(10,2)) LockWaitsPerSec, CAST(@biDeadLock AS NUMERIC(10,2)) NumberOfDeadlocksSec, CAST((@iLatchEnd - @iLatchStart)/ @fSeconds AS Numeric(10,2)) LatchWaitsPerSec, CAST((@iLatchTmEnd - @iLatchTmStart)/ @fSeconds AS Numeric(10,2)) TotalLatchWaitTime from (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 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>%') as x ) as y order by record_id desc
Thanks a lot to all
November 9, 2012 at 11:10 am
Thanks for catching that! :w00t:
September 22, 2015 at 7:17 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply