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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy