ConvertCounterData creates a derived table to place the performance-counter data into columns. Then, the procedure aggregates the data by using the GROUP BY clause. During aggregation, the procedure converts float data to numeric (18,2) to round the trailing decimal points to two so that they're easier to view. Notice the hour, minute, and second variables, which we use in our data access pages to let us drill down graphically into our data by hour, minute, and second. A typical data access page uses the hour, minute, and second fact tables, but those tables don't work for the three-dimensional data access page design we use. In a typical data access page, hours, minutes, and seconds are duplicated for every day you view. So a graph of 2 days would show 48 hours in a row on the X-axis, and a graph of 3 days would show 72 hours, including minutes and seconds, instead of representing all the days within the graph by using a common 24-hour time bar. You can circumvent this annoying behavior by adding the hours, minutes, and seconds variables that Listing 2 shows. Additionally, the procedure uses the RecordIndex variable to verify that it's importing only new data into MyCounterData from CounterData. Keep in mind that our counters might not be the counters you want to track. If not, you need to modify the ConvertCounterData procedure and the MyCounterData table to suit your needs.
If you're a keen observer, you've probably noticed that we're first truncating, then converting the CounterData table's CounterDateTime value to a datetime field. As I mentioned earlier, in setting up our solution, we found that CounterDateTime is a char(24) field by default, not a datetime field, which was disappointing—especially because Microsoft describes CounterDateTime as being stored in Universal Time Coordinate (UTC) format. Also, the System Monitor logging tacks an extra, hidden ASCII character to the CounterDateTime field, making datetime conversion impossible unless you first truncate the field to 23 characters—thereby removing the extra character. To remove the hidden character, we use the LEFT() function, as Listing 1's code shows. As you implement the ConvertCounterData procedure, verify that the CounterID numbers in your implementation match the proper counter names, as they do in our example. (For information about another possible bug in data access pages, see the sidebar "30-Second Timeout.")
After you've created ConvertCounterData, use a SQL Server Agent job to schedule it to run every 5 minutes. You might want to run the procedure manually at first to verify that it's importing rows from CounterData and CounterDetails. And run it as many times as you like—it won't import redundant data. Run the job manually at first and view the job history to verify that the job is functioning as intended. If you create queries to run against the MyCounterData, CounterData, and CounterDetails tables, you'll notice that you can pull data from MyCounterData faster than from CounterData and CounterDetails because data has been aggregated, consolidated, and indexed.
When you begin storing data in the MyCounterData table, you might realize another benefit besides added speed. If System Monitor corrupts its tables or recreates them, you've got your historical data intact in the MyCounterData table as an alternate source untouched by System Monitor.
Creating the Data Access Page
At this point in the implementation, you've created a database—DBADMIN—to store System Monitor data, created a DSN to connect to the DBADMIN database, and configured System Monitor to log data to DBADMIN. System Monitor has generated system tables in the DBADMIN database and started to capture data to them, but data retrieval from those tables is slow. To speed your data retrieval, you created a stored procedure, ConvertCounterData, to convert System Monitor table data into a table called MyCounterData where data retrieval is faster. You then scheduled the ConvertCounterData procedure to run every 5 minutes by using a SQL Server Agent job. Now the hard work is complete.
Next, you create the data access page, which is a relatively easy process. First, open Access, select New from the File menu, and click Blank Data Access Page in the New File window on the right. Then, click New Source in the resulting Select Data Source dialog box. Verify that Microsoft SQL Server is selected in the Data Connection Wizard's list box, and click Next. In the Data Connection Wizard dialog box, type your SQL Server's name in the Server name text box—we typed SQL2KDEV1. Be sure that the Use Windows 2000 Security option is selected, and click Next. In the Data Connection Wizard-Choose Data dialog box, select the DBADMIN database from the drop-down list, and click Next. Accept the Office DataSource Connection (.odc) filename in the Data Connection Wizard Finish dialog box, and click Finish.
Now, let's graph some collected SQL Server Cache Hit Ratio data to show how you can begin to graph similar System Monitor data. With the .odc file in the Select Data Source dialog box, click Open. A new data access page is now available for design.
Expand the design grid to six squares across by three squares down. Verify that the toolbox is visible (click the toolbox button on the toolbar if it's not), and click Office Chart on the Toolbox. Drag the Office chart from the upper left to the lower right to consume most of the grid. Double-click the Office Chart Object, and select the Data from a database table or query option on the Data Source tab in the Commands and Options dialog box.
Prev. page
1
2
[3]
4
next page 
