SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 55 of 365 Next >

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.

Online Resources

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.

< Back Page 55 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address