Home Forums SQL Server 2014 Development - SQL Server 2014 Odd Behavior by Instance RE: Odd Behavior by Instance

  • July 24, 2017 at 3:42 pm

    #1952478

    Jacob Wilkins - Monday, July 24, 2017 3:29 PM

    ..the query which takes about 2 seconds to run normally clocks until the timeout is reached.

    Ok, this is what I was looking for.

    A few questions.

    First, from what application are you running the query? I ask because SQL Server itself will let a query run any arbitrary length of time; if you're hitting a timeout then it's an application timeout.

    Second, what evidence have you collected that suggests the issue is some sort of blocking?

    Third, if you run the query from SSMS, and from a separate query window check the wait type, wait time, IO, and CPU for the session, what do you see?

    Are IO and/or CPU steadily increasing, is it sitting with one wait type and a steadily increasing wait time, is it constantly changing wait types with relatively short wait times, etc.?

    Cheers!

    I agree with Jacob here.  I've seen times where this happens and it turns out the application isn't handling the response from SQL properly.  When I ran profiler on SQL (I know, I should use EE, but profiler is just so easy...), I saw the SQL returned in less than 1 second.  The application just didn't consume the data and said there was a timeout.  If memory serves, it was actually LINQ that was causing our problem.  Our developer changed the way his code worked, and the problem went away.  Although for us, it was that it worked fine in DEV, but when it went to our test system (which was identical from what I could tell), it failed.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.