Development > Utilities > Verifications > Database statistics > SQL Server statistics 

This function is used to view the statistics generated for the database when the database used is SQL Server™.

It is also used to update the statistics.

Important remark

Warning, this type of functionfacilitates, via a user interface that is homogenous with the software, the launch of procedures devolved to a database administrator. It is constructed to be launched by such an administrator (normally called DBA).

It therefore assumes a prior knowledge of database functioning and optimization in order to be used profitably.

However, an inappropriate usage of the function may pose serious performance problems and place the security of the software usage in peril.

Screen management

This function is used to display and to generate or delete the statistics on the database.
These elements are presented over two tabs : the first gives a list of the index and the statistics status for each, the second gives the list of the database parameters linked to the generation of the 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)

Nom de la base de données

  • Version (field VER)

Numéro de version de la base de données

  • field VERDES

Description de la version de la base de données

Close

 

Tab Index

Presentation

This tab displays the list of indexes and the status of the statistics for each of these.

Close

 

Fields

The following fields are present on this tab :

Table

  • 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 the statistics :

    • for a index by right click,
    • for all or some of the indexes by selecting the line required before triggering the action using the button at the bottom of the screen

AFC/APATCHA/40/210

AFC/APATCHA/40/215

Functions accessed by right click on the grid

Refresh the line

Used to refresh the display of the current line in the grid

Generate for the line

Used to generate the statistics for the current line in the grid

 

Fermer

 

Tab Basis

Presentation

This tab presents the list of database parameters linked to the automatic generation of statistics.

Close

 

Fields

The following fields are present on this tab :

Table 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 operate on these values here.

Specific Buttons

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

Used to generate the statistics for all the selected lines in the grid in the Index tab.

Explanation of the Sql Server statistics

When must the statistics be updated ?

SQL Server conserves the statistics on the distribution of the key values in each index and uses these statistics to determine the indexes to be used in the requests process.

Therefore it can be useful to update the statistics if

    • major modifications are carried out on the key values in the index.
    • a large amount of data for an indexed column has been added, modified or deleted (in other words, if it is believed that the distribution of the key values has changed)

Why?

Microsoft® SQL Server™ is used to create statistical information on the distribution of the values in a column that must be created. These statistics can be used by the request processor in order to determine the optimal strategy for the evaluation of a request. When an index is created, SQL Server automatically stores the statistical information related to the distribution of the values in the indexed column(s). The requests optimizer in SQL Server uses these statistics to estimate the cost of the usage of the index for the request. In addition, if the database option AUTO_CREATE_STATISTICS is activated (ON), which is the default case, SQL Server automatically creates the statistics for the columns without index in a predicate.

If the data in a column changes, the statistics for the index and the column risk becoming obsolete and therefore lead the request optimizer to take potentially risky decisions on how to process a request. For example, if a table is created with an indexed column and 1000 data lines, all containing unique values in the indexed column, the request optimizer considers that the index columns construct a "good" solution to recover the data for a request. If the data in a column is updated by creating numerous duplicated values, the column is no longer an ideal candidate for use in a request. However, the request optimizer always considers it as an appropriate candidate from the obsolete distribution statistics for the index, which were based on the data before the update.

Thus, SQL Server automatically updates the statistical information in a regular manner, as and when the data in the tables are modified. The sampling is carried out randomly from the data pages and comes from the table or a smaller index not sorted in clusters on the columns necessary to the statistics. After the reading of a data page from the disk, all the lines in the page are used to update the statistics information. The frequency of the update is a function of the volume of data present in the column or index and the quantity of variable data.

SQL Server always guarantees the sampling of a minimum number of lines. Tables of a size less than 8 MB are always the object of a complete analysis with the goal of improving the statistics.

The cost of this automatic update of the statistics is minimised by means of the data sampling ; these are not all analyzed.

Technical information

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

    • sysusers
    • sysobjects
    • sysindexes

The dates for the statistics are obtained with the help of the STATS_DATE function.

The information on the automatic generation of the statistics on the indexes are obtained with the help of the sp_autostats procedures.

The information on the automatic generation of the statistics on the database are obtained with the help of the procedure sp_dboption.

The statistics are updated with the instruction UPDATE STATISTICS

Error messages

The only error messages are the generic ones.

Tables used

SEEREFERTTO Refer to documentation Implementation