April 3, 2013 at 6:51 pm
We have a department who has a couple of SQL servers,they said there sometimes maybe disk I/O issues. That is all information I got from them. They would like me to help troubleshooting.
How can I help them to identify the problem using SQL server native tools.
I login to the server and found it has 24 GB memory and max memory set to 20 GB.
What information I need from them to start troubleshooting?
Currently it runs fine.
PS: I know I can use PM, DMV, or any other recommendations to start with?
I think I have to set up real time monitor, otherwise it will collect too many data to save somewhere, or shall I first ask them when the slowness usually happen to monitor a certain time period?
Thanks,
April 4, 2013 at 5:20 am
Disk IO? How they confirm this. Do they have any IO related error in event log ? .
select * from sys.dm_io_virtual_file_stats (Null,null)
Run the query to see any IO stalls (IO_stalls_read_ms and IO_stalls_write_ms) are
there (considerable IO stall for considerable duration)
You may run Performance Monitor.exe to find disk IO metrics.See for "Disk Queue Length".
April 5, 2013 at 4:59 am
Also look at sys.dm_os_wait_stats. That's going to tell you specifically what is causing things to slow down on the server. It won't narrow it down to particular processes or anything, but you'll know if it's I/O, memory, CPU or whatever.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply