Maintenance Tasks – Update Statistics

Update Statistics

It is going to generate a histogram one snapshot of a information of a table or index ,its going to generate a query plan .

• It stores information of a table(s) and its sizes.
• What all indexes it has for a table .

Tables Effected

Two system tables which are effected whenever running update statistics are :

• sysstatistics
• systabstats

Accurate statistics are essential for query optimization

Query Optimization

Generates a query plan that best suites to generate accurate statistics

Importance of statistics

ASE is cost based optimizer uses tables,indexes,columns named in a query to estimate query costs

• It chooses the access methods which determines the least cost.
• Some Statistics like no of pages which determines the least cost.
• Other statistics such as histogram on columns are updated only when we run update statistics.
• Update statistics command updates column related statistics such as histogram and densities.

Index

Faster way of retrieving data from a given table.

• It is a combination of distribution of keys in a sorted order.
• Update statistics needs to be updated on those columns where the distribution of keys in the index changes in the way that effect the use of indexes for query’s .
• Update statistics command helps ASE to make the best decision about which index to be used when it process a query by keeping it up to date about the distribution of key values in the indexes.
• It goes for a table scan to know which index to be used.
• When update statistics run in business hours

• Uuses max C.P.U utilization
• Uses of procedures and data cache
• Uses I/O condition as well
• So it is recommended in off business hours

• Update statistics is usually run on all user tables to increase query performance .
• When we run update statistics the statistics information is stored in sysstatistics and systabstats tables.

Syntax : update statistics <table name >

update statistics <table name > <index name> {sp_helpindex shows how many indexes are there for a particular table }

update statistics <table name ><column list>

delete statistics <table name >

Example :

Three ways to generate the update statistics

• update statistics runs on data pages
• update index generates statistics for loading index column of a table / index page .
• Update all statistics generates statistics for both data and index page .

Syntax : update index statistics <table name>

update all statistics <table name>

*** After running update statistics we need to run sp_recompile table name ,this will compile the objects to get the latest statistics information.