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 :
The purpose is to list the counters used. Read more on Performance Monitor Counters for SQL Server performance testing
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
Create a batch file called “perfmon_start_data_gather.bat” and place the command line : logman start perfmon_base
Create a batch file called “perfmon_stop_data_gather.bat” and place the command line:
logman stop perfmon_base
Move the files to the server. This is the way I implement the files
This creates the collection
Starts the Perfmon data collecting
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
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