February 3, 2021 at 10:31 am
Hi Experts,
Yesterday, we had some users complaining about database performance.
They claim SQL instance is tool slow and some of the front jobs are taking more than 5 mins which is longer than usual thereby crossing 40 secs SLA.
They also mentioned there are experiencing some timeouts.
From server side there we was no blocking and the cpu usage is under 40% usage. I don't see any timeout errors logged in the SQL Server ERRORLOG. However, I see some I/O related warnings that I/O taking more than 15 secs in the errorlog.
What kind of information could have been requested from the Application team to handle this particular issue. We have no idea if any network was slow or their piece of code or anything else which was contributing to the slowness. Any relevant / specific data points or questions which DBA is missing here to get better control of such performance requests?
-Bob
February 3, 2021 at 10:50 am
Regarding your I/O errors: You MUST investigate these as SQL Server is only raising this alert if a single I/O takes more that 15 SECONDS !
This may indeed have a direct impact on all applications using data/indexes of that file.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2021 at 12:37 pm
And when those I/O operations take that long, the problem is not inside SQL Server. It's outside. The OS, disk controllers, network, switches, hubs, or the disks themselves. That's why you're seeing no evidence of load within SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2022 at 7:06 pm
I agree that MS says the 15 second warnings are only due to hardware. I have no actual written proof but we've tested. All our stuff is on some killer SSDs and we're tested read and writes and it looks like this can still all be inside SQL Server in the form of performance challenged code that's riddled with performance challenged code that creates a bunch of "accidental cross joins" because of inappropriate many to many joins, poor programming practices, and a wad of other things. If one of those queries is used many times, everything might be waiting on I/O and CPUs aren't real busy if they're waiting for I/O.
And then there are things like blocking that will hold up the whole world. One of the major hidden issues there is the page splits that occur when you rebuild or (gasp!... the worst) reorganize your indexes after doing incorrect Index Maintenance is done. The really horrible part is that the supposed "Best Practices" for Index Maintenance accepted by most of the world is almost completely incorrect.
For example, if you have a large clustered index that quickly fragments to more than 50% and it's using the default Fill Factor of "0", which is the same in all other aspects as "100", what do you thing that's going to do to the number of page splits (which use an impenetrable system-level lock on the affected pages).
Another question is, what are you doing about statistics? As a long running experiment starting on 16 January 2016, I went 4 years on my prod-box with no index maintenance but was rather religious about keeping statistics up to date. Performance seriously increased in the first 3 months and stayed that way for the other 3 years and 9 months when I had to do some thoughtful rebuilds to recover disk space.
I don't recommend avoiding all index maintenance unless you have plenty of extra disk space but it was a good test to prove my point. My point is, keeping stats up to date is one of the most important performance benefit actions you can take. And stop using the supposed "Best Practice" Index Maintenance. It's actually a killer mostly because REORGANIZE doesn't work the way most people think it does. I have even made insert-heavy indexes based on Random GUIDs go quite literally for months with <1% fragmentation by doing index maintenance the right way.
Again, some of the tools in Brent Ozar's "First Responder Kit" will help a lot in determining such things.
I'm not saying that it couldn't be an issue with external storage. I'm saying that you can't assume anything. "Must look eye".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2022 at 7:14 pm
To seriously summarize, it looks like you have a bit of a hot mess on your hands and you have no clue as to where to even start looking. I seriously recommend that you get a tool to help you and I've talked about Brent Ozar's "First Responder Kit" many times. He also has 'tubes on how to use and interpret whats going on. I recommend that you make the mental and time investment.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply