Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Weird performance differences

    Hi,

    When I develop and test my queries, I note how many records are affected and the duration of the query.
    - The number of affected records is a good indicator to spot bugs in your code at an early stage.
    - The duration is just nice to know.

    The queries I'm working on now, take from 1 to 10 seconds to complete, with a total of about 25 seconds. When I run all 5 the scripts at once, the execution seems to take forever. I stopped the execution after 5 minutes. When I run the scripts one by one again (select a single query, tap <F5>), they take 1 - 10 seconds to complete, as expected. Running them all at once (select nothing, tap <F5>), leads to a seemingly endless execution time again.

    When running all the scripts at once, the system does flag problems like a WITH clause (CTE) that is not preceded by a GO and the like (errors that were not encountered when running the queries one by one). Once all such problems are solved, execution time takes "forever".

    What is causing this and what can I do about it?

    MS SQL Server Management Studio - SQL Server 2008R2
    Last edited by Wim; 09-03-13 at 10:57.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That is weird. On a separate connection, run sp_who2 SPID where is the spid of the window running the queries. This can give you whether something is blocking the spid running the queries. You can also run
    Code:
    select wait_type
    from sys.dm_exec_requests
    where session_id = spid
    sys.dm_exec_requests will only show currently running items (and all system processes), so if the spid does not show up there, it is not actually running (which could be even more confusing).

    Also, if you are running with the actual execution plan being generated, that can take some time, so disabling that may help. You would of course need to go back to getting the execution plan for each query individually.

    Lastly, SQL Server will only send results back to the client once the buffer has filled with enough results (I think 4K is the default network buffer size), so if the total result set is less than that, you won't get results until the last query finishes.

    After that, the only other oddities I can think of are the separation of messages from results in the results pane of SSMS, but I think you are above that.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    That awkward moment when you go to the garage mechanic with your car that has been making a strange noise for weeks. And once there, your car runs completely silent...

    I can't reproduce the problem for the moment.

    I added
    Code:
    PRINT 'My spid = ' + CAST(@@SPID as VARCHAR(5));
    at the beginning of the SQL file and now it completes in 24 - 26 seconds. I tried it with different files that had all showed that problem in the past. None showed the performance problem again.

    Commenting out the PRINT statement didn't bring back the performance problem neither.

    That awkward moment .... I don't know if I should be happy or not. I have had this problem on and off in the past months. It may be back tomorrow or next week, or when we go in production and I will be forced to select and execute each and every query by hand (INSERT's and sometimes one or more related UPDATE's) to do the data migration of dozens of tables...

    Thank you for your reply, MCrowley.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to hazard a guess that the problem was another process (spid) which might have had a lock of some sort on TWO of the tables that you referenced. When you run each of your queries by itself, they only lock one table or the other. When you run your entire query then a "deadly embrace" becomes possible, which leads to a deadlock condition which is really hard to detect. As this isn't a classic deadlock situation, it usually takes SQL Server minutes to find these, and some database engines never find them.

    If you either explicitly or implicitly killed another process between the time that the query broke and it worked, then I'd be pretty comfortable that this was the case even though you'll probably never be able to prove it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm developing on the development database. Other programmers use that database too. Yet they all use very short transaction that lock-release within a fraction of a second.

    If it happens again, I will make a copy of the development database and run my scripts in that database. "Deadly embraces" should not occur then.

    Would I get an error message if the system detected a "deadly embrace"?
    But then, I never allow the system to kill one of the locking processes, I kill the process myself when my patience runs out.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A conventional deadlock will be found immediately and reported in milliseconds. Depending on the complexity, a deadly embrace may take minutes to discover and theoretically could take a very long time (thousands of minutes) if the lock pile is big and complex enough.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Wouldn't you need to wrap all 5 queries in an explicit transaction for the deadly embrace to happen? If there is no explicit transaction, each query should* take out, and release their locks in serial fashion.

    * I can be quite wrong about this, and may be about to be schooled.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The answer is the ever popular: It depends.

    In theory, every SQL Statement in Transact-SQL that isn't surrounded by an explicit transaction is surrounded by an implicit transaction. Those transactions are usually left pending until the batch completes, although you can change that via session/server/etc. settings. The default values (when SQL is configured out of the box) are pretty simple, but those tend to drift over time as the DBA changes various settings due to problems that occur. This makes problem determination "interesting" in cases like this!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •