Strange Process Wait on a small table for a Select statement

Question

Post reply

Strange Process Wait on a small table for a Select statement

YeePee

Mr or Mrs. 500

Points: 561
More actions
August 27, 2009 at 8:39 am

#138505

Hi all,
Environment : SQL 2005 SP3
I have a Strange issue. I have a table with 1500 records in it. Some times during the day ( almost every day ) I see a Process wait on the table.
Here is the query which waits.
SELECT ID,OUTPUT FROM QUEUE WHERE STATUS=0
1.The Status Column is already indexed.
2.I see a Blocking on this Process by the following process ( output of a DBCC input buffer) .It is also Visible in the Activity monitor as a waiting status . On Sp_who2 I see the SPid id blocked.
Here is the Script which blocks the above transaction .
(@P1 nvarchar(32),@P2 nvarchar(32),@P3 nvarchar(62))SELECT ID FROM QUEUEWHERE TOFIELD=@P1 AND FROMFIELD=@P2 AND SUBJECT=@P3 AND STATUS=-1
Can some one shed some light into this ? . At least where should I start the debug process.
TIA
Yeepee

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 1

No one have seen this kind of issue before ?. Intresting...

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

The Select you are doing, is it within a transaction? Are there any open transaction?

-Roy

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

On the status column, how unique / selective is the data in the column?

How many rows could you see returned with a status of 0?

Do you have an index on ID and Output?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 4

It is not within an Open transaction .

The result set is usually 150 records out off 1500.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What's the wait_type and wait_resource?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

1) what is the transaction isolation level of both connections?

2) recall that dbcc inputbuffer only shows the LAST executed command - there could well be previous commands in the same batch/transaction (which could be implicit) that have a blocking lock on the rows/pages/table of concern. I would use sp_lock or a DMV query to determine all the locks on this table when the blocking is happening.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 7

Hi ,

Here is the output when the wait is happening

sql I ran

------

select session_id,request_id ,start_time ,status,command ,user_id ,connection_id,wait_time

wait_type ,last_wait_type ,wait_resource

from sys.dm_exec_requests

---

Output

-------

15302009-09-04 09:33:41.130suspendedSELECT5353F9ACB-4151-46C7-B1B2-03F4985ACFC8764796LCK_M_SKEY: 9:72057594308001792 (ac0069ecc4d8)

---

Another SQL for the Locks

--------------

SELECT session_id ,status ,blocking_session_id

,wait_type ,wait_time ,wait_resource

,transaction_id

FROM sys.dm_exec_requests

WHERE status = N'suspended';

GO

-----------

session_idstatusblocking_session_idwait_typewait_timewait_resourcetransaction_id

153suspended188LCK_M_S1147843KEY: 9:72057594308001792 (ac0069ecc4d8)121718019

------

Does this ring any bell to anyone? .

TIA

YeePee

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Per this document (which is the BIBLE for performance tuning) "SQL Server 2005 Waits and Queues SQL Server Best Practices Article", LCK_M_S means:

"Occurs when a task is waiting to acquire a Shared lock. See the sys.dm_tran_locks topic for a lock compatibility matrix."

SOMETHING has some form of exclusive lock (like I said before) which is preventing your SELECT statement from getting the shared lock it requires. There is an update, delete or insert statement somewhere before the 'blocking' SELECT. Find it and you will have your answer.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Form that the select is blocked, waiting for a lock. Can't see what table teh lock is requested on (query sys.partitions and sys.tables for the partition_id 72057594308001792 to get the table name)

What's the select that's blocked and what's the command that's been run by the connection that's blocking it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 10

there is a great stored proc: sp_lock2 maybe it will help you ?

you can it from: http://support.microsoft.com/kb/255596