Next, you configure System Monitor to connect to the DBADMIN database. To begin, click the Start menu and open the Run command on the Win.NET Server or XP workstation. Run perfmon.exe to open System Monitor. Expand Performance Logs and Alerts, and right-click Counter Logs. Select New Log Settings from the context menu. In the New Log Settings dialog box, type the name of the log as StorePerfSQL and click OK. Then, on the General tab, click Add Counters to add counters from a particular SQL Server 2000 instance (running on Win2K) that you want to monitor. Table 1 shows examples of the kinds of counters you might monitor.
Choose the interval for which you want to log information. For testing purposes, you might want to use the default of 15 seconds or less. However, for production purposes, you should consider logging data at 1-minute intervals or greater to reduce the logging traffic on the network and to reduce the number of aggregations that your queries perform.
Next, you need to configure System Monitor logging to properly authenticate to SQL Server by using a trusted connection. For example, in the Run As text box, we typed the name of our test domain's account, Q\administrator. Because this user is part of the Q domain and not a SQL Server login, SQL Server uses Windows authentication to "trust" that the account is valid. Then, click Set Password, and enter the appropriate password for the domain user.
After you complete the General tab, you need to configure where you want your data to be stored. On the Log Files tab, select the SQL Database Log file type, click Configure, select the system DSN StorePerfSQL from the System DSN drop-down list, and click OK. Next, click the Schedule tab, select Apply to start the performance logging, and click OK.
If you choose to start the log manually, you also have to restart the logging manually when your logging server or workstation reboots. However, if you start the logs by using the At option in the Start log section of the Schedule tab, the OS will automatically start the logs for you when the system restarts.
Verifying the Table Structures and Data
You can verify whether you correctly configured the logging by checking a couple of items. First, verify that the logging icon on the logging machine's System Monitor is green. If the icon is red after you start System Monitor, review the application event log for associated error messages. Second, if logging is configured correctly, you'll be able to retrieve data from the DBADMIN database tables CounterData, CounterDetails, and DisplayToID, which the System Monitor configuration created. If these tables exist, it's a good sign that System Monitor will be subsequently logging data into them. Specifically, you should be able to select data from the CounterData table and review the CounterDateTime field for the latest logging entries. For example, we ran the following SELECT statement to retrieve the latest logging entries:
SELECT TOP 10 *
FROM CounterData
ORDER BY CounterDateTime DESC
If you find that you still can't successfully log System Monitor data but the CounterData, CounterDetails, and DisplayToID tables exist, you might want to try deleting these tables and letting System Monitor recreate them. Remember to archive any data you want to keep before deleting the tables. Microsoft doesn't support modifying system tables, and doing so might cause System Monitor to stop logging data, so we recommend that you don't alter these tables.
Data Access Pages and Slow Queries
At this point, if you look at the logged performance data contained in the DBADMIN database on SQL Server, you'll notice that it looks much different from a System Monitor Comma Separated Value (CSV) file. A CSV file contains column headings representing counter names, followed by rows with timestamps and counter data.
To make this data suitable for graphing, you need to massage the data somewhat. For example, we found that we can't create effective queries that pull data directly from the CounterData and CounterDetails tables; execution of such queries is slow and inefficient for several reasons, including lack of indexes. (For information about how we address this problem, see the sidebar "Improving Query Performance.") Specifically, the CounterDateTime field is an unindexed char(24) field, which makes pulling data from a date range a costly process.
Additionally, to manipulate CounterDateTime as a datetime field, you must truncate the hidden ASCII character at the end of CounterDateTime before converting to a datetime data type to avoid conversion errors. At first, we created a view to perform the necessary data manipulation. But we found that the view execution time was too long because of the unindexed source tables, the datetime conversion, and the sheer amount of data being returned. To speed data retrieval, we would have liked System Monitor to create the logging tables with the appropriate foreign key indexes, create indexes on columns that are used as possible WHERE criteria, and store the date and time information in datetime format rather than character format. But doing all these things might have significantly slowed inserts to the logging tables. Again, we recommend that you don't modify the logging tables that System Monitor creates, especially if you want Microsoft support for your implementation. To work around these shortcomings, we created a stored procedure called ConvertCounterData—which Listing 1, page 32, shows—in the DBADMIN database to port the data in the streamlined format to another table called MyCounterData. Listing 2, page 32, shows the code that creates the MyCounterData table.
Prev. page
1
[2]
3
4
next page 
