Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Data File Physical Fragmentation From Small Auto-Growth Setting Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 7:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:15 PM
Points: 258, Visits: 1,103
I've placed this post in the SS2005 Performance Tuning section of the forum because it was totally ignored in the 2012 section of the forum.

The server is SS2012.

________________

To All,

I need some advice. I've just started a new job and one of the first problems I've encountered is that a 365 GB database has auto-grown 1 MB at a time since the day it was created.

3 years ago, the database was on direct attached storage. Then, it was moved to a Dell-Compellent SAN, a system for which I am a certified administrator. The database migration involved a database backup and a restore to the SAN which eliminated the physical fragmentation. But after that, it kept auto-growing in 1 MB increments. So, it probably has at least 100,000->200,000+ 1MB file fragments scattered all over multiple disk drives.

I am going to review the performance implications of this condition with Dell-Compellent.

In the meantime, I'd like to hear from any of the community on this forum:
1. If you've had this situation, how did you respond to it?
2. Is the potential for server performance degradation reduced if the database data file is on a SAN?
3. If I must take action to rectify this condition, what are my best options?

On the last question, I'm considering 3 options:

1. A full backup and restoration of the database:
1a. Backup the database.
1b. Take the database out of production.
1c. Delete the database.
1d. Recreate the database with appropriate initial and auto-growth settings.
1e. Restore the database.
1f. Reconnect the Login and User SIDs.
1g. Resume production.

2. Take a SAN based snapshot of the database and use it to replace the existing database. (I don't think this will produce the desired results and I'm going to review it with Dell-Compellent.)

3. File defragmenting software. There is a file defragmenting program available from Microsoft that is specifically designed to turn physically fragmented files into contiguous files: http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx. Contig.exe Version 1.7. It sounds promising.

I would be very interested in your responses, especially if you've actually had to solve a similar problem.
Post #1408422
Posted Thursday, January 17, 2013 8:12 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:15 PM
Points: 258, Visits: 1,103
Wow. Not a single response.

My post was either a really good one or a really bad one.
Post #1408695
Posted Friday, January 18, 2013 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
I only considered doing this once but never had to carry it out (we ended up migrating to new servers) so was curious towards the responses. I was watching this thread (and the one in the 2012 forum) to see if anyone would jump in, but for lack of an expert wanting to weigh in I'll throw in my lowly two cents.

When I was considering it my plan was to:

1. run CHECKDB
2. take backups
3. stop the service
4. test the restores somewhere
5. move all the database files to another drive available on the same server (no network involved)
6. reformat the drive (you could also defrag the drive here if reformatting would cause other pain)
7. bring all database files back from temporary drive location
8. start the service
9. run CHECKDB

This was only an option because I had the maintenance window and the drive space to do something like this and there were many databases on the instance that were growing at 1MB per grow operation for several years so you can imagine the physical fragmentation that was in play. Moving files is risky though. I would have had good backups but it could have made for a huge setback if one of my files became corrupt during the process.

You only have one file you're interested in defragging so if I had to choose to start somewhere I would maybe try the way I outlined if you have the space and time or try option 3 with the Sysinternals defragger. I haven;t used it but that group puts out some superior tools so I would have no qualms about trying it. I would definitely run CHECKDB before (as my control group) and after (as my compare group) though just to make sure the process was not destructive to any bytes in the file. Of course it should go without saying that taking good backups (and testing they are valid) is highly important before attempting anything like this.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1408855
Posted Friday, January 18, 2013 6:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 8:46 PM
Points: 1,786, Visits: 1,015
Am not sure if I am understanding the question right, there is autoincrement set to 1 MB for the database files. The files are located on indvigual disk drives. Wouldnt simply changing the file factor and rebuilding indexes be good enough the remove any fragmentation , probably shrinking the database too.





Jayanth Kurup
Post #1408863
Posted Friday, January 18, 2013 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Jayanth_Kurup (1/18/2013)
Am not sure if I am understanding the question right, there is autoincrement set to 1 MB for the database files. The files are located on indvigual disk drives. Wouldnt simply changing the file factor and rebuilding indexes be good enough the remove any fragmentation , probably shrinking the database too.




You're thinking of logical fragmentation. This is physical fragmentation, i.e. fragmentation of the mdf or ldf on the OS file system. No commands in SQL Server handle that directly. Shrinking the file may hep though in certain situations, but not likely in cases where multiple files existed and all started at a very small size and autogrew many times at 1MB each.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1408867
Posted Friday, January 18, 2013 7:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:15 PM
Points: 258, Visits: 1,103
Thank you all for your responses.

I've been really busy in my new job. I haven't had the time to contact our SAN vendor, Dell-Compellent, and review the situation and/or potential solutions with them. I'll let you know if they have any interesting assessments and/or solutions.

I know for a fact that a full database backup and restoration will solve the problem but I'm looking at hours of downtime on my employer's most important production database if I do that and I want to avoid it if possible.
Post #1409155
Posted Tuesday, April 23, 2013 2:23 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:15 PM
Points: 258, Visits: 1,103
I apologize for taking so long to respond. I finally had a conversation with Dell-Compellent on the subject of this post.

My email to my management summarizing the conversation follows:

_______________________

I have been concerned about the subject of this email since early January. Today it was satisfactorily resolved.

I was able to have Dell-Compellent’s Copilot Support arrange a conference call with their SAN Architecture personnel regarding physical disk fragmentation.

I have good news: We don’t need to fix it.

The details:
When I first came to work and began to evaluate the SQL Server on the main database server, I found what I usually find when I first look at a database server: The SQL Server default auto-growth settings had not been customized for the company's requirements. Database data file auto-growth was 1 MB. Database log file growth was 10%. By my rough estimates, we should have well over 500,000 physical file fragments that have been allocated for our databases.

This is a major problem on direct attached storage. In this condition, serial data reads create a frenzy of disk head seeks to locate all of the physical file fragments. This wears out disk drives and destroys database server performance. The only cure is to backup the database, delete it from the server, recreate the database with appropriate auto-growth settings, and restore it. This process is slow and causes a significant amount of downtime.

Because Dell-Compellent’s SAN architecture is designed to allocate and store data across multiple disk drives, there is no such thing as a serial read. All I/O appears to be random. Spread across enough disks, it makes I/O more efficient. In actual tests of serial reads, there are a few percentage points of improved performance for storage space that has been allocated in large “chunks” vs. storage space that has been allocated in small “chunks. But in practicality, it isn’t enough to matter.
Post #1445678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse