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
Author
Message
Posted Wednesday, April 3, 2013 6:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:56 PM
Points: 1,749, Visits: 3,154
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,
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse