Session 6: Using Operating System Performance Analysis in Performance Analyst for Microsoft

From DBArtisan
Jump to: navigation, search

Go Up to Performance Analyst for Microsoft SQL Server Tutorial

In many scenarios, an optimally tuned database may not perform well because there are constraints imposed by the system where the database is running. These constraints may include processes competing with the database sever for resources (CPU, I/O or Memory), a slow CPU, insufficient I/O devices, and insufficient memory. Performance Analyst allows the DBA to examine the relevant operating system metrics for Windows Platforms (NT /2000/XP/2003) as they relate to SQL Server.

To view the operating system metrics of the machine hosting the SQL Server you are monitoring, do the following:

  1. Click the OS Performance Analyst toolbar button.
    OR
  2. Navigate to the OS Home Page by going to OS, and then Home.
    Note: The first time you invoke the OS feature you will be prompted for an operating system login. Please specify the correct operating system, host name, username & password and protocol.

The Operating System home page has been designed in a similar fashion as the datasource home page. On this page you can see the pertinent operating system metrics displayed in five quadrants broken down into the following categories:

  • Resource Usage
  • Memory Analysis
  • Disk Analysis
  • Workload Analysis
  • Bottleneck Analysis

Obtaining Details on the Operating System Using Performance Analyst for Microsoft

You can easily obtain more information regarding operating system problems that are degrading database performance by drilling down into a particular analysis quadrant. For example if you'd like to see more information regarding the processes running on the host machine, do the following:

  1. Navigate to the Performance Analyst Operating System home page.
  2. Double-click the Top Memory Processes in the Workload Analysis quadrant.
    Performance Analyst opens the Processes Drilldown view.
    OR
  3. Right-click anywhere in Performance Analyst and select the Navigate to, OS, and then Processes.

The Processes detail tab shows all the processes on the host machine. From this view you can easily determine which processes are showing high marks in I/O or CPU, for example, by simply double clicking on the column headers and sorting the processes in ascending or descending order.

Proceed to Session 7: Identifying and Getting Help With Performance Problems Using Performance Analyst for Microsoft