Reg: Statistics in SQL Server...

Question

Hi Experts,

Can i know ,in which cases sql server creates the statistics with out manual intervention?

if the table does not have any indexes then will  sql server create statistics on any column ?(if i write the where condition on any of the column on the table does not have any indexes)

Please advice..

Thanks in Advance.

Answer 1

Yes, as long as auto stats is on for the database (which is the default), SQL Server create as a statistics objects on a column when it thinks it needs it. See this example:

DROP TABLE IF EXISTS stattest
go
CREATE TABLE stattest(object_id int  NOT NULL, 
                      column_id int  NOT NULL,
                      system_type_id int NOT NULL,
                      user_type_id   int NOT NULL,
                      max_length     int NOT NULL)
INSERT stattest 
   SELECT object_id, column_id, system_type_id, user_type_id, max_length
   FROM   sys.columns
go
SELECT s.name AS stats_name, c.name AS column_name, sc.stats_column_id
FROM   sys.stats s
JOIN   sys.stats_columns sc ON s.object_íd = sc.object_id
                           AND s.stats_id  = sc.stats_id
JOIN   sys.columns c ON sc.object_id = c.object_id
                    AND sc.column_id = c.object_id
WHERE  s.object_id = object_id('dbo.stattest')
ORDER  BY s.name, c.name
go
SELECT * FROM stattest WHERE max_length = -1
go
SELECT s.name AS stats_name, c.name AS column_name, sc.stats_column_id
FROM   sys.stats s
JOIN   sys.stats_columns sc ON s.object_íd = sc.object_id
                           AND s.stats_id  = sc.stats_id
JOIN   sys.columns c ON sc.object_id = c.object_id
                    AND sc.column_id = c.column_id
WHERE  s.object_id = object_id('dbo.stattest')
ORDER  BY s.name, c.name
go

While written for SQL 2008, this white paper is still very relevant: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd535534(v=sql.100)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi Venkata Vara,

 

>>Can i know ,in which cases sql server creates the statistics with out manual intervention?

 

The Query Optimizer already creates statistics in the following ways:

 

1. The Query Optimizer creates statistics for indexes on tables or views when the index is created. These statistics are created on the key columns of the index. If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index.

 

2. The Query Optimizer creates statistics for single columns in query predicates when AUTO_CREATE_STATISTICS is on.

 

>>if the table does not have any indexes then will  sql server create statistics on any column ?(if i write the where condition on any of the column on the table does not have any indexes)

 

Please check whether the AUTO_CREATE_STATISTICS Option is ON for the database. You can query the sys.databases view to check it like " select name, is_auto_create_stats_on from sys.databases ".

 

If the AUTO_CREATE_STATISTICS Option is ON for the database, Query Optimizer will create statistics for the column.

 

For more detailed information, please refer to the document: When to create statistics.

 

Best Regards,

Emily

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]