August 17, 2014 at 7:13 pm
Hi,
I have a database for a packaged software that we use for Point of Sale. We have the database on a IBM SAN device and the spindle on which the data file (.mdf) is installed is 271 GB available space after RAID 10. The .mdf file is currently 251 GB and growing fast. I also have another spindle with 271 GB available space with RAID 5. I want to split the database between these two drives.
Another thing I noticed is that in the top 15 tables (by data size) the index size is pretty huge. It is almost equal to the data size in some of tables and around 50% of data size in others. Is this normal or is there something wrong with the design of the database
Any help would be appreciated. I am not a trained or qualified SQL Administrator and I am in this role by reason of heading IT in our small company.
Regards
Sudarshan.
August 18, 2014 at 12:08 am
The RAID 5 will obviously be slower, so you'll need to be careful, especially with tables that are written to quite a lot.
You could move the non-clustered indexes over to a new filegroup, rather than entire tables. Check this link for moving the non-clustered indexes:
http://technet.microsoft.com/en-us/library/ms175905%28v=sql.90%29.aspx
You could also consider table partitioning http://technet.microsoft.com/en-us/library/ms188706%28v=sql.90%29.aspx and archiving older data.
Also check out the fill factor for the indexes and data_compression on the larger tables.
Make sure you test everything on a copy of production first!
With that database size, you are moving into areas where you should have a DBA or consultant take a look and give some advice.
August 18, 2014 at 10:25 am
As far as index sizes, there certainly are cases where an index can be nearly as large as the base table itself, and I'd argue that if that is the case that there is probably some kind of application or database design issue. Before I'd go that far I need to know if any index maintenance is being done on the database? Are you ever rebuilding or reorganizing the indexes to reduce fragmentation? Fragmentation can cause the size of an index to grow beyond what is really needed.
You should look at potentially moving indexes onto a separate file group and possibly partitioning, but as this is a vendor database you'd need to work with them, especially for partitioning as changes like this could violate a support agreement.
Since you are on a SAN (I'm no SAN expert), I'd think you could just expand LUN that is being presented to the server for the data drive and have space. My understanding is that this is one of the benefits of running on a SAN.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2014 at 10:48 am
You will need to know what kind of growth rate these two databases will have over the next year to 5 years unless your SAN admin will simply give you a new drive that is 2 TB or so. Once you have another drive you would simply schedule downtime, have everyone disconnect from the database, back it up and then restore it to the new drive.
August 18, 2014 at 3:45 pm
Thank you for all your suggestions. I shall start considering all the options presented. I will be working closely with our Product vendor on this and so hopefully we will not have any support issues later on.
August 18, 2014 at 3:52 pm
Table partitioning - one of the solutions suggested above really had me excited for a minute. Then I realized that we were on SQL 2005 Std edition for 64 BIT. I should have mentioned this earlier :blush:
August 18, 2014 at 4:14 pm
pssudarshan (8/18/2014)
Table partitioning - one of the solutions suggested above really had me excited for a minute. Then I realized that we were on SQL 2005 Std edition for 64 BIT. I should have mentioned this earlier :blush:
Lookup "Partitioned Views", a "ppor man's" version of "Partitioned Tables". Sometimes I think they're actually better than table partitioning although they DO have their own set of whacko caveates.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2014 at 4:59 pm
Jack Corbett (8/18/2014)
As far as index sizes, there certainly are cases where an index can be nearly as large as the base table itself, and I'd argue that if that is the case that there is probably some kind of application or database design issue. Before I'd go that far I need to know if any index maintenance is being done on the database? Are you ever rebuilding or reorganizing the indexes to reduce fragmentation? Fragmentation can cause the size of an index to grow beyond what is really needed.You should look at potentially moving indexes onto a separate file group and possibly partitioning, but as this is a vendor database you'd need to work with them, especially for partitioning as changes like this could violate a support agreement.
Since you are on a SAN (I'm no SAN expert), I'd think you could just expand LUN that is being presented to the server for the data drive and have space. My understanding is that this is one of the benefits of running on a SAN.
This was a great idea. Instead of splitting the data file across 2 LUNs as per my original concept, we basically took 2 hard drives from the RAID 5 LUN and added it to the RAID 10 Data LUN and extended the drive from windows disk management thereby increasing the space by 135 GB. So, the issue is resolved for now. Thanks for the idea Jack.:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply