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.
The process to use the update statistics command is:
1. Connect via ODBC or JDBC to the database.
2. Run the statement: update statistics for owner.tablename and then execute the command.
Several tables can be chained together if separated by a semi-colon (;) and the tables will then be updated sequentially.
When running update statistics on Windows 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 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.
After running Update Statistics, if a select * from sysprogress.systblstat is executed (i.e from SQL Explorer), the following information will 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.
Update Statistics measures the use of indices and puts the values into a system table named sysprogress.systblstat.
Successive executions of the Update Statistics for Tablename, updates the information contained in the sysprogress.systblstat, and based on these numbers will modify the way that a query executes. It does not change the intent of the query but the index used may be changed to "optimize" the query. |
