July 11, 2013 at 1:45 pm
From books online:
Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.
I was having a debate with a co-worker the other day and we were talking about a small table (heap) that had absolutely no indexes. There are 35k rows in just over 1,000 pages.
My co-worker was of the opinion that this was a "small" table. This is a 90+% read lookup table (heap), and I was able to show that after creating a clustered index and a single non-clustered composite index of three key columns and a few included columns that I could now cover the query, and reduced the 1048 logical reads of a table scan to a 2 logical read index seek.
So this specific example aside, and given that mileage will vary, my question is are there general rules of thumb for what is the threshold for a "small" table?
I started thinking in terms of storage internals, that if a table is sized so it fits in under 8 pages (a single extent) that SQL Server would effectively read the entire table during a normal disk read, then there would be absolutely no benefit from indexes. It seems like once we cross the 8 page threshold, adding at least one non-clustered index should prove beneficial. I know now we're venturing into a bit of the theoretical realm, but I'm curious what are the technical boundaries for when we can expect to see benefits.
For the purposes of this (theoretical) discussion lets leave out the "tipping point" and assume any non-clustered indexes we may create are covering, and that queries are not predicated on the clustering key. There may be other assumptions necessary for a debate, so please feel free to add.
Thanks,
Jared
July 11, 2013 at 2:11 pm
With 35K rows (1000 pages!) I would definitely have an index on it. How important the index will be depends on how big the table is, what kind of data it contains, how often the table is referenced and how complex the queries are (JOINS etc.) will be.
My rule of thumb is that if the table can fit in one page and will never grow beyond that, then an index is really not very important. Most of the time these sorts of smaller tables are key value look-up tables and without a primary key to prevent duplicates you would have manage that yourself. Also, as a heap, if you are updating, inserting and deleting rows in the table it can grow and become fragmented. In that case a clustered index will server to manage allocation of table space so its a plus to have a clustered index. Thus, generally speaking it is best for every table to have a clustered index, and much of the time this ends up being the primary key, but that is not always optimum.
You need to understand how the table will be used before you decide on any indexing scheme for it.
The probability of survival is inversely proportional to the angle of arrival.
July 11, 2013 at 4:11 pm
It depends.
If you have a query which reads 1 record from that table each time you run it, but it runs 35K per minute, then it's beter to create an index.
If you have a query that each time reads all 35K records, let's say you join this table to another 5M records table, then any index will be useless, since the scan and hash/merge join will be much better choice than nested loops join.
July 11, 2013 at 4:19 pm
All tables should have an index of the simple reason that all tables should have a primary key. (With one possible exception: tables that should only have a single row, a condition that may be better implemented with a trigger).
This consideration overtrumps the discussion that Books Online is wandering into.
And a 35000 row table is certainly not a small table in this regard, and it's big enough to cause a performance disaster if there are no approriate indexes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 11, 2013 at 5:17 pm
To be honest, I'd test out indexes on a table no matter how small it is (OK, maybe not a 1-row table). If the index improved query performance, keep it. If it doesn't, drop it.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply