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.

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

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.

Thanks,

Shirley

Thanks for the response.

I'm happy to see good answers already posted here.  Jim Thompson 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)

UPDLOCK

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. 

Hi Brain, thanks for quick response and the information provided. I know the Multiple threads executing in parallel is the answer on this question. Could you please explain how can a sleeping process SPID 169 block another process SPID 163?

Thanks

Shirley

It could be a couple of things.  It could be that it's waiting on a sleeping thread that is sleeping because it's waiting on another thread... which may be sleeping for the same reason etc.  If you aren't seeing deadlocks, sounds like that's the case.  My suspicion would increase if both/all SPIDs are executing the same SQL.

Thank you all for the information.