Script To Collect Performance Metrics From SQL Server Query Analyzer

Yes you can collect value of performance counters from query analyzer. Below is demo query to do same. You can create collect any counter vale for instant

Quick Download:

/*************************************************/ 

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

/*************************************************/

Read also:  How to Use Performance Monitor in Windows 2008