Run DBCC DBREINDEX on all tables and 50 are 10% fragmented and 3 are 100%

Question

Post reply

Run DBCC DBREINDEX on all tables and 50 are 10% fragmented and 3 are 100%

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 12, 2013 at 1:40 pm

#275261

I ran DBCC DBREINDEX on all tables and 50 are 10% or more fragmented and 3 are 100%. Most of the 50 are in the 60% to 90% range.
USE DataWarehouse
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
I know that DBREINDEX is be deprecated.
So for the top 10 tables I ran the following:
ALTER INDEX ALL ON Schema.TableName
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
What could be preventing the tables from dropping down to 0%?
Some of the tables are Heap. I could see why that would be a problem.
Edit: typo
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I was thinking of using Dave Pinal's script to rebuild the Indexes:

DECLARE @TableName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

After I ran the ALTER INDEX the Indexed Tables dropped below 1%.

Could it be the command is more effective or the I ran a REINDEX/REBUILD twice?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I ran the REBUILD on the Production Server and 26 Indexes are still 100% fragmented and 112 and above 80%.

Any ideas would be greatly appreciated.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dedicatedtosql Ten Centuries Points: 1147 More actions · Answer 4

Hi,

In that case the indexes you are looking at should be very small indexes. Its better you look at the page count of those indexes. Below query will give the page count.

SELECT SCHEMA_NAME(O.schema_id)+'.'+O.name AS TableName

, I.name AS IndexName

, P.row_count

, P.reserved_page_count AS TotalPagesReserved

FROM sys.objects O JOIN sys.indexes I ON O.object_id = I.object_id

JOIN sys.dm_db_partition_stats P ON I.object_id = P.object_id AND I.index_id = P.index_id

WHERE OBJECTPROPERTY(O.object_id, 'isMSShipped') = 0

If number of pages for a index is too small Rebuild index might not works because the pages allocated to that Index might be from mixed extents. In mixed extents the pages are allocated to different objects.