Auto Grow is the bane of my existence
Posted by: mrdenny
Auto Grow, Performance Problems, SQL Server
OK, that’s not entirely true, but it’s pretty damn close. The auto grow feature of SQL Server shouldn’t be counted on. It should be disabled, or at least used on as an emergency basis only.
Having the auto grow setting enabled will cause your database to grow when ever it needs to, not when you want it to. It will also cause fragmentation on the disk, as the physical database files will end up becoming fragmented as your various database all grow as they need fragmenting the files across the disks.
Look at your databases, and figure out how much they are growing. You’ll want to preallocate the space to the database so that the database space is allocated all in a single chunk on the disk. This will allow the disk to more easily load data from the disk into the buffer cache as all the data from a single database will be contiguous on the disks. If the databases and fragmented because of auto grow then as you are trying to load data from the disk, the disk will need to keep moving from place to place sporadically reading data from the disk which takes more time; and more time is bad.
Denny
2 Comments
RSS Feed
Email a friend
