Database Tuning Advisor statistics generation

Last Post 02 Dec 2008 11:28 PM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jdunleavvertex
New Member
New Member

--
05 Jun 2008 11:29 AM
Sql Server 2005 SP2
I have auto create and auto update statistics set to true.

Based on a sample job the Database Tuning Advisor is recommending creating statistics on a lot of table columns that the auto statistics settings don't pick up.

What are the costs associated with creating these additional statistics?

Why doesn't the auto setting pick these columns up?

Thanks.

jdunleavvertex
New Member
New Member

--
05 Jun 2008 08:27 PM
In doing some more research the statistics recommened by DTA are all multi-column statistics. I'm going to run some more tests to see if the creating the multi column statistics help performance.
jdunleavvertex
New Member
New Member

--
09 Jun 2008 10:24 AM
Is there any way to have DTA create a DDL text file with the statistics ddl?
The only option I see for a DTA outputfile is in xml format.
SQLUSA
New Member
New Member

--
09 Jun 2008 12:39 PM
Have you looked at the missing index DMV?

select * from sys.dm_db_missing_index_details

DTA is great as long as the sample you supplied is characteristics.

Let us know how that statistics recommendations work out.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
Lee
New Member
New Member

--
29 Nov 2008 05:35 AM
Be careful with the 2005 DTA; use it as a starting point; make sure that it's not adding columns that aren't needed (included columns) with generous testing. The tool is in its infancy, and needs more work.

Good luck!

www.texastoo.com
SQLUSA
New Member
New Member

--
02 Dec 2008 11:28 PM
That is a good point.

If you submit a single query to DTA and implement recommedations, likely you get superfast response due to the included columns. That mayb OK if the query is very frequent or business critical. However, if it is only one of the many queries running in the database, then other queries may slow down due to the inflated index (inflated with the included columns).

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy