DBREINDEX question - 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)

DBREINDEX question

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-09-29 : 14:58:33

ok this may be a silly question so bear with me;
let's say I perform a dbcc cleantable and dbreindex; these functions will just defrag the tables in question.
they are NOT going to change the actual values of my PK's and FK's right?
for example, record ID 23 is still going to be 23 right?

Thanks,

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 17:29:30

"You are correct Sir!!!!!"

http://www.maximumdonline.com/tvwavs/mcmahon.wav

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-29 : 18:40:53

Nobody would run the commands if they modified the data. Can you imagine what it would do to banking and hospital systems?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 18:44:13

quote:
Originally posted by tkizer

Nobody would run the commands if they modified the data. Can you imagine what it would do to banking and hospital systems?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

So...I would end up with Medicinal Mary Jane?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-09-30 : 10:21:29

Thank you, that's what I thought but to be safe I asked anyway.
The "reindexing" part is what sounded strange
maybe they should have called it "defrag" or something.
more importantly, I conducted my own tests:
I have a Calendar table that was 94% fragmented and
a Numbers table 35% fragmented.
SQL 2005 express
I ran dbcc cleantable and dbcc dbreindex on both tables and
the calendar table shrank down to 12% (yay) but the
Numbers table stubbornly stayed at 35% (??)
I ran it again on Numbers and it went up! 85%! (Ouch)
on SQL 2000
the calendar table went down
I don't have a Numbers table on the SQL 2000 side. so i created one and it's not fragmented (??)

I ran dbcc UpdateUseage to no effect.

Any ideas as to why the Numbers table increases fragmentation on 2005?
Should I just drop the table and recreate it? Or is there another way to defrag it?
Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-30 : 12:44:56

For SQL Server 2005, you shouldn't be using DBCC DBREINDEX or DBCC CLEANTABLE. Use ALTER INDEX instead.

Here's my custom script for it: http://weblogs.sqlteam.com/tarad/archive/2009/08/31/DefragmentingRebuilding-Indexes-in-SQL-server-2005-and-2008.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-09-30 : 13:08:38

Sorry Tara but I'm blocked from getting the script. Can you paste it in a reply?

I tried this:

alter index ALL on Numbers
REBUILD

and dbcc updateusage

and it still has 87% for avg_fragmentation_in_percent

What am I doing wrong? Did I mess up this table by using dbreindex and cleantable?

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-30 : 13:18:47

Tara,

I get this when I try to compile

Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 91
Incorrect syntax near '('.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-30 : 13:19:21

[code]
-- required table
IF OBJECT_ID('DefragmentIndexes') IS NULL
CREATE TABLE DefragmentIndexes
(
DatabaseName nvarchar(100) NOT NULL,
SchemaName nvarchar(100) NOT NULL,
TableName nvarchar(100) NOT NULL,
IndexName nvarchar(100) NOT NULL,
DefragmentDate datetime NOT NULL,
PercentFragmented decimal(4, 2) NOT NULL,
CONSTRAINT PK_DefragmentIndexes PRIMARY KEY CLUSTERED
(
DatabaseName,
SchemaName,
TableName,
IndexName,
DefragmentDate
)
)
GO

IF OBJECT_ID(N'[dbo].[isp_ALTER_INDEX]') IS NOT NULL
DROP PROC [dbo].[isp_ALTER_INDEX]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
--
-- INPUTS : @dbName - name of the database
-- @statsMode - LIMITED, SAMPLED or DETAILED
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @logHistory - whether or not to log what got defragmented
-- @sortInTempdb - whether or not to sort the index in tempdb;
-- recommended if your tempdb is optimized (see BOL for details)
--
-- OUTPUTS : None
--
-- DEPENDENCIES : DefragmentIndexes, sys.dm_db_index_physical_stats, sys.objects, sys.schemas,
-- sys.indexes, sys.partitions, sys.indexes, sys.index_columns, INFORMATION_SCHEMA.COLUMNS
--
-- DESCRIPTION : Defragments indexes
/*
EXEC isp_ALTER_INDEX
@dbName = 'QHOSClient1',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000,
@logHistory = 1,
@sortInTempdb = 1
*/
/*
http://weblogs.sqlteam.com/tarad/archive/2009/03/27/Defragmenting-Indexes-in-SQL-Server-2005Again.aspx
Bug Fix - added SET QUOTED_IDENTIFIER ON to the script
Feature - added logging feature
http://weblogs.sqlteam.com/tarad/archive/2009/06/23/DefragmentingRebuilding-Indexes-in-SQL-Server-2005.aspx
Bug Fix - initialized @lobData to 0 for each pass through the loop
Bug Fix - checked for LOB data in included columns of non-clustered indexes
Feature - added SORT_IN_TEMPB option
http://weblogs.sqlteam.com/tarad/archive/2009/08/31/DefragmentingRebuilding-Indexes-in-SQL-server-2005-and-2008.aspx
Bug Fix - added index_level = 0 to sys.dm_db_index_physical_stats query
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_ALTER_INDEX]
(
@dbName sysname,
@statsMode varchar(8) = 'SAMPLED',
@defragType varchar(10) = 'REORGANIZE',
@minFragPercent int = 25,
@maxFragPercent int = 100,
@minRowCount int = 0,
@logHistory bit = 0,
@sortInTempdb bit = 0
)
AS

SET NOCOUNT ON

IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
BEGIN
RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)
RETURN
END

IF @defragType NOT IN ('REORGANIZE', 'REBUILD')
BEGIN
RAISERROR('@defragType must be REORGANIZE or REBUILD', 16, 1)
RETURN
END

DECLARE
@i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname,
@schemaName sysname, @partitionNumber int, @partitionCount int,
@sql nvarchar(4000), @edition int, @parmDef nvarchar(500), @allocUnitType nvarchar(60),
@indexType nvarchar(60), @online bit, @disabled bit, @dataType nvarchar(128),
@charMaxLen int, @allowPageLocks bit, @lobData bit, @fragPercent float

SELECT @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE
avg_fragmentation_in_percent > @minFragPercent AND
avg_fragmentation_in_percent < @maxFragPercent AND
index_id > 0 AND
index_level = 0
ORDER BY ObjectId

-- LIMITED does not include data for record_count
IF @statsMode IN ('SAMPLED', 'DETAILED')
DELETE FROM #FragIndex
WHERE RecordCount < @minRowCount

SELECT @i = MIN(FragIndexId)
FROM #FragIndex

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@fragPercent = FragPercent,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@allocUnitType = AllocUnitType
FROM #FragIndex
WHERE FragIndexId = @i

WHILE @@ROWCOUNT <> 0
BEGIN
-- get the table and schema names for the index
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + QUOTENAME(@dbName) + '.sys.objects o
JOIN ' + QUOTENAME(@dbName) + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'

SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

-- get index information
SET @sql = '
SELECT @indexName = [name], @disabled = is_disabled, @allowPageLocks = allow_page_locks
FROM ' + QUOTENAME(@dbName) + '.sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId'

SET @parmDef = N'
@objectId int, @indexId int, @indexName sysname OUTPUT,
@disabled bit OUTPUT, @allowPageLocks bit OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT, @disabled = @disabled OUTPUT,
@allowPageLocks = @allowPageLocks OUTPUT

SET @lobData = 0

-- for clustered indexes, check for columns in the table that use a LOB data type
IF @indexType = 'CLUSTERED INDEX'
BEGIN
-- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml
SET @sql = '
SELECT @lobData = 1
FROM ' + QUOTENAME(@dbName) + '.INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA = @schemaName AND
TABLE_NAME = @objectName AND
(DATA_TYPE IN (''text'', ''ntext'', ''image'') OR
CHARACTER_MAXIMUM_LENGTH = -1)'

SET @parmDef = N'@schemaName sysname, @objectName sysname, @lobData bit OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @schemaName = @schemaName, @objectName = @objectName,
@lobData = @lobData OUTPUT
END
-- for non-clustered indexes, check for LOB data type in the included columns
ELSE IF @indexType = 'NONCLUSTERED INDEX'
BEGIN
SET @sql = '
SELECT @lobData = 1
FROM ' + QUOTENAME(@dbName) + '.sys.indexes i
JOIN ' + QUOTENAME(@dbName) + '.sys.index_columns ic
ON i.object_id = ic.object_id
JOIN ' + QUOTENAME(@dbName) + '.INFORMATION_SCHEMA.COLUMNS c
ON ic.column_id = c.ORDINAL_POSITION
WHERE c.TABLE_SCHEMA = @schemaName AND
c.TABLE_NAME = @objectName AND
i.name = @indexName AND
ic.is_included_column = 1 AND
(c.DATA_TYPE IN (''text'', ''ntext'', ''image'') OR c.CHARACTER_MAXIMUM_LENGTH = -1)'

SET @parmDef = N'@schemaName sysname, @objectName sysname, @indexName sysname, @lobData bit OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @schemaName = @schemaName, @objectName = @objectName,
@indexName = @indexName, @lobData = @lobData OUTPUT
END

-- get partition information for the index
SET @sql = '
SELECT @partitionCount = COUNT(*)
FROM ' + QUOTENAME(@dbName) + '.sys.partitions
WHERE [object_id] = @objectId AND index_id = @indexId'

SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT

-- Developer and Enterprise have the ONLINE = ON option for REBUILD.
-- Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
-- disabled indexes, XML indexes, indexes on local temp tables, partitioned indexes,
-- clustered indexes if the underlying table contains LOB data types (text, ntext, image, varchar(max),
-- nvarchar(max), varbinary(max) or xml), and
-- nonclustered indexes that are defined with LOB data type columns.
-- When reoganizing and page locks is disabled for the index, we'll switch to rebuild later on,
-- so we need to get setup with the proper online option.
IF @edition = 3 AND (@defragType = 'REBUILD' OR (@defragType = 'REORGANIZE' AND @allowPageLocks = 0))
BEGIN
SET @online =
CASE
WHEN @indexType = 'XML INDEX' THEN 0
WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
WHEN @lobData = 1 THEN 0
WHEN @disabled = 1 THEN 0
WHEN @partitionCount > 1 THEN 0
ELSE 1
END
END
ELSE
SET @online = 0

-- build the ALTER INDEX statement
SET @sql = 'ALTER INDEX ' + QUOTENAME(@indexName) + ' ON ' + QUOTENAME(@dbName) + '.' +
QUOTENAME(@schemaName) + '.' + QUOTENAME(@objectName) +
CASE
WHEN @defragType = ' REORGANIZE' AND @allowPageLocks = 0 THEN ' REBUILD'
ELSE ' ' + @defragType
END

-- WITH options
IF @online = 1 OR @sortInTempdb = 1
BEGIN
SET @sql = @sql + ' WITH (' +
CASE
WHEN @online = 1 AND @sortInTempdb = 1 THEN 'ONLINE = ON, SORT_IN_TEMPDB = ON'
WHEN @online = 1 AND @sortInTempdb = 0 THEN 'ONLINE = ON'
WHEN @online = 0 AND @sortInTempdb = 1 THEN 'SORT_IN_TEMPDB = ON'
END + ')'
END

IF @partitionCount > 1 AND @disabled = 0 AND @indexType <> 'XML INDEX'
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

-- run the ALTER INDEX statement
EXEC (@SQL)

-- log some information into a history table
IF @logHistory = 1
INSERT INTO DefragmentIndexes (DatabaseName, SchemaName, TableName, IndexName, DefragmentDate, PercentFragmented)
VALUES(@dbName, @schemaName, @objectName, @indexName, GETDATE(), @fragPercent)

SELECT @i = MIN(FragIndexId)
FROM #FragIndex
WHERE FragIndexId > @i

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@fragPercent = FragPercent,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@allocUnitType = AllocUnitType
FROM #FragIndex
WHERE FragIndexId = @i
END

GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-30 : 15:42:15

quote:
Originally posted by X002548

Tara,

I get this when I try to compile

Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 91
Incorrect syntax near '('.

It needs to be placed into a database with a compatibility level of 90 or 100. You'll only get that error, as far as I know at least, if the database compatibility level is 80.

The code can reindex an 80 database on 2005, but the isp_ALTER_INDEX object needs to be in a database with 90 or 100.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-30 : 15:42:57

quote:
Originally posted by Zim327

I tried this:
alter index ALL on Numbers
REBUILD

and dbcc updateusage

and it still has 87% for avg_fragmentation_in_percent

What am I doing wrong? Did I mess up this table by using dbreindex and cleantable?

Not all tables can be defragmented. How big is the table? How big is the index? How many rows, how many pages?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-10-02 : 10:55:59

Here's what showcontig said about the table:
DBCC SHOWCONTIG scanning 'Numbers' table...
Table: 'Numbers' (562101043); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 6
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 12.50% [1:8]
- Logical Scan Fragmentation ..................: 87.50%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 972.0
- Avg. Page Density (full).....................: 87.99%

The current compatibility level is 90.

I tried both your stored proc and alter index.
do you think this is just a problem with SQL Express? Meaning if I had the full enterprise version this would have worked?

Thanks to everyone for the assistance,

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-02 : 11:38:14

on a seperate angle, you may be over analysising the problem. your table looks to be small enough not to matter if it is 10, 20, 70 or 90% fragmented. any decent server will pull in that full table into cache on the first activity.

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-10-02 : 12:25:00

ok fair enough,
so what should I use specifically as a threshold (how many rows + total fragmentation plus any other factors etc etc) to decide when it's time to defrag a given table?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-02 : 12:36:06

I discussed that topic with Microsoft a few months back. The answer was to not bother with defragmenting until fragmentation was greater than 75% and there are at least 1000 pages in the index. I chose to look at indexes over 50% fragmentation just in case. I haven't yet added the page count check in my custom code though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-10-02 : 14:00:07

Excellent, and a big thank you to everyone!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-02 : 15:04:06

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources