Filtered Statistics on columns with no indexes

If you have queries with complex but predictable where clauses which include multiple columns then creating filtered statistics might make sense. They are cheaper than indexes as far as the system resources are concerned, but still provide information to the engine about so-called cardinality estimates (a fancy word for selectivity). The information is stored for each prefix column of the statistics, meaning that if the queries fail to include the prefix column in the predicates then the statistics are useless, just like the indexes are useless when the first column of the index is not included.

I am sure that someone will have a better answer, but these are my 2 cents.

Oleg

I cannot seem to get this example to work as I wanted. Maybe it can be improved upon - it is just too late for me today.

I play in my test DB SandBox, that it all you will need to change to get this to work.

I clear the cache in this script too - DO NOT PLAY WITH THIS ON A PRODUCTION SYSTEM!!!!!!

/* Turn off Statistics creation and updates */
ALTER DATABASE SandBox SET AUTO_CREATE_STATISTICS OFF, AUTO_UPDATE_STATISTICS OFF

/* Create a test table */
CREATE TABLE dbo.TestTable
(Col1 int NOT NULL)
;
/* Insert a row */
INSERT INTO dbo.TestTable
        (Col1)
SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) ) FROM sys.columns
;


/* Now turn on Actual Estimation Plan*/
SET STATISTICS XML ON

/* Query Test Table */
SELECT * FROM dbo.TestTable
;
/* Estimate Rows = 1 , Actual Rows = 1*/

/* Insert lots of rows */
INSERT INTO dbo.TestTable
        (Col1)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL) )+1 FROM sys.columns
;
/* Query Test Table */
SELECT * FROM dbo.TestTable
;
/* Estimate Rows = 486 , Actual Rows = 486*/

/* Hmmm - no difference for table scans with no WHERE clause.... */


/* Query Test Table - with a WHERE clause */
SELECT * FROM dbo.TestTable WHERE col1<100
;
/* Estimate Rows = 145.8 , Actual Rows = 99 */

/* AHA!! The execution plan is different - SQL Server even warns that there are missing Stats on the execution plan */

/* Create a filtered statistic */
;
CREATE STATISTICS stat ON dbo.TestTable (Col1) WHERE col1 <100 
;
/* Query Test Table - with a WHERE clause */
SELECT * FROM dbo.TestTable WHERE col1<100
;
/* Estimate Rows = 145.8 , Actual Rows = 99 */

/* The execution plan is the same as before - SQL Server even warns that there are missing Stats on the execution plan - why is that? */

/* 
    Of course - the execution plan from the first select with WHERE clause has been cached and reused - PARAMETER SNIFFING!
    Let's clear the proc cache and try that again! Brute force cleanup, it can be done for a certain plan though
*/
-- Uncomment these lines to do the buffer cleanup
--dbcc dropcleanbuffers with no_infomsgs;
--dbcc freeproccache with no_infomsgs;
--dbcc freesystemcache('All') with no_infomsgs;

/* Query Test Table - with a WHERE clause */
SELECT * FROM dbo.TestTable WHERE col1<100
;
/* Estimate Rows = 145.8 , Actual Rows = 99 */


/* Drop table, reset statistics settings for DB */
DROP TABLE dbo.TestTable;
ALTER DATABASE SandBox SET AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS ON

Thanks @WillianD. Seems that filtered stats are still not helping the optimizer. I will do some additional research about this. The