Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Indexes - 2X the database size - is this a problem?

Posted on 2013-06-21
3
Medium Priority
?
376 Views
Last Modified: 2013-06-22
I have a SQL Database with 1 primary data table in which most fields are used (about 20 columns). Index optimisation over a few months has resulted in 5 indexes. The concern I have is the database is 50Gb but the indexes take a further 95Gb. This simply feel wrong to me.

Is there a tool that can analyse the existing indexes, and the frequency of use of the included elements and suggest more efficient formats?

I have used profiler to capture transactions, and used database tuning to recommend indexes. I have then checked these against my knowledge of the queries and combined where logic says I should. This has been an on-going process and the database is fast; no queries taking longer than a second or 2. But having the indexes take up more "space" than the data suggests I have missed a trick.
0
Comment
Question by:Bird757
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 1500 total points
ID: 39267672
Sql takes track of the usage of the indexes. See this article http://blogs.msdn.com/b/craigfr/archive/2008/10/30/what-is-the-difference-between-sys-dm-db-index-usage-stats-and-sys-dm-db-index-operational-stats.aspx

but be aware of initialisation of these statistics on server restart or even index rebuild
0
 

Author Closing Comment

by:Bird757
ID: 39267719
Thank you. That points me at which indexes are being used abd how often. From there I should be able to get the index sizes down.
0
 
LVL 25

Expert Comment

by:jogos
ID: 39267725
Important is not to look at each index in the same way

Clustered index (basicly your table, size !) is the most important to start. The choise of the  clustered key is important to how inserts of new records will fragment your index or not. But also the size of the clustered index key will define the size of all other indexes.
https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

Non clustered indexes can be suggested from an opimiser, but not always good to blindly create the suggested indexes, it can be good to extend a previous index so it covers also the new suggested index.    And always a trade-off should I add more columns to the index key or the include columns so the index is all the query needs and the table won't be used during the query. Or do you limit the index in size for good filtering and then get the data from the table.

Don't forget that an optimiser can suggest an index based on the usage of your database, but   the gain can also be in not selecting fields you don't realy need (at that point) so your query can be very good off with a smaller index.  Example see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28163616.html
So when a query for current employees could gain with adding the active-column to the index and adding the birthday and the marital status as included columns for that index.  But maybe it's smarter to not adding those columns to the index but removing them from the query while the only thing you need is it's name and department (SELECT * is the killer)

For limiting the index size take also a look at filtered indexes, they can very good for optimizing frequently fired queries and not taking up much space (disk space, disk  I/O and cache) http://msdn.microsoft.com/en-us/library/cc280372(v=sql.100).aspx

Multiple indexes means also more work for each insert, update and delete. If you include a daily changing column in multiple indexes then that can be very costly.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question