October 19, 2014 at 5:12 am
Hi,
Any idea why index reorganizing is 10 times slower then rebuild with "ONLINE=ON" clause?
Thanks,
Jacek
October 19, 2014 at 6:51 am
Contention for resources? Not sure without more information. Have you looked at the blocks and wait resources of the process while it's running? That's where I'd start in order to understand what's happening.
By the way, an argument can be made that reorganizing indexes doesn't supply enough performance enhancement to make the resource cost worth the time. Especially in your current situation, you might want to evaluate that.
"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
October 19, 2014 at 8:42 am
Most likely cause is simply that you are reorging wth too much fragmentation. The page swapping can CRUSH you (and the tlog and data files with writes) when you have significant fragmentation.
You can watch what is happening with sp_whoisactive to check for blocking, read/write activity and a whole host of other metrics.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 19, 2014 at 9:24 am
I'm interested in your case.
I'll need to implement only Reorganize with update stats of indexes because of a Standard edition.
Are your tables very big and having high fragmentation? Are you rebuilding with sort_in_tempdb=ON, and then make comparison with the Reorganizing (reorganizing is done in the database)? It also does matter how your tempdb is configured.
Can you share if you find out the reason why reorganizing is taking much more time than rebuilding in your case? Thanks in advance.
Igor Micev,My blog: www.igormicev.com
October 20, 2014 at 9:12 am
Grant Fritchey (10/19/2014)
Contention for resources? Not sure without more information. Have you looked at the blocks and wait resources of the process while it's running? That's where I'd start in order to understand what's happening.
Thank you Grant. I am going go test it tomorrow. I have hectic Monday.
By the way, an argument can be made that reorganizing indexes doesn't supply enough performance enhancement to make the resource cost worth the time. Especially in your current situation, you might want to evaluate that.
What is the best way to evaluate reorganizing is worthy?
Thanks,
Jacek
October 20, 2014 at 9:18 am
TheSQLGuru (10/19/2014)
Most likely cause is simply that you are reorging wth too much fragmentation. The page swapping can CRUSH you (and the tlog and data files with writes) when you have significant fragmentation.
Fragmentation is relatively low - around 20%.
You can watch what is happening with sp_whoisactive to check for blocking, read/write activity and a whole host of other metrics.
Thanks for suggesting to use sp_whoisactive - great tool! I have used it today.
Jacek
October 20, 2014 at 9:40 am
Igor Micev (10/19/2014)
Are your tables very big and having high fragmentation?
Table is a 13GB, fragmentation 20%,
Are you rebuilding with sort_in_tempdb=ON, and then make comparison with the Reorganizing (reorganizing is done in the database)?
Nope. I what cases is it recommended?
It also does matter how your tempdb is configured.
What do you mean?
Can you share if you find out the reason why reorganizing is taking much more time than rebuilding in your case? Thanks in advance.
No worries. I like such unusual situations. It requires to enhance knowledge. I will be happy to share the conclusions.
Jacek
October 20, 2014 at 9:47 am
20% frag on a 13GB table could be quite a lot of page swaps. Track the tlog usage for both evolutions and see what the difference is.
Also, doing this online could result in a lot of activity related to keeping track of modified values on various pages, right?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2014 at 10:02 am
TheSQLGuru (10/20/2014)
20% frag on a 13GB table could be quite a lot of page swaps.
The point is, what produce more page swaps: reorganize or rebuild?
According to Microsoft "Reorganizing an index uses minimal system resources".
Also, doing this online could result in a lot of activity related to keeping track of modified values on various pages, right?
I tested it on test database with the same configuration on mirror server with the same result. I think keeping track of modified values is not the issue.
Jacek
October 20, 2014 at 10:06 am
Jacek Falkiewicz (10/20/2014)
Grant Fritchey (10/19/2014)
Contention for resources? Not sure without more information. Have you looked at the blocks and wait resources of the process while it's running? That's where I'd start in order to understand what's happening.Thank you Grant. I am going go test it tomorrow. I have hectic Monday.
By the way, an argument can be made that reorganizing indexes doesn't supply enough performance enhancement to make the resource cost worth the time. Especially in your current situation, you might want to evaluate that.
What is the best way to evaluate reorganizing is worthy?
Thanks,
Jacek
Basically, how much faster are your queries after a reorganization? If they're faster, are they faster enough that the time saved is worth the time spent on the reorg? I've seen considerable testing that suggests, for most people, most of the time, it's not (and for those who ask, it was an article Brad McGehee was working on, not sure if it's published). In your case, doesn't sound like it, but measure to be sure.
"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
October 20, 2014 at 10:10 am
Jacek Falkiewicz (10/19/2014)
Hi,Any idea why index reorganizing is 10 times slower then rebuild with "ONLINE=ON" clause?
Thanks,
Jacek
You can't just go by the logical frag %.
Could you post the full details of the index fragmentation and the query you used to generate it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2014 at 1:40 pm
Are you rebuilding with sort_in_tempdb=ON, and then make comparison with the Reorganizing (reorganizing is done in the database)?
Nope. I what cases is it recommended?
It's good when you're doing it online. It doesn't put more load into the user database.
I always rebuild indexes using the tempdb. I usually configure it with 8 files each with same initial size and same autogrowth.
I'd recommend using it if you're rebuilding online. If the user database is inactive while the rebuild is taking place then it doesn't matter.
Igor Micev,My blog: www.igormicev.com
October 22, 2014 at 12:43 pm
Jacek Falkiewicz (10/20/2014)
TheSQLGuru (10/20/2014)
20% frag on a 13GB table could be quite a lot of page swaps.The point is, what produce more page swaps: reorganize or rebuild?
According to Microsoft "Reorganizing an index uses minimal system resources".
That's quite a sweeping assertion from MS! It certainly can use less...it depends on the data. For a rebuild you literally build an entire new copy of the table and swap it out. If you're tight on space in your database you may find this a bad option as its going to grow your data file (rebuilt a 120GB table this afternoon...then shrunk the db back down...which fragmented the index again! 😀 ). Paul Randal said his 10-30 percent reorg and 30+ percent rebuild guidelines were pretty arbitrary but they are reasonable for us in *most* scenarios. It's very hard to have hard and fast rules about this, but if you have a small amount of fragmentation, the reorganize is going to do less work, only hitting what it needs to, vs rebuilding the whole thing regardless.
We have one table (the aforementioned big one) where we don't get great performance on reorganize...our current theory is that the row sizes being what they are (1200 bytes) and fill factor being 0/100, the reorg is trying too hard to compact data down to minimize page empty space. We're trying cranking fill factor down to 80 and see if the reorgs happen a bit quicker then (since they will not need to work as hard on the leaf-level compaction phase), will see next week...
Good luck!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply