nonclustered index on view just created, but get warning columns miss statistics

Question

obviously there is something I do not understand about index statistics.

I have a table with millions of rows for logging purposes. To improve performance I created a view and a unique clustered index on that (had to because first index must be unique clustered) as well as a non clustered index to be able to include some more columns into the index.

Now I compare the performance of some queries with and without those indices and the view; I indeed get some more speed out of it and reads and cpu-usage seem lower also.

But when I look at the execution plan the sql server applied, I discover a warning on the Index Seek on that nonclustered index that some columns are missing statistics; those columns also do appear in my where clause so I think there might still be some performance to improve here. There are two columns the execution plan is complaining about having no statistics; one of them is inside the non clustered index the other one is inside that index's include clause. Anyways I do not understand how those statistics are still missing.

For example here http://msdn.microsoft.com/en-us/library/ms190397.aspx it states that "The query optimizer creates statistics for indexes on tables or views when the index is created.". Well I just DID create those indices and no rows were updated or inserted since then. I also checked and ensured that AUTO_UPDATE_STATISTICS as well as AUTO_CREATE_STATISTICS are enabled, what am I missing here?

I also took a look into the statistics on that index via SQL Server Management Studio: (expanding the view then rightclicking that non clustered index under statistics then selecting properties) There under statistics columns one of the two mentioned columns can be seen (that one that is not within the include clause).

How can I get rid of this warning, i.e. create/enforce those statistics to exist?

Answer 1

Try to create the stat with this code:

CREATE STATISTICS StatName
ON dbo.IndexedView(ColumnName,...)
WITH SAMPLE 25 PERCENT