SQL 2000 and 2005 Offline Table Reindex Determination

5 pts.
Tags:
SQL 2000
SQL 2005
SQL Database
Hello, I have prcessed SQL 2000 offline reindexes in the past for specific tables when there was scheduled downtime, but I was wondering if there is a tool or script that I can run for a specifc SQL database to determine which tables in the database would need to be reindexed. I do not want to reindex all the tables. Thank you, bankerdba

Answer Wiki

Thanks. We'll let you know when a new response is added.

In SQL 2000 you can look at the output from DBCC SHOWCONTIG to see if the indexes for a table need to be rebuilt.

In SQL 2005 and SQL 2008 you can look at the sys.dm_db_index_physical_stats DMV to see if the indexes need to be rebuilt.

I can’t help with SQL 2000, but here’s a query I use for detecting which indexes need rebuilding in SQL 2005. It uses some arbitrary numbers to make recommendations, but this would give you a starting point.

USE MyDatabase

DECLARE @DBID smallint
SELECT @DBID = DB_ID()

SELECT
Object_Name(dt.[object_id], dt.Database_ID) AS TableName,
si.name AS IndexName,
Page_Count AS [PageCount],
dt.avg_fragmentation_in_percent AS IndexFragmentation,
dt.avg_page_space_used_in_percent AS PageSpaceUsed,
CASE WHEN (Page_Count > 1 AND Avg_Page_Space_Used_In_Percent < 60) OR (Avg_Fragmentation_In_Percent > 15)
Then ‘Reorganize’
WHEN (Page_Count > 1 AND Avg_Page_Space_Used_In_Percent BETWEEN 60 AND 75) OR (Avg_Fragmentation_In_Percent BETWEEN 10 AND 15)
Then ‘Rebuild’
ELSE NULL END AS Recommendation
FROM
(
SELECT
[Object_ID],
Database_ID,
Index_ID,
Avg_Fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count
FROM sys.dm_db_index_physical_stats(@DBID, NULL, NULL, NULL, ‘DETAILED’)
WHERE Index_ID <> 0
AND (Page_Count > 1 AND Avg_Page_Space_Used_In_Percent < 75) OR Avg_Fragmentation_In_Percent > 10
) AS dt –does not return information about heaps
INNER JOIN sys.indexes si ON si.object_id = dt.object_id AND si.Index_ID = dt.Index_ID

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following