SQL Server Operability recipe – Handy list of PERFMON counters to capture IO stress testing results

 

I treat Scalability and Performance like a two-side coin whenever the SQL Server operability consulting is involved. The Scalability and Performance is guaranteed when the reliable storage is configured at the hardware level. In general, the storage configuration and management is handled by the Storage Administrators and (default complaint) it is a compulsory exercise for an organization to involve DBAs at configuration level that will shape up a well-built database server on hardware perspectives.

Further to the above the transactional consistency and concurrency must be maintained for the better data storage too. When we talk about data there will be structered data and unstructured data, which means the data that is stored outside of database such as image files, documents or media files (remember FILESTREAM feature from SQL Server 2008 R2 onwards). Also the SPATIAL type of data can be managed efficiently, such as the environment needs to be high-performance and high-end hardware with a massive data storage solution. However, SQL Server features on these two new data types have enabled the applications to support various methods that will allow for creation, comparison, analysis, and spatial data retrieval.

Nothing specific to existing environment or new setup of environment the storage STRESS testing is important task that every DBA must follow. Stress Testing means simulating the heavy load and see how the hardware is performing, it is essential to highlight the critical business process. As a part of my consulting toolkit I always recommend and follow to perform STRESS testing on the environment, in specific to storage.  SQLIOSIM utility is the best tool in this regard. I would recommend you to see more about SQL Server I/O patterns that is referred in above KBA and to obtain such information about SQL Server I/O patterns, see Chapter 2 of Microsoft SQL Server I/O Basics, by Bob Dorr. Also there is a good information and explanation about pre-installed toolkit from Windows Operating system see Windows Performance Analysis Toolkit (WPT) for more information.

Lastly, as per the subject line I collect the following PERFMON counters whenever a stress testing is performance within physical or virtual environment. The counters are:

 

Logical Disk & Physical Disk:

  • IO Bandwidth related:
  • Disk Bytes/Sec
  • Disk Read Bytes/Sec
  • Disk Write Bytes/Sec
  • IOPS for SAN related:
  • Disk Bytes/Sec
  • Disk Reads/Sec
  • Disk Transfers/sec
  • Disk Latency information capture:
  • Disk Sec/Reads
  • Disk Writes/Sec
  • Disk Sec/Writes
  • To calculate IO transfer size (blocks):
  • Disk Bytes/Read
  • Disk Sec/Transfers
  • Disk Bytes/Writes
  • Disk Bytes/Transfer
  • To obtain disk queue length:
  • Disk Bytes/Transfer
  • Avg Disk Read Queue Length
  • Avg Disk Queue length
  • Avg Disk Write Queue Length (obtain more information from your Storage SME)

Also you can refer through about basics on disk related blurb,  SQL Server and caching disk controllers basics, using disk drive caching with SQL ServerUsing hard disk controller caching with SQL Server and SQL Server diagnostics added to detect unreported I/O problems links.

Also I have covered the basics to best practices in terms of Scalability and Performance topic in my book SQL Server 2008 R2 Administration cookbook