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 12»»

Server log error Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 4:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:04 PM
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
Post #1467069
Posted Tuesday, June 25, 2013 4:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1467073
Posted Tuesday, June 25, 2013 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:04 PM
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?
Post #1467423
Posted Wednesday, June 26, 2013 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1467492
Posted Wednesday, June 26, 2013 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:04 PM
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.
Post #1467494
Posted Wednesday, June 26, 2013 2:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1467497
Posted Wednesday, June 26, 2013 5:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:17 AM
Points: 1,289, Visits: 2,844
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.



Post #1467588
Posted Friday, June 28, 2013 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:04 PM
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,
Post #1468456
Posted Friday, June 28, 2013 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1468468
Posted Sunday, June 30, 2013 11:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:04 PM
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
Post #1468929
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse