SQL Saturday #277 in Richmond, VA

This time I attended only two sessions:

Kevin Feasel's "T-SQL Anti-Patterns" about bad SQL programming behavior and

Jessica Moss' "Data Warehouse ETL Rollback with SSIS 2012" about how to do a rollback in DW with implemented Slowly Changing Dimensions (SCD). Both sessions were wonderful.

Unfortunately Jessica is not going to publish her slides and solution, but if anybody is interested send her an email request.

By the way, that SQL Saturday was for me the second one where I made my own presentation. My presentation was about "Index Internals".

You can download it from SQL Saturday #277 schedule page or view it online here.

 Briefly: it shows how SQL Server stores data in a table with Heap and Clustered Index. How it does Seek on Clustered and regular index and how Covering Filtered index can change a game.

How statistics work for indexes.

There were some interesting questions about statistics for Indexes:
- When Index statistics are created?
- Are Index statistics combined or separate for each column?
- If statistics are separate are they created all together?

In this post I'll try to answer these questions.

_______________________________________________________

At first will verify that my test database has right settings:

USE [TestModes] GO SELECT is_auto_create_stats_on, is_auto_update_stats_on FROM sys.databases WHERE name = 'TestModes';

Result shows that my database is set to Auto-Create and Auto-Update Statistics:






Now will create a table, fill it with some data, build an index and look for statistics on a table:

CREATE TABLE ABC(ID INT IDENTITY(1,1), F CHAR(1)); GO INSERT INTO ABC(F) VALUES ('A'),('B'),('C'),('D'),('E'), ('F'),('G'),('H'),('I'),('J'), ('K'),('L'),('M'); GO CREATE INDEX NCLIX_ABC ON ABC (ID,F); GO SELECT t.name as Table_Name, c.name as Column_Name, s.name as Stat_Name, STATS_DATE(t.object_id,s.stats_id) AS Last_Updated FROM sys.tables as t INNER JOIN sys.columns as c ON t.object_id = c.object_id INNER JOIN sys.stats_columns as sc ON sc.object_id = t.object_id and sc.column_id = c.column_id INNER JOIN sys.stats as s ON s.object_id = t.object_id and sc.stats_id = s.stats_id WHERE t.name = 'ABC';

As you can see, after index creation statement, SQL engine auto created statistics for that particular index:

Here we can answer first question: Statistics for Indexes are created at the time of Index creation.

Now will take a look at these statistics:

DBCC SHOW_STATISTICS (ABC,NCLIX_ABC);

From that output you can see that statistics are generated only on the very first column of the index.

That is our answer for the second question: Statistics collected separately for all columns within an index.

Now will try to use the index we created and check statistics after:

SELECT * FROM ABC WHERE ID = 5 and F = 'F'; GO SELECT t.name as Table_Name, c.name as Column_Name, s.name as Stat_Name, STATS_DATE(t.object_id,s.stats_id) AS Last_Updated FROM sys.tables as t INNER JOIN sys.columns as c ON t.object_id = c.object_id INNER JOIN sys.stats_columns as sc ON sc.object_id = t.object_id and sc.column_id = c.column_id INNER JOIN sys.stats as s ON s.object_id = t.object_id and sc.stats_id = s.stats_id WHERE t.name = 'ABC';

As the result of that new statistic for a column "F" has been created.

 So, to answer our questions: Statistics for Indexes are created at index creation and only for first column. Statistics for other columns are created on demand when any query try to use these columns.



Just do not forget to clean out after yourself:

DROP TABLE ABC;