DBCC REINDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DBCC REINDEX

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-04 : 14:01:20

Im trying to write details of the dbcc showcontig so i can see what effect the index is having and when i should run more often.

if i do
DBCC SHOWCONTIG ('DELTEK.POOL_SIE_SUPPORT') WITH ALL_INDEXES

i get three rows of data.....
DBCC SHOWCONTIG scanning 'POOL_SIE_SUPPORT' table...
Table: 'POOL_SIE_SUPPORT' (719055); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 41708
- Extents Scanned..............................: 5424
- Extent Switches..............................: 5423
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 96.13% [5214:5424]
- Extent Scan Fragmentation ...................: 70.76%
- Avg. Bytes Free per Page.....................: 181.9
- Avg. Page Density (full).....................: 97.75%
DBCC SHOWCONTIG scanning 'POOL_SIE_SUPPORT' table...
Table: 'POOL_SIE_SUPPORT' (719055); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 4787
- Extents Scanned..............................: 601
- Extent Switches..............................: 600
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.67% [599:601]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 20.63%
- Avg. Bytes Free per Page.....................: 22.3
- Avg. Page Density (full).....................: 99.72%
DBCC SHOWCONTIG scanning 'POOL_SIE_SUPPORT' table...
Table: 'POOL_SIE_SUPPORT' (719055); index ID: 3, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 5171
- Extents Scanned..............................: 650
- Extent Switches..............................: 649
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [647:650]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 15.85%
- Avg. Bytes Free per Page.....................: 24.0
- Avg. Page Density (full).....................: 99.70%

My script that builds a table.

SET NOCOUNT ON
--
DECLARE @SQLString VARCHAR(2000) -- String used to hold SQL Statements to be executed.
DECLARE @ObjectID INT -- Not used, but may be useful for enhancements
DECLARE @TableName VARCHAR(120) -- Table name
DECLARE @TableSchema VARCHAR(40) -- Owner of the table
DECLARE @MinPages INT -- The minimum number of pages needed reindex
DECLARE @MaxScanDensity INT -- The maximum scan density that will be reindexed
DECLARE @ServerName NVARCHAR(30) -- Server NAME
DECLARE @DBName NVARCHAR(30) -- DB Name
SET @MinPages = 0 -- Modify to reduce results
SET @MaxScanDensity = 100 -- Modify to reduce results
--

SET @ServerName = 'ASRCFHSQL1'
--SET @DBName = 'MP2'
SET @DBName = DB_NAME()

CREATE TABLE #TempForShowContig(ObjectName VARCHAR (60),
ObjectId INT,
IndexName VARCHAR (60),
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)
--
CREATE TABLE #TempForTableName (TableName VARCHAR(120),
TableSchema VARCHAR(40),
ObjectID INT)

CREATE TABLE #TempForShowContigX(--Servername VARCHAR(30),
--DBName VARCHAR(30),
ObjectName VARCHAR (60),
OwnerId VARCHAR(60),
ObjectId INT,
IndexName VARCHAR (60),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
ScanDensity DECIMAL,
OrigFillFactor tinyint,
AvgPageDensity INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
DBIndexCommand NVARCHAR(150) )

--
DECLARE c_table CURSOR FOR
SELECT TableName, TableSchema, ObjectID
FROM #TempForTableName

SET @SQLString = 'SELECT o.name , USER_NAME(o.uid), o.id ' +
'FROM sysobjects o, ' +
' sysindexes i ' +
'WHERE o.type = ''U''' +
' AND o.id = i.id ' +
' AND i.indid IN (0,1) ' +
' AND o.name != ''dtproperties''' +
' AND substring(o.name,1,2) <> ''Z_'''

INSERT #TempForTableName
EXECUTE(@SQLString)
------------------------------------------------------------------------
OPEN c_table
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #TempForShowContig
EXEC ('DBCC SHOWCONTIG (''[' + @TableSchema +'].['+ @TableName + ']'') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
END
CLOSE c_table
--
-------------------------------------------------------------------------
DEALLOCATE c_table
-------------------------------------------------------------------------
-- The following SELECT can be uncommented to display the SHOWCONTIG
-- results for all of the tables and indexes.
-------------------------------------------------------------------------
-- SELECT '-- ', * FROM #TempForShowContig
--
PRINT '---------------------------------'
PRINT '-- Date: ' + CAST(GETDATE() AS CHAR(20))
PRINT '-- Database: ' + DB_NAME()
PRINT '---------------------------------'
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
--
insert INTO #TempForShowContigX(
ObjectName,
OwnerId ,
ObjectId,
IndexName ,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
ScanDensity ,
OrigFillFactor ,
AvgPageDensity ,
AvgRecSize ,
ForRecCount,
Extents,
ExtentSwitches ,
AvgFreeBytes,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag,
DbIndexCommand)
SELECT RTRIM(t.ObjectName) as 'TABLE',
RTRIM(USER_NAME(o.uid)) AS 'OWNER' ,
t.ObjectId AS 'OBJECT_ID' ,
RTRIM(t.IndexName) AS 'INDEX' ,
i.Indid,
t.Lvl,
t.CountPages AS 'COUNT PAGES' ,
t.CountRows AS 'COUNT ROWS' ,
t.MinRecSize ,
t.MaxRecSize,
t.ScanDensity AS 'SCAN DENSITY' ,
i.OrigFillFactor AS 'ORIG FILL FACTOR' ,
t.AvgPageDensity AS 'AVERAGE PAGE DENSITY' ,
t.AvgRecSize AS 'AVERAGE REC SIZE',
t.ForRecCount,
t.Extents,
t.ExtentSwitches,
t.AvgFreeBytes,
t.BestCount,
t.ActualCount,
t.LogicalFrag,
t.ExtentFrag,
CAST('-- DBCC DBREINDEX(''' + RTRIM(USER_NAME(o.uid)) + '.' + RTRIM(t.ObjectName)
+ ''',''' + RTRIM(t.IndexName) + ''',)' AS VARCHAR(180))
FROM #TempForShowContig t,
sysindexes i,
sysobjects o
WHERE i.id = t.ObjectId
AND i.name = t.IndexName
AND i.id = o.id

DROP TABLE #TempForShowContig
DROP TABLE #TempForTableName
DROP TABLE #TempForShowContigX

SET NOCOUNT OFF

Only gives the first two results index id 2 and 3

I was wondering why it does not give index 0

When the dbcc index rebuilds is it only rebuilding index 2 and index 3
Not understanding why i got three results

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 14:19:43

What is the DDL for the PK?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-04 : 16:26:19

CREATE TABLE [POOL_SIE_SUPPORT] (
[FY_CD] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PD_NO] [smallint] NOT NULL ,
[ALLOC_GRP_NO] [smallint] NOT NULL ,
[POOL_NO] [int] NOT NULL ,
[PROC_SEQ_NO] [smallint] NOT NULL ,
[S_ACCT_TYPE_CD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ORG_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CUR_AMT] [decimal](14, 2) NOT NULL ,
[YTD_AMT] [decimal](14, 2) NOT NULL ,
[CUR_ALLOC_AMT] [decimal](14, 2) NOT NULL ,
[YTD_ALLOC_AMT] [decimal](14, 2) NOT NULL ,
[CUR_RT] [decimal](10, 8) NOT NULL ,
[YTD_RT] [decimal](10, 8) NOT NULL ,
[CUR_BUD_AMT] [decimal](14, 2) NOT NULL ,
[YTD_BUD_AMT] [decimal](14, 2) NOT NULL ,
[CUR_BUD_ALLOC_AMT] [decimal](14, 2) NOT NULL ,
[YTD_BUD_ALLOC_AMT] [decimal](14, 2) NOT NULL ,
[CUR_BUD_RT] [decimal](10, 8) NOT NULL ,
[YTD_BUD_RT] [decimal](10, 8) NOT NULL ,
[MODIFIED_BY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TIME_STAMP] [datetime] NOT NULL ,
[ROWVERSION] [int] NULL ,
[CUR_BASE_AMT] [decimal](14, 4) NOT NULL ,
[YTD_BASE_AMT] [decimal](14, 4) NOT NULL ,
[PROJ_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-04 : 16:27:20

There no primary key just two indexes.

its the vendors EPR system.

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 00:51:48

Assuming no clustered index (which is probably what I should have said!) I'm not sure how you defragment the underlying data - i.e. index 0.

Personally if you have gone to the trouble of writing such a routine I would suggest you use INDEXDEFRAG rather than DBREBUILD (except on small tables - our cutoff point is where SHOWCONTIG's CountPages is less than 10,000)

Might be worth doing a manual

DBCC INDEXDEFRAG (MyDatabase, MyTable, MyIndexName) WITH NO_INFOMSGS

but I'm not sure what indexname you use for the underlying data, if you have no clustered index.

What's the reason why you don't have a PK on this table?

Kristen

pootle_flump

1064 Posts

Posted - 2006-12-05 : 04:31:33

quote:
Originally posted by Kristen

Assuming no clustered index (which is probably what I should have said!) I'm not sure how you defragment the underlying data - i.e. index 0.

You don't. Without an index there is no fragmentation. Fragmentation is when logically contiguous values are not physically contiguous. On a heap there is no logical order therefore there cannot be fragmentation.

Paul's article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-05 : 08:36:14

The entire database has no primary keys the only primary keys are the ones i added as i need replication the vendor only supplys index which is bizaree.

Is there a way to add a column and make it clustered ...then do a rebuild ? then remove it?

pootle_flump

1064 Posts

Posted - 2006-12-05 : 08:53:18

You do know that a primary key and a clustered index are not really related? One is a logical constraint the other how the SQL Server engine stores the table. It just so happens that SQL Server uses an index to maintain the contraint and by default makes this clustered. Just in case you are thinking they are interchangable...

quote:
Originally posted by TRACEYSQL

Is there a way to add a column and make it clustered ...then do a rebuild ? then remove it?

Of course. The question is: why on earth would you want to?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources