June 21, 2024 at 5:31 pm
how have you managed to max the VM in terms of storage? unless you have a crap vm (which by the looks of it isn't, as a 32 vcpu is already an acceptable vm), would would need to have hundreds of terabytes to have maxed it.
what is the VM machine type and how many disks you have - including disk type and individual size (e.g. standard hdd, standard ssd, premium ssd, premium ssd v2)
June 21, 2024 at 5:53 pm
32 vcpu is excessive on any vm, at the very least the host cpu scheduler will have hard time scheduling the vcpus unless you’ve been smart with vm placement
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
I am attaching the index data collection file. See if you infer any useful information.
Ok then, this will be short.
The code says it ignores UNIQUE indexes. Your spreadsheet says that more than half your indexes (634 out of 1050) have had zero reads. I'm not buying that because of the scant 4 days that the usage data has been accumulating.
To wit, I'm not making any recommendations until sufficient time has passed. I also strongly recommend that you add page density and logical fragmentation so that we can figure out which indexes are going through bad page splits to take a first pass at a possible Fill Factor.
And, since almost all of your Fill Factors are set to 0 (same as 100 in all other regards), I'm going to recommend that you do like I recommended and stop doing index maintenance. If indexes are fragmenting, that means they're doing page splits. Removing all free space is a guaranteed way to make the page splits MUCH worse and you're simply perpetuating fragmentation.
Doing index maintenance wrong is much worse than doing none at all... and you ARE doing it wrong. Yes, you're probably following some form of "Best Practices"... I'm here to tell you that means you're doing it wrong. Period.
Add the columns I asked you to, stop rebuilding indexes for at least two weeks, do only statistics updates, and take another measurement before you make the mistake of doing index maintenance again.
We'll have a look at your new spreadsheet then.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2024 at 12:21 pm
Doing index maintenance wrong is much worse than doing none at all... and you ARE doing it wrong. Yes, you're probably following some form of "Best Practices"... I'm here to tell you that means you're doing it wrong. Period.
Got it Sir. Thank you for those valuable suggestions. These maintenance have been laid out by some other person, we are maintaining it.
As the database size keeps growing, we are seeing all sorts of side effects. Thanks for the honest feedback.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply