Capture performance monitor counters using performance monitor

How to run performance counters on Windows Server 2008 R2

Today I needed to setup a configuration to mirror what someone had sent me so that I can estimate a baseline of disk performance to calculate disk IOPs for a SQL server move to a different datacenter.  This will only be a simple setup to get at the numbers I need so I can start the compare.  If I have more time I will show values and calculations in another post.

1. Open Performance Monitor by running PERFMON in the “Run…” textbox or navigating to Icon under all programs
2. Under “Reliability and Performance”, expand “Data Collector Sets”.
3. Right-click on “User Defined”
4. Select “New” from the popup menu then select “Data Collector Set”.
5. Assign a name for the new log settings. Eg.: “ServerName_Perfmon.”
6. Under the “Create data logs” section, check the checkbox for “Performance counter”.
7. Click the “Add…” button to start adding counters for each “Performance object” you wish to add counters from. Add the desired counters.  In my example today I am using PhysicalDisk > Disk Transfers/sec, Disk Reads/sec, Disk Writes/sec and _Totals

8. Once you have added all the required counters, click the “OK” button and then click the “Next” button.
9. Specify the location to save the file.
10. Click the “Finish” button.

I wanted to Run my User Defined data collector set now so I Right-click the new file under the “User Defined” and selected "Start"  You could continue onto step 11 if you wanted to Schedule a Start and End time

11. Right-click the new file under the “User Defined” folder and select “Properties”.
12. Click on the “Schedule” tab.
13. Click on the “Add” button to schedule a “Beginning date:” and “Start time:” and then click on the “Stop Condition” tab to specify the “Overall duration.”

To make the PERFMON file as a template to make it reusable, you can save the list of counters as an .xml file so that you can use it on other SQL Server hosts. Right-click on the new log folder under the “User Defined” folder and select “Save Template…” to save the file as .xml in to the designated location.

To use a template perfmon counter log file in a Windows Server 2008 or 2008 R2 host server, open PerfMon > Reliability and Performance > Data Collector Sets > right-click on “User Defined” > New > “Data Collector Set” > name the file > select the “Create from a template (Recommended)” radio button > click the “Browse” button to browse to the template file > select the template .xml file > click the “Next” button > specify the location to save the file > the “Save and close” radio button will be selected > hit the “Finish” button.

To modify the new “DataCollector01” set, right-click on it and select “Properties” to add or remove the counters as necessary. Click the “OK” button when done.

To schedule the new log file, right-click on the new file under the “User Defined” section, click “Properties”, then click the “Schedule” tab.

To convert perfmon .blg files to .csv files, you can use the command prompt and navigate to the directory the binary file is located and type:

relog logfile.blg -f CSV -o newfile.csv

REFERENCES:

How to create a log using System Monitor in Windows
https://support.microsoft.com/en-us/help/248345/how-to-create-a-log-using-system-monitor-in-windows

Using Performance Monitor from https://docs.microsoft.com

Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more