SQL Server statistics

Important remark

Warning, this type of function facilitates, via a user interface homogenous with the software, the launch of live procedures dedicated to a database admin. It is made to be launched by such an admin (usually called DBA).

To be used optimally, the user should be familiar with the functioning of databases and their optimization.

Conversely, an inappropriate use of the function can be problematic for the performances of the system and might compromise the utilization of the software.

Header
Tab Index
Tab Base

Screen management

This function is used to visualize the statistics generated on the data base when the data base is SQL Server™.
These elements are displayed on two tabs: the first tab shows the list of indexes and the status of statistics for each index, the second tab shows the list of database parameters relating to the generation of statistics.

Header

Presentation

Used to identify the characteristics of the database used.

Close

Fields

The following fields are present on this tab :

Database (field BDDNAM)

Name of the database.

Version (field VER)

Version number of the database.

field VERDES

Description of the database version.

Close

Tab Index

Presentation

This tab displays the list of indexes and the statistic report for each of them.

Close

Fields

The following fields are present on this tab :

Grid

To process (field PROFLG)

Used to define if the line must be processed in generation of the statistics, using the corresponding button at the bottom of the screen.

Table (field TAB)

Name of the table in the database.

Index (field INDEX)

Name of the index in the database

Number of lines (field NBRLIN)

Number of lines in the table corresponding to the index (count)

Automatitic statistics (field STTAUT)

Indicate if the statistics are automatically generated for the index.

Corresponds to the database parameter automatic UPDATE STATISTICS for the index.

Statistics generated (field STTFLG)

Indicate if the valid statistics are generated in the database.

Last analysis date (field STTDAT)

Date of the last analysis of the statistics

field STTHOU

Time of the last analysis of the statistics

Close

It is possible to generate statistics:

via right click for an index,
for all indexes or a part of the indexes, by selecting the requested lines before triggering the action via the button at the bottom at the page.

Action icon

Yes to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer Yes to all the lines starting from the current line.

No to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer No to all the lines starting from the current line.

Refresh the line

Used to refresh the display of the grid's current line

Generate for the line

Used to generate statistics for the grid's current line.

Close

Tab Base

Presentation

This tab displays the list of database parameters relating to the automatic generation of statistics.

Close

Fields

The following fields are present on this tab :

Grid Statistics parameters

Parameter (field PAR)

Display the options set on the database for the automatic generation of the statistics.

The options are as follows :

Option	Description
auto create statistics	If true, the missing statistics, required by a request for the optimization, are automatically created during the optimization.
auto update statistics	If true, the statistics not updated, required by a request for the optimization, are automatically created during the optimization.

Value (field PARVAL)

Close

It is not possible to act on these values.

Refresh
Generate

Specific Buttons

This button, also accessible using the short cut, used to refresh the display of the tabs.

Can be used to generate statistics for all the requested lines in the table of the Index tab.

Explanation of the Sql Server statistics

When do the statistics need to be updated?

SQL Server saves the statistics of key value distribution in each index and uses these statistics to determine the indexes to be used in the processing of queries.

Therefore, it can be useful to update the statistics if

major modifications are made to the key value of the index.
A big share of data for an indexed column has been added, modified or deleted (that is to say, if you think the distribution of key values has changed)

Why ?

With Microsoft® SQL Server™, it is possible to create statistic information relating to the distribution of key values in a column that must be created. These statistics can be exploited by the query processor in order to define the best strategy for the evaluation of a query. When an index is created, SQL Server automatically stores the statistic information relating to the distribution of values in the indexed column(s). The SQL Server query optimizer uses these statistics to estimate the use cost of the index for a query. Moreover, if the AUTO_CREATE_STATISTICS database option is activated (ON), which is the case by default, SQL Server automatically creates statistics for columns without index used in a predicate.

If data of a column change, the statistics of the index and the column may become obsolete and thus, they may lead the query optimizer to take wrong decisions when it comes to processing a query. For instance, if a table is created with an indexed column and 1.000 lines of data, all of them containing unique values in the indexed column, the query optimizer considers that the indexed column is a "good" solution to receive the data for a query. If the data of a column are updated by creating several duplicated values, the column is no longer a perfect candidate to be used in a query. However, the query optimizer still considers that it is a good candidate based on the obsolete distribution statistics of the index that were based on the data before update.

From then on, the SQL Server automatically and regularly updates the statistic information, as data contained in the tables are modified. The sampling is carried out randomly through the pages of data and it comes from the table or the smallest index not ordered in clusters on the columns required for the statistics. After the reading of a page of data from the disk, all the lines of the page are used to update the statistic information. The update frequency of these statistics depends on the volume of data contained in the column of the index and it also depends on the quantity of variable data.

SQL Server always guarantees the sampling of a minimum number of lines. Tables whose size is lower than 8 Mo are always subject to a complete analysis in order to elaborate statistics.

The cost of this automatic update of statistics is minimized thanks to the sampling of date; not all of these are analysed.

Technical information

All information on tables, indexes and statistics are read directly in the database via SQL Server tables and views:

sysusers
sysobjects
sysindexes

The dates of statistics are obtained using the STATS_DATE function.

The information about the automatic generation of statistics on the indexes is obtained using the sp_autostats process.

The information about the automatic management of statistics on the database is obtained using the sp_dboption process.

The statistics are updated using the UPDATE STATISTICS instruction.

Generic messages

Error messages

The only error messages are the generic ones.

Tables used

SEEREFERTTO Refer to documentation Implementation