October 8, 2010 at 12:16 pm
I have a daily job that gives me a few reports (IndexesUsed, IndexesNotUsed, MissingIndexes, InefficientIndexes, etc), in my inefficient report, I show some indexes where there aren't any index seeks, but there are index scans. Question, is the index "worthwhile" if there are only scans? I'm guessing that's a relative question, but just in case...
Just curious if I can delete all of my indexes that have ONLY index scans, and no index seeks.
Thanks
October 8, 2010 at 12:19 pm
gregory.anderson (10/8/2010)
Question, is the index "worthwhile" if there are only scans? I'm guessing that's a relative question, but just in case...
Maybe. Index scans are going to be cheaper than table scans in most cases.
It may be that you have queries with non-sargable predicates that need fixing. Or maybe you have catch-all queries. Or maybe the columns are in the wrong order for the queries.
Just curious if I can delete all of my indexes that have ONLY index scans, and no index seeks.
You can. Whether it'll be good for performance or not is another matter.
If you delete those indexes, the queries that were using them will either have to scan a less efficient index or scan the entire table.
Take a copy of the database, a workload from production, delete the indexes and see how performance changes.
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
October 8, 2010 at 12:21 pm
Index scans just mean the index was used with no criteria, or the criteria was broad enough that a scan was a better operation. If there was a better index for it to use, the optimizer would most likely pick the better one.
If you drop an index with no seeks and only scans, there's a 99.9% chance you'll see a drop in performance somewhere.
October 8, 2010 at 12:26 pm
Adding in, the indexes are much, much, smaller (typically) than your data. So if you are scanning these, it is quicker than scanning the CI (as Gail noted).
October 8, 2010 at 12:28 pm
Thanks all!
October 8, 2010 at 2:10 pm
Index scans can actually be the exact thing that makes a query fast, if you're looking for a range of information from multiple rows.
If, for example, you have a table of Orders, with an AccountNumber foreign key to an Accounts table, and you get an index scan on that one when you select all the orders for a specific account, that's a good thing. It means the index is built at least well enough to support that function.
So, like Gail said, you'll have to test it, and look at how it's being used.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply