PerfMon - Sizing an existing database environment

Estimating the number of I/Os required for a system is crucial when sizing one or multiple existing databases on a new I/O subsystem - i.e. SAN considerations - to SQL Server. Often in mixed environments (OLTP/RDW) I must take the blended approach for managing the I/O for our SQL Server and the recommondations of the I/O subsystem manufacturer.

...

**Tab 1) Typical I/O Workloads**
OLTP (Online Transaction Processing)	RDW (Relational Data Warehousing)
Typically, heavy on 8KB random read / writes Many “mixed” workloads	Typical 64-512KB sequential reads (table and range scan) 128-256KB sequential writes (bulk load)

Arranging the reads and the writes according to the proper mix of these environments defines the performance profile for the database system. So I take a closer look for a good balance between reporting and online transactions - i.e. the read/write ratio. After reading a couple of I/O Best Practices, I conclude this for my mixed environment(s):

**Tab 2) read/write ratio percentages**
Database System Type	Read Percentage	Write Percentage
OLTP	60%	40%
RDW	80%	20%
Mixed	70%	30%

I strongly recommended to read Microsoft SQL Server on SAN Best Practices by Mike Ruthruff, Mike is a member of the SQL Server Customer Advisory Team (SQL CAT).

The Best Practices includes the following:

Characteristics of SQL Server I/O operations
Best practices
- SQL Server Design Practices
- Storage Configuration
- Common Pitfalls
Monitoring performance of SQL Server on SAN
Emerging Storage Technologies
Additional Material in Appendix Section
- How to validate a configuration using I/O load generation tools
- General SQL Server I/O characteristics
- How to diagnose I/O bottlenecks
- Sample Configurations unfortunately not a sample of my environment

So I went back to my existing environment and decide to take a look at the physical reads and writes, to gather statistics during periods of peak stress on my system. I think it is a good choose to use PerfMon. PerfMon is an excellent way to develop a behavior profile for the I/O subsystem of the SQL Server, and it is a part of the Windows Server system. The PerfMon tool can be used to monitor SQL Server instances from the local system or from a remote system. The remote system does not require SQL Server to be installed - however, both operating systems must have the same operating system service packs installed!!!

I start the PerfMon tool and create a data collection set from Server Manager. Here are my steps:

Open the Diagnostics Section and Performance subsection.

I create a data collection manually.

Create the data logs (Performance counter).

I change the Sample interval to 5 seconds and add the performance counters.
Select the LogicalDisk and SQLServer:Databases counters for all my SQL server instances.

Physical disk counters:
- Disk reads/sec. Read IOPS for the disk drive or drives
- Disk transfers/sec. Total read plus write IOPS for each disk drive
- Disk writes/sec. Write IOPS for each disk drive
- Avg. disk sec/read. Read latency or average time for each read operation
- Avg. disk sec/write. Write latency or average write time for each operation
- Average disk queue length. Average of both read and write requests that are in the queue
For a well-tuned I/O subsystem, the read and write latency should be within 5 to 10 ms. If the latency is 20 ms, the system might be experiencing an I/O issue. Latency exceeding 30 ms is in the unacceptable range. As all values are relative, the effect of measured latency depends on the type of operations taking place on your storage system and what they mean to to you - you are the DBA.

SQLServer:Databases ( Yes, I select them all :-(
- Active Transactions Number of active update transactions for the database.
- Backup/Restore Throughput/sec Read/write throughput for backup/restore of a database.
- Bulk Copy Rows/sec Number of rows bulk copied.
- Bulk Copy Throughput/sec KiloBytes bulk copied.
- Commit table entries The size of the in-memory part of the commit table for the database.
- Data File(s) Size (KB) The cumulative size of all the data files in the database.
- DBCC Logical Scan Bytes/sec Logical read scan rate for DBCC commands
- Log Bytes Flushed/sec Total number of log bytes flushed.
- Log Cache Hit Ratio Percentage of log cache reads that were satisfied from the log cache.
- Log Cache Reads/sec Reads performed through the log manager cache.
- Log File(s) Size (KB) The cumulative size of all the log files in the database.
- Log File(s) Used Size (KB) The cumulative used size of all the log files in the database.
- Log Flush Wait Time Total wait time (milliseconds).
- Log Flush Waits/sec Number of commits waiting on log flush.
- Log Flushes/sec Number of log flushes.
- Log Growths Total number of log growths for this database.
- Log Shrinks Total number of log shrinks for this database.
- Log Truncations Total number of log truncations for this database.
- Percent Log Used The percent of space in the log that is in use.
- Repl. Pending Xacts Number of pending replication transactions in the database.
- Repl. Trans. Rate Replication transaction rate (replicated transactions/sec.).
- Shrink Data Movement Bytes/sec The rate data is being moved by Autoshrink, DBCC SHRINKDATABASE or SHRINKFILE.
- Tracked transactions/sec Number of committed transactions recorded in the commit table for the database.
- Transactions/sec Number of transactions started for the database.
- Write Transactions/sec Number of transactions which wrote to the database in the last second.
Review the performance counters.
Data will collect into the %systemdrive%\PerfLogs\Admin\… directory in a .BLG binary format.
Start the data collection.
Viewing the report for the 1. data collection

And now what - viewing the reports in Server Manager is amazing but I want to see and calculate “real” numbers. So I use Relog.exe (this is built into Win 7, 2008, 2008 R2) to convert the .BLG files into a CSV file type. The Relog Syntax could be found here.

By using Excel, you could open the CSV file and convert the Disk Read Bytes/sec. Divide it by 1,024 to get your reads into MB/sec. Then convert the Disk Write Bytes /sec. Divide by 1,024 to get writes into MB/sec. Also divide AVG. Disk Bytes/Transfer by 1,024 to get I/O size in KB …

But there is another way, so go to CodePlex and use SQL Performance Analyzer - this tool gave you an overview of lots of counters that are collected by using perfmon.

Now I have a initial situation for, …

To be continued …

I wish you a nice day,
tosc