SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server log error


Server log error

Author
Message
chung_boon
chung_boon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 43
Hi,

My company user complain to me about the web application getting error every day on certain time. So I check my SQL server log for any error and found out few special event, please refer below

" SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file in databases. The OS file handle is 0x0000000000000B4C. The offset of the latest long I/O is: 0x000000d7e68000 "

It happen for few database and i am not sure how to troubleshoot it.

Regards,
Chung Boon
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233521 Visits: 46361
You have slow IO requests. Check that the IO subsystem is not overloaded, try tuning queries to read less data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chung_boon
chung_boon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 43
So how to check the IO is overloaded?

My drive is from SAN so mostly it is not the drive issue, but most likely my query. Any query that i can use to check the top query running in my sql server?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233521 Visits: 46361
There's nothing magical about a SAN that means it's not the problem. In fact, it's very likely it is the SAN, a misconfigured SAN.

Check the throughput you're getting from the sAN (google, there's detailed docs on that), chat with your SAN admin, check the SAN's performance.

As for queries:
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chung_boon
chung_boon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 43
Ok, thanks a lot.

My SAN admin say nothing wrong with the thing so i will go thru the troubleshooting for my SQL server.

Thanks for the link.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233521 Visits: 46361
In my experience, the SAN admin almost always says there's nothing wrong even when the SAN is clearly, obviously and provably the problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Summer90
Summer90
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 3831
GilaMonster (6/26/2013)
In my experience, the SAN admin almost always says there's nothing wrong even when the SAN is clearly, obviously and provably the problem.


I completely agree. The three times I have experienced this issue I get a nothing is wrong from SAN guy.... Open a case with MSFT and it is nearly impossible to figure out what exactly is wrong and then finally SAN team admits they have a problem.
chung_boon
chung_boon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 43
Hi Guys,

I run a few query on my SQL server, and found out everyday at certain time, my server CPU usage will raise up until 100% and that is where the error log appeared.

I need some guide line to check while file or which query causing the high CPU usage, i already tried certain query from internet but still unable to identified what is the main reason for that error. Thanks

Regards,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233521 Visits: 46361
chung_boon (6/28/2013)
I need some guide line to check while file or which query causing the high CPU usage, i already tried certain query from internet but still unable to identified what is the main reason for that error. Thanks

Regards,




Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chung_boon
chung_boon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 43
Hi Gail,

I am doing fine with the part 1 to capture the queries and store procedure, i already have the procedure Name ( i use a test database to try out the procedure)

example: msdb.dbo.sp_sqlagent_get_perf_counters

How to examine the queries statistics and execution plan from it? Because i have no idea what the code for procedure looks like. Is it procedure refer to TextData column like what we obtain during part 1?

Regards,
Chungb
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search