SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Window Perfmon scripting, SQL Server perfmon and how to perfmon

17 June,2011 by Jack Vamvas

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

Perfmon counters 

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

 Perfmon monitor

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer