gistfile1.sql

DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE
    WHEN @@SERVICENAME = 'MSSQLSERVER'
    THEN @CounterPrefix + ''
    ELSE 'MSSQL$'+@@SERVICENAME+':'
    END;
				SELECT 					
					server_name = @@SERVERNAME,
					server_instance = @@SERVICENAME,
					object_name, 
					counter_name, 
					instance_name, 
					cntr_value		
				FROM master.sys.dm_os_performance_counters
					WHERE (
								(object_name = @CounterPrefix + 'Transactions' AND counter_name = 'Transactions' )
							OR
								(object_name = @CounterPrefix + 'Locks'
										AND counter_name IN (
																'Lock Wait Time (ms)',  
																'Lock Waits/sec',
																'Average Wait Time (ms)',
																'Lock Requests/sec',
																'Lock Timeouts (timeout > 0)/sec'))
							OR 
								(object_name = @CounterPrefix + 'Latches'
										AND counter_name in (
																'Latch Waits/sec',        
																'Average Latch Wait Time (ms)',      
																'Total Latch Wait Time (ms)',          
																'Number of Deadlocks/sec'
										))        
							OR
								(object_name = @CounterPrefix + 'Deprecated Features')
							OR
								(object_name = @CounterPrefix + 'General Statistics'
										AND counter_name in (
																'Logins/sec',                  
																'User Connections'
										))
							OR
								(object_name = @CounterPrefix + 'Plan Cache') 
							OR
								(object_name = @CounterPrefix + 'Cursor Manager by Type'
										AND 
											(counter_name = 'Active cursors'
												OR (counter_name = 'Errors/sec'
														and instance_name in ('DB Offline Errors', 'Kill Connection Errors')
													)
											)
								)

							OR 
								(object_name = @CounterPrefix + 'SQL Statistics'
									AND counter_name in (
										'Batch Requests/sec',    
										'Failed Auto-Params/sec'                  ,
										'Safe Auto-Params/sec'                     ,                                                                           
										'Unsafe Auto-Params/sec'                 ,
										'SQL Compilations/sec' ,
										'SQL Re-Compilations/sec'    ,
										'SQL Attention rate' 
									)
								)
							OR
								(object_name = @CounterPrefix + 'Memory Manager'
									AND counter_name in (     
										'Granted Workspace Memory (KB)',   
										'Maximum Workspace Memory (KB)' ,    
										'Memory Grants Outstanding'      ,                                                                             
										'Memory Grants Pending'           ,                                                                                                
										'Total Server Memory (KB)'         ,                                                                                               
										'Target Server Memory (KB)'
									)
								)
							OR
								(object_name = @CounterPrefix + 'Databases'
									AND counter_name in (
										'Data File(s) Size (KB)',                                                                                                          
										'Log Bytes Flushed/sec'  ,     
										'Log File(s) Size (KB)'   ,    
										'Log File(s) Used Size (KB)'                                                                                                      ,
										'Log Flush Wait Time',  
										'Log Flushes/sec',    
										'Log Growths',          
										'Log Shrinks',  
										'Log Truncations' ,
										'transactions/sec' ,                       
										'Percent Log Used' 
									)  
	        
								)
							OR
								(object_name = @CounterPrefix + 'Access Methods' 
									AND counter_name in (                                                                                                       
										'Forwarded Records/sec',                                                                                                           
										'Full Scans/sec'       ,
										'Index Searches/sec'   ,
										'Page Splits/sec'      ,
										'Workfiles Created/sec' ,                                                                                                          
										'Worktables Created/sec' ,   
										'Worktables From Cache Ratio'                                                                                                     ,
										'Table Lock Escalations/sec'   
									)  
								)
							OR (object_name =@CounterPrefix + 'Buffer Manager'
									AND counter_name in (
										'Free list stalls/sec',
										'Lazy writes/sec'     , 
										'Readahead pages/sec'  ,
										'Checkpoint pages/sec' ,
										'Page life expectancy' ,
										'Page lookups/sec'     ,
										'Page reads/sec'       ,
										'Page writes/sec'      ,
										'Readahead pages/sec'   ,  
										-- --Free System Page Table Entries  
										'Database pages', 
										-- --procedure cache pages
										'Target pages'   ,                     
										'Free pages'    ,
										'Stolen pages'
									)
								)		             
							)