Statistics from DTA
in [Programming SQL]
|
Prev: copying rows
Next: if statement
From: tshad on 5 May 2010 14:04 I ran DTA on my select statements and found that it suggested about 8 statistics. But how do you determine if that if you should use all 8 or just some. There is already about 30 on that file (all assigned by the system) which all have only one column on them. Wouldn't there be a perfomance hit on this table with all these statistics? The ones it mentioned are: CREATE STATISTICS [_dta_stat_1535448644_5_4_3] ON [dbo].[TASK]([CompanyCustomer], [ObjectID], [ContentVersion]) go CREATE STATISTICS [_dta_stat_1535448644_4_5_3_8_24] ON [dbo].[TASK]([ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], [EnvironmentID]) go CREATE STATISTICS [_dta_stat_1535448644_11_9_24_3_8_4] ON [dbo].[TASK]([RequesterID], [DelegateID], [EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID]) go CREATE STATISTICS [_dta_stat_1535448644_17_24_3_8_4_5] ON [dbo].[TASK]([DueDate], [EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], [CompanyCustomer]) go CREATE STATISTICS [_dta_stat_1535448644_7_24_3_8_4_5] ON [dbo].[TASK]([StatusID], [EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], [CompanyCustomer]) go CREATE STATISTICS [_dta_stat_1535448644_6_24_3_8_4_5_7_11] ON [dbo].[TASK]([PriorityID], [EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], [CompanyCustomer], [StatusID], [RequesterID]) go CREATE STATISTICS [_dta_stat_1535448644_24_3_8_4_5_11_9_6] ON [dbo].[TASK]([EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], [CompanyCustomer], [RequesterID], [DelegateID], [PriorityID]) go CREATE STATISTICS [_dta_stat_1535448644_6_7_11_9_4_5_3_8_24_17] ON [dbo].[TASK]([PriorityID], [StatusID], [RequesterID], [DelegateID], [ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], [EnvironmentID], [DueDate]) go Thanks, Tom
From: TheSQLGuru on 5 May 2010 16:55 1) DTA is a REALLY harmful product for most users. I have made a very significant amount of money cleaning up it's messes for clients over the years. 2) You can see if a statistic helps the optimizer by viewing the query plan with and without the statistic in place. 3) yes, having extra statistics can harm performance as they are updated after sufficient DML activity. you can control this by disabling autoupdate stats, but there are very good reasons to NOT do that. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "tshad" <tfs(a)dslextreme.com> wrote in message news:%2376rm1H7KHA.420(a)TK2MSFTNGP02.phx.gbl... >I ran DTA on my select statements and found that it suggested about 8 >statistics. > > But how do you determine if that if you should use all 8 or just some. > > There is already about 30 on that file (all assigned by the system) which > all have only one column on them. > > Wouldn't there be a perfomance hit on this table with all these > statistics? The ones it mentioned are: > > CREATE STATISTICS [_dta_stat_1535448644_5_4_3] ON > [dbo].[TASK]([CompanyCustomer], [ObjectID], [ContentVersion]) > go > > CREATE STATISTICS [_dta_stat_1535448644_4_5_3_8_24] ON > [dbo].[TASK]([ObjectID], [CompanyCustomer], [ContentVersion], > [AssigneeID], [EnvironmentID]) > go > > CREATE STATISTICS [_dta_stat_1535448644_11_9_24_3_8_4] ON > [dbo].[TASK]([RequesterID], [DelegateID], [EnvironmentID], > [ContentVersion], [AssigneeID], [ObjectID]) > go > > CREATE STATISTICS [_dta_stat_1535448644_17_24_3_8_4_5] ON > [dbo].[TASK]([DueDate], [EnvironmentID], [ContentVersion], [AssigneeID], > [ObjectID], [CompanyCustomer]) > go > > CREATE STATISTICS [_dta_stat_1535448644_7_24_3_8_4_5] ON > [dbo].[TASK]([StatusID], [EnvironmentID], [ContentVersion], [AssigneeID], > [ObjectID], [CompanyCustomer]) > go > > CREATE STATISTICS [_dta_stat_1535448644_6_24_3_8_4_5_7_11] ON > [dbo].[TASK]([PriorityID], [EnvironmentID], [ContentVersion], > [AssigneeID], [ObjectID], [CompanyCustomer], [StatusID], [RequesterID]) > go > > CREATE STATISTICS [_dta_stat_1535448644_24_3_8_4_5_11_9_6] ON > [dbo].[TASK]([EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], > [CompanyCustomer], [RequesterID], [DelegateID], [PriorityID]) > go > > CREATE STATISTICS [_dta_stat_1535448644_6_7_11_9_4_5_3_8_24_17] ON > [dbo].[TASK]([PriorityID], [StatusID], [RequesterID], [DelegateID], > [ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], > [EnvironmentID], [DueDate]) > go > > Thanks, > > Tom > >
From: tshad on 5 May 2010 16:58 "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:C5CdncNLFpjDR3zWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... > 1) DTA is a REALLY harmful product for most users. I have made a very > significant amount of money cleaning up it's messes for clients over the > years. > So you are saying DTA is not good to use? > 2) You can see if a statistic helps the optimizer by viewing the query > plan with and without the statistic in place. > How do you know it is using the statistics or not? > 3) yes, having extra statistics can harm performance as they are updated > after sufficient DML activity. you can control this by disabling > autoupdate stats, but there are very good reasons to NOT do that. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:%2376rm1H7KHA.420(a)TK2MSFTNGP02.phx.gbl... >>I ran DTA on my select statements and found that it suggested about 8 >>statistics. >> >> But how do you determine if that if you should use all 8 or just some. >> >> There is already about 30 on that file (all assigned by the system) which >> all have only one column on them. >> >> Wouldn't there be a perfomance hit on this table with all these >> statistics? The ones it mentioned are: >> >> CREATE STATISTICS [_dta_stat_1535448644_5_4_3] ON >> [dbo].[TASK]([CompanyCustomer], [ObjectID], [ContentVersion]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_4_5_3_8_24] ON >> [dbo].[TASK]([ObjectID], [CompanyCustomer], [ContentVersion], >> [AssigneeID], [EnvironmentID]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_11_9_24_3_8_4] ON >> [dbo].[TASK]([RequesterID], [DelegateID], [EnvironmentID], >> [ContentVersion], [AssigneeID], [ObjectID]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_17_24_3_8_4_5] ON >> [dbo].[TASK]([DueDate], [EnvironmentID], [ContentVersion], [AssigneeID], >> [ObjectID], [CompanyCustomer]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_7_24_3_8_4_5] ON >> [dbo].[TASK]([StatusID], [EnvironmentID], [ContentVersion], [AssigneeID], >> [ObjectID], [CompanyCustomer]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_6_24_3_8_4_5_7_11] ON >> [dbo].[TASK]([PriorityID], [EnvironmentID], [ContentVersion], >> [AssigneeID], [ObjectID], [CompanyCustomer], [StatusID], [RequesterID]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_24_3_8_4_5_11_9_6] ON >> [dbo].[TASK]([EnvironmentID], [ContentVersion], [AssigneeID], [ObjectID], >> [CompanyCustomer], [RequesterID], [DelegateID], [PriorityID]) >> go >> >> CREATE STATISTICS [_dta_stat_1535448644_6_7_11_9_4_5_3_8_24_17] ON >> [dbo].[TASK]([PriorityID], [StatusID], [RequesterID], [DelegateID], >> [ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], >> [EnvironmentID], [DueDate]) >> go >> >> Thanks, >> >> Tom >> >> > >
From: TheSQLGuru on 6 May 2010 18:00 1) yes, I am saying that. 2) you can see different estimated rows returned for each query plan. this may well induce the optimizer to chose a different plan, including different index usage, join types, etc. using text or xml plans you may even be able to see actual statistics used, but I don't think so -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "tshad" <tfs(a)dslextreme.com> wrote in message news:ufgQDXJ7KHA.5848(a)TK2MSFTNGP06.phx.gbl... > > "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message > news:C5CdncNLFpjDR3zWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >> 1) DTA is a REALLY harmful product for most users. I have made a very >> significant amount of money cleaning up it's messes for clients over the >> years. >> > So you are saying DTA is not good to use? > >> 2) You can see if a statistic helps the optimizer by viewing the query >> plan with and without the statistic in place. >> > How do you know it is using the statistics or not? > >> 3) yes, having extra statistics can harm performance as they are updated >> after sufficient DML activity. you can control this by disabling >> autoupdate stats, but there are very good reasons to NOT do that. >> >> -- >> Kevin G. Boles >> Indicium Resources, Inc. >> SQL Server MVP >> kgboles a earthlink dt net >> >> >> "tshad" <tfs(a)dslextreme.com> wrote in message >> news:%2376rm1H7KHA.420(a)TK2MSFTNGP02.phx.gbl... >>>I ran DTA on my select statements and found that it suggested about 8 >>>statistics. >>> >>> But how do you determine if that if you should use all 8 or just some. >>> >>> There is already about 30 on that file (all assigned by the system) >>> which all have only one column on them. >>> >>> Wouldn't there be a perfomance hit on this table with all these >>> statistics? The ones it mentioned are: >>> >>> CREATE STATISTICS [_dta_stat_1535448644_5_4_3] ON >>> [dbo].[TASK]([CompanyCustomer], [ObjectID], [ContentVersion]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_4_5_3_8_24] ON >>> [dbo].[TASK]([ObjectID], [CompanyCustomer], [ContentVersion], >>> [AssigneeID], [EnvironmentID]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_11_9_24_3_8_4] ON >>> [dbo].[TASK]([RequesterID], [DelegateID], [EnvironmentID], >>> [ContentVersion], [AssigneeID], [ObjectID]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_17_24_3_8_4_5] ON >>> [dbo].[TASK]([DueDate], [EnvironmentID], [ContentVersion], [AssigneeID], >>> [ObjectID], [CompanyCustomer]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_7_24_3_8_4_5] ON >>> [dbo].[TASK]([StatusID], [EnvironmentID], [ContentVersion], >>> [AssigneeID], [ObjectID], [CompanyCustomer]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_6_24_3_8_4_5_7_11] ON >>> [dbo].[TASK]([PriorityID], [EnvironmentID], [ContentVersion], >>> [AssigneeID], [ObjectID], [CompanyCustomer], [StatusID], [RequesterID]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_24_3_8_4_5_11_9_6] ON >>> [dbo].[TASK]([EnvironmentID], [ContentVersion], [AssigneeID], >>> [ObjectID], [CompanyCustomer], [RequesterID], [DelegateID], >>> [PriorityID]) >>> go >>> >>> CREATE STATISTICS [_dta_stat_1535448644_6_7_11_9_4_5_3_8_24_17] ON >>> [dbo].[TASK]([PriorityID], [StatusID], [RequesterID], [DelegateID], >>> [ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], >>> [EnvironmentID], [DueDate]) >>> go >>> >>> Thanks, >>> >>> Tom >>> >>> >> >> > >
From: tshad on 6 May 2010 18:26
"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:586dnb9azIOOpn7WnZ2dnUVZ_qWdnZ2d(a)earthlink.com... > 1) yes, I am saying that. > So how would you determine what statistics to add over what is created by the system? > 2) you can see different estimated rows returned for each query plan. > this may well induce the optimizer to chose a different plan, including > different index usage, join types, etc. using text or xml plans you may > even be able to see actual statistics used, but I don't think so > Not sure what you mean by that. Where do you see the different estimated rows returned? I assume you can't tell if the choice of using a different plan was based on statistics. Thanks, Tom > > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:ufgQDXJ7KHA.5848(a)TK2MSFTNGP06.phx.gbl... >> >> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message >> news:C5CdncNLFpjDR3zWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >>> 1) DTA is a REALLY harmful product for most users. I have made a very >>> significant amount of money cleaning up it's messes for clients over the >>> years. >>> >> So you are saying DTA is not good to use? >> >>> 2) You can see if a statistic helps the optimizer by viewing the query >>> plan with and without the statistic in place. >>> >> How do you know it is using the statistics or not? >> >>> 3) yes, having extra statistics can harm performance as they are updated >>> after sufficient DML activity. you can control this by disabling >>> autoupdate stats, but there are very good reasons to NOT do that. >>> >>> -- >>> Kevin G. Boles >>> Indicium Resources, Inc. >>> SQL Server MVP >>> kgboles a earthlink dt net >>> >>> >>> "tshad" <tfs(a)dslextreme.com> wrote in message >>> news:%2376rm1H7KHA.420(a)TK2MSFTNGP02.phx.gbl... >>>>I ran DTA on my select statements and found that it suggested about 8 >>>>statistics. >>>> >>>> But how do you determine if that if you should use all 8 or just some. >>>> >>>> There is already about 30 on that file (all assigned by the system) >>>> which all have only one column on them. >>>> >>>> Wouldn't there be a perfomance hit on this table with all these >>>> statistics? The ones it mentioned are: >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_5_4_3] ON >>>> [dbo].[TASK]([CompanyCustomer], [ObjectID], [ContentVersion]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_4_5_3_8_24] ON >>>> [dbo].[TASK]([ObjectID], [CompanyCustomer], [ContentVersion], >>>> [AssigneeID], [EnvironmentID]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_11_9_24_3_8_4] ON >>>> [dbo].[TASK]([RequesterID], [DelegateID], [EnvironmentID], >>>> [ContentVersion], [AssigneeID], [ObjectID]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_17_24_3_8_4_5] ON >>>> [dbo].[TASK]([DueDate], [EnvironmentID], [ContentVersion], >>>> [AssigneeID], [ObjectID], [CompanyCustomer]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_7_24_3_8_4_5] ON >>>> [dbo].[TASK]([StatusID], [EnvironmentID], [ContentVersion], >>>> [AssigneeID], [ObjectID], [CompanyCustomer]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_6_24_3_8_4_5_7_11] ON >>>> [dbo].[TASK]([PriorityID], [EnvironmentID], [ContentVersion], >>>> [AssigneeID], [ObjectID], [CompanyCustomer], [StatusID], [RequesterID]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_24_3_8_4_5_11_9_6] ON >>>> [dbo].[TASK]([EnvironmentID], [ContentVersion], [AssigneeID], >>>> [ObjectID], [CompanyCustomer], [RequesterID], [DelegateID], >>>> [PriorityID]) >>>> go >>>> >>>> CREATE STATISTICS [_dta_stat_1535448644_6_7_11_9_4_5_3_8_24_17] ON >>>> [dbo].[TASK]([PriorityID], [StatusID], [RequesterID], [DelegateID], >>>> [ObjectID], [CompanyCustomer], [ContentVersion], [AssigneeID], >>>> [EnvironmentID], [DueDate]) >>>> go >>>> >>>> Thanks, >>>> >>>> Tom >>>> >>>> >>> >>> >> >> > > |