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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-03-29 : 18:52:16
|
| I've got a table with about 760,000,000 rows. It's got a couple of indexes, and I'm suspecting that one of them is useless and just taking up space.However, there are maybe 15 different stored procedures and 10 more ad-hoc queries that are frequently run against this table. I'd hate to invest the energy in removing the index if it's going to cause some poor query to table scan 760 million rows.Is there any way to get SQL server to tell me if the index is being used? Or to virtually disable the index without deleting it so I could see the performance impact?Any thoughts much appreciateed.Thanks-b |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-29 : 19:30:52
|
| Well if you allow ad hoc queries then no. You don't know what the user is going to filter on so they may put in a criteria for which the index is useful.Stored procedures are easy - just look at the query plan for the various possibilities.Usually you can just look at the query and give a good guess as to whether it would use an index.Run a query which table scans. If your queries return more quickly then they must be using an index (make sure data isn't in memory for the test). They may be scanning the index which would be quicker - how much depends on the width of the index and width of rows.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-03-29 : 19:36:34
|
| Run profiler to capture a representative workload and feed it through the Index Tuning Wizard, as well as recommending new indexes, it can indicate if indexes are not being used and will recomend dropping them.HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
|
|
|
Determining if an index is being used?