Index and Statistics

Statistics in Sql Server

As the data and the sql query size increase , the performance of the application goes down. 

Before going though statistics Read Indexing in Sql Server

Suppose there is a procedure which evaluate the analytical report/general purpose report from database. With passes of time , the data size goes on increase so the perform of the stored procedure goes down.let initial procedure is returning record set in 4 sec , but after a time it start taking 7~10 sec . that causes the performance of application goes down. Then the application owner/org ask development team to do something good so that performance of application be good. The team think and put some indexes on the columns which are participating in where clause of the sql queries used by stored procedure . By that way the performance increase. By Creating indexing , sql server creates statistics automatically. & Queries Optimizer analyse the records from statistics. So Statistics play role in optimizing the result set generated by sql commands.

So query optimizer use the statistics to create execution plan which cause increase in performance. It represent the distribution of the data for a column of a table. If statistics are wrong or missed or outdated, then it may effect the performance of the sql query...

 Each table in database had their own statistics details.

Type of Statistics: 

There are three type of statistics:

•  Index/Distribution Statistics: created due to index on column. Same name as Index name.
•  Column Statistics: created due to optimizer. Name start as _WA_*
•  Custom Statistics: created by CREATE STATISTICS command.

Statistics options

There are three options that you can be set which effect when and how the statistics are created and updated. These are set at Database level.

• Auto_Create_Statistics
• Auto_Update_Statistics
• Auto_Update_Statistics_Async

When AUTO_CREATE_STATISTICS option, is ON. query optimizer automatically create the statistics for individual column in query predicate to improve the estimate for query execution.

When query optimizer creates statistics due to this option ON, it name start with _WA_.

Query to show that type of statistics

ALTER DATABASE DataeName SET AUTO_CREATE_STATISTICS ON

When AUTO_UPDATE_STATISTICS option, is ON. query optimizer automatically update statistics by determines that it's out of date. The Statistics goes out-of-date after insert or update or delete operation on the column of the table.

Query optimizer check the statistics out-of-date  before executing query from cache execution plan.

this option is apply to the statistics generated by index or by create statistics command.

ALTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS ON

AUTO_UPDATE_STATISTICS_ASYNC option , determine whether the query optimizer uses synchronous or async statistics update. By Default this option is off(false)  & the query optimizer updates statistics synchronously.
with this option on(true), the query compile with existing statistics whether the statistics is out-of-date.

Suppose application have to execute similar queries/ cached query plans , then the response time of async statistics update predictable then sync statistics update . because optimizer no need to re-build / review query execution plan.


INCREMENTAL STATS: this option is available in SQL SERVER 2014 . IF Option ON , the statistics created as per partition , IF OFF, the statistics tree drop and re-computed

Whenever new partion of a block data added to table  , the statistics update


Conc : Statistics cause performance in queries. indexing and statistics increase the performance of select queries. For analytical, MIS reporting & general purpose reporting from an application database, the select query mostly used command , design with proper indexing and statistics

Also read Index in Sql Server http://sunitkanyan.blogspot.in/p/indexing-in-sql-server.html

~Sunit
technocrats@sunitkanyan.in