Significance of Historical waits report in SQL server Performance dashboard
-
I have the following historical wait report for my SQL server.
I have noticed that Wait Category Tran Log IO is consuming 27.09% of percentage time followed by Query Store which takes 24.34%. I want to know if this distribution is normal or should I take any adequate steps to improve the performance of my server.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
The tran log I/O seems really high to me, as does query store. Without having more esoteric knowledge of the system, I'm hesitant to make any judgement on that.
I will ask the questions ...
- How often are you running index maintenance?
- If you are running index maintenance, what does it do? For example, is it using REORGANIZE from (as an example) when for indexes with between 5 and 30% fragmentation and doing REBUILDs for anything over 30% fragmentation?
- How often are you doing statistics updates?
The reason I ask about #1 and 2 above is that the high amount of log wait suggests to me that you might be suffering from a large number of page splits and doing index maintenance incorrectly can be a primary cause of such a thing. The settings I spoke of in #2 are one of the worse ways to do index maintenance and doing any index maintenance on indexes that a fragmenting without knowing the reason why it's fragmenting is futile. If you're rebuilding indexes that have a "0" fill factor, you could actually be making the page splits much worse. And, no... adding a Fill Factor other than 0/100 is frequently NOT going to help.
Microsoft changed their recommendations about index maintenance back on 20 April 2021 and have updated that recommendation a couple of times since then but they've still not correctly documented nor even have suggested the absolute train wreck that using REORGANIZE can make of your indexes.
My initial recommendation would be that if you are doing any index maintenance, then stop doing it because doing it incorrectly is a whole lot worse than not doing it at all. I stopped doing any form of regular index maintenance on my production servers way back on the 18th of January, 2016. The only time I'll rebuild an index if it's taking up too much space (or if it's a Random GUID that has gone over 1% fragmentation... GUIDs are a whole 'nuther subject).
Try it for a month and see what happens to your numbers. If you have indexes that drop below 80% page density (and you probably will), let's talk about those before you make the mistake of defragging them the wrong way.
In the meantime, make sure that you're updating statistics that need it on a regular basis, especially if you're clustered indexes have "ever-increasing" keys.
--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) -
And for Query Store, you may want to look at the settings for data collection. Prior to 2019, the default was to capture all executions. I'd suggest switching that to Auto which filters the queries captured. As with anything, testing is your friend.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply