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]([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.
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?