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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy