How to reindex tables in SQL 2008

drhixson
drhixson used Ask the Experts™
on
I am new to the DBA world, can someone tell me how to reindex tables in SQL 2008 database?

thank you!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
for a single table:

dbcc dbreindex('tablename', '', 80)

this rebuilds all of the indexes on the table named "tablename" with a fill factor of 80
Awarded 2008
Awarded 2008

Commented:
here's an article I wrote on how to detect fragmentation in 2005 and 2008

http://blogs.techrepublic.com.com/datacenter/?p=251
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
chapmandew,
     DBCC REINDEX is deprecated in SQL Server 2008 and hence you have to use ALTER INDEX statement to achieve the same objective.

http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx

Hope this helps
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Awarded 2008
Awarded 2008

Commented:
rrjegan17, Actually, its not deprecated in 2008 at all.  I just tried it and it works fine.  It may be removed from the next version of SQL Server, but works 100% fine in 2009.
Awarded 2008
Awarded 2008

Commented:
2008, not 2009.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
chapmandew,
     You can detect the fragmentation using the logic mentioned in your article and the only change would be instead of DBCC REINDEX command you have to incorporate the ALTER INDEX statement

Exec sp_MSForEachtable 'ALTER INDEX ALL ON ? REBUILD'

Hope this helps.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Actually, its not deprecated in 2008 at all

Oops.. You are correct.
I meant it would be deprecated after 2008 and hence not advised to use it out so that during migration to future versions would not be an issue.

Instead we can go with the recommended ALTER INDEX approach right.
Awarded 2008
Awarded 2008
Commented:
You DO NOT have to use ALTER INDEX...DBREINDEX works fine.  

Also, it is worth noting to tell the author that you should NEVER (EVER EVER) do a blind reindex on each table in your db.  NEVER.  Always check the fragmentation to see if it is necessay.
Awarded 2008
Awarded 2008

Commented:
No, we can go with my approach.  ALTER INDEX works too...but the author should absolutely not go and blindly and reindex every table with an 80 fillfactor...especially if they do not need it.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> but the author should absolutely not go and blindly and reindex every table with an 80 fillfactor

Agreed... Blind reindexing would not help in many scenarios.
Commented:
I think a better option is to query sys.indexes, so that you can grab only the ones you want. In my example here, I'm only grabbing the ones that have fragmentation over 30%.

--First I want a function that wraps up sys.dm_db_index_physical_stats (borrowed from Paul Randal's blog)
CREATE FUNCTION my_index_physical_stats (
@database_id INT,
@object_id INT,
@index_id INT,
@partition_number INT,
@mode INT)
RETURNS @result TABLE (
database_id SMALLINT NULL, object_id INT NULL, index_id INT NULL, partition_number INT NULL,
index_type_desc NVARCHAR(60) NULL, alloc_unit_type_desc NVARCHAR (60) NULL, index_depth TINYINT NULL,
index_level TINYINT NULL, avg_fragmentation_in_percent FLOAT NULL, fragment_count BIGINT NULL,
avg_fragment_size_in_pages FLOAT NULL, page_count BIGINT NULL, avg_page_space_used_in_percent FLOAT NULL,
record_count BIGINT NULL, ghost_record_count BIGINT NULL, version_ghost_record_count BIGINT NULL,
min_record_size_in_bytes INT NULL, max_record_size_in_bytes INT NULL, avg_record_size_in_bytes FLOAT NULL,
forwarded_record_count BIGINT NULL)
BEGIN
INSERT INTO @result SELECT * FROM
sys.dm_db_index_physical_stats (@database_id, @object_id, @index_id, @partition_number, @mode)
RETURN
END;


--Now to call my stuff
declare @qry nvarchar(max);
select @qry = (select 'ALTER INDEX ' + i.name + ' ON ' + quotename(s.name) + '.' + quotename(object_name(i.object_id)) + ' REBUILD '
from sys.indexes i
      join
      sys.objects o
      on o.object_id = i.object_id
      join
      sys.schemas s
      on s.schema_id = o.schema_id
      cross apply
      dbo.my_index_physical_stats(db_id(), o.object_id, i.index_id, null, null) ips
where ips.avg_fragmentation_in_percent > 30
and i.name is not null
for xml path('')
);
exec sp_executesql @qry;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial