Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Posts
    5

    Unhappy Unanswered: DBCC ShowContig With TableResults

    I've looked through BOL, and tried all the samples I could find, but I can't seem to get it to work. I need either the results from ShowContig or more specifically the Scan Density number. I need this in a table or a recordset so I can get to it from an .exe. So far I haven't been able to get this to work.

    This needs to work with both SQL 7.0 & 2000. Some of the options aren't avaible under 7.0, and the parameters differ slightly.

    My code runs it, but doesn't put the results into the temp table.

    Does anyone know the way to simply get the Scan Density value without using ShowContig?

    Here's the code I've got so far:
    -- SQL
    CREATE TABLE #fraglist (
    ObjectName CHAR (255),
    ObjectId INT,
    IndexName CHAR (255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL)

    INSERT INTO #fraglist
    (ObjectName,
    ObjectId,
    IndexName,
    IndexId,
    Lvl,
    CountPages,
    CountRows,
    MinRecSize,
    MaxRecSize,
    AvgRecSize,
    ForRecCount,
    Extents,
    ExtentSwitches,
    AvgFreeBytes,
    AvgPageDensity,
    ScanDensity,
    BestCount,
    ActualCount,
    LogicalFrag,
    ExtentFrag)

    EXEC ('DBCC SHOWCONTIG (270624007, 2)
    WITH TABLERESULTS, NO_INFOMSGS')
    Select * from #fraglist
    Drop table #fraglist


    -- Output
    DBCC SHOWCONTIG scanning 'tbInventory' table...
    Table: 'tbInventory' (270624007); index ID: 2, database ID: 5
    LEAF level scan performed.
    - Pages Scanned................................: 65
    - Extents Scanned..............................: 9
    - Extent Switches..............................: 8
    - Avg. Pages per Extent........................: 7.2
    - Scan Density [Best Count:Actual Count].......: 100.00% [9:9]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 11.11%
    - Avg. Bytes Free per Page.....................: 112.8
    - Avg. Page Density (full).....................: 98.61%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (0 row(s) affected)

    ObjectName ObjectId IndexName IndexId Lvl CountPages CountRows MinRecSize MaxRecSize AvgRecSize ForRecCount Extents ExtentSwitches AvgFreeBytes AvgPageDensity ScanDensity BestCount ActualCount LogicalFrag ExtentFrag
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------- ------------ -------------- -------------------- ----------- ----------- -------------------- --------------------

    (0 row(s) affected)

    Thanks!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Hi

    I ran the script that you posted on SQL Server 2000, however I changed the DBCC command to :

    EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

    Since I don't have your table, I used authors in Pubs. This worked fine:

    ObjectName ObjectId IndexName
    -------------- ------------- ----------------
    authors 1977058079 UPKCL_auidind

    I didn't include the whole output, it just wraps around.

    For SQL Server 7.0 you'll need to do some coding:

    Code:
    set nocount on
    
    create table #Density(line varchar(80))
    create table #contig (tb varchar(40), value float, description varchar(30))
    
    declare @cmd    varchar(250)
    declare @tb     sysname
    declare @id     int
    
    declare tb_cur cursor
    for
    select  table_name,
            object_id(table_name)
    from    Information_Schema.tables
    where   table_type = 'base table'
    
    open tb_cur
    
    fetch next from tb_cur into @tb, @id
    
    while @@fetch_status = 0 begin
            select @cmd = 'use ' + db_name() 
                               + ' insert #Density EXECUTE master..xp_cmdshell ''isql -E -Snike -dSDMT -Q"dbcc showcontig (' + convert(varchar(100),@id) + ')"'''
    
            exec (@cmd)
    
            insert  #contig 
            SELECT  @tb,
                    CONVERT(FLOAT,REPLACE(SUBSTRING(line, PATINDEX('%: %', line) + 2, 6),'%','')),
                    CONVERT(VARCHAR(30),
                    CASE 
                            WHEN line LIKE '%Pages Scanned%'        THEN 'Pages Scanned'
                            WHEN line LIKE '%Extents Scanned%'      THEN 'Extents Scanned'
                            WHEN line LIKE '%Extent Switches%'      THEN 'Extent Switches'
                            WHEN line LIKE '%Avg. Pages per %'      THEN 'Avg. Pages per Extent'
                            WHEN line LIKE '%Scan Density%Best%'    THEN 'Scan Density'
                            WHEN line LIKE '%Logical Scan%'         THEN 'Logical Scan Fragmentation'
                            WHEN line LIKE '%Extent Scan%'          THEN 'Extent Scan Fragmentation'
                            WHEN line LIKE '%Avg. Bytes Free%'      THEN 'Avg. Bytes Free per Page'
                            WHEN line LIKE '%Avg. Page Density%'    THEN 'Avg. Page Density (full)'
                            ELSE Line
                    END)
            FROM    #Density
            WHERE   line        LIKE '%- %'
    
            truncate table #Density
            fetch next from tb_cur into @tb, @id
    end
    close tb_cur
    deallocate tb_cur
    
    select  *
    from    #contig
    
    
    drop table #Density
    drop table #contig
    go
    It's not the fastest code but it works.

  3. #3
    Join Date
    Nov 2001
    Posts
    5
    I've been working with your code (thank you!) and also doing some other testing. This is interesting...

    -- SQL2000
    EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

    Produces 1 row of data, looks like you did a select top 1 * from a table.

    -- SQL7
    EXEC ('DBCC SHOWCONTIG (117575457) WITH TABLERESULTS, NO_INFOMSGS')

    or
    dbcc showcontig (117575457, 1) with TableResults

    Produces:
    DBCC SHOWCONTIG scanning 'authors' table...
    Table: 'authors' (117575457); index ID: 1, 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]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 6008.0
    - Avg. Page Density (full).....................: 25.77%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    It doesn't look like SQL7 is honoring the TableResults parm.

    Unfortunately the example you provided (which does work) runs extremely slow on our server. Not sure exactly why, none of it looks that complicated. I'm guessing it's because we are shelling out to isql??

  4. #4
    Join Date
    Nov 2001
    Posts
    5
    Is there any way to use OpenRowSet with this DBCC Command?

Posting Permissions

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