April 28, 2016 at 10:52 pm
Hi all,
I need some advise please. I have noticed a sudden significant increase in the Avg. Disk Queue Length on one of our servers. See the attached image.
There are only two types of files on the disk in question. My *.ldf log files and the tempdb *.mdf files (I would love to move tempdb files to its own drive, but there is no budget for that at the moment).
I have already reviewed:
1. the business process activity and nothing has changed there in the last month.
2. code changes by the devs and related stored procedure performance stats and execution plans, nothing obvious is standing out. I could easily be missing something here, there are a lot of changes nearly every day.
3. the log files and Tempdb have not grown in size, nor have the backups of the transaction logs files
The increased disk queue length is not causing immediate problems, but I don't like change and would like to know what is causing it. Do you guys have any suggestions into how to approach this investigation? What would you go look at if this was your server?
I am leaning towards the cause being increased data being dumped/spill into tempdb, but I’m not 100% sure yet how to track down the culprit. I will definitely be requiring additional monitoring and logging, but what should I log?
The server is running SQL 2014 SP1 CU4
Anything can be fixed
April 29, 2016 at 7:54 am
I would look at the wait stats and top running queries over the same period of time for the spike.
It looks like you're using SQL Monitor. You can use the "Rewind Time" feature to get the queries and wait stats for the time period in question. You can also then sort them by either runtime or IO (I'd do both). Identify what was being processed, and that will help you identify what changed (if it's code, structure, or just data volume). Something is changed. You won't just get a random, sustained spike for no reason at all.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply