Online Documentation for SQL Administrator for SQL Server

Collected Statistics

This node of the Performance section shows you time diagrams of the collected statistics. But unlike the Current Statistics it is collected via Statistics Collector service. It records the data (performance counters values) about SQL server productivity to the defined repository database. Diagrams are graphed on the basis of the data stored in that database.

 

These statistics help you to learn about the current SQL Server productivity as well as the Current Statistics do, but also allow you to compare the server productivity performance within the defined time intervals.

Note: Gaps on diagrams indicate periods of time when the Statistics Collector service was not running.

 

 

 

Initially the work area is separated into six sections:

• Processor. % Processor Time. It displays a diagram of the processor time, that is the percentage of time the processor is busy by measuring the percentage of time the thread of the Idle process is running and then subtracting that from 100 percent. This measurement is the amount of processor utilization.
• Memory. Available (MB). This diagram shows the amount of memory that is left after nonpaged pool allocations, paged pool allocations, process' working sets, and the file system cache have all taken their piece.
• Memory. Pages/sec. The Pages/sec diagram shows the rate at which pages are read from or written to disk to resolve hard page faults.
• Physical Disk. % Disk Time. This diagram is a general mark of how busy the disk is. It indicates a disk problem, but must be observed in conjunction with the Disk Queue Length counter to be truly informative.
• Physical Disk. Avg. Disk Queue Length. This counter converts the % Disk Time to a decimal value and displays it.
• SQL Server: Plan Cache. Object Plans Cache Hit Ratio. Ratio between cache hits and lookups.

 

You can enlarge the particular section by double-clicking it.

 

Use the toolbar items to manage the displayed statistics:

• Chart View - select the necessary set of the diagrams

• Date Intervals - select the period during which you want to view collected statistics

set counter instance filter, i.e. set disks for some counters

• Smoothing - use the '+' / '-' buttons or just move the runner of this control to set the preferable graph smoothing

create and print the report

 

Setting chart view

Using this section you can set the groups of charts that then could be selected in the Chart View field.

The context menu of the section allows you to:

create chart view

rename chart view

delete chart view

move up

move down

 

Charts

Use this section to manage charts in the chart view:

create chart

rename chart

delete chart

move up

move down

 

When creating a new chart the Performance Counter window appears:

 

 

 

It lists groups of performance counters that help you to determine the performance of the processor, logical and physical disks, memory, server etc. To create a new chart select the necessary performance counter from the list and click the 'OK' button.

 

Counters

Use this section to manage performance counters of the selected chart:

add counter

remove counter

move up

move down

 

The Performance Counter window appears on the adding a counter action.

 

Note: Some statistics are not collected if the WMI Connection to the server is not set.

 

Setting date interval

When configuring the date interval you can add the preferable period manually by clicking the button or edit an existing one by clicking the button. These buttons open the Date Intervals dialog.

 

 

 

This window shows date intervals of the time domain. So, here you can define at which periods the collected statistics should be displayed.

The context menu of the list allows you to:

create date interval

edit date interval

delete date interval

move up

move down

 

The following window appears on adding or editing a date interval:

 

 

 

First, select the source: Source - detailed statistics or Source - average statistics.

In the first case use the Start time and End time fields to set the time interval. You can enter it manually or pick up a date from the drop-down calendar.

 

In the second case set the Year and Month in the corresponding fields to display average statistics collected in this month.

 

Note: If you set several date intervals then the first one will be displayed on the horizontal axis. If there are longer intervals in the set then a difference between its length and the first interval length will be added to the axis. So the length is equal to the duration of the longest interval but the start point is the start time of the first interval of the set.

Also, if you have set an average statistics interval among the current statistics intervals then the average statistics is displayed in compliance with the longest interval.