0

I was tuning this query, the execution plan looks fine for me but based on the statistics the Lock waits time is very high .What should i do to fix this problem?

(@P0 bigint)
SELECT mgmtemails0_.Id AS Id3_0_,
   mgmtemails0_.BCCToId AS BCCToId3_0_,
   mgmtemails0_.BCCToMe AS BCCToMe3_0_,
   mgmtemails0_.CCToId AS CCToId3_0_,
   mgmtemails0_.CCToMe AS CCToMe3_0_,
   mgmtemails0_.CreatedDate AS CreatedD6_3_0_,
   mgmtemails0_.EmailBody AS EmailBody3_0_,
   mgmtemails0_.FromId AS FromId3_0_,
   mgmtemails0_.InstanceId AS InstanceId3_0_,
   mgmtemails0_.isDraft AS isDraft3_0_,
   mgmtemails0_.EmailTemplateId AS EmailTe17_3_0_,
   mgmtemails0_.Priority AS Priority3_0_,
   mgmtemails0_.ProfileId AS ProfileId3_0_,
   mgmtemails0_.ReplyToId AS ReplyToId3_0_,
   mgmtemails0_.ReplyToMe AS ReplyToMe3_0_,
   mgmtemails0_.SendToId AS SendToId3_0_,
   mgmtemails0_.Subject AS Subject3_0_,
   mgmtemails0_.UpdatedDate AS Updated16_3_0_
   FROM dbo.MgmtEmailSent mgmtemails0_
 WHERE mgmtemails0_.Id=@P0

enter image description here

Usage stats : Reads: 892,766 (892,766 seek) Writes:37,457

Op Stats : 901,699 singleton lookups; 332,618 scans/seeks; 0 deletes; 0 updates;

size : 3,034,470 rows; 3.5GB; 29.2MB LOB

Lock waits : Row lock waits: 681; total duration: 7,317 minutes; avg duration: 10 minutes;

these are the top wait stats enter image description here

  • 1
    Out of curiosity, what are your setting for MAXDOP and Cost Threshold for Parallelism? THREADPOOL waits are usually not a good thing to have, especially in that quantity. – Kris Gruttemeyer Oct 16 '15 at 14:51
  • MaxDOP is 8 , Cost threshold for parallelism is 50 – sebeid Oct 16 '15 at 14:57
  • 2
    Can you run the query with OPTION (RECOMPILE, MAXDOP 1) ? Also, check if there is any blocking going on or not. A peek into sys.dm_os_waiting_tasks WHERE wait_type = ‘THREADPOOL’ will show you the spids waiting for the threadpool. – Kin Shah Oct 16 '15 at 14:59
  • i think there is no problem in the query it self, it may be a victim for the waits. it usually don't take long time. this just happened last night – sebeid Oct 16 '15 at 15:29
  • @sebeid the thread pool wait means that you were running out of worker threads - did you check if there was prolonged blocking or deadlock schedulers (any dumps generated in the log folder) ? – Kin Shah Oct 16 '15 at 15:51
2

You're hitting two waits that are huge problems:

  • THREADPOOL: Running out of worker threads
  • RESOURCE_SEMAPHORE: Running out of memory for queries

There's another telling problem, here:

  • Long average waits on locking

You didn't post the hardware your server has, so I can't say if that's even close to adequate, or if your server settings are appropriate for it.

What's really lousy is that when queries are being blocked by a lock wait, all of the resources they grab to run (CPU, Memory, etc.) are held for the duration of the lock waits.

That means you're not only waiting on the locks to resolve, but then other queries totally unrelated to the blocking chain start piling up waiting for blocked queries to let go of resources.

If hardware is generally adequate, it's most likely a case of needing to tune queries and indexes to make modification queries as fast as possible so the blocking chains don't pile up and cause resource contention.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.