December 13, 2007 at 8:18 am
Hi,
Is there a rule of thumb or general idea of how much free space should be available on the disks where the data files resides or how much space should be available within data files itself?
Let me explain the situation in short. On our 6.7 TB database server, we have total 300 GB left totalling all the free space on all the data files. Database Files are expanded to the full capacity on the each drive letters. Database growth is about 12-18 GB per day. We had requested management to dedicate some more SAN space on the disks. In stead they would like to us to truncate couple of work tables to free up space. Truncating these tables will free up another 300 GB. That will get us through the holiday season and then we will get some downtime to reindex tables with several hundred million /billion rows. ( sql server version 2000).
I am trying to convince them that the performance issues that they have started experiencing are because of lack of free space too. Reindexing hasn't happened on teh databases in over a year. I am sure page split is already happening without reindex and now sql server can not find contiguous space on teh disks to write to the tables. I am looking for some articles to send to the management as proofs that we will even need space to reindex.
( I can not run showcontig as some of the tables are over 200-300 gb in size . This is OLTP system with very heavy load due to holidays. So, I don't have proofs in terms of statistics from sql server.)
Could you please point me to the articles that I can show to the management that we need at least 30% or whatever % free space on teh data files/drives for sql server as a good practice.
I have been at this place for 8 weeks now. Issues are much bigger the space issue like bad application design, no maintenance, no archiving of data..I am trying to tackle things one at a time right now. So, please do not post discouraging comments on the dba ability.
Thanks
December 13, 2007 at 3:01 pm
I don't think the amount of space available actually affects the database performance. I could be wrong, but I've never run into any data indicating that would happen, and I've never run into it in any of my databases.
Indexes that are a year out of date are probably the first performance issue I'd worry about. That and table fragmentation. Judging by what you wrote, you probably can't do anything about either of those right now, and are already planning on fixing them ASAP, so nothing to add to that.
It sounds like you inherited a riteous mess, so good luck on sorting it all out. Hopefully someone will have better suggestions that I.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2007 at 4:16 pm
I'll respectfully disagree with GSquared. If the queries run do a lot of disk I/O then having disks at near-capacity can indeed impact performance. That's not to mention the fragmentation (index as well as hard drive) issues.
I've got a bunch of articles, etc. for 2005 but they won't really help with 2000, unfortunately. Check out http://www.sql-server-performance.com
here's a few links that might help:
http://support.microsoft.com/kb/917047/en-us (tempdb aand I/O)
http://support.microsoft.com/?scid=ph;en-us;2852 (sql2K solution center)
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx (SQL Server best practices)
You're definitely on the right track. No disparaging here. Good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply