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%'

*/ 



 

Srcript: Analysing Performance monitor data

After capturing the data in  the tables  ( link here - http://sql-dba-online.blogspot.com/2015/02/performance-monitor-how-to-run-relog-to.html) The following script will help to analyze the data.

/*show data collected for specific collector set, ordered by counter and time */

SELECT * from counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

ORDER BY cdt.[ObjectName],

cdt.[CounterName],cd.[RecordIndex]



/* List max,min,avg,stdev for each counter for a specific collector*/

SELECT CONVERT(VARCHAR(10),cd.counterDateTime, 101) AS "collection" ,

RTRIM(cdt.objectName) + '\' + rtrim(cdt.CounterName) +

Case

WHEN cdt.InstanceName IS NULL then ''

WHEN cdt.instanceName is not null then '_' + rtrim(cdt.InstanceName)

END as "Counter",

CAST (MIN(cd.CounterValue) AS Decimal(6,0)) AS "Minimum",

CAST (MAX(cd.CounterValue) AS Decimal(6,0)) AS "Maximum",

CAST (AVG(cd.CounterValue) AS Decimal(6,0)) AS "Average",

CAST (STDEV(cd.CounterValue) AS Decimal(6,0)) AS "StDev"

FROM counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

GROUP BY CONVERT(VARCHAR(10),cd.counterDateTime, 101),cdt.objectName,cdt.CounterName,cdt.instanceName

Performance Monitor : How to run relog to process performance monitor data

Run relog from the directory where the datacollector file is

Type the following in the command prompt
c:\cd PerfLogs\Admin\FromTemplate\000001

( the above is the path where my file is)

running relog
c:\cd PerfLogs\Admin\FromTemplate\000001 > relog ProdSample.blg -f  SQL -o SQL:BaselineData!ProdSample.blg


ProdSample is my perflog file
-f is the format in which you want
-o is the output
SQL:BaselineData  ( it is the ODBC  connection that  I had created pointing to the database where I wanted  the data to be dumped)

Once I ran the above command  relog transferred the data and it created the following tables in the database connected trough ODBC . BaseLinedata in this case.

DisplayToID
CounterData
CounterDetails