Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SecretSQL
Starting Member
4 Posts |
 Posted - 2008-02-11 : 21:56:51
|
| Hi, I'm just testing the database engine tuning advisor. I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this. The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.Is it normal for DET to recommend that any index not referenced by the workload file be dropped? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-11 : 22:53:14
|
| Opening views is not a good enough workload in order to get good results from the advisor. You need to run through statements with WHERE clauses, GROUP BYs, ORDER BYs, ..., pretty much anything that your application does where you want to see improvement.And yes it is normal for it to recommend that you drop indexes if it doesn't think you need them. The reason for this is that they negatively impact DML operations as they constantly need to get updated as data changes. But don't drop these indexes per the recommendation as your workload isn't sufficient.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-18 : 13:01:50
|
| I would normally take some traces based on some realistic workloads , across a couple of different periods. And even then , you shouldn't blindly apply all the recommendations. You have to look at the overall contect of what you are trying to do in your db.Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-18 : 13:53:14
|
| This query returns the list of indexes ordered by the usage. Indexes that are listed at the top of the result-set may not be as beneficial as indexes listed at the bottom.select object_name(i.object_id) as tablename, i.name as indexname, s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usagefrom sys.indexes i inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id and s.database_id = db_id()where objectproperty(i.object_id, 'IsUserTable') = 1 and i.index_id > 0 order by usageBut test yourself before making decision and what your applications wants. |
|
|
|
|
|
|
Database Engine Tuning Recommendations