Index Rebuild Online - Actual Execution Plan.

Question

Index Rebuild Online - Actual Execution Plan.

SteveOC

Ten Centuries

Points: 1255
More actions
November 24, 2018 at 11:50 am

#378709

Hello.
Just tried running an Online Index Rebuild for the first time (we have Enterprise Edition installed) and thought I'd include the Actual Execution Plan to see what it told me (never tried doing this before).
I was taken aback looking at the plan because it seemed to show 0 rows of data flowing other than between the initial index scan of the CI and the Online Index Insert (where I see a thick black line representing the 2Million+ Rows). I thought that perhaps there was some threshold preventing the rebuild, but I started googling and eventually found an article on Online Index Rebuild with a plan looking similar to mine.
Curious to know why the Actual values are missing in most of the plan - and only the estimated are shown?
Thanks
Steve O.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

The answer is pretty simple (IMHO). If you look at the "Messages" tab, you'll notice there was absolutely no report of the number of row affected. That's because the Index Rebuild code is (at least I believe it is) machine language code that was compiled and simply wasn't set up to report rows affected to SQL Server except for the initial scan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SteveOC Ten Centuries Points: 1255 More actions · Answer 2

Jeff Moden - Saturday, November 24, 2018 1:51 PM
The answer is pretty simple (IMHO). If you look at the "Messages" tab, you'll notice there was absolutely no report of the number of row affected. That's because the Index Rebuild code is (at least I believe it is) machine language code that was compiled and simply wasn't set up to report rows affected to SQL Server except for the initial scan.

Thanks Jeff.
I did try again with Set Statistics IO On and get 2 lines - io figures for the scan on the CI and lots of zeroes for something called worktable.
Regards
Steve O.

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 3

SteveOC - Saturday, November 24, 2018 4:24 PM
Jeff Moden - Saturday, November 24, 2018 1:51 PM
The answer is pretty simple (IMHO). If you look at the "Messages" tab, you'll notice there was absolutely no report of the number of row affected. That's because the Index Rebuild code is (at least I believe it is) machine language code that was compiled and simply wasn't set up to report rows affected to SQL Server except for the initial scan.
Thanks Jeff.
I did try again with Set Statistics IO On and get 2 lines - io figures for the scan on the CI and lots of zeroes for something called worktable.
Regards
Steve O.

That could be logical scan the one that showed lots of zero, and worktable is something that SQL Server internally uses to process.