Post withdrawn.  Found something odd.

@sbhagyalakshmi95,

So do you have another column that would enforce the order of the data you posted or doesn't the order in the resulting rows matter?

Awesome info, Grant.  Thanks.

Post withdrawn. Found something odd.

From the original results posted for sys.db_db_index_physical_stats, I think it's pretty clear that ghost rows are an issue.  The fast inserts "feature" may also be an issue.

You first need to...

Neither a Pivot nor a nasty fast CROSSTAB can be done without an aggregate.  The good new for you is, MAX() is an aggregate.  Do the Pivot using MAX().  You'll...

The trouble with that  is that can go away and...

It sounds like there are multiple years (maybe 6 or so?) stored in the table.  Column Store or not , I'd split it up into multiple tables, at least by...

If you search for interview questions for DBAs, you'll find a wealth of questions.  I also want you to be aware that, while the questions can be pretty good, some...

Please look into the database ROLES for MSDB.  There a 3 that are totally different than they are in any other database.  They'll be obvious to you.  Then, look them...

That would make sure that you don't ever have to worry about the query using an index to seek....

Lucky but logical guess on my part.

Lordy... my apologies.  Really bad day or two, apparently.  I totally missed the fact that this was for SSRS 2016.  I don't even know how to spell SSRS.

Good question but...

The actual correct answer should be... "None of the above because there's no such thing as sys.backupset.  The correct schema for the table is dbo"... unless...

I can confirm this issue.  I was actually going to post about it but Homebrew01 beat me to it.

That and it now takes about a thousand years for the system...