Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Last updated Mar 28, 2003.
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.
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.
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.
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:
- DBCC OPENTRAN
- DBCC INPUTBUFFER
- DBCC OUTPUTBUFFER
- DBCC PROCCACHE
- DBCC SQLPERF
- DBCC TRACESTATUS
- DBCC USEROPTIONS
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.
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.
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:
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.
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.
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:
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 is one of the best commands to remember — it simply shows you the syntax of the other commands:
DBCC HELP (’CHECKDB’)
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.
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.
Microsoft’s MSDN Reference site for DBCC commands is here.
Dean Thompson has a great article about DBCC SHOWCONTIG on the SQL Performance site.
Another good article on the popular DBCC SHOWCONTIG command is here.
Here’s a link to some undocumented DBCC commands - use with care!
Here are good scripts with DBCC examples.