August 7, 2015 at 7:02 am
Hi Folks,
I have some indexes over 30% fragmented. Some are much more...
I have a job maintenance plan to "re-organize" indexes every Sunday on all databases. I didn't know if I should also be "re-building" the indexes, and if this would fix the fragmentation???
Any comments on fixing the fragmentation would be appreciated - I also wanted to ask if I should do this with the index online - assuming the performance hit during the rebuild is acceptable.
Thanks in advance for any tips.
August 7, 2015 at 8:08 am
ShorePatrol (8/7/2015)
Hi Folks,I have some indexes over 30% fragmented. Some are much more...
I have a job maintenance plan to "re-organize" indexes every Sunday on all databases. I didn't know if I should also be "re-building" the indexes, and if this would fix the fragmentation???
Any comments on fixing the fragmentation would be appreciated - I also wanted to ask if I should do this with the index online - assuming the performance hit during the rebuild is acceptable.
Thanks in advance for any tips.
The timing really shouldn't be when users are likely to need access to the data, unless the overall time a complete DROP and CREATE for the given index would take just a few seconds. As to fragmentation, that may well depend on the nature of the disk space available at the time the indexes get re-created, if the index re-creation would cause database growth, or on the nature of the free space within the database filegroup at the time. I'm not an index expert, but dropping and re-creating the entire index can often be quite useful. Be sure to keep your statistics up to date, as even the best index can't easily overcome a problem with stale stats.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 7, 2015 at 8:25 am
ShorePatrol (8/7/2015)
Hi Folks,I have some indexes over 30% fragmented. Some are much more...
I have a job maintenance plan to "re-organize" indexes every Sunday on all databases. I didn't know if I should also be "re-building" the indexes, and if this would fix the fragmentation???
Any comments on fixing the fragmentation would be appreciated - I also wanted to ask if I should do this with the index online - assuming the performance hit during the rebuild is acceptable.
Thanks in advance for any tips.
If you are working with maintaining indexes you need to check out this page. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It will seriously blow the doors of anything you can build yourself.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 9:01 am
Thanks all -
I will check that page out...
But for clarification, is "rebuilding" an index better than reorganizing? I know I read somewhere that it was overkill to do both - but maybe that wasn't correct - and since I'm currently not rebuilding, I think doing that, and updating statistics would be a good start....and I will do it during the wee hours of the morning when the system is at it's low usage point....
August 7, 2015 at 9:08 am
ShorePatrol (8/7/2015)
Thanks all -I will check that page out...
But for clarification, is "rebuilding" an index better than reorganizing? I know I read somewhere that it was overkill to do both - but maybe that wasn't correct - and since I'm currently not rebuilding, I think doing that, and updating statistics would be a good start....and I will do it during the wee hours of the morning when the system is at it's low usage point....
There is no right answer to this question. There are times when rebuilding makes the most sense and times when reorganizing makes the most sense. Check out this article from the Stairways on Indexes. http://www.sqlservercentral.com/articles/Stairway+Series/72443/[/url]
That one is the 11th article in the series of indexes. It might be good to start all the way back at the beginning and read the whole thing too. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 9:11 am
I guess rebuilding "can't hurt" - in any case (performance aside)....
Thanks for sharing that info! I'll check it out.
August 7, 2015 at 9:21 am
Sean Lange (8/7/2015)
ShorePatrol (8/7/2015)
Hi Folks,I have some indexes over 30% fragmented. Some are much more...
I have a job maintenance plan to "re-organize" indexes every Sunday on all databases. I didn't know if I should also be "re-building" the indexes, and if this would fix the fragmentation???
Any comments on fixing the fragmentation would be appreciated - I also wanted to ask if I should do this with the index online - assuming the performance hit during the rebuild is acceptable.
Thanks in advance for any tips.
If you are working with maintaining indexes you need to check out this page. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It will seriously blow the doors of anything you can build yourself.
My recommendations would be to use either of these two products because they work better.
or
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2015 at 9:22 am
SQLRNNR (8/7/2015)
Sean Lange (8/7/2015)
ShorePatrol (8/7/2015)
Hi Folks,I have some indexes over 30% fragmented. Some are much more...
I have a job maintenance plan to "re-organize" indexes every Sunday on all databases. I didn't know if I should also be "re-building" the indexes, and if this would fix the fragmentation???
Any comments on fixing the fragmentation would be appreciated - I also wanted to ask if I should do this with the index online - assuming the performance hit during the rebuild is acceptable.
Thanks in advance for any tips.
If you are working with maintaining indexes you need to check out this page. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It will seriously blow the doors of anything you can build yourself.
My recommendations would be to use either of these two products because they work better.
or
Awesome I will check those out. Thanks!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 9:38 am
Maybe this will help.
I added a step in my database backup jobs that reorganizes if fragmentation is between 10-30% rebuilds if over 30%. Also, object is a user table, big enough to make a difference, not a heap , and not XML.
i.e.
1. create table variables for @reorganize and @rebuild
2.
Modify and run this for each table
insert into @reorganize(indexname, schemaname, objectname)
select i.name, s.name, o.name
from sys.objects o
left outer join sys.schemas s on o.schema_id = s.schema_id
left outer join sys.indexes I on o.object_id = i.object_id
left outer join sys.dm_db_index_physical_stats(db_id(@databasename),null,null,null,'LIMITED') AS ips on i.object_id = ips.object_id and i.index_id = ips.index_id
where o.[type] = 'U' --is a user table
and page_count >30 --big enough to make a difference
and i.index_id > 0 --not a heap
and avg_fragmentation_in_percent between 10 and 30 --%
and index_type_desc not like '%XML%'
3. loop through the table doing the reorg/rebuilds
August 7, 2015 at 9:48 am
Every situation is different, but this is the order of my backup jobs:
1. Reorganize/Rebuild index
2. Check integrity
3. Update Statistics
4. Backup database.
August 7, 2015 at 10:05 am
SQLRNNR (8/7/2015)
My recommendations would be to use either of these two products because they work better.
or
+1 here for me. I like the SQLFool script, but hear good things about MInion
August 7, 2015 at 10:07 am
delizat (8/7/2015)
Every situation is different, but this is the order of my backup jobs:1. Reorganize/Rebuild index
2. Check integrity
3. Update Statistics
4. Backup database.
I don't like this as if the rebuild and update stats have issues or slow down, I still want a backup. Also, I don't necessarily want to delay integrity checks. I know this means corruption in the backup, but I still have the backup.
I tend to backup first, since that's precautionary. I want it done as soon as I can after some work is complete from users. Then I can run the other items and deal with issues.
August 7, 2015 at 10:36 am
Another vote for Minion Reindex. Absolutely worth checking out for maintaining your indexes.
"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
August 7, 2015 at 2:10 pm
delizat (8/7/2015)
Every situation is different, but this is the order of my backup jobs:1. Reorganize/Rebuild index
2. Check integrity
3. Update Statistics
4. Backup database.
Please tell me you aren't updating statistics on indexes that you rebuilt.
August 8, 2015 at 11:16 am
Lynn Pettis (8/7/2015)
delizat (8/7/2015)
Every situation is different, but this is the order of my backup jobs:1. Reorganize/Rebuild index
2. Check integrity
3. Update Statistics
4. Backup database.
Please tell me you aren't updating statistics on indexes that you rebuilt.
Agreed. REBUILDing an index automatically rebuilds statistics. You only need to rebuild stats for indexes that have been REORGINIZEd.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply