May 27, 2008 at 1:40 pm
I recently ran the DTA on a decent sized workload file...I was only interested in index tuning on one table. The DTA returned a suggested 49 indexes!!! That would put me at over 50% index size to table size....a lot of the suggested indexes are reverse or redundant. Did I miss something in the DTA that would have made more sense out of the work load?
Any advice is greatly appreciated.
Thanks,
Megan
May 27, 2008 at 1:46 pm
You didn't miss anything. DTA has a very bad habit of massively over-suggesting indexes and statistics. Take its suggestions with a large pinch of salt.
I would suggest that you try the indexes out one by one, see which have the most effect on your queries then only implement those (being very careful to ensure that the ones you put on really are used by the queries)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2008 at 2:05 pm
I would also add, be wary of indexes defined on too many keys (more than 3). These are usually overkill, unless you need a covering index.
Also, as Gail mentioned, check the suggested indexes against your query to see if they make sense. A good way to do this is to examine the execution plan of the query. Focus on operators accounting for the bulk of the workload (percentages next to each operator). Which indexes are associated with these operators? Do they match some of those suggested by the DTA?
SQL Server 2005 comes with the very powerful new feature of dynamic management views (DMVs) containing a whole bunch of performance info on the SQL instance. To get the most needed missing indexes on your SQL instance run the following (your instance must have been running for at least a few days for adequate stats):
SELECT
index_advantage
,user_seeks
,last_user_seek
,avg_total_user_cost
,avg_user_impact
, equality_columns
,included_columns
,[statement]
FROM
(SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage
,migs.* FROM sys.dm_db_missing_index_group_stats migs
) AS migs_adv
inner join
sys.dm_db_missing_index_groups AS mig
ON
migs_adv.group_handle = mig.index_group_handle
inner join
sys.dm_db_missing_index_details AS mid
ON
mig.index_handle = mid.index_handle
WHERE
migs_adv.index_advantage > 10000
ORDER BY
migs_adv.index_advantage desc
If the indexes returned from this match those returned by the DTA, then you can be more certain the DTA's recommendations are on target.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 27, 2008 at 2:44 pm
Thank you both so much!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply