June 17, 2008 at 5:53 am
I am trying to rebuild indexes that have 75% defragmentation. When I run the Alter Index statement
ALTER INDEX [IX_DeliveryDate_DeliveredTo] ON [dbo].[EmailBlastHistory] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
It says it completed but when I go back to look at the fragementation percent it still says 75%. Has anyone else ever had this problem?
Any help would be appreciated
June 17, 2008 at 8:34 am
I've seen that before in small tables. How many rows are there in the table the index is on?
The other thing you might do is rebuild the index by dropping and re-creating it directly, instead of using the rebuild command. But don't bother if the index is less than a couple of thousand rows. Below a certain point, there's no reason to worry about fragmentation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 7:19 am
I found this script works well with defragmentation.
USE database --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
June 18, 2008 at 1:49 pm
I've found that using the DBCC DBREINDEX function works for that purpose.
Here is a script that will defrag all indexes in a database:
USE [0324_03_00_P]
GO
/****** Object: StoredProcedure [dbo].[util_defrag_all_indexes] Script Date: 06/18/2008 15:48:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[util_defrag_all_indexes]
AS
SET NOCOUNT ON
--Identify all user tables in the current database
SELECT [id], [name] INTO #UserTables
FROM sysobjects
WHERE type = 'U'
ORDER BY [id]
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
--Defragment all tables/indexes
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
WHILE @TableCnt > 0
BEGIN
SELECT TOP 1 @id=[id]
, @oname = name
FROM #UserTables
WHERE [id] > @id
-- PRINT 'DBCC DBREINDEX ('+@oname+', '''', 0)'
DBCC DBREINDEX (@oname, '', 0)
SET @TableCnt = @TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
SET NOCOUNT OFF
June 19, 2008 at 3:18 am
It's not worth to rebuild indexes with less than 1000 pages (not rows), fragmentation on such a small index is not an issue.
Wilfred
The best things in life are the simple things
June 20, 2008 at 8:16 am
How much free space is in the database? Are you allowing the database to grow using autogrowth or are you proactively sizing the database? If the former, double the size of the database (note that this can lock out users for the growth time so beware in a production environment) and the try the index rebuild. I have NEVER been to a client yet that was proactive about file size and they have all had issues because of this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply