We have SQL Server 2017 with Always On High Availability. Two days ago, we noticed that the disk io is significantly higher than normal. Normally it is at most 15MB/S, but it has been about 100MB/S. We checked the Activity Monitor and noticed that the tempdb templog.ldf has about 100MB/S Written. We tried to fail over to the back up server and fail back to the main server, but it did not work. We estimate that overall performance of the SQL server is down about 20% because of that. How do we get it fixed? Thanks.
Answer by Jeff Moden ·
So, what changed two days ago??? Who deployed new or modified code or a new index and what was it? Or did someone make a change to the server settings?
You need to find out what's going on with TempDB first if 100MB/S is different than your baseline. You do have a baseline, right? ;)
I also agree with @Kev Riley above... using a script like sp_whoisactive to find out what the code is that's running right now.
And, seriously... if you don't know what your normal settings for the server is or you don't know what your baselines are, you really need to make a change so that you can prove to someone else what it is and to help you to decide where to look.