Window Perfmon scripting, SQL Server perfmon and how to perfmon

Windows Perfmon and SQL Server perfmonance  is critical to any migration project. Test before and test after , also known as baselining , provides a great way to review and analyse server performance issues.

 Standardised  capture of Windows perfmon data is one of the requirements for data migration projects. For larger projects , automating is the way forward. Perform scripting allows the automation to occur in a repeatable  method.

 Using  similar workloads across the two environments allows a comparison and detection of underlying discrepancies.

 The steps to create repeatable and standardised testing is :

 Step 1  - Create the “perfmon_base_counters.cfg”

 The purpose is to list the counters used. Read more on Performance Monitor Counters for SQL Server performance testing

Step 2 – Create the “perfmon_data_gather.bat”

 Create a batch file and place the command line:

Logman delete PERFMON_BASE

logman create counter PERFMON_BASE -f bincirc -max 100 -si 00:00:05 --v -o "c:\perfmon_base\data\perfmon_data" -cf "c:\perfmon_base\perfmon_base.config"

 Creates a data collector called PERFMON_BASE – with a 100 MB maximum size limit

It’s refreshed every five seconds and the output is stored in : “c:\perfmon_base\data\perfmon_data”

Using the config file c:\perfmon_base\perfmon_base.cfg

 Step 3 – Create the “perfmon_start_data_gather.bat”

 Create a batch file called “perfmon_start_data_gather.bat”  and place the command line : logman start perfmon_base

 Step 4 – Create the “perfmon_stop_data_gather.bat”

Create a batch file called “perfmon_stop_data_gather.bat”  and place the command line: 

 logman stop perfmon_base

 Step 4 – Install on Server

 Move the files to the server. This is the way I implement the files

Step 5 – Execute “perfmon_data_gather.bat”

This creates the collection

 Step 6 – Execute “perfmon_start_data_gather.bat”

Starts the Perfmon data collecting

 Step 7 – View Performance monitor (Optional)

 To view the monitor from the perfmon log file

a)Command line : perfmon /sys

b)Add Log Data button

c)Add Log Files from the Data Source Collection

d)Click Open on log file  | OK

e)Add Counters

Step 8 – Execute “perfmon_stop_data_gather.bat”

Stops the Perfmon data collecting

Windown perfmon and SQL server perfmon  is great at measuring queues. Accompanied with wait stats analysis – and you’ve got a comprehensive view of a server performance  and you can hit your SQL Server Tuning Targets . My next step is to  manage through Powershell – where I can manage a larger level of servers

Read More

SQL Server Performance Checklist - SQL Server DBA

Performance Monitor Counters for SQL Server ... - SQL Server DBA

The future of the SQL Server Performance Tuning ... - SQL Server DBA