Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I setup a perfmon collector to capture data around some recent SQL Server command timeout errors and most indicators point to disk issues. CPU and memory numbers look normal. This is a SQL Server 2008 physical server with 2 disks. SQL data files reside on a one disk (RAID 5) and SQL log files on another.

At the time of the errors these particular counters are way beyond normal (about 10x worse than normal):

  • % Disk Time
  • Disk Reads/Sec (on the data disk)
  • Disk sec/Read (on the data disk)
  • SQLServer:Buffer Manager\Page life expectancy

With these perfmon findings, what would the next step be to isolate the problem? Running SQL Profiler?

share|improve this question

migrated from stackoverflow.com Apr 9 '12 at 21:18

This question came from our site for professional and enthusiast programmers.

1  
What version of SQL Server are you using? Where is TempDB? You want to be looking for latch waits if you think it's IO - see the white paper available from here for SQL 2008: msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx –  dash Apr 9 '12 at 20:59
    
connection time out or command timeout? Very different things. –  Remus Rusanu Apr 9 '12 at 21:03
    
@dash version is 2008. Thanks for the link. –  RyanW Apr 9 '12 at 21:08
    
np. Good luck, and remember to update the question if you find more information and need more help. –  dash Apr 9 '12 at 21:09
    
@RemusRusanu command timeout, I've updated the question with this. –  RyanW Apr 9 '12 at 21:11
show 1 more comment

2 Answers 2

Command timeout simply means that your queries don't return in the 30 seconds allocated by default. What is the size of your database and how much RAM you have?

The high disk usage is most likely a symptom, not a cause, and is caused by table scans, ie. missing indexes. A quick look at the execution plan of your timing out queries will reveal if this guess is correct or not (See Displaying Execution Plans by Using SQL Server Profiler Event Classes.

As a general advice I suggest you approach this as a performance investigation and deploy the well tested Waits and Queues methodology.

share|improve this answer
    
Database is about 5 GB. All of the timeouts occur on writes. Continuing to look at your links. –  RyanW Apr 9 '12 at 22:14
    
Timeout on writes would most probably indicate lock contention with queries (scans). Table scans are still possible (and likely) the cause. Waits and Queues explains how to narrow down the problem. –  Remus Rusanu Apr 9 '12 at 22:38
add comment

Using the SQL Server Management Studio, open the activity monitor. Identify the connection that is in error and then observe the status of the connection. You can see whether the connection is doing a large query (e.g. table scan of large table) or whether it is in an IO wait. Both could be likely as you go past the 1GB size of your table, but if you are seeing several fail all at the same time, you may have a deadlock.

The Deadlock is likely caused by a query that is taking a long time (e.g. insertion on a large table) which is taking an update lock and preventing any queries that require consistency from reading data from the database.

Have a look at the index fragmentation of any of your large tables. I've seen poor performance occur on updates when the database index is badly fragmented and all updates result in an index miss and have to do order(n) search at the end of the table for a new page.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.