Monitoring Databases  «Prev 

Maintaining database statistics

Create Statistics syntax

Create Statistics in SQL Server 
CREATE STATISTICS statistics_name ON table
(column [,...n])
[WITH
[ [ FULLSCAN
[ SAMPLE number PERCENT ] [,] ]
[NORECOMPUTE]

Create Statistics in SQL Server

High Performance SQL-Server

Auto create statistics

Data-distribution statistics are a key factor in how the SQL Server Query Optimizer creates query execution plans. This option directs SQL Server to automatically create statistics for any columns for which statistics could be useful. The default for this option is set to ON. To set auto create statistics ON for database_sample 
ALTER DATABASE database_sample 
SET AUTO_CREATE_STATISTICS ON;

  1. Statisitics_name – A unique name identifying the statistic group.
  2. Table – The table for which you want statistics.
  3. Column [, …n] – The columns in the table on which to create statistics.
  4. FULLSCAN – Tells the create statistics command to scan the whole table when creating the statistics.
  5. Sample number PERCENT – Allows you to specify how much of the table to scan.
  6. Norecompute – Stops SQL Server from automatically updating statistics on this statistics group.