0

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?

Improve this question
6
  • 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
    Commented Apr 9, 2012 at 20:59
  • connection time out or command timeout? Very different things.
    – Remus Rusanu
    Commented Apr 9, 2012 at 21:03
  • @dash version is 2008. Thanks for the link.
    – RyanW
    Commented Apr 9, 2012 at 21:08
  • np. Good luck, and remember to update the question if you find more information and need more help.
    – dash
    Commented Apr 9, 2012 at 21:09
  • @RemusRusanu command timeout, I've updated the question with this.
    – RyanW
    Commented Apr 9, 2012 at 21:11

2 Answers 2

Reset to default
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.

Improve this answer
2
  • Database is about 5 GB. All of the timeouts occur on writes. Continuing to look at your links.
    – RyanW
    Commented Apr 9, 2012 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
    Commented Apr 9, 2012 at 22:38
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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