Verify the importance of statistics? Database with auto-create stats works?

Working on SQL Server 2005, I want to use the database engine tuning advisor to help me tune my webserver.

First thing I do is to create a server side profile trace and run it for about an hour.

this is the trace file generated.

When I use this file on the DTA I get the following recommendations:

The recommendations translate into the script below, the comments are there because I double checked indexes suggestions.

```
CREATE STATISTICS [_dta_stat_2108846875_3_6] ON [dbo].ProductBulletPoint

CREATE NONCLUSTERED INDEX [_dta_index_ProductBulletPoint_39_2108846875__K6_K1_K3_2_4_5_7] ON [dbo].[ProductBulletPoint]
(
[NoteTypeCode] ASC,
[Tier1] ASC,
[LanguageId] ASC
)
INCLUDE ( [SeasonItemId],
[SortOrder],
[NoteText],
[NoteGroup]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--SP_HELPINDEX9 'ProductBulletPoint'
-- the index [_dta_index_ProductBulletPoint_39_2108846875__K6_K1_K3_2_4_5_7] is ok to implement

CREATE NONCLUSTERED INDEX [_dta_index_ProductShipTax_39_745366020__K7_K5_K2_K3_K1_K4_6] ON [dbo].[ProductShipTax]
(
[TaxRegionId] ASC,
[ItemNo] ASC,
[DateFrom] ASC,
[DateTo] ASC,
[ProductShipTaxID] ASC,
[TaxRate] ASC
)
INCLUDE ( [TaxCode]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--SP_HELPINDEX9 'ProductShipTax'
-- the index [_dta_index_ProductShipTax_39_745366020__K7_K5_K2_K3_K1_K4_6]

CREATE NONCLUSTERED INDEX [_dta_index_SiteRewriteCache_39_1481368642__K9_K10_2] ON [dbo].[SiteRewriteCache]
(
[StartDate] ASC,
[EndDate] ASC
)
INCLUDE ( [CacheKey]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--SP_HELPINDEX9 'SiteRewriteCache'
-- the index _dta_index_SiteRewriteCache_39_1481368642__K9_K10_2 is ok to be implemented

CREATE STATISTICS [_dta_stat_89363683_3_2_4] ON [dbo].[ProductClassSegGroupT1]([GroupID], [SegmentID], [Tier1

How come I have so many statistics to create, since my database has auto create stats on?

Automatic statistics can only create single column statistics objects. The statistics suggested by DTA are all multi-column statistics that capture potentially-useful (though limited) correlation information.

For details see the Statistics documentation.


How can I double check whether each of these stats it recommends to create are actually helpful?

In the usual ways. The new statistics may or may not improve cardinality estimates, plan selection, and performance. As with any other DTA recommendation, test before and after, and only keep changes you find (or expect) to be beneficial.

StackExchange Database Administrators Q#137992, answer score: 8