Statistics from DTA
in [Programming SQL]
|
Prev: copying rows
Next: if statement
From: TheSQLGuru on 8 May 2010 10:17 1) I would do it by part art and part science and a LOT of experience doing performance analysis and tuning on SQL Server over the past 15 years or so. 2) show the actual execution plan graphically. put your cursor over any part of the graphic and note the popup. Also you can hit F4 to see much more detailed properties of each element of the plan. you can also show the text plan or xml plan. Once you note different estimated rowcounts you can often understand the optimizers plan choices. Also, a key issue here is to compare estimated with ACTUAL rowcounts. That is a leading cause of bad performance. Most often the problem is low (or 1) estimated values with telephone numbers for actuals. That will give you the dreaded nested-loop-join-for-a-kajillion-rows scenario where you can pack a lunch due to the extraordinarily high logical reads you will get. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "tshad" <tfs(a)dslextreme.com> wrote in message news:eVmKtsW7KHA.5848(a)TK2MSFTNGP06.phx.gbl... > > "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 >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > |