Server log error

  • 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

  • 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
  • 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?

  • 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
  • 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.

  • 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
  • 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.

  • 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,

  • 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
  • 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

  • Did you read part 2?

    You get the code for the procedures by going to the server and scripting the procedure out, you get the performance characteristics by running the procedure (on a non-production server)

    p.s. Ignore the sqlagent procedures. They're system procedures, you can't change them and they shouldn't be a 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
  • If i unable to import the trace file into SQL Management Studio because one of the column "TextData" is too big compare with my column in table "TraceResult" , and i get the below error message.

    Msg 8152, Level 16, State 10, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    How to truncate and import it? Thanks

    Regards,

  • CAST(TextData as Varchar(8000))

    put that in the insert instead of just the column name.

    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
  • My top 3 procedure were this, do you think it is causing my server disc read/write high usage?

    1. sp_executsql

    2. proc_DeleteEventReceiver

    3. msdb.dbo.sp_sqlagent_log_jobhistory

    Regards,

    Chungb

  • Since you've given no indication what the sp_executesql is executing or what proc_DeleteEventReceiver does, I have no idea. Why don't you investigate further?

    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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply