Starting the Load Test
To begin the load test, first double-click the ProcSlave.exe application in the Database Hammer folder you created. You can run both the ProcSlave and ProcMaster applications on the server or on the client machine to simulate one client with multiple connections. You can also run several ProcSlave and ProcMaster applications on different client computers simultaneously if you want to test a multiple-client, multiple-connection client/server setup.
Second, double-click the ProcMaster.exe application. The UI is the same as the LoadMaster interface, but in the lower left corner you'll see a Stats button, which is grayed out until your test begins. Also, the Instances field is no longer grayed out. The application will spawn as many instances of the ProcSlave application as you select. By default, half the statements call the UPDATE stored procedure and the rest call the SELECT stored procedure. During the test, you can view the statistics for each application instance by clicking the Stats button. Spawning all the connections takes a few minutes (my test took 6 minutes to spawn 500 connections), so keep an eye on the number of connections to make sure you're running counters at the desired thresholds. Once the number of connections reached the number of instances I'd selected in the ProcMaster interface, I started the counter log. To start your counter log, right-click the counter log you created and select Start. To stop, right-click the counter log you created and select Stop.
In my test, I wanted to measure 5 minutes of activity for each scenario. Before stopping the ProcMaster executable, I stopped the counter log at the 5-minute mark so that the disconnection of the slave processes wouldn't affect the averages. To stop the test, click the Pause button in the Database Hammer window (ProcMaster.exe) and close the window.
Because Microsoft provides the VB files for the Database Hammer application, you can customize the code as you need to. For example, you might want to change the SELECT-to-UPDATE ratio or perhaps institute a more complicated command against the table to reflect likely transactions in the production environment. You can change the ratio within the GetCommand procedure in the ProcSlaveClass.cls class module. However, I found that the sample test load fit my needs well.
My Test Scenarios
Before looking at the results of my Database Hammer test, let's review the test scenarios I used. I started by creating the test database with the log and data files on the same SAN partition. Then, I loaded the 10 million records, being careful to record the start and finish times, and kept the performance-counter log running during the load. I then restarted the SQL Server service to ensure that SQL Server caching wouldn't affect the test. Next, I ran the Database Hammer test from my client for three scenarios: 100, 500, and 800 connections. I'd originally decided to test 1000 connections, but my client machine started giving me "thread creation" errors at around 850 connections. (The application couldn't spawn more than 850 connections from my client.) I made sure that the performance-counter log measured activity only when all connections were spawned, shutting it off before stopping the test and deallocating the connections. I also restarted SQL Server before the start of each test. I then created a new test database with the log and data files on separate partitions. Using this database, I repeated steps 2 through 4 (i.e., loading 10 million records, restarting the SQL Server service, and running the Database Hammer test for 100, 500, and 800 connections).
Coupled with information from Performance Monitor counter logs, the Database Hammer was an excellent tool for performing stress tests and revealed performance data that I didn't expect. Figure 6 shows the test results; the figures highlighted in red are the counter-log results that displayed the biggest differences. The single-partition SAN database displayed a lower Average Disk Queue Length for all three tests. Average Disk Queue Length measures the number of read and write requests that are queued for an array. For this RAID 5 array, the number includes average queued read and write activity across all disks in the array. The rule is to divide the Average Disk Queue Length by the number of physical disks making up the array; if that average result exceeds 2, you might have an I/O bottleneck. This counter's results were surprising in that one array would presumably be more of an I/O bottleneck than two arrays.
Beginning with the 500- and 800-connection tests, SQL Server Average Latch Wait time was also significantly lower on the single-partition SAN database than on the multi-partition SAN database. Higher Average Latch Wait time values can suggest either memory or I/O problems. The normal value for Average Latch Wait time depends on your hardware, database design, and activity. To determine whether either test (single-partition or multi-partition) suggests an I/O bottleneck would require further investigation, but the results do show that the single-partition configuration had significantly less latching and fewer queued I/O operations for the same Database Hammer tests.
To make sure these test results weren't a fluke, I ran them each three times, at different times of the day. All tests displayed similar results. These tests showed that for my setup and system, placing the data and log file on separate SAN partitions results in more I/O than placing both files on the same partition. I believe these results could change as we add more applications and databases to the SAN. Unlike with local disk arrays, when considering SAN performance, you must consider the impact of the SAN cache controller, SAN bandwidth limitations, traffic on the SAN caused by other applications, and algorithms used to optimize the cache and RAID array performance. As our environment changes and more databases are added to the SAN, I'll use Database Hammer and Performance Monitor again to verify whether conditions and I/O behaviors have changed.
End of Article
Prev. page
1
2
[3]
next page -->
