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;

Ancient programming technics using GOTO

While I was improving my Documenter, I've been thinking about simplification and code re-usability without using functions or stored procedures and remembered old good days of programming and usage of GOTO command.

Here is a code we can generate in SQL to use artificial subroutine.

From any part of your code or stored procedure you can call standard set of commands (aka sub-stored procedure) and come back without actually calling another stored procedure or function.

USE Tempdb

GO

CREATE PROCEDURE #Test_Loop    AS

DECLARE @TestText varchar(100);

DECLARE @i int;

DECLARE @RetCall TINYINT;

-- CALL #1

SELECT @RetCall = 1, @TestText = 'CALL #1', @i = 10;

PRINT 'First Subroutine call:'

GOTO PrintFunction;

CALL1:

-- CALL #2

SELECT @RetCall = 2, @TestText = 'CALL #2', @i = 15;

PRINT 'Second Subroutine call:'

GOTO PrintFunction;

CALL2:

GOTO EndCall;

-------------------------------------------------------PrintFunction:

WHILE @i > 0

BEGIN

  PRINT CAST(@i as VARCHAR) + ' - ' + @TestText

  SET @i -= 1;

END

IF @RetCall = 1 GOTO CALL1;

IF @RetCall = 2 GOTO CALL2;

GOTO EndCall;

------------------------------------------------------

EndCall:

RETURN 0;

GO

EXEC #Test_Loop

GO

DROP PROCEDURE #Test_Loop

Forcing query to recompile by targeted cleaning of SQL Plan Cache

Users had a lot of complains about particular query. Sometimes it runs for less than a second and sometimes it runs for up to a minute.

The "Silver bullet" for that problem was updating statistics.
As management calls it "Throwing virgin into a volcano with hopes that everything will go back to normal".

Finally that query reached my Management Studio.

It was extremely easy one and well programmed one. Precompiled, with passing parameters.
There were no visible problems other than linking five tables in a long chain, where results were extracted only from first and fifth and all filters were applied to intermediate ones.

There were some opportunities to create additional indexes, but knowing that query runs well enough without them stopped me with that suggestion.
So, I've advised to add OPTION (RECOMPILE) to a query to fix the problem.

Unfortunately, query is sent by third party application and it is close to impossible to change the app.

Then, if we can't do a query recompilation we can just force it to be recompiled by our "Silver bullet" - Update Statistics, but instead of invalidating query plan we can just simply delete that plan from SQL Sever cache.

Deletion of a single plan from a cache is usually manual task, but in our case we need something automated, when you just press the button to make a trick.

For that automation you have to know the signature of your query, because you want to target ONLY that query without any impact on anything else in cache. That query signature can be extended to multiple queries by using table name for it.

In my example I use following signature: "FROM Production.TransactionHistory".
All queries in in the plan cache, which happen to have that signature will be deleted.

Sample script runs against "AdventureWorks" database.

Do not run this example in production environment!

USE AdventureWorks;

GO

DBCC FREEPROCCACHE;

GO

SELECT top 0 * FROM Production.TransactionHistory

WHERE ReferenceOrderID = 41602;

GO

SELECT top 0 * FROM Production.TransactionHistory

WHERE TransactionID = 100012;

GO

SELECT top 0 * FROM HumanResources.Employee

WHERE OrganizationLevel = 4;

GO

/**/SELECT qs.[plan_handle], st.TEXT AS SQLBatch

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.[plan_handle]) AS st

WHERE st.text not like '/**/%';

GO

/**/DECLARE @Q TABLE (ID INT IDENTITY(1,1), Handle VARBINARY(64));

DECLARE @SQL VARCHAR(MAX);

DECLARE @i INT;

INSERT INTO @Q(Handle) SELECT qs.[plan_handle]

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.[plan_handle]) AS st

WHERE st.text like '%FROM Production.TransactionHistory%';

SELECT @i = MAX(ID) FROM @q;

WHILE @i > 0

BEGIN

  SELECT @SQL = 'DBCC FREEPROCCACHE (' +

sys.fn_sqlvarbasetostr(Handle) + ');'

  FROM @Q WHERE ID = @i;

 

  PRINT  @SQL;

  EXEC (@SQL);

 

  DELETE FROM @Q WHERE ID = @i;

 

  SET @i -= 1;

END

GO

/**/SELECT qs.[plan_handle], st.TEXT AS SQLBatch

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.[plan_handle]) AS st

WHERE st.text not like '/**/%';

Hope you learned something from this and might apply the same approach in your environment.