Sql-server – Long running query with index seek

lockingsql server

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

Best Answer

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.

Related Question