7 Replies Latest reply on Feb 10, 2015 6:41 PM by ltxd

    Hourly SQL wait time question


      In hourly timeframe screen, there is a SQL statement which was @showing 6 hours wait time during 1 hour period from 2pm to 3pm.  What does 6 hours mean? Very confuse 6 hours wait time in an hours period.



        • Re: Hourly SQL wait time question

          I'm not DBA but basically you can run multiple queries at once.


          Query 1 has a 10 second wait.

          Query 2 has a 10 second wait.


          You may run these concurrently or at the same time. So in the end both queries finish in 10 seconds but this counts as 20 seconds of wait time.

          • Re: Hourly SQL wait time question

            Either multiple executions of the query as Jim said, or there may be parallel query processing happening.

            It would be worth looking at the Historical Charts for the query and see how many executions there are, and what the main wait times are actually waiting on - there could be a tuning opportunity for you

            1 of 1 people found this helpful
              • Re: Hourly SQL wait time question

                Thanks for the response.

                A SQL Hash: 5113826157, FETCH API_CURSOR0000000000012C4A has 9 hours wait time between 10am to 11am. Could you please explain what happened and what the cause was. I've attached a few screenshots for your review below.






                screen3_1&3_2.pngScreen3_3&4.pngThanks for the response.









                  • Re: Hourly SQL wait time question

                    I'm happy to see good answers already posted here.  contactjt is right.  Multiple threads executing in parallel is the answer.  As far as what is happening, I see a locking wait type which jives with the query you shared because it is using a query hint demanding locks.  That query hint tells the database instance to use locks until the transaction is complete.  Add to this that I think the lock type I see there (it's a little blurred) is LCK_M_U, which is a wait for an update lock.  So you might look at the whole stack of SQL that is running with concern that you wrap up those transactions quickly to release the locks.  Because I don't have the whole stack of SQL here, it's hard for me to give more details but cursors are always concerning for SQL Server because they are generally slower operations.  Database engines excel with set based operations and cursors are iterative operations (loop constructs).  The larger the loops, the longer they take.  They don't scale well.


                    Table Hints (Transact-SQL)


                    Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

                    When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level. 

                    1 of 1 people found this helpful