Using the overviews - SQL Monitor

The overviews in SQL Monitor show the current health of your monitored servers, and are arranged into a hierarchy:

The overview pages update automatically every 30 seconds. You do not need to hit Refresh in your browser.

You can drill down through the hierarchy in two ways:

• From the Monitored servers list
• By clicking a hyperlink on the current overview page to take you to a lower level (e.g. from a host machine to a SQL Server instance or from an instance to a database)

Global Overview

The Global Overview is the default front page for SQL Monitor. It presents a high-level summary of the health of all your servers, showing which servers have uncleared alerts raised against them, and what their current CPU and memory usage is. Servers that require attention are color-coded. From the Global Overview, you can quickly decide which server to investigate in more detail.

Following links from the Global Overview

• Click on any object (server or database) in the Monitored servers list to view the overview page for that object.
• Click on a group to filter the Global Overview to show only servers in the selected group.
• Click on the name of a host machine or SQL Server instance in the Servers column to go to the Overview page for that server. You can also click in the Status column.
• Click on a number in the alerts summary panel at the top of the page to go to the Alert Inbox pre-filtered by your selection (e.g. High or Unread) across all servers.

  

• Click on the colored bar chart under Uncleared alerts to go to the Alert Inbox to view alerts for that host machine or SQL Server instance:

  

• Click on a sparkline graph in the Processor time or Memory Used column to go the Analysis page for those counters, showing the last 10 minutes:

  

Cluster overview

The cluster overview shows all nodes in the cluster, and the SQL Server instances running on the cluster:

You can drill down from the Cluster overview to the Alert Inbox or Analysis page in the same way as from the Global Overview.

You can also see information about the quorum path and to which resource groups the various resources (including SQL Server instances) are allocated:

• The quorum is a shared network drive that controls which nodes host which resources, and maintains the configuration data necessary for recovery of the cluster. In SQL Server terms, this equates to which node is active for a particular instance.
• The resources are various entities that are capable of being managed by a cluster. A resource can only be owned by one node at a time.
• A resource group is a collection of resources that are managed as a single unit (e.g. the SQL Server group). During a failover, the groups is moved from one node to another node.

For more information, see Cluster fundamentals (MSDN article).

Host machine overview

The host machine overview is similar to the cluster overview. You can see the status for the machine itself, view all its monitored SQL Server instances, and follow links to the Alert Inbox.

• Click in the Name or Status column for an SQL Server instance to drill down to overview page for the selected instance.
• Click on the colored alert bar for a SQL Server instance to go to the Alert Inbox for all uncleared alerts relating to that instance
• Click on Uncleared alerts, Unread alerts or Last 24hrs in the alerts summary panel at the top of the page to go to the Alert Inbox for all alerts relating to the host machine and everything below it in the hierarchy (alerts for all SQL Server instances and databases)

What does the Status column mean?

The Status column shows the level of the highest uncleared alert on the machine or any level beneath. If there are no uncleared alerts then the status is OK.

The status also shows if the monitoring status is currently anything other than Monitoring (Connected):

If the Status column shows there is a problem with monitoring, for example, it displays Monitoring stopped or Unreachable, then do the following:

1. Click the Manage monitored servers link under the Monitored servers list on the left.
2. All your monitored servers are listed, and their current status. Click Show log for the server with the problematic status:

   

3. Review the log of data collection events to investigate the problem.

For more information about monitoring status, see Monitoring status explained.

Performance data on the host machine overview

The host machine overviews display various types of current information about your machine:

Each sparkline graph show the current numerical value (in bold) and the last five minutes performance trend. Click on a sparkline graph to go to the Analysis page for the selected counter, where you can view a larger graph and specify a time range:

Note: Processor time % and memory used graphs are not available for individual system processes on the Analysis page.

SQL Server instance overview

The SQL Server instance overview lists all the databases hosted on the instance and shows the status of each:

• Click in the Name or Status column for a database to drill down to the overview page for the selected database.
• Click on the colored alert bar for a database to go to the Alert Inbox for all uncleared alerts relating to that database
• Click on Uncleared alerts, Unread alerts or Last 24hrs in the alerts summary panel at the top of the page to go to the Alert Inbox for all alerts relating to this SQL Server instance (and all its databases)

It shows various types of current information about the instance itself and and performance data for the last five minutes for a range of useful counters. As for the other overviews, click on any sparkline graph to go to the Analysis page for that counter.

Top 10 expensive queries

Note: This feature is not available for servers running on Microsoft SQL Server 2000.

The SQL Server instance overview displays the top 10 queries that used the most resource over a selected period of time. This data helps you to evaluate the performance of queries and the efficiency of I/O usage, and can be checked to see what queries were running around the time certain alerts were triggered.

Click on the query text to display the full query together with the identifier for the query plan from which it originates:

What data is displayed?

The following query data is displayed:

• Execution count - the number of times this query statement was executed. By default, queries are listed in descending order according to this metric.
• Duration - how long it took in milliseconds to execute the query.
• CPU time - how much processor time in milliseconds was used to execute the query.
• Physical reads - the number of times a page is read into the buffer cache. If the page is in the cache already, it uses the page already in memory and does not generate a physical read.
• Logical reads - the number of times the database engine requested a page from the buffer cache.
• Logical writes - the number of times data is modified in a page in memory. If a page stays in memory for an extended period, more than one logical write may be required before it is physically written to disk.

The database that each query was run against is displayed in the Database column.

Updating the list of queries

The data in the table is automatically updated every 60 seconds. You can also update the list as follows:

• Select a different time period from the drop-down list above the table. Time periods range from the last five minutes (selected by default), to the last three days.
• Click Totals to display queries based on total values.
• Click Avg. per execution (selected by default) to display queries based on average metrics over the time period selected. Note: This does not affect Execution count which always displays total values.
• Click on a different column heading to display queries based on that metric in descending order.

Note: Selecting one of these options does not simply change the sort order of the existing list of queries. A new table is generated according to the selected option, so different queries are likely to be displayed.

Database overview

The database overview is the lowest level in the overview hierarchy. It shows information about the database, its general properties, its data and log file sizes and locations, its top 10 most expensive queries and also any backups:

Note: The top 10 expensive queries are not available for servers running on Microsoft SQL Server 2000.

Why aren't all my backups listed?

The backups section shows only the most recent backup of each type:

Viewing the full path to data files

The path to your data and log files is likely to be truncated on screen. To see the full path, move your mouse pointer over it to display a tooltip: