Our database data file is currently enabling auto growth with 1 MB File Growth and Unlimited Maximum File Size. After reaching the initial size, repeated data file auto growth events are occurring. Can these repeated actions cause performance issues?
It goes down to the old "it depends" answer. 1MB file growth can cause issues if your disks are very slow, backbone to the SAN is saturated, your infrastructure is maxed out with other operations. As each transaction which triggered growth has to wait for the OS operation to complete to be able to complete, which has to wait for the SAN, which has to wait for other operations, it's all down to how things are architected and designed.
Now with things like "instant file initialisation (perform volume maintenance tasks)" enabled this can become a thing of the past. Now is 1MB to small of a growth, if your growing a few hundred times a day, is it not best to pre-size it daily with a one off growth out of hours. Is it not worth changing the auto growth setting to something other than 1MB.
Also if you have 1MB growth on your LDF I would seriously suggest reviewing that as your causing yourself a real headache with the amount of VLF's you will have.
Yes they can. Every time the file needs to grow, all activity on the database will stop and wait for the file growth to finish.
It is recommended that you pre-size the database files, and also set the auto-growth amount to be something more in line with the database size - for example if your database needed to be 100GB, you wouldn't then want it growing incrementally in 1MB chunks - maybe better to be 10 or 20GB.
Also ensure that you have instant file initialization enabled - then when the data file needs to grow, it's a faster operation.
Thank you Kev! I also read in an article that auto-growth events make the database physically fragmented. I recently detected that one of our databases runs this event repeatedly over the past week making it physically fragmented and does affect its performance. What actions would you suggest to fix the fragmentation issue? I will really appreciate your recommendations. Thanks!
Are you running on spindle disks? If not, then the physical fragmentation probably isn't an issue. If you are, then you'd have to defrag the drive itself which would probably introduce performance issues with SQL. In fact, you might have to shut the instance down during the process unless it can handle transactional consistency. Honestly, I've never tried that.
17 People are following this question.
