July 19, 2020 at 4:12 pm
Man, thanks for the example, Erland. Great stuff and timely, as well. We just installed some 3rd party software where they required the database the software uses to be snapshot isolation enabled. I didn't see why it was required but they won't support the software if you don't but I won't get into all that.
Reorganize just doesn't work as well as a lot of people hope and it has some pretty nasty side effects on the condition of the data in pages and huge amounts of log file usage compared to rebuilds. I've recently come across a couple of articles that suggest you shouldn't use Reorganize if you using AG, especially in the synchronous mode. My disclaimer is that we don't use AG and I've not personally used it so I don't know if these recommendations are good ones or not but it seems to be something that one needs to look into further.
https://sirsql.net/2015/05/18/availability-groups-reindexing/
http://glenn-pepper.co.uk/2018/10/12/index-maintenance-and-availability-groups/
I'm still looking for other links having to do with snapshot isolation vs rebuild/reorganize.
Thank you again, Erland, for the time you spent sharing the information you come upon by testing.
--Jeff Moden
July 20, 2020 at 2:40 pm
But, yes, depending in which way your index is disorganised, REORGANIZE can mean a lot of operations, as it performs a bubble sort of the file.
I don't think so. As I understand it, REORG never needs more than one additional 8K page to do its work (that's its biggest appeal). I can't imagine how it could always accomplish a bubble sort with only one extra page.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 20, 2020 at 3:06 pm
Erland Sommarskog wrote:But, yes, depending in which way your index is disorganised, REORGANIZE can mean a lot of operations, as it performs a bubble sort of the file.
I don't think so. As I understand it, REORG never needs more than one additional 8K page to do its work (that's its biggest appeal). I can't imagine how it could always accomplish a bubble sort with only one extra page.
I can't imagine that any software produced by Microsoft would use bubble-sort on anything, particularly in SQL Server where sorting is one of its core function.
July 20, 2020 at 4:34 pm
I don't think so. As I understand it, REORG never needs more than one additional 8K page to do its work (that's its biggest appeal). I can't imagine how it could always accomplish a bubble sort with only one extra page.
My recollection may be wrong, but I seem to recall that "bubblesort" was the word that Paul Randall used when I heard him talk about REORGANIZE. He might have used it figuratively.
One example on the extreme he gave was this: assume that there is an index in which there is only a single page which is in the wrong place. It is the first page in the logical order, but physically it is last. The way REORGANIZED works, getting this page in the right place, will log as many pages as there pages in he index, because this page will bubble up one slot a time.
Also, with relevance to the original question, this blog post from Paul Randall, certainly is of interest.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 20, 2020 at 6:07 pm
The pages might "bubble up" one at a time, but that has nothing to do with a "bubble sort", which requires vastly more RAM / storage.
Also, it seems to that would be true only if there were no empty pages. If, say, there's an empty page in the first physical extent, I would think SQL would just move the first logical page to that open physical page and other extents wouldn't need affected, unless perhaps there were open pages there.
Keep in mind, I said I mostly prefer reorg only when large numbers of rows / pages have been deleted.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 21, 2020 at 1:11 am
Erland Sommarskog wrote:But, yes, depending in which way your index is disorganised, REORGANIZE can mean a lot of operations, as it performs a bubble sort of the file.
I don't think so. As I understand it, REORG never needs more than one additional 8K page to do its work (that's its biggest appeal). I can't imagine how it could always accomplish a bubble sort with only one extra page.
Think back to the days when you wrote a bubble sort. You only needed one holding variable. You move one of the two items you want to swap to a variable, move the other one to the item you just freed up, and the move the content of the holding variable to the final open spot. Instead of 2 elements in an array and a holding variable, you have two pages in a file and a "working" page.
However, I do agree that it's not likely an actual "Bubble Sort" because it doesn't actually need to sort. Because of the pointers on each page (including the one page held in mid-air), it know where all the pages are and doesn't actually need to sort them. I think what most people are talking about is only the "swap elements" portion of a "Bubble Sort".
--Jeff Moden
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply