Logging the performance counter

The following script creates a table PerfMondata in a SQLMonitor database and logs the counter in the interval of 30 seconds



use [SQLMonitor]



GO
 SET NOCOUNT ON ;

DECLARE @PerfCounters TABLE (

[Counter] nvarchar(770),

[CounterType] int,

[FirstValue] decimal (38,2),

[FirstDateTime] datetime,

[SecondValue] decimal(38,2),

[SecondDateTime] datetime ,

[ValueDiff] AS ([SecondValue] - [FirstValue]) ,

[timeDiff] AS (DateDiff(ss,[FirstDateTime],[SecondDateTime])),

[CounterValue] decimal(38,2)

) ;




 INSERT INTO @PerfCounters (

[Counter],

[CounterType],

[FirstValue],


[FirstDateTime]
)
 SELECT RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name]),[cntr_type],[cntr_value],GETDATE()

from sys.dm_os_performance_counters

WHERE [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec' ,'Buffer cache hit ratio'



)
 order by [OBJECT_NAME] + N' : ' + [counter_name]+ N':' +RTRIM([instance_name])



  Waitfor delay '00:00:30';


Update @PerfCounters

SET [SecondValue]= [cntr_value],

[SecondDateTime] = GETDATE()

from sys.dm_os_performance_counters

where [Counter] = RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name])

AND [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec','Buffer cache hit ratio' )


 
--for per sec counters

Update @PerfCounters

SET CounterValue = ([SecondValue]-[FirstValue])/(DateDiff(ss,[FirstDateTime],[SecondDateTime]))

where [counterType] = 272696576;


--for non per sec counters

 Update @PerfCounters

SET CounterValue = [SecondValue]

where [counterType] <> 272696576;

select * from @PerfCounters



INSERT INTO PerfMondata (perfCounter,Value,CAptureDate)

SELECT [counter],[CounterValue],[SecondDateTime] from @PerfCounters



--select * from PerfMondata




--following stored procedure takes in a counter as the parameter and displays the report on a specific counter

use [SQLMonitor]



GO
 

if OBJECTPROPERTY(OBJECT_ID('proc_sqlAdmin_PerfMonReport'),'IsProcedure')= 1

DROP PROCEDURE proc_sqlAdmin_PerfMonReport

GO





CREATE PROCEDURE dbo.proc_sqlAdmin_PerfMonReport

(



@Counter nvarchar(128)= '%'

)



AS



BEGIN ;

SELECT [Perfcounter],value,captureDate

from perfMondata

WHERE [Perfcounter] like @Counter

ORDER BY [Perfcounter],[captureDate]



END ;



/*



exec dbo.proc_sqlAdmin_PerfMonReport '%Page life expectancy%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Batch Requests/sec%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Page Writes/sec%'

*/