SQL Q&A: Shrinking, Growing and Redesigning Databases

SQL databases come in all shapes and sizes and schemas. This month, our SQL expert helps with condensing, growing and redesigning databases.

Paul S. Randal

The Incredible Shrinking Database

Q: We’re occasionally forced to run shrink on our databases because of a lack of disk space, even though I know doing so can cause performance problems. We take care of any index fragmentation afterward. Can you explain why shrink seems to run much slower for some databases than others, even when they’re of similar size?

A: I’m glad you’re cognizant of the side effects of running a database shrink. I also realize that sometimes it’s just unavoidable.

Concurrent database activity and the schema of tables in the database can affect the runtime of a database shrink operation. This means two databases of equal size but with different schemas could take dramatically different amounts of time to shrink.

Shrink works by moving data file pages around to consolidate free space at the end of the file, which it then returns to the file system (by reducing the size of the date file). To move a data file page, SQL Server must acquire an exclusive lock on the page. This means no one else can have any locks or any records on the page. If there’s concurrent activity in the database that involves acquiring locks, shrink could be blocked and then have to wait for the lock it needs. This will cause shrink to take longer to run than if there’s no other activity in the database.

Another factor when shrink moves a data file page is that if any other database structures have physical pointers to data on the page being moved, it must update those physical pointers with the new page location. This isn’t a problem except when a table is a heap (has no clustered index) and/or when a table has one or more large object (LOB) columns stored off-row (separate from the table data record).

When a table is a heap, all the non-clustered indexes on the heap contain physical pointers to the table data records. When shrink moves a data page from the table, the non-clustered indexes need to be updated. SQL Server does this by calling into the Query Processor to perform index maintenance on the non-clustered indexes 100 rows at a time.

When a table has off-row LOB data, the data records point to the off-row LOB data. There’s no back-pointer from the LOB data to the data records, however. This means when shrink moves a text page (containing off-row LOB data), all the data records that point to the LOB data on that page must be updated. As there are no back-pointers, it must perform a table scan to find the correct data records to update. As you can imagine, this process can be pretty slow for a table with a lot of LOB data.

Although shrink can be slow, from SQL Server 2005 onward it provides progress reporting through the percent_complete column of the sys.dm_exec_requests dynamic management view. You can also monitor the Shrink Data Movement Bytes/sec performance counter in the Databases performance object to see how fast shrink is progressing.

Let It Auto-Grow

Q: I’m a new DBA and I’ve been reading a lot of information online about best practices for database settings. I’m confused by the conflicting views on whether auto-grow should be enabled or not. Can I just turn it off without causing any problems?

A: The simple answer is that you should always enable auto-grow, but not rely on it. The general practice is to monitor data and transaction log file size/usage and proactively grow them (or investigate sudden, unexpected growth). Enable auto-grow for emergency cases when whoever is responsible for monitoring file size/usage isn’t immediately available to manage the files.

If auto-grow isn’t enabled for the transaction log file, and the file fills up, then all write activity on the database will be prevented until more space is made available in the transaction log. And if auto-grow isn’t enabled for data files, insert operations or database maintenance operations like rebuilding indexes may fail.

The tricky part is figuring out the auto-growth setting. From SQL Server 2005 onward, the default auto-growth for transaction log files is 10 percent and 1MB for data files. However, a percentage-based auto-growth means that as your files expand, so, too, does the level of auto-growth. This also means the time it takes can also increase if you don’t have instant file initialization enabled. Therefore, both file types should have a fixed auto-growth so auto-grow behavior is predictable.

Having an extremely large or percentage-based auto-growth can be especially problematic for transaction log files. Instant file initialization is not an option, so any newly allocated file space must be zero-initialized. While the zero-initialization takes place, all write activity to the transaction log is blocked. So it makes sense to balance the transaction log file auto-growth so it’s large enough that operations can continue for a while, but not too large that the flow of operations is interrupted for too long.

For data files, a 1MB auto-growth is ridiculously small, but it’s hard to determine the proper value. This depends whether you want auto-growth to be an emergency stopgap measure, or to replace manual data file size management. It also depends on how much new space you need every day to accommodate data being inserted into the database. The bottom line is: You should enable auto-growth, and set it to an appropriate, non-percentage amount.

Storage Schema

Q: I’m redesigning our database schema so queries are more efficient. Some of the tables involved have a lot of character data and I’d like to make sure I’m storing it in the most efficient way. Are there any guidelines or best practices you can share?

A: The way you choose to store your LOB data can have a huge effect on query performance, so it’s vital that you pick the right technique. A detailed analysis of all the options is beyond the scope of this column, but here are a few guidelines:

First, will the data always be less than 8,000 bytes? If so, try a data type that’s (n)char or (n)varchar, but not one of the true LOB data types like XML, (n)varchar(max), varbinary(max), (n)text or image unless it’s absolutely necessary. If you require a true LOB data type because of data size, don’t use (n)text or image as these data types were deprecated in SQL Server 2005. They’re not as functional as the other, newer LOB data types.

Second, if you require a true LOB type, consider whether to store the data in-row (in the same table data record as the other columns in the table) or off-row (stored in separate data file pages with a link from the table data record). If you use the LOB data frequently, it would be better to store it in-row as queries can retrieve it more efficiently. If not, it’s usually better to store it off-row. Occasional queries pay a slightly higher cost to retrieve the LOB data, but the data records will be smaller, leading to denser data storage and overall better query performance. Note that you can only store LOB data in-row up to 8,000 bytes, or whatever amount is possible given the other columns in the data record—after which it’s automatically pushed off-row.

Third, if a table contains a LOB column, online index operations are prevented for any indexes that include the LOB column. By definition, this affects the table’s clustered index. For this reason, some people store the LOB data in a totally separate table (vertically partitioning out this LOB column), and then perform a JOIN operation between the main table and the LOB table when the LOB data is required by a query. This incurs a bit more storage because of the complexity of the JOIN, but allows more choice of index maintenance strategy.

You may also be concerned about fixed-width versus variable-length data types, and possibly even require fast streaming access to the data, in which case you should consider the SQL Server 2008 FILESTREAM data type. For a more in-depth analysis of all the LOB data storage types, see my blog post, “Importance of choosing the right LOB storage technique”.

Critical Checks and Balances

Q: I’m reworking the database maintenance practices at our company and I’m going to start running DBCC checks on our critical databases. How often I should run a check on each database?

A: Proactive consistency checks are a vital part of any comprehensive database maintenance plan—for user and system databases. It’s also important to use a page verification method. For SQL Server 2005 databases and later, enable page checksums. For SQL Server 2000 databases, use torn page detection.

As far as consistency checks are concerned, it’s hard to give an absolute answer for how often to run them. I generally recommend running them as often as possible, at least once a week. The optimum frequency of consistency checks for you is a classic “it depends.”

Here are a couple of the factors to consider:

First, what is your maintenance window? Consistency checks consume large amounts of CPU, memory and I/O resources, so if the maintenance window of when you can spare these resources is shorter than the time it takes to run all the consistency checks, you may not be able to check all databases at once. You may need to stagger the consistency checks over a whole week, or even offload the consistency checks to a non-production system (by restoring a backup and running the consistency checks on the restored database).

Second, how stable is the I/O subsystem where the databases are stored? If the I/O subsystem is having problems, you may want to run consistency checks as often as possible to get the earliest possible indication of corruption. In my experience, the longer that corruption goes unnoticed, the more pervasive it gets and the harder it is to recover the database while meeting the Recovery Point Objective and Recovery Time Objective.

The bottom line is that it’s up to you and your comfort level. In August 2009, I conducted a survey on my blog and out of 276 respondents, 37 percent run weekly consistency checks and a further 25 percent run them daily. You can see the full results of my survey, along with a lot more information on figuring out the checking frequency at www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx.

Thanks to Kimberly L. Tripp of SQLskills.com for her technical review of this month’s column.

Paul Randal

Paul S. Randal* is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Randal is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at Twitter.com/PaulRandal.*