June 20, 2006 at 3:48 pm
I have a SQL Server that is running two applications against seperate databases at the same time. One application, which runs just fine at several clients, has been steadily getting slowere each day as the database grows. A clean database was created and put in place. The application was quick the first day but is getting steadily slower each day.
Facts:
1. The database was created at 100MB with an auto grow of 100MB
2. The database imports files with more than a 100MB multiple times in a day
3. The other application is writting to its database at the same time that the troubled one is writting and getting auto grown
4. The database has grown from 100MB to 1.4 GB in 5 days
6. I want to maintain the database at 6.5GB with my database utility
Question:
Is my problem stemming from the fact that the database is getting enlarged at the same time it is getting written to and the file is becomming fragmented? I was going to delete this database and create a clean one starting at 6.5GB. At this size, it shouldn't have to auto grow. Is this a good assumption of what needs to be done, or am I off here?
June 20, 2006 at 9:45 pm
Yup, the auto-growth is your problem. Growing database files is disk-intensive, and is only happens when the space is needed, which is during writes. In your case, it appears to be happening in the middle of lots of writes - the file imports. Sure, fragmentation can be a problem, but it's nothing compared to what an ill-timed auto-growth will do to you.
Generally, give yourself much more space than you think you need in volitile databases such as yours. You want to grow the files on your schedule (when users aren't using the database), not on the data's schedule (when the users are hitting the database so hard it needs to grow). Where did this 6.5GB number come from? Will this database grow more than 1GB a week?
Be sure you're granting loads of space for the log file(s) as well.
-Eddie
Eddie Wuerch
MCM: SQL
June 21, 2006 at 7:22 am
The 6.5 GB comes from the fact that the current database grew 1.4 GB in 5 days and I want to keep 20 days worth of data in the db before I clean it up. I figure the active data should take up about 6GB. Do you think I should have more space than 1/2 a GB as 'free' space? Could to much 'free' space cause problems?
Thanks for your advise.
Scott
June 21, 2006 at 10:34 am
You can never be too rich, too thin, or have too much free data and log space.
Eddie Wuerch
MCM: SQL
June 22, 2006 at 2:37 pm
June 22, 2006 at 11:54 pm
If you database and log files are on the same disk it's also likely with the autogrow that your database and log files have become interleaved on the disk. To fix this issue you're likely going to need to take the database offline and defrag. If you've got mutiple databases on the same disk and they are all autogrowing, then you're interleaving is between those databases.
If your not interleaved and fragmented on the disk, you may be fragmented within the database files themselves. That's why rebuilding the indexes during off-line hours as Pedro suggests can really help performance. The tables/indexes will be defragmented as a result.
Ideally, since you know the desired size of your database, you'll also want to do one manual grow to that size. That way if you do have multiple databases on the same disk, you won't interleave them.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply