Check index fragmentation and rebuild query

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

General SQL Server Forums

New to SQL Server Administration

Check index fragmentation and rebuild query

Author

Topic

nmtinx
Starting Member

1 Post

Posted - 2012-04-26 : 15:00:49

Dear all,

I would like to add a query in my maintenance plan to check all indexes to see whether they have fragmentation > 10 or 30% and then reorganize and rebuild those indexes respectively based on the result. I wonder have anyone done that and can you share your query? I don't want to their standard option to rebuild all indexes no matter what. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-26 : 16:11:21

Use's Ola's index maintenance script: http://ola.hallengren.com/

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-04-30 : 12:48:15

Anyone have any scripts pertaining to 2000?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-30 : 12:58:58

Tara's got some on her blog:

http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx
http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-05-01 : 07:18:45

I should have been more specific, any Select queries that will retrieve fragmentation levels, and that DON'T involve creating procedures (which would require change requests)?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-01 : 09:14:33

DBCC SHOWCONTIG will give you fragmentation stats: Link

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-01 : 12:03:11

This is from Book On Line

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');

IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources