Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Performance troubleshooting Expand / Collapse
Posted Wednesday, April 3, 2013 6:51 PM


Group: General Forum Members
Last Login: Friday, November 20, 2015 12:04 PM
Points: 1,871, Visits: 3,520
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?

Post #1438629
Posted Thursday, April 4, 2013 5:20 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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".
Post #1438721
Posted Friday, April 5, 2013 4:59 AM



Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 15,976, Visits: 30,342
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1439158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse