asked on

Statistics information on SQL Server DB

When i check DB options :
Auto Create Statistics: True
Auto Update Statistics: True

It means that if i created any indexes in this DB will automatically update statistics? am i correct?

When i used below query:
The results are:
auto_created user_created
0 0
It should be 1 becasue detabase level is True.

USE TestDB
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

Open in new window

I got confusion when we make true at database level, it should automatically set true to index level? am i correct

Vitor Montalvão

It means that if i created any indexes in this DB will automatically update statistics? am i correct?

No. When you create an index then the related statistics is also created (not updated).
The auto update will take place during massive write operations (INSERT, UPDATE or DELETE).

Pawan Kumar

In SQL we have statistics on column and index. We can create column statistics on a single and multiple column. Index statistics are automatically create when the index is created. You can read more about statistics from here

Now when the SQL Server update the statistics for you - refer the blog post from SQL skills.

https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/

1.Statistics are updated when the first row is added in table.
2.Assume 500 rows are there in the table now statistics are updated after each 500 changes in table.
3.case when we have more than 500 rows then statistics are updated for each 500 + 20% of rows changes in table.

Vijay

ASKER

Hi Pawan,

Yes you are right.
But my requirement us to run T-sql script on all the databases to collect last statistics updated date and time. the above script only runs on single database. i have to collect these information for around 100 databases.
As of now we are not going to update statistis.

Pawan Kumar

pls see if this help from microsoft - https://blogs.msdn.microsoft.com/sql_server_team/getting-more-statistics-information-programatically/

Vitor Montalvão