Tag Archives: Perfmon

Powershell for the perfmon files

Posted on by

Recently, I was working on a requirement to convert a number of BLG files into CSV and then changing the CSV files into Excel files.

So, the script below does the following:

1. Picks the BLG files from a folder and then creates a CSV file from the BLG file using RELOG.EXE

2. Then the same CSV file is converted to a XLS file

As always, this is a Powershell script which can be adapted in any way possible. Happy perfmon analysis!

The script can be downloaded from OneDrive as well.


#################################################################################
#                                        #
#                                        #
#                                        #
#                                        #
#    Script Name: Relog                            #
#    Author: Amit Banerjee                            #
#    Date: May 10, 2014                            #
#    Description:                                 #
#    The script uses relog to create CSV files from BLG files         #
#    It then converts the CSV files to XLS files                 #
#                                        #
#                                        #
#                                        #
#                                        #
#                                        #
#################################################################################

# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys fees, that arise or result from the use or distribution of the Sample Code.

# Enumerates BLG files on disk and starts running relog on them
$path = "C:\PerfmonFiles\" # Replace with correct path
$files = Get-ChildItem $path -Filter *.blg
foreach ($file in $files)
{
    # Create the CSV filename
    $filename = $file.FullName.Split(".")[0] + ".csv"
    # Run relog with the correct arguments
    $AllArgs =  @($file.FullName,  '-f', 'csv', '-o',  $filename)
    & 'relog.exe' $AllArgs
}

# Enumerates CSV files on disk and starts converting them to Excel
$files = Get-ChildItem $path -Filter *.csv 
foreach ($file in $files)
{
    # Launch Excel
    $xls = new-object -comobject excel.application
    $xls.visible = $true
    # Open the CSV file
    $Workbook = $xls.workbooks.open($file.FullName)
    $Worksheets = $Workbooks.worksheets
    $filename = $file.FullName.Split(".")[0] + ".xls"
    # Save the file as Excel
    # Depending on your compatibility settings you might have to accept a prompt to save the file
    $Workbook.SaveAs($filename,1)
    $Workbook.Saved = $True
    # Close Excel
    $xls.Quit()
    # Delete the CSV file
    Remove-Item $file.FullName
}
Advertisement

What does cntr_type mean?

Posted on by

Have you ever wondered if the cntr_type column value in the sys.sysperfinfo or sys.dm_os_performance_counters output has a significant meaning or not. Well since the column value is there, it obviously has a meaning. Additionally, if the raw values represented by the output of some of the counter types is considered at face value, then your performance base lining can take a severe beating.

Each cntr_type value meaning can be found from the WMI Performance Counter Type or Windows Server Performance Counter Type documentation on MSDN. 

The common counter types in SQL Server are:
PERF_COUNTER_RAWCOUNT | Decimal | 65536
Raw counter value that does not require calculations, and represents one sample.

PERF_COUNTER_LARGE_RAWCOUNT | Decimal | 65792
Same as PERF_COUNTER_RAWCOUNT, but a 64-bit representation for larger values.

PERF_COUNTER_COUNTER | Decimal | 272696320
Average number of operations completed during each second of the sample interval. NOTE: For "per-second counters", this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used. For example, batch requests/sec is a per-second counter, it would show cumulative values.

PERF_COUNTER_BULK_COUNT | Decimal | 272696576
Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.

PERF_AVERAGE_BULK | Decimal | 1073874176 | Decimal | 537003264
Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.

PERF_LARGE_RAW_BASE | Decimal | 1073939712
Base value found in the calculation of PERF_RAW_FRACTION, 64 bits.

Example:
If you had the following values:
SQLServer:Plan Cache | Cache Hit Ratio | Temporary Tables & Table Variables | 381
SQLServer:Plan Cache | Cache Hit Ratio Base | Temporary Tables & Table Variables | 386
Then the Temp Table/Variable cache hit ratio percentage would be: 98.7% (approx.)

You can use the query below to get the comments for each counter type as discussed above: 

select object_name,counter_name,instance_name,cntr_value,



case cntr_type 



	when 65792 then 'Absolute Meaning' 



	when 65536 then 'Absolute Meaning' 



	when 272696576 then 'Per Second counter and is Cumulative in Nature'



	when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'



	when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 



end as counter_comments



from sys.dm_os_performance_counters



where cntr_type not in (1073939712)


 


Documentation on MSDN:

      
WMI Performance Counter Types


http://msdn.microsoft.com/en-us/library/aa394569(VS.85).aspx 


SQL Server 2005 BOL Topic


sys.dm_os_performance_counters (Transact-SQL)  


The broad classes of counters are as follows:


Non-computational Counter Types 


http://msdn.microsoft.com/en-us/library/aa392713(VS.85).aspx


Basic Algorithm Counter Types 


http://msdn.microsoft.com/en-us/library/aa384813(VS.85).aspx


Counter Algorithm Counter Types 


http://msdn.microsoft.com/en-us/library/aa389384(VS.85).aspx 


Timer Algorithm Counter Types 


http://msdn.microsoft.com/en-us/library/aa393909(VS.85).aspx


Precision Timer Algorithm Counter Types 


http://msdn.microsoft.com/en-us/library/aa392755(VS.85).aspx


Queue-length Algorithm Counter Types 


http://msdn.microsoft.com/en-us/library/aa392905(VS.85).aspx


Base Counter Types 


http://msdn.microsoft.com/en-us/library/aa384811(VS.85).aspx


Statistical Counter Types 


http://msdn.microsoft.com/en-us/library/aa393663(VS.85).aspx

Setting up Perfmon Logs

Posted on by
One of best ways to monitor your system performance for disk contention, high CPU, memory crunch etc. is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time. In Windows Server 2008 & Windows Vista, Perfmon has a cool new utilty (a management MMC snap-in) called Reliability Monitor which helps you a bird’s eye view of your system stability.

Steps to setup Perfmon data collection for Windows Server 2003/XP
This can be done by opening up Perfmon:

  1. Click on “Performance Logs and Alerts”
  2. Click on “Counter Logs”
  3. Right click on the same and click on “New Log Settings”
  4. Give the log a name
  5. Click on “Add Objects” and add all the objects that are needed for your data collection
  6. Click on the “Log Files” tab
  7. You can change the log file location by clicking on the “Configure” button
  8. Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
  9. Click on the “OK” button to create the log
  10. Then right-click on the Log and click on “Start” to begin the logging
  11. Right-click on the log file and click on “Stop” to end the logging

How to setup a Perfmon data collection for Windows Server 2008/ Vista/ Windows Server 2008 R2

  1. Open up the Performance Monitor snap-in (Start -> Run -> perfmon)
  2. Expand the “Monitoring Tools” option and Right Click on “Perfomance Monitor” -> “New” -> “Data Collector Set“
  3. Give the data collector set a name and click on Next
  4. Give the location where you want to save the Perfmon Logs and click on Next
  5. Here you can provide the Run As user of leave that as the Default user
  6. Select the option “Save and Close” and click on Finish
  7. Then go back to the Perfmon snap-in main window and you should see a Data Collector set with the same name that you created under Data Collector Sets -> User Defined
  8. Click on the Data Collector Set and on the right hand pane, you should see a System Monitor Log Performance Counter. Right click on it and click on properties.
    • Under the Performance Counters tab, add the relevant performance counters required for your data collection, set the log format (binary, SQL, CSV, comma separated) and the sample collection interval time.
    • Under the File tab specify the file format name and logging properties for the file.
  9. Now you can start the Perfmon data collection by right clicking on the Data Collector Set and click on Start or you could do some more work for maintaining disk space by setting up some rules using the Data Manager to create .CAB files or delete older files in case we are setting up perfmon logs for long term monitoring.

There is hardly any performance impact in collecting perfmon logs on any server. Any perfmon data collection unlike other forms of data collection don’t generate voluminous data in terms of size but contain hordes of infromation which can provide valuable insight to an issue which is impacting critical business applications running on a server.

Windows Reliability and Performance Monitor

http://technet.microsoft.com/en-us/library/cc749154.aspx