SQL Server 2012 : Measuring SQL Server Performance (part 5) - Viewing the Data Collector Data - Server Activity History

2/20/2014 8:56:56 PM

6. Viewing the Data Collector Data

The data collector is now running on the server where you designated it. It’s collecting the data and storing it in the cache directory you defined. To start to view this data, you need to first get it from the cache directory to the management data warehouse. All the collection jobs are set up and running, but they’re not transmitting the data collected, and this transmission must be started. Initially, you can do this manually. Later, you may want to set up scheduled jobs through the SQL Agent to gather the data collector data. To get the data manually, right-click any of the defined data collection sets, and choose Collect and Upload Now from the context menu. This will take the data from the disk cache and load it into the management data warehouse. It’s now ready for viewing. You should perform this step if you’re following along to have data visible in the next steps.

The data is available in standard tables, so you could access it directly through SQL queries if you wanted. However, the data collector comes installed with a few standardized reports. If you right-click the data collector icon in the Object Explorer window, you can select Reports from the context menu and then select Management Data Warehouse from there. With the default install, three reports are available:

  • Server Activity History
  • Disk Usage Summary
  • Query Statistics History
Server Activity History

If you select the first item in the list, Server Activity History, you’ll see a window with a number of charts. All this information represents basic server-level data, showing information such as the amount of CPU or memory used, what processes are waiting when running inside SQL Server, and the aggregated number of activities over time. All of this information is very useful for performance monitoring and tuning. There are so many charts that we can’t show them all in a single figure. The top half of the report will look like Figure 10.


Figure 10. Server Activity History report, top section

At the top, you can see which server the data is being displayed for. Below that is the time period for which the data is being displayed, and you can modify or scroll through the various time periods using the controls provided. As you change the time period, the other graphs on the chart will change. Immediately below the controls, the time range currently selected is displayed. In the case of Figure 10, it’s showing a time range between “4/24/2009 2:00AM” and “4/24/2009 6:00AM.”

Next are a series of graphs showing different information about the system. Each graph, where appropriate, shows information about the operating system and SQL Server, color-coded as green and blue, respectively. These graphs are nice, but if you need detailed information, you need to click one of the lines. Figure 11 shows the detail screen for the % CPU for the system.


Figure 11. % CPU Time per processor details report

You can see that once more you have the ability to change the time frame for the report. It also displays a nice graph, even breaking down the behavior by CPU and, finally, showing aggregate numbers for % CPU over the time period. Each of the separate graphs in the main window will show different detail screens with more information specific to that performance metric. To navigate back to the main report, you click the Navigate Backward button located near the top of the window.

Back on the main window of the Server Activity History report, the bottom half of the report looks like Figure 12.


Figure 12. Server Activity History report, bottom section

Once again, these are incredibly useful reports all by themselves, showing the SQL Server waits broken down by wait type and showing SQL Server activity broken down by process type. With either of these reports, it is possible, as before, to select one of the lines or bars of data and get a drill-down menu showing more information.

Note Initially, immediately after starting the data collector, your graphs might be quite empty. As more data accumulates, the graphs will fill in. You can also use the Timeline tool to zoom in on smaller time slices to get a more complete picture.

  •  SQL Server 2012 Security : How Hackers Attack SQL Server
  •  SQL Server 2012 Security : Partially Contained Databases
  •  SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views
  •  SQL Server 2012 : SQL Server Audit (part 2) - Auditing Server Events, Auditing Database Events
  •  SQL Server 2012 : SQL Server Audit (part 1) - Creating an Audit Object, Recording Audits to the File System
  •  SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate
  •  SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE
  •  SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest
  •  SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move
  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th