After selecting all the counters you want to monitor, click Close. On the same General tab, change the interval to 1 second instead of the default 15 seconds to make your counter logging more granular. After you click OK, the counter will turn from red to green, meaning that it started up automatically. You need to right-click the counter and select Stop. You'll start this counter log at the beginning of each test.
Performance Monitor automatically creates a new file under your C:\PerfLogs drive each time you start a counter log. The log name will be the same name as your counter log, with a six-digit number attached (e.g., MyLog_000002.blg). The system automatically increments the number in the file names each time you start and stop your counter log. Make sure to track the file name for each test you run so that you don't compare the wrong results.
To view your counter log results, start Performance Monitor. In the left pane, click System Monitor. In the right pane, click the Properties icon (a hand pointing at a piece of paper). In the System Monitor Properties dialog box, select the Source tab. In the Data source section, select Log file instead of Current activity. Click Browse, select the counter log from the C:\PerfLogs folder, then click Open. Next, select the Data tab and click Add. In the Add Counters screen, select each counter you want to view results for (and its associated instances) and click Add. When you're finished, click Close.
Next, go to the General tab and make sure either Default or Average is selected in the Report and histogram data section. Both Default and Average show the average results for each counter. Click OK. In the right pane, select the View Report icon (piece of paper). The resulting window shows your average data results for the test, as Figure 3 shows. I exported my results into a Microsoft Excel spreadsheet for easier comparison. (Right-click the results window and select Save As. In the Save As dialog box, select the file destination path and file name. Select Save As Type "Report (*.tsv)" to make the file readable by Excel. Double-click the .tsv file and select Microsoft Excel for Windows in the Open With dialog box.)
Setting Up Database Hammer
All the necessary Database Hammer files are in the \ToolsAndSamples\DatabaseHammer folder on the SQL Server 2000 Resource Kit CD-ROM. You need the VB runtime msvbvm60.dll on your client before you can run the executables. The SQL Server Client Utilities install the necessary VB runtime files. You also need Windows 2000 Server and SQL Server 2000 to run Database Hammer. According to the documentation, VB 6.0 is required for modification of the Database Hammer code.
To set up the test, first copy all Database Hammer files from their folder on the CD to a folder on your local server's hard drive. Next, create the test database. In my case, I had to create two databases to test: one database that had the data and log file on the same SAN partition, and one where the data and log files were on separate SAN partitions. To prevent excessive file auto-growths that can slow down the initial database load, set the size of the data file and the transaction log file to 500MB.
After creating the database, create a SQL Server user login (not a Windows login) and give this user dbo permissions for your test database by making the user the dbo or adding the user to the db_owner database role.
Open Query Analyzer and, from the toolbar, select the database you just created. Next, run the createdb.txt script, which you can find in the DatabaseHammer folder. Unlike the file name suggests, createdb.txt creates the TestTransaction table, not a database. Next, run the storedProcs.txt file in your selected database to create two stored procedures, which the VB sample code will use to perform SELECT and UPDATE queries against the TestTransaction table.
Double-click LoadSlave.exe to start the VB application in the background. (No UI is associated with this executable.) The loading of data isn't part of the official Database Hammer test; however, you might want to track and compare the total load time between tests. Keep in mind that running the data load from a client will take more time than running it from the server.
To complete the loading process, double-click LoadMaster.exe in the DatabaseHammer folder. Figure 4 shows the UI that appears. In this box, you enter the names of the server and the database that you'll be testing. You must also enter the SQL Server user you created previously. LoadMaster.exe uses LoadSlave.exe as an out-of-process COM object and is therefore dependent on LoadSlave.exe being started first. Before you continue, be advised that by clicking Start, you begin inserting 10 million rows of test data into the TestTransaction table. This process takes a while (for my test, it took almost 3 hours) and produces close to 400MB of data, so make sure you have sufficient disk space and database space allocated or auto-growth set so that your data load doesn't stop prematurely. (You can set auto-growth in the Database Properties dialog box through Enterprise Manager or by using the ALTER DATABASE command.)
One "gotcha" here is that LoadMaster.exe doesn't shut down or notify you after loading the 10 million rows. The application stops at 10 million rows, but you'll get no visual notification of when to shut down the UI. To get around this shortcoming, periodically run
SELECT COUNT(*) FROM dbo.TestTransaction
WITH (NOLOCK)
in Query Analyzer to see whether it's time to end the application. Another quick way you can check is to query the sysindexes table for the number of rows in the TestTransaction table.
I found it enlightening to have my performance-counter log running during the data load. I was curious to see which database would load faster. Figure 5 shows the results of my record load in both test scenarios (data and log on separate partitions versus data and log on the same partition). As you can see, the data load ran 28 minutes faster on the database that had the log and data file on the same partition. The only other significant difference was the higher SQL Server Average Latch Wait time (975.657ms on the database with the data and log on separate partitions versus 461.306ms on the database with data and log on the same partition).
Prev. page
1
[2]
3
next page 
