5

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?

  • 1
    Auto create stats does not means that every time a small amount of row changes, the stats would be updated stats would be updated when roughly 20% of data changes and then auto update stats get fired. I suggest you read Understanding When Statistics Will Automatically Update. Not every thing suggested by DTA is useful but in case of stats I have not seen it giving false recommendation – Shanky May 9 '16 at 13:44
8

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.