Most DBA’s in various organizations are confronted with this simple little feature during their normal database-monitoring carrier. Most often the effects of the same are not realized unless we see the side effects of the same in the production code machines. This article would take you through some of the optimizing and practices that are followed in various parts of the world.  

Before I get into the specifics of this feature, let me walk you through how we can use this feature and why on first place it was introduced. There are two options that are possible while configuring the same, the GUI method using the SQL Server Enterprise Manager and the command line mode using SQL Server Query Analyzer. The Enterprise Manager gives a nice little UI to configure such Server level / database level options. And more often than not I’ve seen lots of DBA’s using this simple tool for administration. Shown below a screen shot of the options present at the Enterprise manager for a particular database Datafile/Logfile settings.

The same set of options can be configured using the Query Analyzer also using the “Alter Database” command. So why have we taken this topic for discussion here. Are there any pitfalls in using them? But before we get to the same let me talk out why was this feature given on first place.

Interesting that over the years I see Hard disks are getting cheaper and cheaper everyday. Gone are those days when I used to work with 512K machines and these days even my RAM has more space than the HDD I used a decade back. Having said that the HDD space is cheaper and we would like to capitalize on them for our daily activities. To give you a perspective to the largest database in SQL Sever is a 5.5 TB database (from SQL Server official site). Imagine the amount of data we are talking and imagine the rate of data increase. Hence this whole concept of utilizing the available HDD is surely in the minds of all. And SQL Server capitalizes on this capability to expand the space requirement of a particular database automatically giving you such options.   

We all are not aware of the direct effect of using one option over the other. And to give you a perspective to the trend we get while using a auto-growth and the percentage option (see first figure for the options).

Do you see a trend here? Yes, this is an exponential increase in the HDD space utilization. And one fine day you would see that you have run out of your HDD space. And this is one of the most common questions that are asked at the public newsgroups. Now let me also explore the other option and see the trend we would get.

The trend is surely not that staggering when seen from far. But understand that there is something that is quite obvious we might miss. Let me show you the other side of the coin. The above examples start from 100 MB and use 50MB for static growth and 10% for percentage growth. Now when we use static growth we are unaware of the amount of fragmentation we have made over the years. This is again a factor that is not noticed and left alone. Fragmentation can cost you dear wrt performance. They are a killer and can slow you system as much as 10 times. That’s quite a lot of time.

Note: Whenever the datafile is on the expansion mode then all the reads and the writes are blocked for the period of time.

Tale of TempDB

This is a very interesting part of the whole discussion. And this is what triggered me to write this article on first place. TempDB is a special database and is used by the system for various other activities. SQL Server rebuilds tempdb each time the database system is stopped and restarted. Tempdb files might have auto-grown, which is the default configuration, since SQL Server was last started. SQL Server doesn't remember the size of the tempdb files when it rebuilds tempdb during a restart. Instead, SQL Server resets the files to the size they were manually configured to be.

Let me explain the same with some numbers in place. Consider the following situation. Tempdb in SQL Server 2000 defaults to having one data file that has the logical name tempdev. The initial size of this file is 2MB. Assume that you manually increase the file size to 250 MB by using the ALTER DATABASE command. The file subsequently grows to 500 MB through auto-grow operations. SQL Server will reset the tempdev file to 250 MB when it rebuilds tempdb. Now, assume that no one manually increased the file size from 2MB to 250MB. Instead, SQL Server auto-grew tempdb to 500 MB. In this case, SQL Server resets tempdev back to 2 MB when it's stopped and restarted.

And assuming that we have configured it as 10 Percent growth as the default configuration then we are growing approximately 50 times to reach the 500 MB mark from 2 MB and during each of the expansion process we are blocking any read and write operation on the database. A costly operation indeed. Apart from this there is a high level of fragmentation we are encountered with such setup. I hope the problem in hand is quite clear.  

Homework DBA’s

Now that we have understood how fragmentation and auto-growth can be fatal for applications and database performance. Here is my best bet to tackle this situation. manually setting tempdb files to a reasonable size will help you avoid significant auto-growth operations. Most importantly, if tempdb files do auto-grow, you need to determine whether the new file sizes are reasonable high-water marks. If they are, consider manually setting the file size to compensate. This tip applies to all the databases, which grows considerably over a period of time.

SELECT

      alt.filename [File Name]

      ,alt.name [Database Name]

      ,alt.size * 8.0 / 1024.0 AS [Originalsize (MB)]

      ,files.size * 8.0 / 1024.0 AS [Currentsize (MB)]

FROM master.dbo.sysaltfiles alt

INNER JOIN dbo.sysfiles files

ON       alt.fileid = files.fileid

WHERE           alt.size <> files.size

The above query allows us to find the current status of our databases and their corresponding final file growths. Use further filter conditions to fetch the databases that are of interest to you.  

Conclusion

This articles looks into how a fantastic feature can turn lethal against you if used in a careless manner. Many and most of the DBA’s overlook this topic. And I hope this article has surely brought some awareness in using such features in the future. We will surely take more topics as these while we start learning SQL Server internals more.