SQL SERVER DBA, Linux and Azure: Script To Collect Performance Metrics From SQL Server Query Analyzer

Script To Collect Performance Metrics From SQL Server Query Analyzer

/*************************************************/ 
USE Master
GO
Create Table Perfmon (Object_name Varchar (200),
Counter_name varchar (300) , Instance_name varchar (100),
cntr_value bigint, cntr_type bigint , date varchar (20))
GO 
Insert into Perfmon
SELECT object_name , counter_name , instance_name , cntr_value, cntr_type
, convert (varchar (20),getdate () , 120) as date
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME in 
(
'MSSQL$Servername:Databases' ,
'MSSQL$Servername:General Statistics',
'MSSQL$Servername:Buffer Manager' ,
'MSSQL$Servername:Locks'
)
AND counter_name in 

'Transactions/sec' ,'User Connections','Page life expectancy',
'Buffer cache hit ratio', 'Buffer cache hit ratio base' ,
'Free pages','Total pages','Target pages','Lock Wait Time (ms)'
)
Select @@servername
GO
Select *, case
when cntr_type  = 65792  Then 'instant_value'
when cntr_type = 272696576 Then 'Incremental'
When cntr_type = 1073939712 Then 'Instant fraction'
When cntr_type = 537003264 Then 'Use this with Base value'
else 'null' end  
from Master.dbo.perfmon
/*************************************************/

No comments:

Post a Comment