Is Index Fragmentation Still Relevant in a Cloud Base/Virtual SQL Server Setup

Question

Post reply

Is Index Fragmentation Still Relevant in a Cloud Base/Virtual SQL Server Setup

YSLGuru

SSC-Insane

Points: 21661
More actions
November 9, 2020 at 8:05 pm

#3806105

We moved our mission critical DB to the software vendors private cloud. This meant we (my company) no longer handle the setup and maintenance of the SQL server hosting the DB. I don't know how the vendor has it setup (our access is limited) but I'm certain everything is virtualized, no more separate physical drives to use for each data file and log file as was the case in the old days, when dinosaurs roamed the network area storage.
Now in this virtualized world how relevant is index fragmentation? Does a heavily fragmented index still negatively impact performance in the cloud and if so is it just as important as pre-cloud or less so?

Kindest Regards,
Just say No to Facebook!

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

To answer your question, my answer would be "Probably even more so".

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I see the big issue as one of less RAM. I suspect the max ram you receive will be far less than what you'd get in an on-premises db.

When fragmentation occurs, the data will spread out across more data pages than it would be without fragmentation, thus taking up more RAM.

Thus, I agree with you that the I/O is no longer much of an issue (such vendors likely have dozens or, perhaps even hundreds, of spindles servicing the logical drives). However, RAM will be more of an issue. How much more, and whether it's enough to noticeably effect performance, is not certain, but fragmentation can only be bad. I'd try to avoid it especially for (very) large tables.

That said, the most effective tuning is still first to determine the very best clustered index on each table first, then work on the nonclus indexes. Hint: for most tables, an identity is *NOT* the best clus index.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

Thanks Jeff & Scott for taking the time to reply. It may sound like a dumb question but I asked anyway because the hardware/network folks keeping trying to say that with cloud based hardware and virtualization we no longer need things we used when a SQL Server was on a physical server with multiple drives and I fear they may not be doing tings they should.

Thanks again

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

YSLGuru wrote:

Thanks Jeff & Scott for taking the time to reply. It may sound like a dumb question but I asked anyway because the hardware/network folks keeping trying to say that with cloud based hardware and virtualization we no longer need things we used when a SQL Server was on a physical server with multiple drives and I fear they may not be doing tings they should.
Thanks again

To add to what Scott said (with the understanding that I'm not a cloud expert), RAM is indeed an issue but there's another caveat to it, as well. If something has been pushed out of RAM and you need it, it will still have to be read from "disk". Unless I'm completely wrong in my understanding, reading from disk is a chargeable event. If you protect RAM from having a lot of unused but allocated space, you can fit more in memory and not have to read it from disk as often, thereby reducing that "chargeable event", perhaps by a whole lot.

That puts even more emphasis on page density, which is frequently a problem with fragmented indexes.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Hmm, I wouldn't think there'd be a direct money charge just from having to reread something into RAM. Then again, that could change by vendor I guess (we use MS).

But it would add to the overhead / "overall cost" that is available for tier of service that you have. That is, the more (re)reads from disk you need to do, the less resources that are in available remaining in your tier to do other tasks.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".