June 21, 2008 at 8:41 am
Kim Tripp and others have written about the negative impact that can arise from excessive log file (LDF) autogrowths (http://www.sqlskills.com/blogs/kimberly/default,month,2005-06.aspx for one). Proper sizing the log file from the outset is way to avoid this. This problem can be readily identified with dbcc loginfo(). And the problem can be easily remedied by shrinking the log file and then immediately altering it to the desired (larger) size with a better growth increment.
But I have not seen any discussion of this type of effect on data files (MDF, NDF). Can anyone answer this question: Is it or is it not a performance problem if I discover a 30GB database that has been autogrown thousands of times in 10MB increments?
If it is no problem then I'll just fixup my growth increments to address this going forward.
But if this can cause a performance hit, then
a) How can I identify these situations? I don't see anything akin to dbcc loginfo to help analyze data files, and I don't see any way to determine the originally-specified file size.
b) Has anyone come up with a methodology for remedying it? Would a similar approach to the one described above for log files work?
June 21, 2008 at 8:44 am
I should have mentioned that while I believe this is a topic that applies equally to SQL 2000 and SQL 2005, and I'm keenly interested in both, my current focus is with one particular SQL 2000 server.
So if there is a difference between SQL 2000 and 2005 that affects this discussion, please present it!
June 21, 2008 at 8:50 am
Hi Mike,
10MB growth for a 30GB database is too low, try setting the autogrow value to 5%.
Is there any bulk insert or DBCC running on the database?
Regards..Vidhya Sagar
SQL-Articles
June 21, 2008 at 9:04 am
The only way I know of to check this for data files is to use the analyze feature of defrag. With a 30GB data file grown in 10MB chunks, I would expect file level fragmentation (and quite a bit at that).
I have heard about several cases where defragmenting the data file itself has improved performance drastically. So, what I would do is:
1) Defrag the file
a) Easiest way to do this is a backup/restore. The restore operation will lay down a new contiguous file.
b) Use a defrag tool like Diskeeper which is supposed to work with SQL Server (never tried it myself).
2) Reset the autogrowth to a much larger size (5GB), depending upon your I/O system and how long it would take to grow that file. Since you are on 2005 - make sure you have instant initialisation turned on for the SQL Server service account and this won't be an issue.
3) Immediately following the restore, grow the database so you have at least 20% of available space in the data file. Set up monitoring and schedule growing the database as needed to maintain the available space. In other words, don't rely on autogrow to grow the database - only rely on autogrow to save you when you haven't been able to schedule the growth yourself.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2008 at 9:54 am
Autogrows can slow performance and fragment the file on disk. The impact depends on your system.
you should be monitoring space, keeping enough pad in the files for 3-6 months, and manually growing them by enough space for another 3-6 months when needed.
June 21, 2008 at 10:00 am
Vidha, Jeffrey, Steve: Thanks. I am confident what to do going forward, and agree with your suggestions. But that's not really the point of this posting.
I just started administering a new server, this is how I found it. Lot of 10MB growth increments on multi-GB databases. I am reasonably confident that the excessive LDF autogrowths are indeed a performance problem and am taking steps to fix this.
But what I have not read anywhere is whether or not performance is similarly degraded by excessive autogrowths of data files.
Vidhya, I don't know if any bulk-loads are happening or not. I run various DBCCs. But I don't think this matters, am not concerned how it got this way, or how to prevent it going forward.
Jeffrey, I had not thought of looking at disk-level fragmentation. Just checked the existing fragmentation, using built-in windows defrag tool. It only reports on the top 20 or so fragmented files, but there is good info there. The LDF fragmentation seems to correlate very well with the dbcc loginfo output. And I do see some other MDF, NDF files in this list. Very interesting. I'd have to go through some hoops & obtain special permissions from several business elements to take the outage required for backup/restore. I think I could achieve same end result by just introducing new right-sized files, and using ALTER DATABASE to gradually move the data over to the new files, eventually removing the heavily fragmented ones. Going to check into this....
June 21, 2008 at 10:06 am
I should have said this:
"But what I have not read anywhere is whether or not performance is similarly degraded by excessive autogrowths of data files which have already occurred in the past."
June 21, 2008 at 2:20 pm
It's possible that they are. the reason is that read aheads, scans of indexes or heaps, etc., don't take place in contiguous order, at least not outside 10MB. Any extents allocated for a table might end up being spread all over the disk, EVEN IF you have rebuilt the clustered or NC indexes.
How much performance? Hard to say. You'd have to run tests, then clean things up and rerun the tests.
June 21, 2008 at 2:33 pm
Steve, thanks. I agree totally, am pursuing this right now. I'll post before/after results. I'll be surprised if fixing this makes a noticeable improvement, but there are lot of people who are adamant about this with LDFs, so maybe? I'm open to advice on appropriate metrics for such a before/after test. I think physical IOs would not be affected. But measuring time, and maybe perfmon physical disk counters might be good.
On a side note...I just took a new job, am trying to get comfortable with it all, requires a little push to get started. Plus it's pouring here in Tampa today. So I don't feel overly geekish hanging out on a SQL forum all day on a Saturday in June. But what's your excuse? Don't you have a life? Reading your editorials, I got the impression you did. Or maybe you're pulling this whole thing off via a PDA while you're outside basking in the sun?
June 21, 2008 at 3:29 pm
Ugh... thousands of growths probably also mean thousands of fragments for the database and thousands of fragments for the underlying file. For the same reason that people use defrag software on their files, thousands of previous autogrowths can really impact the performance of a server simply because of fragmentation and all that goes with it whether it's an LDF, MDF, NDF or any other file type.
A good defrag both at the operating system level and on the database would be a good thing. Some defrag software is capable of doing a defrag even while a database file is attached and active.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2008 at 4:34 pm
Mike Good (6/21/2008)
a) How can I identify these situations? I don't see anything akin to dbcc loginfo to help analyze data files, and I don't see any way to determine the originally-specified file size.
Have a look at the CONTIG utility. It won't give the original file size but it will show how fragmented an .MDF has become.
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
contig -a myDatabase.MDF
June 21, 2008 at 5:00 pm
Good call! I've used contig in last couple months, and it's right there in my Bin. But I did not think about it in this context--never used it with SQL files before.
a) I can use contig to analyze how many fragments in each DB file
b) I can use contig to defrag the DB files (with SQL online, or so it appears so far)
Thanks for bringing this up!
So assuming I use contig to successfully identify and defrag my existing MDF and NDF files that were heavily fragmented due to running for years with very small growth increment.... Is that it? No need to worry about some linked list internal to SQL that has to be traversed due to the thousands of file fragments that were added by small autogrowths?
The other approach I've been trying today is using shrinkfile to empty data out of heavily fragmented MDF/NDF files, into a temporary file that I just added to the same filegroup. Then I figured I'd right-size the original MDF/NDF, then empty the data back out of the temporary file into the original, and drop the temporary file. Should leave me with a right-sized MDF/NDF file, that has only one or two fragments.
Problem I'm running into so far is where the DB has all data in the primary MDF file...emptying it appears to move the data over to the temporary file, but it does not shrink the primary MDF. The output of shrinkfile indicates that the data has indeed moved over to the temporary file (used & estimated pages equal 72 on the original MDF, and equal large# on temporary file). But just can't make the primary MDF get smaller. I'd like to know how to work around this.
Even if I could figure out how to make the MDF smaller, this is a slow & tedious approach. Contig would certainly be easier & faster.
June 21, 2008 at 6:44 pm
Rebuilding the indexes after that might help... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2008 at 9:45 am
If you read the internals books or dig into BOL, there are pages inside SQL Server that create a map for all allocations. Just as pages have a map of sorts for the rows on the page. When you defragment from contig, those internal pointers are still valid, the physical fragment moves, but the logical pointer that gets there doesn't. The file still has extent1, extent2, extent3, etc., after defragging. As opposed to
extext1 someotherfile.txt extent2 anotherfile.txt extent3
So while there isn't anything to worry about from an integrity standpoint, I wouldn't' do this online. I recommend against diskkeeper or similar products running online.
June 23, 2008 at 4:12 am
I had a similar issue using SHRINKFILE EMPTYFILE to move the data and then being unable to release the empty space.
It seems that in this situation the file needs to be closed and reopened before the space can be released. This can be done in a number of ways, choose the one that is best for you... a) Put the database (or the filegroup!) into readonly state then back into read/write b) Put Autoclose on the DB and monitor for a close/reopen event, then remove Autoclose as this hurts performance, c) Stop/Start SQL Server
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply