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

Performance monitor counters - Top 10 counters for monitoring SQL server

Perfmon helpful couters

Buffer cache hit ratio :

It is the percentage of data server had in the cache and did not have to read it from the disk.
Buffer cache hit ratio should be more than 90% for a SQL Server

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio

FROM sys.dm_os_performance_counters a

JOIN (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base'

AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME

WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'




Page Life Expectancy (PLE) :

It is the number of seconds the data will stay in the  cache. The ideal value is 300 if less than 300 or between 300-400 the sever will require more memory.


SELECT *,cntr_value as [PLE in secs],cntr_value / 60 as [PLE in mins],

cntr_value / 3600 as [PLE in hours],

cntr_value / 86400 as [PLE in days]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page life expectancy'

AND OBJECT_NAME = 'SQLServer:Buffer Manager'