SQL Server – Importance of Statistics and Auto-Create Stats

sql serversql-server-2005statistics

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:

enter image description here

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]([LanguageId], [NoteTypeCode])


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])

CREATE STATISTICS [_dta_stat_89363683_6_2_4] ON [dbo].[ProductClassSegGroupT1]([GenderCode], [SegmentID], [Tier1])

CREATE STATISTICS [_dta_stat_89363683_1_2_3_4_5] ON [dbo].[ProductClassSegGroupT1]([ClassID], [SegmentID], [GroupID], [Tier1], [SortOrder])

CREATE STATISTICS [_dta_stat_89363683_1_4_2] ON [dbo].[ProductClassSegGroupT1]([ClassID], [Tier1], [SegmentID])

CREATE STATISTICS [_dta_stat_473365051_3_4_2_1] ON [dbo].[ProductImages]([Language], [Tier1], [SeasonItemID], [ProductImageID])

CREATE STATISTICS [_dta_stat_473365051_2_1_3] ON [dbo].[ProductImages]([SeasonItemID], [ProductImageID], [Language])

CREATE STATISTICS [_dta_stat_745366020_4_7] ON [dbo].[ProductShipTax]([TaxRate], [TaxRegionId])

CREATE STATISTICS [_dta_stat_745366020_4_5_7_3_2] ON [dbo].[ProductShipTax]([TaxRate], [ItemNo], [TaxRegionId], [DateTo], [DateFrom])

CREATE STATISTICS [_dta_stat_745366020_7_1_4_5_2_3] ON [dbo].[ProductShipTax]([TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo])

CREATE STATISTICS [_dta_stat_745366020_5_3_2_1] ON [dbo].[ProductShipTax]([ItemNo], [DateTo], [DateFrom], [ProductShipTaxID])

CREATE STATISTICS [_dta_stat_745366020_3_7_5] ON [dbo].[ProductShipTax]([DateTo], [TaxRegionId], [ItemNo])

CREATE STATISTICS [_dta_stat_745366020_1_5_2] ON [dbo].[ProductShipTax]([ProductShipTaxID], [ItemNo], [DateFrom])

CREATE STATISTICS [_dta_stat_745366020_2_7] ON [dbo].[ProductShipTax]([DateFrom], [TaxRegionId])

CREATE STATISTICS [_dta_stat_745366020_1_7_5_2] ON [dbo].[ProductShipTax]([ProductShipTaxID], [TaxRegionId], [ItemNo], [DateFrom])

CREATE STATISTICS [_dta_stat_1033367046_6_2_1_8] ON [dbo].[ProductURL]([Tier1], [ClassID], [ProductURLID], [Tier3])

CREATE STATISTICS [_dta_stat_1033367046_8_9_6_2_1_3_4_7] ON [dbo].[ProductURL]([Tier3], [LanguageID], [Tier1], [ClassID], [ProductURLID], [SegmentID], [GroupID], [Tier2])

CREATE STATISTICS [_dta_stat_1033367046_1_3_4_7] ON [dbo].[ProductURL]([ProductURLID], [SegmentID], [GroupID], [Tier2])

CREATE STATISTICS [_dta_stat_1033367046_6_2_1_3_4_7_8] ON [dbo].[ProductURL]([Tier1], [ClassID], [ProductURLID], [SegmentID], [GroupID], [Tier2], [Tier3])

CREATE STATISTICS [_dta_stat_1481368642_10_9] ON [dbo].[SiteRewriteCache]([EndDate], [StartDate])

This is my database in question. You can see it has auto create stats on, and auto update stats on.

enter image description here

Questions

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

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

For indexes, I have a look first on what I currently have for a particular object, then I decide maybe alter an index, or create with drop existing, or creating a new one, depending on the case, but how about would I go on with the stats?

Best Answer

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.

Related Question