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
Showing posts with label datacollector. Show all posts
Showing posts with label datacollector. Show all posts
Wednesday, February 18, 2015
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
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
Subscribe to:
Posts (Atom)