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 |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-02-20 : 00:36:48
|
I have an activity table with 4.3 million rows. There are lots of inserts into the table but lots of selects as well. These are the stats. Should I drop any of these indexes. I'm hesitant too because even though some have only been used a few thousand times how do I know that those few thousand queries wouldn't have been very long without those indexes... |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-20 : 00:42:53
|
| Yes, If table has more DML Operations then More index will hurt performance. Check Index Usage Statistics from DMV and Find Indexes with Low user_seeks and high Scans,Lookups and Updates and remove those index. Remember: If you have not restarted SQL Service for long time,this method applies. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-20 : 02:15:18
|
| I don't know much about this, but my thoughts are:User Seeks is very high, compared to User Scans. I think that is a good thing.User Updates to User Seeks is pretty high for some tables particularly _dta...K2_1_5_10, DTA_Activity3, DTA_Activity5 and idx_Activity_StudentsIDHow long would it take to re-create an index? On 4.3 million rows I doubt it would be long. Dunno if it is acceptable, but an option would be to drop an index and a) see if anyone screams and b) turn on SQL Profiler with Long-duration filter and see what gets caught (probably best to try that before you drop the index so you have a baseline to compare to)Are any of these indexes storing NULLs? SQL 2008's filtered indexes (allowing you do NOT index certain values) would help there. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-20 : 02:55:45
|
| DTA is very fond of creating a lot of unnecessary indexes. I can't tell without seeing the columns in those indexes, but there's a good chance that some are redundant.I would suggest, on a dev machine, test out how performance changes when you drop an index. If it's not much (or not at all) consider that index as 'potentially unnecessary' and repeat with the others, one by one. Once you've got a list of 'potentially unnecessary' try dropping all of those (still on dev) and seeing how the performance changes. If it's not much, consider doing the same to prod, one index at a time.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-20 : 03:01:39
|
| What's DTA? (Just curious) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-20 : 04:29:48
|
| Database Tuning Advisor. That index naming is what DTA will use if left to defaults. Basically, lots of indexes named _dta_index_... generally means that someone has run DTA on the production server and has let it go off and create the indexes itself with no user input--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-20 : 04:39:11
|
| Thanks Gail. I've used it in the past, but never let it do its own thing on its own! I thought the Index names looked a bit mechanically-generated ... |
|
|
|
eric081166
Starting Member
1 Post |
Posted - 2010-10-25 : 08:47:25
|
| Hello Gail,I have a SQL server 2005 Entreprise SP3 / Windows Server 2003 - 64bits. Somemeone run DTA and they created some indexes named _dta_index_... but they appear only in Statistics. Do you have a script to delete these statistics ?Thanks for your help,Eric |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-25 : 10:00:38
|
| Please post new questions in a new thread in future.Look up DROP STATISTICS.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Too many indexes?