January 26, 2009 at 2:47 am
I've heard that if an index grows beyond a certain point SQL Server is unable to use it.
That is, if you had a 100 billion row table with a compound index then the index wouldn't work.
I've heard this from a number of credible sources but not the specifics as to "how can you tell if your index is too big for SQL Server".
Has it got something to do with some or all of the following
1. Installed memory?
2. An OS limitation thing?
3. A limitation of 32bit SQL which could be overcome by using 64bit SQL?
January 26, 2009 at 8:58 am
SQL Server uses the query engine to decide which index to use - if it is cheaper to not use the index it won't use it.
The cost is based on cpu, memory and io that is required to use it - if you know better than the optimizer then you can force the use of a specific index using a query hint SELECT * FROM TABLE WITH(INDEX=ix_ixname).
I haven't heard of a specific size limit to an index and the way the meta data (iam's etc are structured I would be surprised if there was a limit)
Ed
January 26, 2009 at 9:36 am
David.Poole (1/26/2009)
I've heard that if an index grows beyond a certain point SQL Server is unable to use it.
May I ask where you heard that from?
As far as I know, there's no size limitation on indexes where suddenly they become useless. The only thing that I know about is the threshold where seeking on an index and then doing a bookmark lookup (key lookup in later versions) becomes more expensive than a scan of the table. That's a percentage of rows in the table, not related to the size of the index itself.
For that, see - http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
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
January 26, 2009 at 9:36 am
I've never read anywhere that there's a maximum size to an index. Wouldn't make sense if there were, since the index really can't be bigger than the table. How would it make sense to scan a large table over seeking in an index on that table?
The size limits I know of for indexes are all horizontal. To be precise, no more than 900 bytes of key values.
So, I'd have to say that the max size datum is probably coming from someone who messed up an index, and decided to blame it on the size, instead of on the selectivity, key columns, etc.
- 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
January 26, 2009 at 12:48 pm
GilaMonster (1/26/2009)
David.Poole (1/26/2009)
I've heard that if an index grows beyond a certain point SQL Server is unable to use it.May I ask where you heard that from?
I heard it from one someone who specialises in data warehouses. I also saw a variation on the comment on http://www.sqlservercentral.com/Forums/Topic430004-203-23.aspx posted by LeeBear35 but I think that was a reference to selectivity of BIT fields.
It struck me as one of those things that 99.9% of DBAs would never come across but someone working with the billions of records in a large data warehouse would. The largest database I personally dealt with is only 1.5TB so I couldn't speak from experience.
January 26, 2009 at 1:40 pm
David.Poole (1/26/2009)
I heard it from one someone who specialises in data warehouses.
Can you ask then to substantiate the statement? Either prove it with an exec plan (where a covering or highly selective index is ignored because it's too large), or find an official statement.
I've never heard such a statement made by an official source, though that doesn't mean there isn't one.
Index usage on really large tables does get tricky. That's often because the statistics only have 256 steps and, as such, on really large tables, the stats are a poor reflection of the table's data distribution, even when created with full scan. Sampled updates are even worse. A poor estimate can result in a perfectly good index been ignored, but it's not due to the size of the index, rather a bad row count estimate resulted in the optimiser ignoring the index because it didn't look useful.
I also saw a variation on the comment on http://www.sqlservercentral.com/Forums/Topic430004-203-23.aspx posted by LeeBear35 but I think that was a reference to selectivity of BIT fields.
That's talking about selectivity and covering, which is what I showed in the blog post I linked above. See Lynn's reply further down the same page.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply