June 30, 2009 at 6:57 am
Hi All,
We have one database(total size: 375 GB). In this database about 70% space is used by indexes. Please advice me what should I do to reduce index sizes?
Thanks in advance.
June 30, 2009 at 7:32 am
Look at the DMV's for index usage.
taken from https://www.sqlservercentral.com/blogs/brian_kelley/archive/2006/06/12/639.aspx
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns
sys.dm_index_usage_stats
The first two allow us to identify cases when we need an index but one isn't present. If we see a lot of hits showing up on a particular column or column(s), then we should consider adding indexes appropriate. The third one allows us to see which indexes aren't being heavily used, if at all. Indexes which get little or no use will show up in the third DMV. If we don't see usage stats increasing for a particular index, it's a good candidate for elimination.
Depending on the usage of the database, a lot of indexes doesn't have to be a bad thing. When the database is accessed mainly for reading purposes these SELECTs can highly benefit from the indexes. Only INSERTing and UPDATing rows will take a bit longer because the indexes also have to be updated. And of course backup and restores take longer.
You have to analyze the usage of the database and act according to your findings.
June 30, 2009 at 7:34 am
Two options, identify indexes that are not used or that are too big and eliminate them, as per the previous post. Another option is to upgrade to 2008 and use compression.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply