June 14, 2005 at 8:11 am
I'm running a step in a DTS package and I see on SQL server it is "Blocked by -2" when I check the SP_ID. Normally it gives you the SP_ID of who is blocking or who you are being blocked by. What does "Blocked by -2" mean?
June 15, 2005 at 2:48 am
The process of SPID 2 has a lock of some type that prevents the blocked SPID from completing its work.
Russel Loski, MCSE Business Intelligence, Data Platform
June 15, 2005 at 3:03 am
June 15, 2005 at 7:57 am
If you are performing several queries.. most times where you are just viewing the data in a table or a subset of the data, you can avoid locking of the tables by using the query hint " WITH (NOLOCK)". You will also notice performance gains by telling SQL that all you want is a snapshot of the data from a query.
Here is a comparison of the difference in performance:
-- UnHinted Query
USE pubs
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = GETDATE()
SELECT titles.title, publishers.pub_name, pub_info.logo
FROM Titles JOIN publishers ON titles.pub_id = publishers.pub_id
JOIN pub_info ON publishers.pub_id = pub_info.pub_id
ORDER BY titles.title
SELECT @EndTime = GETDATE()
PRINT 'Query executed in: ' + CONVERT( varchar, DATEDIFF( ms, @StartTime, @EndTime)) + ' milliseconds.'
GO
Results: Query executed in: 16 milliseconds.
-- Same Query with Hints added:
USE pubs
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = GETDATE()
SELECT titles.title, publishers.pub_name, pub_info.logo
FROM Titles WITH (NOLOCK) JOIN publishers WITH (NOLOCK) ON titles.pub_id = publishers.pub_id
JOIN pub_info WITH (NOLOCK) ON publishers.pub_id = pub_info.pub_id
ORDER BY titles.title
SELECT @EndTime = GETDATE()
PRINT 'Query executed in: ' + CONVERT( varchar, DATEDIFF( ms, @StartTime, @EndTime)) + ' milliseconds.'
GO
Results: Query executed in: 1 milliseconds.
======
This example returns only 18 rows... The larger the rowset you are returning and better your indexes are, the better performance you will have.
Hope this helps,
-Mike Gercevich
June 16, 2005 at 1:26 am
Sorry, i was a little short in my answer.
Blocked by -2 means it's blocked by an orphaned distributed transaction. Look it up in books online by performing a search on "spid -2". You kan only kill the transaction by knowing it's UOW.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply