Density of indexes -> HELP !

  • Hello everybody,

    if you issue a DBCC SHOW_STATISTICS (<table>, <index>)

    you will get a resultset with the density.

    What I want is the result in a RECORD in my DB.

    Does anybody know of a way to retrieve the density for an index and store the result in a table........

    Thanks in advance........

    I tried the following : ( But it didn''t work )

    create table #temp1

    (

    [Updated] datetime,

    [Rows] int,

    [Rows Sampled] int,

    [Steps] int,

    [Density] real,

    [Average key length] int,

    [Columns Names] sysname,

    [RANGE_HI_KEY] int,

    [RANGE_ROWS] int,

    [EQ_ROWS] int,

    [DISTINCT_RANGE_ROWS] int,

    [AVG_RANGE_ROWS] int

    )

    insert into #temp1

    EXEC ('DBCC SHOW_STATISTICS (<table>, <index>')

  • The thing is, I am almost certain that you are mistaken in thinking that you get a resultset as the output from DBCC SHOW_STATISTICS (<table>, <index>).

    If you take a closer look at the results it returns it actually resembles 3 separate resultsets.

    quote:


    if you issue a DBCC SHOW_STATISTICS (<table>, <index>)

    you will get a resultset with the density.


    You may be able to do what you want through DTS.

    Nigel Moore
    ======================

  • You might consider creating a TXT file from the output of DBCC SHOW_STATISTICS and processing through that TXT file inserting only the data you want into your table.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Hmmm I know i get three resultsets......

    that is why I am having this problem.

    Switching to TXT is not an option.

    suppose running a script on a production server with thousands of tables.

    I guess it would have a undesired performance impact

    So what i am looking for is a way to catch the output in a table

  • The problem is it comes from multiple output recordset not a singe. You may be able to write a DTS package with Active Script to do what you want using NextRecordset to move between the 3 outputs as you need or just the first 2.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • I think this is the only option, To write a DTS Package that does it all.

    But i remember i saw something on a web page somewhere that did it immeadiately.

    but i have been looking for two days now so i guess i will create the DTS package.

    Unless somebody else has the trick for me.........

  • I developed the following stored procedure to archive density information about clustered indexes for every user database:

    CREATE PROC RecordAllIndexStats

    AS

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    DECLARE @tablename VARCHAR (128)

    DECLARE @dbname VARCHAR (128)

    DECLARE @command VARCHAR (1000)

    DECLARE @startrundate VARCHAR(19)

    SET @startrundate = GETDATE()

    -- Create the table

    CREATE TABLE #fraglist (

    [ObjectName] CHAR (255),

    [ObjectId] INT,

    [IndexName] CHAR (255),

    [IndexId] INT,

    [Level] INT,

    [Pages] INT,

    [Rows] 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)

    -- Declare databases cursor

    DECLARE databases CURSOR FOR

    SELECT name from master..sysdatabases WHERE name NOT IN ('pubs', 'tempdb', 'Northwind', 'model', 'master')

    OPEN databases

    FETCH next FROM databases into @dbname

    WHILE @@fetch_status = 0

    BEGIN

    DELETE FROM #fraglist

    SELECT COUNT(*) FROM #fraglist

    -- Declare tables cursor

    SELECT @command =

    'USE ' + @dbname + ' DECLARE tables CURSOR FOR

    SELECT TABLE_SCHEMA + ''.'' + TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = ''BASE TABLE'''

    EXEC (@command)

    -- Open the cursor

    OPEN tables

    -- Loop through all the tables in the database

    FETCH NEXT

    FROM tables

    INTO @tablename

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    SELECT @command =

    'USE ' + @dbname + ' INSERT INTO #fraglist

    EXEC (''DBCC SHOWCONTIG ([' + @tablename + '])

    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'')'

    EXEC (@command)

    FETCH NEXT

    FROM tables

    INTO @tablename

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Get the the number of rows from sysindexes

    SELECT @command =

    'UPDATE #fraglist

    SET Rows = a.rows

    FROM ' + @dbname + '..sysindexes a

    WHERE a.indid < 2

    AND #fraglist.ObjectId = a.id'

    EXEC (@command)

    -- save fragmentation data

    SELECT @command = 'INSERT INTO DBTools..AllIndexStats

    SELECT ''' + @startrundate + ''' AS RunDate, ''' + @dbname + ''' [DatabaseName], c.name AS TableOwner, Left(a.ObjectName, 30) AS TableName, LEFT(a.IndexName, 50) AS IndexName, CAST(a.ScanDensity AS int) AS ScanDensity, a.Rows AS Row_Count, CAST(ROUND(a.Rows * ((100 - a.ScanDensity)/100),0) AS INT) AS FragRows

    FROM #fraglist a, ' + @dbname + '..sysobjects b, ' + @dbname + '..sysusers c

    WHERE a.ObjectId = b.id

    AND b.uid = c.uid

    ORDER BY FragRows DESC'

    EXEC (@command)

    -- Close and deallocate the databases cursor

    FETCH NEXT FROM databases INTO @dbname

    END

    DEALLOCATE databases

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

    Here's the table they're inserted into:

    CREATE TABLE [AllIndexStats] (

    [RunDate] [datetime] NOT NULL ,

    [DatabaseName] [sysname] NOT NULL ,

    [TableOwner] [sysname] NOT NULL ,

    [TableName] [sysname] NOT NULL ,

    [IndexName] [sysname] NOT NULL ,

    [ScanDensityPct] [int] NOT NULL ,

    [Row_Count] [int] NOT NULL ,

    [FragRows] [int] NOT NULL

    ) ON [PRIMARY]

    GO

  • Just tried the proc and table on a server here. It worked great and was quick. SChase, you might post that in the scripts section of this community. Thanks again!

    Jody

  • The SAP database that I'm working with is currently only 16GB but anticipated to grow beyond 500GB when put into production. So, I use the density stats in my AllIndexStats table to rebuild only those indexes that have a density that's less than 90%. This reduced the tuning part of my Maintenance Plan from 3hrs to just 10mins.

    CREATE PROC RebuildFragmentedIndexes

    AS

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    DECLARE @tablename VARCHAR (255)

    DECLARE @command VARCHAR (1000)

    -- Create the table

    CREATE TABLE #fraglist (

    [TableName] VARCHAR (255))

    INSERT INTO #fraglist

    SELECT RTRIM(

    ''''

    + DatabaseName

    + '.'

    + TableOwner

    + '.'

    + TableName)

    + '''' [TableName]

    FROM DBTools..AllIndexStats a, DEV..sysindexes b

    WHERE a.IndexName = b.name

    AND b.indid = 1

    AND DatabaseName IN ('DEV')

    AND FragRows > 0

    AND ScanDensityPct < 90

    AND RunDate = (SELECT MAX(RunDate) FROM DBTools..AllIndexStats)

    ORDER BY FragRows DESC

    -- Declare databases cursor

    DECLARE tables CURSOR FOR

    SELECT TableName from #fraglist

    OPEN tables

    FETCH NEXT FROM tables into @tablename

    WHILE @@fetch_status = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    SELECT @command =

    'DBCC DBREINDEX (' + @tablename + ','''',0)'

    EXEC (@command)

    SELECT @command =

    'CHECKPOINT'

    EXEC (@command)

    FETCH NEXT FROM tables INTO @tablename

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

  • I actually run the RecordAllIndexStats before AND after the RebuildFragmentedIndexes procedure. This gives me a before/after picture with the following query:

    SELECT RTRIM(a.DatabaseName + '.' + a.TableOwner + '.' + a.TableName) [TableName],

    RTRIM(a.IndexName) [IndexName],

    a.ScanDensityPct [a.Dens],

    b.ScanDensityPct [b.Dens],

    a.Row_Count [a.RowCount],

    a.FragRows [a.FragRows],

    b.FragRows [b.FragRows],

    a.FragRows - b.FragRows [FragDiff],

    a.RunDate [a.RunDate],

    b.RunDate [b.RunDate]

    FROM DBTools..AllIndexStats a, DBTools..AllIndexStats b, DEV..sysindexes c

    WHERE a.IndexName = c.name

    AND c.indid = 1

    AND a.DatabaseName *= b.DatabaseName

    AND a.TableOwner *= b.TableOwner

    AND a.TableName *= b.TableName

    AND a.IndexName *= b.IndexName

    AND b.RunDate = (SELECT MAX(RunDate) FROM DBTools..AllIndexStats)

    AND a.DatabaseName IN ('DEV')

    AND a.FragRows > 0

    AND a.ScanDensityPct < 90

    AND a.RunDate =

    (SELECT DISTINCT MAX(RunDate)

    FROM DBTools..AllIndexStats

    WHERE RunDate < (SELECT MAX(RunDate) FROM DBTools..AllIndexStats))

    ORDER BY a.FragRows - b.FragRows DESC

  • Good Posts!!

    Just remember not to confuse the 'Density' of a statistic with 'fragmentation' in an index. These are two completely seperate topics.


    "Keep Your Stick On the Ice" ..Red Green

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply