February 1, 2011 at 2:06 pm
Hi,
I am using the following to reindex each index on each table in the database:
USE DBName;
EXEC SP_MSFOREACHTABLE
'DBCC DBReindex(''?'', '''', 90)'
GO
My question is How can I use this statement to only reindex non-clustered indexes?
Also I wanted to re-index only on indexes that had fragmentation of higher than 30 %.
How do I do that using SP_MSFOREAHTABLE?
Thanks.
February 1, 2011 at 2:16 pm
Short answer: You don't. You borrow some long convoluted code off someone else, learn what it does to make sure you can fix it, and use that.
There's a number of samples and complete code batches out there. Do a google search on: logical fragmentation detection dbcc reindex
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 3:43 pm
Try to look at sys.DM_DB_INDEX_PHISICAL_STATS
On msdn: http://msdn.microsoft.com/en-us/library/ms188917.aspx
you can find also an example "Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes".
Regards,
Iulian
February 2, 2011 at 3:40 am
you can use something similar to this to get the index names to rebuild
select a.[object_id], b.name from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a
join sys.objects b on a.[object_id]=b.[object_id]
where index_id=2 and avg_fragmentation_in_percent > 30
then need to build a cursor round the index name to dynamically create the dbcc command
its all in the article link above
February 2, 2011 at 4:31 am
While I wrote my own code to defrag indexes (in the book, listed below), I really prefer Michelle Ufford's[/url] code. It works wonders.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 3, 2011 at 8:01 am
Kimberly Tripp has some code on her blog on how to do this. Her routine will rebuild clustered and non-clustered indexes based on the fragmentation level.
February 3, 2011 at 9:14 am
get the complete and FREE maintenance suite at ola.hallengren.com!!! Can't say enough good things about this - and it does much more than just index mx.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply