Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 52 of 361 Next >

DBCC Commands

Last updated Mar 28, 2003.

If you’re looking for more up-to-date information on this topic, please visit our SQL Server article, podcast, and store pages.

When you us the graphical tools in SQL Server, you're hiding a lot of complexity. The tools have more wizards than a fantasy novel, such as the Maintenance Wizard, the Index Tuning Wizard, and the Backup Wizard and others in SQL Server 2000. In SQL Server 2005 this changed a bit and you now have terms like Database Tuning Advisor, but the point is that you can pretty much follow the wizards for most things you need to do to take care of your system.

That is, of course, unless something goes wrong. When that happens, all those wizards don’t help a lot, because you now need to know a bit more about that complexity. There are also times when you need to customize the maintenance you perform because of size or other reasons. It’s then that you need to graduate to the next level of command knowledge.

In this tutorial I'll explain those commands that you can use to watch, manage and correct many of the issues you encounter on your database server.

Microsoft provides a set of commands that will help you, called the DBCC commands. Originally that stood for Database Consistency Check commands, but they’ve been expanded since then to do a bit more. The DBCC commands are divided into four main categories: Status, validation, maintenance, and miscellaneous commands. Let’s take a look at a few of the more common ones. I'll provide links to the others.

I should mention that these commands change slightly with each higher version of SQL Server. Most often you'll see them replaced with newer commands, so make sure you check those references to ensure they work properly on your version of SQL Server.

Status commands

The status commands are the ones you normally run first. With these commands, you can gain an insight into what you’re server is doing. Let’s take a look at the ways you’d use each of these commands. I’ll only cover the commands you’ll use the most, and I’ll reference the others. You can always look these up in Books Online for the complete syntax. Here's a quick reference for that.

DBCC SHOWCONTIG

This is the command you’ll probably use the most. DBCC SHOWCONTIG shows you how fragmented a table, view or index is. Fragmentation is the non-contiguous placement of data. Just like a hard drive, it’s often the cause of the slowness of a system. Here’s a sample I ran on my server, against a table named tblNames:

DBCC SHOWCONTIG (tblNames)
GO
DBCC SHOWCONTIG scanning ’tblNames’ table...
Table: ’tblNames’ (357576312); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8072.0
- Avg. Page Density (full).....................: 0.27%

Here you can see the statistics returned on my server. It’s beyond the scope of this article to explain what each piece means in depth, but there are some interesting bits of information you can get from even this simple example. The most telling piece of information here is the Scan Density. The closer this number is to 100, the more contiguous the data.

There’s a lot more to this command; look for the resources at the end of this article.

DBCC SHOW_STATISTICS

Paired with the DBCC SHOWCONTIG command is DBCC SHOW_STATISTICS. Again, you need to have a pretty firm grasp of how SQL Server stores data, and how indexes are referenced. I've got references for more information here.

By providing the name of the table in question and the proper index, you receive a plethora of information about the "spread" of the index. The important piece of information here is once again the density number. This time, though, a lower number is better (if that’s the index you want favored) because a low number here means that index is favored to be used in a query.

There are a few other commands in this category that will show you the open transactions (useful in programming), the current input and output buffers and so forth. Here's a list of those commands with a reference to each:

Validation commands

Once you’ve seen the performance issues due to fragmentation or index problems, you normally run these commands next, since they will flush out the problems the various database objects (including the database itself) are having. Again, I’ll cover the ones you’ll likely use the most.

DBCC CHECKDB

By far the most widely used command to check the status of your database is DBCC CHECKDB. This command has two purposes: To check a database, and to correct it. Let’s take a look at a couple of the more common options.

The first option on the command looks like this for the TestDB database:

DBCC CHECKDB (’TestDB’, NOINDEX)

The command above checks the TestDB database but not its indexes. This won’t take long at all. The output returned will tell you if there are problems with the database. If so, check to make sure your backup is handy and then you can run the next level of this command:

DBCC CHECKDB (’TestDB’, REPAIR_FAST)

This command will attempt to fix many errors, but won’t allow any data to be lost. If that doesn’t work, the next level of the command is:

DBCC CHECKDB (’TestDB’, REPAIR_REBUILD)

This command takes longer, but will also correct the indexes (if it can). It will also not allow data loss. Should this command not correct your errors, you’ll definitely want to have that backup handy, because you’re going to need it. The next level of this command will potentially lose data. It looks like this:

DBCC CHECKDB (’TestDB’, REPAIR_ALLOW_DATA_LOSS)

As you can probably guess, this command could potentially lose data or make your applications unusable, depending on what data is lost (if any). I only use this command to repair the database on another server and then pull data selectively where I need it.

DBCC CHECKTABLE

The DBCC CHECKTABLE command does many of the same functions as the DBCC CHECKDB command, except on a table. You use the same options as the CHECKDB command.

The rest of the commands are largely subsumed by the DBCC CEHCKDB command, but some provide more specific information if that’s what you’re after. Here's a list and the reference links for those:

Maintenance commands

The maintenance commands are the final steps you normally run on a database when you’re optimizing the database or fixing a problem. Some of these commands have reporting features as well.

DBCC DBREINDEX

The DBCC DBREINDEX command rebuilds the indexes on a database. You can specify a particular index or all of them. This is the most popular and time consuming command you’ll normally run, and the one you’ll use most often for making your database access fast.

The format for the command is:

DBCC DBREINDEX (TableName, IndexName, Fill Factor)

The part that’s the most difficult to figure out is the fill factor. A fill factor is how much room to fill up the index before SQL allocates more space to the index. Here’s the idea: If you use a factor of 100, all the space in an index is filled. That packs the indexes nice and tight, making their space use very efficient. If new data has to be entered into the index, you pay a penalty for each update. If you specify a low fill factor, say 50 or so, you have a lot more room free and don’t pay as much of a penalty when the index is added to, but the indexes become larger and can also affect performance.

DBCC INDEXDEFRAG

The DBCC INDEXDEFRAG command defragments the index rather than rebuilding it. This command is normally used when time is an issue, such as in cases of very large databases. What’s normally done here is that this command is run during the week, and the DBCC DBREINDEX is run once a week.

There are other maintenance commands that you can look up, but be careful, some are older commands only included for backwards compatibility:

Miscellaneous commands

These commands perform such tasks as enabling row-level locking or removing a dynamic-link library (DLL) from memory.

DBCC dllname (FREE)

I’ve actually had to use the DBCC dllname (FREE) command — it’s primarily a programming convention. It frees up memory used by a DLL that’s often been called by an extended stored procedure.

DBCC HELP

DBCC HELP is one of the best commands to remember — it simply shows you the syntax of the other commands:

DBCC HELP (’CHECKDB’)

DBCC PINTABLE

This command "pins" a table into memory. Once the table is accessed, it stays in the buffer cache of memory and performance (for that table, anyway) is improved. Unless you’ve got a real driving need for this command, you probably shouldn’t use it. Its sister is DBCC UNPINTABLE which of course, releases the table from memory.

DBCC TRACEON

This command, and its sister command DBCC TRACEOFF Turn trace flags off and on, which can control the way SQL Server implements some of its behavior. These flags are normally used for debugging purposes, and I haven’t seen them in use on a production system.

InformIT Articles and Sample Chapters

SQL Server Tuning: Database Maintenance. Article By Kevin Kline, Baya Pavliashvili.

Books and e-Books

Microsoft SQL Server 2000 DBA Survival Guide, by Mark Spenik, Orryn Sledge. Published by Sams.

SQL Server Backup and Recovery: Tools and Techniques (ISBN 0130622982), By Frank McBath. Published by Prentice Hall PTR. Currently out of print, but it might be found at used bookstores.

The Guru’s Guide to Transact-SQL, by Ken Henderson. Published by Addison Wesley Professional.

Microsoft SQL Server 2000 Unleashed, by Ray Rankins, Paul Jensen, Paul Bertucci. Published by Sams.

Essential SQL Server 2000: An Administration Handbook, by Buck Woody. Published by Addison Wesley Professional.