January 13, 2013 at 12:42 pm
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.
April 23, 2013 at 2:18 pm
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.
April 23, 2013 at 4:22 pm
Yeah, that's the official story. But I don't 100% buy it.
With that many different fragments, if I were you, I'd run contig.exe on that(those) file(s) anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 23, 2013 at 4:40 pm
ScottPletcher (4/23/2013)
Yeah, that's the official story. But I don't 100% buy it.With that many different fragments, if I were you, I'd run contig.exe on that(those) file(s) anyway.
I agree with Scott on this one. I have seen plenty of Dell Compellent SANS (and others for that matter) that have had this kind of fragmentation issue. A simple defrag decreased IO stalls on the attached LUNS and improved performance from an IO perspective by 10% or more.
I even had a SAN admin on the phone once toeing the line with the same response as DELL. We did a simple test to compare. He was astounded by the performance gains by defragging a LUN attached to the SAN.
Moral of the story - you should probably test and not just go on their word.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply