dbcc checkdb (’databaseName’)

Verifies that Index and Data Pages are linked properly,
that Indexes are sorted, that the pointers in the database
are accurate, that the data looks OK, and that there are
proper page offsets.

Advanced checkdb Options:
NOINDEX, REPAIR_FAST, REPAIR_REBUILD, REPAIR_ALLOW_DATA_LOSS

  • noindex:  Checks the database but not it’s indexes.  Results in faster operation.  If there are problems, make sure to have backup handy and run next level:
  • repair_fast: This command will attempt to fix many errors, but won’t allow data to be lost.  if that doesn’t work, the next level command is:
  • repair_rebuild: this will take longer but it will also correct indexes (if it can).  It will also not allow data loss.  If this command doesn’t work, definitely get the backup out because you’re going to need it. The next level command is:
  • repair_allow_data_loss: This command can potentially lose data or make your applications unusable depending on what data is lost (if any).  (_I_ wouldn’t use this unless you’re desperate and ready to pull some data back across to this database.


dbcc show_statistics (Table1, Table2) with stat_header

Determine statistics sampling rate.

dbcc checktable

Check any particular table that has generated an error.

dbcc indexdefrag

Defragments the index rather than rebuilding it.  This command
is normally used when time is an issue (large databases).
This is normally used during the week and dbreindex once a
week.

dbcc dbreindex (’tableName‘,’indexName‘, FillFactor)

  • tableName: name of the table the index belongs to
  • indexName: name of the index to defragment.  if you leave this blank, it will do all the indexes.
  • fillFactor: tells process how much space to use for index storage unit (a page).  rule of thumb:  if you think the indexes won’t be added to very often then set it higher (like 90), and if you think that the index will be added to often, then set it a bit lower (like 70).  It’s a guessing game

dbcc showcontig

Example output:

DBCC SHOWCONTIG scanning ’test_table’ table...
Table: ’test_table’ (420196547); index ID: 1, database ID: 7
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]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8009.0
- Avg. Page Density (full).....................: 1.05%
DBCC execution completed.
If DBCC printed error messages, contact your system administrator.

Important bits in this are Scan Density and Avg. Page Density

Scan Density display ratio of how contiguous the physical
structure is.  The closer to 100% this number, the more
contiguous the data is laid out and faster the access will be.

Avg. Page Density relates to data that is stored in the table.
The higher this number, the more data is on a page (phys.
storage unit).  This is good for reads but more problematic
for writes.

backup database ‘databaseName‘ to disk=’c:\temp\db.bak‘

This creates a backup and puts it in c:\temp as db.bak.  If
you want to overwrite existing files that may be there
use:  WITH INIT at the end of that line.

Multi-processor, L2-cache > 1mb per processor, optimized
network cards, separate drives that store database from
transaction logs.  Also SCSI disks a plus with hardware
RAID.

Interesting that you see Extent Scan Fragmentation increase
after a dbreindex.

Things from Paul Randal:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54391

And don’t base your decision on Scan Density or Extent
Fragmentation – always use Logical Fragmentation.

http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx

About auto-shrink

In the Database Maintenance workshop we did at SQL Connections
last week I promised to blog a script I used to show how data
file shrink operations cause massive fragmentation of indexes.
The reason is that data file shrink starts at the end of the
data file and moves a single page at a time to a free space
below the shrink threshold. In the process of doing so, it perfectly reverses the physical order of the pages comprising
the leaf level of an index thus perfectly fragmenting it!!
Bottom-line: auto-shrink should *NEVER* be turned on

Links

http://wardyit.com/blog/blog/archive/2006/03/07/89.aspx
Determine if autoshrink is enabled on database via SMO/DMO VBS script.

You might also be interested in...

Print This Post Print This Post