September 30, 2010 at 12:19 pm
Hello All,
It is advised not to have more indexes on a table as it reduces the performance; I have not used more than 27 indexes on a table. My question is we have 250 (1 clustered and 249 non clustered indexes) and I am sure that we may not have created more then 30, then why did Microsoft has increased the number of indexes from 250 in MS SQL server 2005 to 1000 indexes in MS SQL server 2008.
Can anyone help me...?
Thank you,
Vinay
September 30, 2010 at 1:38 pm
I asked around and the consensus seems to be that, because of filtered indexes and sparse columns, the number of indexes in support of larger tables using these new constructs had to go up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 2:11 pm
Bear in mind that in read-heavy (or read only) situations (reporting DB, data warehouse) many more indexes may be desirable than for an OLTP system.
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
September 30, 2010 at 2:29 pm
I'm in agreement with Gail.
In a star-schema data warehouse you usually end up with one index per each dimension pointing to a fact table so, "n" dimensions pointing to a fact table will translate into having at least "n" indexes on that particular table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 2:33 pm
GilaMonster (9/30/2010)
Bear in mind that in read-heavy (or read only) situations (reporting DB, data warehouse) many more indexes may be desirable than for an OLTP system.
Another exemple. I have a system where the search in our catalog takes a staggering 98% of all ressources on the server.
Most of that is on the items table.
With that in mind it makes very good sense to heavily index that table for all possible search options.
Also in my case, the base table only contains 25K items and the space used is around 50 MB.
With all those reasons I just don't care to build 20 covering indexes which adds 200 MB of data in the DB. That could make sense for our big rush time of the year but I have not gone ahead with that plan yet.
Moreover that table is never updated manually (or very rarely 1 item once in a while), but only with unattended file uploads so in that specific real world scenario, having a ton of indexes makes perfect sense.
Altho there's no chance to ever get up to 1000 indexes unless for some reason I would decide to build a filtered index for each categories and types values in that table.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply