![](/style/images/good.png)
![](/style/images/bad.png)
Performance Monitor Counters for SQL Server Storage
source link: https://www.mssqltips.com/sqlservertip/6982/performance-monitor-counters-for-sql-server-storage/?utm_campaign=Feed%3A+MSSQLTips-LatestSqlServerTips+%28MSSQLTips+-+Latest+SQL+Server+Tips%29
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Server Disk Related Performance Monitor Counters
By: Joe Gavin | Updated: 2021-09-27 | Comments | Related: More > Monitoring
Problem
How do I use Windows Performance Monitor to look for disk related SQL Server issues?
Solution
So far, we've seen how to review some general SQL Server and memory related Performance Monitor output. Now that we'll look at some disk related counter output.
Objects / Counters
The following table has a list of Perfmon Objects / Counters related to disk performance with their descriptions and a guide of expected values. As with any Perfmon data we're using the output as a benchmark. It's not a direct link to a certain problem but the data is useful to see when we need to see what areas to start looking at and to see what changes improve, or hopefully not degrade performance.
Add Counters
Windows Key + R: to open the Run menu.
- Enter perfmon in the run box
![Start Perfmon](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.001.png)
Click the 'X' symbol to remove default % Processor Time counter (This is an optional step. I just find it easier to have as clean an output as possible.)
- Click the 'plus symbol' to bring up 'Add Counters' screen and start adding counters
![Add Counters 1](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.002.png)
- Scroll down to and Click the PhysicalDisk dropdown in the Perform Objects box
- Scroll down and select Avg. Disk sec/Read
- Select drives
![Add Counters 2](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.003.png)
- Select Avg. Disk sec/Read
- Highlight drive letters
![Add Counters 3](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.004.png)
Repeat the process for Disk Reads/sec and Disk Writes/sec then click OK
Create Collector Set
To save the added Counters we'll save them to a Collector Set. This will let run the Counters without having to re-add them and also run on a schedule.
- Right click Performance Monitor
- Data Collector Set
![Create Data Collector Set 1](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.005.png)
- Enter a name (I chose SQL Server Disk Performance, but you can call it anything that makes sense to you.)
![Create Data Collector Set 2](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.006.png)
- Accept or change directory
- Finish
![Create Data Collector Set 3](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.007.png)
- Finish
![Create Data Collector Set 4](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.008.png)
Run Collector Set
Run the Collector set to start gathering data.
- Expand User Defined dropdown
- Right click on Collector Set
- Properties
![Collector Set Properties](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.009.png)
- Directory tab
- Enter name of subdirectory
- Enter MMddyymmss in 'Subdirectory name format:' files (This will let you create a new file for every time the Collector Set is run. Otherwise you'll get an error saying 'When attempting to start the Data Collector Set the following system error occurred: Cannot create a file when that file exists.).
![Log Directory](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.010.png)
We're going to manually start the Collector Set so just need to tell it how long to run.
- 'Stop Condition' tab
- Check 'Overall duration'
- Select number of units to run
- Select units to run
![Stop Condition](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.011.png)
- Right click on Collector Set
- Start
![Start Collector Set](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.012.png)
Save Collector Set to Template
To be able to import the Collector Set to another Windows machine we can save it to a template.
- Right click on Collector Set
- 'Save Template…'
![Save Collector Set to Template 1](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.013.png)
- Select file location
- Name file
![Save Collector Set to Template 2](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.014.png)
View Performance Data
24 hours have passes and it's time to view the performance data.
- Right click on Collector Set
- Latest Report
![View Report 1](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.015.png)
The graphical representation of the data is in the top part of the window and we can click on each Counter and drive to get data for each in the bottom.
The average Disk Reads/sec for the D:\drive is 0.048 seconds = 48 milliseconds. This is approaching the high end of 50 milliseconds and would indicate slow disk performance on this server that needs to be investigated further. I'm running this on a Windows 2019 virtual machine hosted on a laptop so I'm not expecting a whole lot of performance. If this were a real server, it would be time to discuss with the storage team.
![View Report 2](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.016.png)
All the boxes in the Show column can be left checked if you want to view them all together for comparison. Check or uncheck individual Counters and Instances to get a simpler graphical of each.
![View Report 3](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.017.png)
Export Perfmon Data to a .csv File
Depending on how many Counters you're looking at it may be preferable to view and analyze the Perfmon data in a spreadsheet. This will allow filtering and sorting data as well as creating you own graphs. To get started we'll export the data collected to a .csv file.
- Right click anywhere
- Save Data As…
![Export Perfmon Data to .csv 1](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.018.png)
- Choose file location
- Name file
- Select 'Text File (Comma delimited)(.csv)' in dropdown
![Export Perfmon Data to .csv 2](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.019.png)
Made the following formatting changes to make it more readable:
- Format of the first column to a time format
- Top row Alignment to Word Wrap
- Made all columns the same width
![Exported .csv](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.020.png)
File > Save As
- Choose Excel Workbook (*.xlsx) in dropdown
![Save as Excel Spreadsheet](https://www.mssqltips.com/tipimages2/6982_sql-server-disk-related-performance-monitor-counters.021.png)
Next Steps
So far, in this and previous tips we've seen how to analyze some basic disk related Perfmon data, specific memory related data, and specific disk related data.
Here are some links with further information.
About the author
Article Last Updated: 2021-09-27
Comments For This Article
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK