May 9, 2012 at 3:10 am
Hi,
I have configured 6 databases on my db server and recently started
getting timeout errors from my .net web application for all the databases.
When I checked the events of db server i could find the below error
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 833
Date: 5/9/2012
Time: 11:24:47 AM
User: N/A
Computer: CMS-DB1
Description:
SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\DB\DBMH.mdf] in database [DBMH] (7). The OS file handle is 0x00000844. The offset of the latest long I/O is: 0x00000227be6000
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 03 00 00 0a 00 00 00 A.......
0008: 08 00 00 00 43 00 4d 00 ....C.M.
0010: 53 00 2d 00 44 00 42 00 S.-.D.B.
0018: 31 00 00 00 00 00 00 00 1.......
May 9, 2012 at 8:01 am
There could be a zillion reasons for timeout. The most common is poorly written sql. Does this happen for all your sql or just certain procedures?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 8:27 am
you would also want to look at your indexes. if they are missing or the stats are out of date, that would be a problem too.
May 10, 2012 at 2:11 am
This is not happening to us all the time.. we have even noticed timeouts during
non peak hours where database usage is minimal. The same query will run afterwards without any issue.
Also in some procedures we are using table variables and these procedures are used so often. Can this be concern ?
May 10, 2012 at 7:57 am
Poor indexes, stale statistics, parameter sniffing could be any or all of those plus a zillion other reasons. My best guess is periodic performance issues. If you want some help you will have to post some details. Check out this article about what to post for performance issues. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply