Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ago (go-live), and has grown steadily to 80gb+. They did schedule a maintenance job to Shrink all DB's each Sunday night, limit 50mb, free space 10%.

At it's current rate of growth the server's drive space will be maxed in less than 90 days, and I'll be forced to either upgrade the server or move the DB to a SAN. Overall system performance is terrible, system is just plain slow.

When I run the "disk usage by top tables" report I see the following:

  • Number of records: 115,117,737
  • Reserved (KB): 83,609,584
  • Data (KB): 81,867,248
  • Indexes (KB): 52,263,296
  • Unused (KB) 1,742,336

I am waiting to hear back from Epicor regarding proper care and feeding of their system, but the Indexing seems to be using an insane amount of space. As stated, I only recently took over this system and I'm trying to figure it out as quickly as I can.

Any help would be appreciated.

EDIT: Warning to anyone who reads this. Dropped the duplicate indexes as described in the answers here completed hosed by test Epicor 9 database. E9 apparently uses indexes in a non-standard way. It has a Progress layer which optimizes the indexes for Progress and 4GL (aka ABL) code. I can't state definitively that this caused the problem, but it seems the most likely culprit. Attempting to log into E9 Test gives a "SQL Scripts need to run" error and the database appears to be stuck in an ALTER state.

share|improve this question
    
Possible duplicate or unused indexes that could be removed? Statistics carried/created on every table column by default that could be removed? This one might be risky as the vendor might be depending upon them in their queries. –  jl01 Mar 2 '12 at 21:14
    
Link to Kimberly Tripp post on duplicate indexes: sqlskills.com/BLOGS/KIMBERLY/post/… –  jl01 Mar 2 '12 at 21:21
5  
If you're taking advice from someone that suggested setting up a scheduled shrink task, get a different advisor! See Why you shouldn't shrink your data files. –  Mark Storey-Smith Mar 2 '12 at 22:47
    
Haha, the person giving the advice was the Epicor Rep. Hence my decision to look for help elsewhere. –  bluehiro Mar 2 '12 at 23:36
1  
So uh your database is growing by 1000% a year? I'd recommend hiring a SQL Server expert consultant (alongside whatever Epicor recommends) because that's not sustainable unless you have a hell of a budget. (In other words, this problem is beyond the scope of what you should be asking the internet for advice on.) –  Simon Righarts Mar 3 '12 at 2:05
show 5 more comments

3 Answers

Something else that can cause ridiculous growth is failing to backup the transaction log frequently. It will grow until it uses up all your space if you don't back it up. This is separate from the database backup. Check out the size of your transaction logs too.

share|improve this answer
add comment

Take a look at this blog Removing Duplicate Indexes by Kimberly Tripp and use the scripts to check if you have a completely redundant/duplicate indexes

share|improve this answer
    
Thank you checking it now –  bluehiro Mar 2 '12 at 23:37
    
Ok, I checked and there are 73 dupes, how should I get rid of them? Do I just run the "DROP INDEX..." statements? (seems like the obvious next step) –  bluehiro Mar 2 '12 at 23:59
3  
@bluehiro Honestly, you should get some expert help. You've inherited a pup that's getting unwieldy after 7 months at 80GB. In 6 months time it will have moved from unwieldy to unmanageable. In a year you'll be shouting at the vendor and users will be shouting at you. Engage the right expertise now to help you work with the vendor to resolve this. If the over-indexing is as bad as your initial investigations suggest, one can only imagine what other horrors might emerge. –  Mark Storey-Smith Mar 3 '12 at 1:08
    
@Mark it's the weekend now, I haven't had a chance to drop the indexes but I will Monday on our test environment. If it makes a ginormous difference, then yay. But odds are I will be looking for a SQL Consultant to help us straighten out this disaster. –  bluehiro Mar 3 '12 at 18:48
    
EDIT: Warning to anyone who reads this. Dropped the duplicate indexes as described in the answers here completed hosed by test Epicor 9 database. E9 apparently uses indexes in a non-standard way. It has a Progress layer which optimizes the indexes for Progress and 4GL (aka ABL) code. I can't state definitively that this caused the problem, but it seems the most likely culprit. Attempting to log into E9 Test gives a "SQL Scripts need to run" error and the database appears to be stuck in an ALTER state. –  bluehiro Mar 5 '12 at 16:30
add comment

If the indexes are growing like crazy, my gut feeling tells me that it is some bad or unnecessary indexes that are killing the disk space here.

I work with DB2 (as opposed to SQLServer) but I believe the concepts should be the same.

In our case we look to see what the index cardinality is. In other words, how many unique values are there per the columns in the index? The more unique values you have the better. If you have an index over a status field that only has two or three values and you have millions of records, the index has to be continually rebuilt. Kill those indexes as those will take lots of disk space, plus kill performance of your apps when doing INSERTs or UPDATEs.

Also check for redundant indexes (ie the same columns are used over and over again in different indexes). I can't speak for SQLServer, but at least in DB2, redundant indexes are worthless and just chew up disk space. DB2's Query Optimizer will only use one of the indexes and disregard the rest.

Again...I'm coming from a different product, but I believe the thought patterns are the same. Analyze the indexes and the data used by them to see which are really necessary. Dropping a few indexes may save you quite a bit.

share|improve this answer
    
That would certainly fit the performance issues we're seeing. –  bluehiro Mar 2 '12 at 23:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.