CODEWITHSUNDEEP
sql-serverdatabasestatistics
TTeeple
TTeeple

Reputation: 2989

Having the same column in multiple statistics

I can't seem to find any definite answer from BOL about this. I have the scenario where I have been advised by the Database Tuning Advisor to create statistics. This is an example of a couple of them:

 CREATE STATISTICS [_dta_stat_1045578763_1_4] ON [dbo].[BillableTimeEntries]([Id], [Phase_Id]) WITH FULLSCAN
 go

 CREATE STATISTICS [_dta_stat_1045578763_1_11_4] ON [dbo].[BillableTimeEntries]([Id], [LineItem_Id], [Phase_Id]) WITH FULLSCAN
 go

I have already gone through all the suggested creation ones and removed duplicate statistics since this post indicates order is irrelevant (stats on columns a, b, c is the same as stats on columns b, c, a).

My question is since the second CREATE STATISTICS block has Phase_Id and Id included, would the second cover the first or do they both create seperate statistics that will affect a query differently?

Upvotes: 1

Views: 719

Answers (1)

Giova
Giova

Reputation: 1127

you can create the statistics and use the DBCC SHOW_STATISTICS command to see the differences between them. Here http://msdn.microsoft.com/en-us/library/ms190397.aspx you can read how this work:

Query Predicate Contains Multiple Correlated Columns When a query predicate contains multiple columns that have cross-column relationships and dependencies, statistics on the multiple columns might improve the query plan. Statistics on multiple columns contain cross-column correlation statistics, called densities, that are not available in single-column statistics. Densities can improve cardinality estimates when query results depend on data relationships among multiple columns. If the columns are already in the same index, the multicolumn statistics object already exists and it is not necessary to create it manually. If the columns are not already in the same index, you can create multicolumn statistics by creating an index on the columns or by using the CREATE STATISTICS statement. It requires more system resources to maintain an index than a statistics object. If the application does not require the multicolumn index, you can economize on system resources by creating the statistics object without creating the index. When creating multicolumn statistics, the order of the columns in the statistics object definition affects the effectiveness of densities for making cardinality estimates. The statistics object stores densities for each prefix of key columns in the statistics object definition. For more information about densities, see DBCC SHOW_STATISTICS (Transact-SQL).

Upvotes: 1

Related Questions