Microsoft SQL Server 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:
1. Status commands
2. Validation commands
3. Maintenance commands
4. Miscellaneous commands.
Let’s take a look at a few of the more common ones.
1. Status commands:
The status commands are the ones you normally run first.These are the basic commands . With these commands, you can get an insight into what the server is doing.
now we are going to discuss most popular commands only..
1. Status commands
2. Validation commands
3. Maintenance commands
4. Miscellaneous commands.
Let’s take a look at a few of the more common ones.
1. Status commands:
The status commands are the ones you normally run first.These are the basic commands . With these commands, you can get an insight into what the server is doing.
now we are going to discuss most popular commands only..
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.
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.
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.
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
2.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:
3.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.
4.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’)
No comments:
Post a Comment