In previous posts I outlined performance counters that can be used to view windows I/O:
http://sqlvelocity.typepad.com/blog/2010/08/windows-performance-counters-for-windows.html
Perfmon is a great tool for analyzing how busy a given system is. Unfortunately Perfmon needs to sample data over time and by necessity performs data smoothing. If a tool only captures data every 5 seconds it is not going to be absolutely accurate in how it reports specific problems. Think of Perfmon like an MRI. An MRI shows you an approximation of what is going on inside the human body. If you need to verify specific problems then you need a scalpel and a surgeon.
Enter the Windows Performance Analysis Toolkit (XPERF) and Process Monitor. I will relate Process Monitor to a surgical scope and XPERF to an intrusive scalpel. Process Monitor is extremely lightweight and lacks the full suite of diagnostic tools included with XPERF.
Over the coming weeks I will cover both tools in detail.
Process Monitor
Process Monitor is created by the Windows Sysinternals team. Mark Russinovich and Bryce Cogswell created the Sysinternals web site back in 1996 to highlight advanced system utilities and technical information.
Note: Mark Russinovich is a Microsoft Technical Fellow who is a co-author of Windows Internals: http://technet.microsoft.com/en-us/sysinternals/bb963901.aspx - Let's just call this book the definitive guide to Windows. By definitive guide I mean the book folks in Microsoft PSS turn to when they need to complete Kernel mode debugging. Good read if you want to stop Sudoku puzzles for a while and really grow your brain power.
Useful tools referencing disks:
Microsoft Sysinternals Disk and file utilities: http://technet.microsoft.com/en-us/sysinternals/bb545046
Lets start our exercise by Downloading the ProcessMonitor.zip file from: http://technet.microsoft.com/en-us/sysinternals/bb896645
. The .zip file contains three files:
- Eula.txt – Lawyer speak
- procmon.chm – help file
- Procmon.exe – The tool!
Run Process Monitor by executing the Procmon.exe application:
The first time Procmon.exe is executed it will prompt the user to accept its license terms. This will only appear the first time the application is executed. Procmon.exe will write your acceptance to the system registry. Other than that the tool is run from the .exe file.
Process Monitor opens and shows all running processes:
Process Monitor will by default show:
- Registry activity
- File system activity
- Network activity
- Process and thread activity
-
Not shown by default; profiling events
The reader should notice that this is a SQL Server I/O performance blog. As such I am going to turn off everything monitored by this tool other than SQL Server I/O. To view SQL Server disk activity we only want to see file activity (I also like to see what the process and thread activity are doing, for the remainder of this post I am leaving it turned off). Turn off features by toggling the "Show activity" icons until only "show file system activity" is toggled on.
Process Monitor is now configured to show every file system access. For the purposes of viewing SQL server we are going to turn off all processes other than SQLServer.exe. Further pare down what process monitor is displaying by excluding processes. Right click the process to exclude (in this case everything other than SQLServer.exe):
Repeat these steps until only SQLServer.exe is listed (I am doing this to simplify this post. You will probably want to see how many processes interact with the file system and the server. This is a powerful tool!).
For this article I ran a TCP-H like workload. The actual workload runs on a 300GB data file (600GB after indexes are applied).
Process Monitor is showing the operation (in this case read file – my test is running table scans), the accessed file, the result, details, and the Process ID (PID). For this exercise we are primarily interested in the Detail section. Windows is accessing a disk Offset and a length of data.
What is an offset you ask? An offset is an integer that defines location within a data structure http://en.wikipedia.org/wiki/Offset_(computer_science) In this case an offset maps to a Logical Block Address http://en.wikipedia.org/wiki/Logical_block_addressing. Logical Block Address (LBA) is a scheme used by disk drives and storage arrays to map storage sectors on a disk. Here is a (ahem, great – had to teach myself 3D rendering to create this) diagram of a disk.
SQL server stores data within pages and extents (http://msdn.microsoft.com/en-us/library/ms190969(v=sql.100).aspx). My Process Monitor example is showing all of us how SQL server is actually addressing the .mdf files.
Length denotes the size of the I/O request. A length of 524,288 bytes is a 512 KB I/O request (524,288 / 1024 = 512).
I can see in my example that my queries are issuing relatively sequential series of 512K I/O's:
Offset: 62,904,336,384, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 63,134,171,136, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 63,505,760,256, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 62,577,311,744, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Keep in mind that the offset is a representation of a location on the disks (in this case a 2TB volume from a VMAX array that resides on about 80 physical disks) and length is the size of the request. The offsets will not increment by the length.
An example of a join operation pulling data from different tables (mixed 128K and 256K I/O's):
Offset: 366,849,884,160, Length: 131,072, I/O Flags: Non-cached, Priority: Normal
Offset: 78,508,130,304, Length: 262,144, I/O Flags: Non-cached, Priority: Normal
Offset: 369,155,465,216, Length: 106,496, I/O Flags: Non-cached, Priority: Normal
Offset: 78,512,586,752, Length: 262,144, I/O Flags: Non-cached, Priority: Normal
Offset: 369,364,566,016, Length: 262,144, I/O Flags: Non-cached, Priority: Normal
Finally a great example of Random requests (256K, 512K, 80K, 464K, and 384K):
Offset: 15,746,727,936, Length: 262,144, I/O Flags: Non-cached, Priority: Normal
Offset: 13,209,829,376, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 14,578,548,736, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 13,221,363,712, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 14,580,645,888, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
Offset: 13,225,558,016, Length: 81,920, I/O Flags: Non-cached, Priority: Normal
Offset: 14,583,267,328, Length: 475,136, I/O Flags: Non-cached, Priority: Normal
Offset: 10,122,952,704, Length: 393,216, I/O Flags: Non-cached, Priority: Normal
Offset: 10,125,967,360, Length: 524,288, I/O Flags: Non-cached, Priority: Normal
WARNING: Process Monitor can capture a huge amount of data. Be cautious running this tool on production systems. DO NOT let it run in perpetuity! I managed to kill TS on my test machine because I ran a 10 hour test, logging all the while. I performed a 5 minute SQLSERVER.exe file activity capture. The CSV export file is 1.33GB! Use Caution, you have been warned!
This is an amazing tool. I like to use it to give me a detailed snapshot of I/O on my servers. When I detect a performance anomaly with Perfmon I can look in detail at what caused it. Always remember, this tool is a scalpel.
Be sure to get comfortable with Process Monitor on a test system before running it in production.
Note: I need to rant on the beauty and elegance of the SQL engine for a second. SQL server detected that my example test is largely sequential. Instead of grabbing 64K data pages, it is dynamically grabbing 512K I/O. This is a lot more efficient than grabbing a ton of smaller I/O. Please realize that this has its limits. 512K is about the maximum I/O you want to grab from a storage system. If you start getting into the megabyte I/O size you will actually see overall performance degrade and latency will skyrocket. Storage was designed to handle smaller I/O. 256 to 512K I/O's are just about perfect. End of rant!
A great way to do this is to run SQLIO.exe with a known performance profile. Capture Perfmon data, and use Procmon.exe to get a super detailed view of what the tool is actually doing to the storage system. Mike Ruthruff has an excellent article on SQL I/O at: http://technet.microsoft.com/en-us/library/cc966412.aspx.
Part 2 will cover how to export the data into Microsoft Excel to perform some math and graphing magic; let's call it a poor man's XPERF (that is a tool for a later post as well).
Part 3 will demo what random and sequential I/O generated by SQLIO.exe looks like.
Enjoy! Post your questions…
