Consultor Eletrônico

Status: Verified

GOAL:

SQL-92: 'Update Statistics' Explained

GOAL:

What is the 'Update Statistics' function ?

FACT(s) (Environment):

Progress 9.1x
OpenEdge 10.x
All Supported Operating Systems

FIX:

With SQL-92 there is a function called Update Statistics. This function meters the use of indices and determines the best way to execute a query on a particular table based on index use. By using Update Statistics it is possible to improve the performance of a query.

When running update statistics on Windows NT systems, expect the _sqlsrv2.exe to take 99% of the CPU cycles and the SQL Explorer to be unresponsive until the update is completed, these conditions can be verified by the Windows NT Task Manager.

Using the Task Manager, if you examine the Applications tab, expect to see SQL Explorer in a "not-responding" state. If you further examine the Process tab, expect to see _sqlsrv2.exe to be at 99% with a memory slice as large as possible. After the completion of the Update Statistics the system should return to "normal" with _sqlsrv2.exe back to 0% on the Process tab and back to a "running" status on the Applications tab.

If, after running Update Statistics, a select * from sysprogress.systblstat is executed (i.e from SQL Explorer), the following information should be displayed with one line for each of the tables updated:

9.x:
TBLID CARD NPAGES RECSZ PAGESZ RSSID
NN NNNN NNNN NNNN NNNN NNN

Where TBLID is the table number
CARD is the cardinal number of the table
NPAGES is the number of pages in the table
RECSZ is the largest record size
PAGESZ is the memory page size, and
RSSID is the process ID of the Update Statistics

10.x:
TBLID PROPERTY ATTRIBUTE VALUE VAL_IS

Running Update Statistics several times on the same table without querying or modifying the table should yield consistent results -- if no data changes are made in the table, then the table statistics should not change.