I work as a contract DBA for a large research and manufacturing company. My manager recently asked me to find out which database configuration was most effective with our new large RAID 5 Storage Area Network (SAN). The vendor for this SAN didn't have any documented SQL Server 2000 database file-placement recommendations or best practices. I needed to test whether placing the data and log files on separate RAID 5 SAN partitions produced any performance benefits over placing both files on a single RAID 5 SAN partition. Usually, placing database and log files on separate arrays (RAID 5 or RAID 10 for data and RAID 1 for the log) boosts performance. Because SQL Server writes to the transaction log file sequentially and the data files randomly, separating the files lessens the chance of simultaneous read or write contention.
The SAN I was testing took advantage of built-in caching—RAM that resides on the disk controller itself—so I didn't want to assume that the standard rules applied. Some SANs use caching algorithms in the cache controller to help facilitate the movement of data blocks (read-aheads) and writes to the controller cache (memory). For example, the SAN I tested has an algorithm that reads data into the cache based on past I/O request patterns, in an attempt to predict future read requests. Another algorithm gathers sequential writes in cache and attempts to write them to disk in large blocks or full stripes. The desired result of these caching algorithms is to reduce I/O overhead.
To test the database server under various load conditions, I used the Database Hammer tool that's included in the SQL Server 2000 Resource Kit. Database Hammer is a Visual Basic (VB) 6.0 code sample that creates a table, populates the table with 10 million rows of data, then lets you simulate hundreds of concurrent SELECT and UPDATE operations. Database Hammer is great for stress-testing a database; it lets you spawn multiple connections that each issue random read/write operations (the ratio of which you can configure). The tool also records client/server performance statistics. These statistics show current, minimum, maximum, and average response time (in milliseconds) of the stored procedure call (either a Read or a Write) for each slave process. Although this information is good for measuring client/server response time, I was most interested in seeing the stress test's impact on I/O. I decided to set up my own Performance Monitor counter logs to capture this I/O information as well as memory, CPU, and buffer cache statistics.
Let's look briefly at the counters I used in my tests, review how to set up the counter logs, and see how to run the Database Hammer tests. Then, I'll show you the surprising results I got.
Setting Up the Performance Monitor Counters
During each test load, I wanted to measure the performance counters that Table 1 shows. My primary goal was to see which database performed better under excessive I/O activity; for this, I used the Physical Disk counter Average Disk Queue Length and the SQL Server Latches counter Average Latch Wait Time. I also wanted to see what impact the SAN data file partitioning would have on the CPU, RAM, and the SQL Server buffer cache hit ratio. For CPU, I measured the Processor counter % Processor Time. For RAM, I used the Memory counter Pages/Sec. For SQL Server buffer cache hit ratio, I used the SQL Server Buffer Manager counter Buffer Cache Hit Ratio.
To set up a Performance Monitor counter log to use during your Database Hammer tests, first make sure your Performance Logs and Alerts service is running under a domain account that has administrator permissions to access the server you're testing. You can verify this by clicking Start, Settings, Control Panel, Administrative Tools, then double-clicking Services. Double-click Performance Logs and Alerts in the right pane, then go to the Log On tab, which Figure 1 shows, and see which account the service is using. If you're running Performance Monitor from the client and not the server, verify that the Performance Logs and Alerts service account is configured with a domain account that has administrator access to the server. I recommend running your performance-counter logs and Database Hammer tests on a separate client from the test server so that you can test under true client/server conditions. Close the Performance Logs and Alerts dialog box when you're finished, then right-click Performance Logs and Alerts and select Start to start this service.
Next, start Performance Monitor: From your desktop, click Start, Run, and type "perfmon," then click OK. You can also start Performance Monitor from your desktop by selecting Start, Administrative Tools, Performance. Expand Performance Logs and Alerts in the left pane and select Counter Logs. Right-click Counter Logs; then, in the New Log Settings dialog box, type in a name for your counter log, and click OK.
To add a new performance counter to your counter log, in the General tab of the Counter Log dialog box, click Add. Type the test server name (e.g., \\servername) in the Select counters from computer: field and select the objects and associated counters that you want to monitor, as Figure 2 shows. Note that some counters have multiple instances. For example, for the Processor object's % Processor Time counter, if your server has four CPUs, you can choose one, some, or all of the CPUs. In my tests, I monitored all processors. For the Physical Disk object's Avg. Disk Queue Length counter, I selected only one SAN partition for the first database test, placing both files on one drive, and selected both SAN partitions for the two-partition database test.
Prev. page
[1]
2
3
next page 
