Performance counters

-- Overall server health checkup.



declare @plan_cache_size float, @obj_data_size float , @Total_Server_Memory_size float ,
@Target_Server_Memory_size float ,@single_pages_kb float,@Multi_pages_kb float,
@PhysicalMemMB  float ,@AvailableMemMB  float ,@VirtualMemoryMB  float ,@AvailableVirtualMemoryMB  float,
@GrantedWorkspaceMemoryMB float

declare @tbl Table (opt nvarchar(35), minimum int, maximum int, config_value int , MaxSrvMB_running int)
DECLARE  @MaxSrvMB_running int
-- plan cache size
select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters  
WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages'
and instance_name = '_Total'
select @obj_data_size = (cntr_value*8)/1024.0
FROM sys.dm_os_performance_counters  
WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'
select @Total_Server_Memory_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters  
WHERE object_name like '%Memory Manager%' and counter_name = 'Total Server Memory (KB)'
select @Target_Server_Memory_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters  
WHERE object_name like '%Memory Manager%' and counter_name = 'Target Server Memory (KB)'
Select @GrantedWorkspaceMemoryMB = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters
WHERE object_name like '%Memory Manager%' and counter_name = 'Granted Workspace Memory (KB)'

 

Select @PhysicalMemMB  = total_physical_memory_kb/1024  from sys.dm_os_sys_memory

Select @AvailableMemMB  = available_physical_memory_kb/1024  from sys.dm_os_sys_memory
Select @VirtualMemoryMB  = total_page_file_kb/1024  from sys.dm_os_sys_memory
Select @AvailableVirtualMemoryMB  = available_page_file_kb/1024   from sys.dm_os_sys_memory


insert into @tbl exec sp_configure 'max server memory (MB)'

select @MaxSrvMB_running = MaxSrvMB_running from @tbl      
         
select  @@servername as ServerName,

@PhysicalMemMB as PhysicalMemMB,@AvailableMemMB as AvlPhysMemMB,@VirtualMemoryMB as VirMemoryMB ,@AvailableVirtualMemoryMB as AvlVirMemMB
,@MaxSrvMB_running Config_MaxSrvMB
,@Total_Server_Memory_size [TotalSrvMemMB],@Target_Server_Memory_size [TargetSrvMemMB]
,@obj_data_size [DataPagesMB]  , @plan_cache_size [CachePagesMB],
@GrantedWorkspaceMemoryMB [GrantedWorkspaceMemoryMB]

set nocount on;

DECLARE @init numeric(20,2) , @final numeric(20,2) ;
DECLARE @pgrd1 numeric(20,2) , @pgrd2 numeric(20,2) ;
DECLARE @pgwr1 numeric(20,2) , @pgwr2 numeric(20,2) ;
DECLARE @comp1 numeric(20,2) , @comp2 numeric(20,2) ;
DECLARE @recomp1 numeric(20,2) , @recomp2 numeric(20,2) ;
DECLARE @batc1 numeric(20,2) , @batc2 numeric(20,2) ;
DECLARE @frls1 numeric(20,2) , @frls2 numeric(20,2) ;
DECLARE @flsc1 numeric(20,2) , @flsc2 numeric(20,2) ;
DECLARE @lzwr1 numeric(20,2) , @lzwr2 numeric(20,2) ;
DECLARE @ckpt1 numeric(20,2) , @ckpt2 numeric(20,2) ;
DECLARE @frec1 numeric(20,2) , @frec2 numeric(20,2) ;
DECLARE @send1 numeric(20,2) , @send2 numeric(20,2) ;
DECLARE @atr1 numeric(20,2) , @atr2 numeric(20,2) ;
DECLARE @login1 numeric(20,2) , @login2 numeric(20,2) ;
DECLARE @logout1 numeric(20,2) , @logout2 numeric(20,2) ;
DECLARE @lockreq1 numeric(20,2) , @lockreq2 numeric(20,2) ;
DECLARE @ttcr1 numeric(20,2) , @ttcr2 numeric(20,2) ;
DECLARE @start_time datetime , @final_time datetime , @count numeric(20,2) ;
DECLARE @upminutes bigint ;
declare @mbrd1 numeric (20,2), @mbwt1 numeric(20,2);
declare @mbrd2 numeric (20,2), @mbwt2 numeric(20,2);
declare @MemGrantPending1 numeric(20,2) ,@MemGrantPending2 numeric(20,2), @MemGrantOutstanding1 numeric(20,2) , @MemGrantOutstanding2 numeric(20,2)

select @upminutes = datediff(mi,login_time,getdate()) from master.dbo.sysprocesses where spid = 1

select
@mbrd1 = suM( fs.num_of_bytes_read)/1024 , @mbwt1 = sum( fs.num_of_bytes_written)/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) fs

SELECT @init = cntr_value , @start_time = getdate ( ) FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Re-Compilations/sec%' ;
SELECT @comp1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Compilations/sec%' ;
SELECT @recomp1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Re-Compilations/sec%' ;
SELECT @batc1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' ;
SELECT @frls1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Free list Stalls/sec%' ;
SELECT @pgrd1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Reads/sec%' ;
SELECT @pgwr1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Writes/sec%' ;
SELECT @flsc1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Full Scans/sec%' ;
SELECT @lzwr1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Lazy writes/sec%' ;
SELECT @ckpt1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Checkpoint pages/sec%' ;
SELECT @frec1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Forwarded Records/sec%' ;
SELECT @send1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Send I/O bytes/sec%' ;
SELECT @atr1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Attention Rate%' ;
select @login1 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='Logins/sec'
select @logout1 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='Logouts/sec'
select @lockreq1 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) = 'Lock Requests/sec' and instance_name = '_Total'
select @ttcr1 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) = 'Temp Tables Creation Rate'
SELECT @MemGrantPending1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Memory Grants Pending%' ;
SELECT @MemGrantOutstanding1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Memory Grants Outstanding%' ;


waitfor delay '00:00:02';


select @mbrd2 = suM( fs.num_of_bytes_read)/1024 ,@mbwt2 = sum( fs.num_of_bytes_written)/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) fs

SELECT @final = cntr_value , @final_time = getdate ( )
FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Re-Compilations/sec%' ;
SELECT @comp2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Compilations/sec%' ;
SELECT @recomp2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Re-Compilations/sec%' ;
SELECT @batc2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' ;
SELECT @frls2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Free list Stalls/sec%' ;
SELECT @pgrd2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Reads/sec%' ;
SELECT @pgwr2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Writes/sec%' ;
SELECT @flsc2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Full Scans/sec%' ;
SELECT @lzwr2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Lazy writes/sec%' ;
SELECT @ckpt2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Checkpoint pages/sec%' ;
SELECT @frec2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Forwarded Records/sec%' ;
SELECT @send2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Send I/O bytes/sec%' ;
SELECT @atr2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'SQL Attention Rate%' ;
select @login2 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='Logins/sec'
select @logout2 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='Logouts/sec'
select @lockreq2 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) = 'Lock Requests/sec' and instance_name = '_Total'
select @ttcr2 = cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) = 'Temp Tables Creation Rate'
SELECT @MemGrantPending2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Memory Grants Pending%' ;
SELECT @MemGrantOutstanding2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Memory Grants Outstanding%' ;


SET @count = datediff ( ss , @start_time , @final_time ) ;
SELECT @COUNT = CASE @COUNT WHEN 0 THEN 1 ELSE @count END;

insert into @tbl exec sp_configure 'max server memory (MB)'
select @MaxSrvMB_running = MaxSrvMB_running from @tbl
select


( SELECT
CONVERT ( DEC(4,3),ROUND ( CAST ( A.cntr_value1 AS numeric ) / CAST ( B.cntr_value2 AS numeric ) , 3 ) ) AS BufCache_HitRatio
FROM
( SELECT cntr_value AS cntr_value1 FROM sys.dm_os_performance_counters
WHERE
object_name LIKE '%Buffer Manager%' AND counter_name = 'Buffer cache hit ratio' ) AS A ,

( SELECT cntr_value AS cntr_value2 FROM sys.dm_os_performance_counters

WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Buffer cache hit ratio base' ) AS B ) AS [BufCacheHit%]

,(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='Page life expectancy' and object_name like '%Buffer Manager%') as PageLife
,(select cntr_value  From sys.dm_os_performance_counters where rtrim (counter_name) ='Free pages' and object_name like '%Buffer Manager%') as FreePages
,CAST (( @frls2 - @frls1 ) / @count AS numeric(20,2) ) AS 'FreeListStalls/sec'
,CAST ( ( @lzwr2 - @lzwr1 ) / @count AS numeric(20,2) ) AS 'LazyWrites/sec'
,CAST ( ( @ckpt2 - @ckpt1 ) / @count AS numeric(20,2) ) AS 'Checkpointpages/sec'
,CAST ( ( @MemGrantPending2 - @MemGrantPending1 )/@count AS numeric(20,2) ) AS 'MemGrantPending'
,CAST ( ( @MemGrantOutstanding2 - @MemGrantOutstanding1 )/@count AS numeric(20,2) ) AS 'MemGrantOutstanding'
,CAST ( ( @pgrd2 - @pgrd1 ) / @count AS numeric(20,2) ) AS 'PageReads/sec'
,CAST ( ( @pgwr2 - @pgwr1 ) / @count AS numeric(20,2) ) AS 'PageWrites/sec'
,CAST ( ( @batc2 - @batc1 ) / @count AS numeric(20,2) ) AS 'BatchRequests/sec'



From sys.dm_os_sys_info

Select

(  Select CAST ( ( @comp2 - @comp1 ) / @count AS numeric(20,2) )) AS 'SQLCompilations/sec',
(  Select CAST ( ( @recomp2 - @recomp1 ) / @count AS numeric(20,2) )) AS 'SQLReCompilations/sec',
(  Select CAST ( ( @frec2 - @frec1 ) / @count AS numeric(20,2) )) AS 'ForwardedRecords/sec',
(  Select CAST ( ( @flsc2 - @flsc1 ) / @count AS numeric(20,2) )) AS 'FullScans/sec',
(  Select CAST ( ( @atr2 - @atr1 ) / @count AS numeric(20,2) )) AS 'SQLAttentionRate',
(  Select CAST ( ( @login2 - @login1 ) / @count AS numeric(20,2) )) AS 'Logins/sec',
(  Select CAST ( ( @logout2 - @logout1 ) / @count AS numeric(20,2) )) AS 'Logouts/sec',

(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) ='User Connections') as 'UserConnections',
(  Select CAST ( ( @ttcr2 - @ttcr1 ) / @count AS numeric(20,2) )) AS 'TempTablesCreationRate'


    SELECT TOP(1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
                   SystemIdle AS [System Idle Process],
                   100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
                   --DATEADD(ms, -0 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
    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;
 

select DB_NAME (database_id) as DBName,
       file_id,
       io_stall_read_ms ,
       io_stall_write_ms ,
       io_stall,
       io_pending ,
       io_pending_ms_ticks,
       io_type
   
from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
     sys.dm_io_pending_io_requests as iopior
where iovfs.file_handle = iopior.io_handle