April 29, 2016 at 2:39 am
Hello experts,
I heritaged a database with few indexes greater than 2GB .What is the impact from performance perspective or should review the indexes?
Thanks,
Hadrian
April 29, 2016 at 2:52 am
Hadrian
Check that the indexes are actually being used, and that they're being maintained properly. Indexes that size are not a problem in themselves. Indeed, you may find performance would be worse in their absence. If you're new to this and performance is a concern, it may be worth getting someone in to review your indexes and performance in general.
John
April 29, 2016 at 8:00 am
2gb index for a 1mb table... yeah, that might be a problem. 2gb index for a 200gb table. Who cares.
There's nothing bad or good saying an index is X size. Indexes are going to generally be as big as the data within them as defined by their key column(s) and any included columns, plus the size of the B+tree that provides the lookup values. If that's 2gb, it's 2gb. The question is, as already stated, are they being used. Also, are the statistics on the index up to date. Those are the questions you need to answer, not how big the index is.
"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
April 29, 2016 at 9:18 am
You don't want indexes on a table that arn't being used (don't benefit queries or insure uniqueness), because they require I/O to maintain whenever the table is inserted, updated, or deleted. As a general rule, I don't get concerned unless index page allocation for a table is > 1/3 the allocation of data page allocation, meaning a TB sized table could reasonably have a 300 GB worth of non-clustered indexes. However, there have been a few ocasions where index page allocation was justifiably > data page allocation. That said, even the best indexes can bloat in size due to page fragmentation. You need to have a maintenance plan in place to regularly check for fragmentation and perform index reorganization to prevent naturally occurring index fragmentation from accumulating over time.
https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
http://www.travisgan.com/2012/09/sql-detect-fragmentation-with-dmv.html
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy