Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Using the DBCC commands

    Hi,

    I am very new to sql server and I have been reading up on the dbcc commands. For instance, DBCC SHOWCONTIG where do I execute this command? I went into the cmd prompt and opened osql and typed:
    1> DBCC SHOWCONTIG
    2> GO

    This gives me the information for what I assume is the master database. But, how do I use this command under the other databases?

    Also, I have been trying to learn how to determine when to do index maintenance by using the index tuning wizard. But, I don't know what a workload is. Can someone point me in the right direction or give me some information about this topic.

    Thanks,
    Laura

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you are assigned to sysadmin server role, your default database is master. If you want to navigate through databases you need USE database_name command:

    1>use pubs
    2>go
    3>dbcc showcontig --optinally you can specify table name/id and index name/id
    4>go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2004
    Posts
    105
    oh, okay.

    Let me ask you another question. when I do dbcc showcontig how do I know when to perform a defrag? What am I supposed to look for?

    For example, there is no index used in this table but the extent scan fragmentation is 76%. So what does this mean?

    DBCC SHOWCONTIG scanning 'l2level' table...
    Table: 'l2level' (325576198); index ID: 0, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 981
    - Extents Scanned..............................: 124
    - Extent Switches..............................: 123
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 99.19% [123:124]
    - Extent Scan Fragmentation ...................: 76.61%
    - Avg. Bytes Free per Page.....................: 1379.4
    - Avg. Page Density (full).....................: 82.96%

    Thanks,
    Laura

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This site will give you an in-depth info on this and many more aspects of DBCC:

    http://www.sql-server-performance.co...showcontig.asp
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    May 2004
    Posts
    105
    I read the article and it sseems that the lower the extent and logical scan fragmentation the better. So, since mine is 76% this means that it is a good candidate for defragmentation? But, I do not have a index for this table so I cannot use DBCC DBREINDEX or DBCC INDEXDEFRAG. So what would I need to do to defrag this table?

    Thanks,
    Laura

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In your case there are 76.61% fragmented extents.


    Read on this here:

    http://www.sql-server-performance.co...gmentation.asp
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •