Tricks to prevent excessive table locks

---

Script for listing current blocks

NOTE: Most blocks Ive seen in TW last only a split second, so this is only so useful

SELECT
  a.spid
 ,a.status
-- ,a.sid
 ,a.hostname
 ,a.program_name
 ,a.cmd
 ,a.cpu
 ,a.physical_io
 ,convert(sysname, rtrim(a.loginame))
        as Loginname
 ,a.blocked As Blocking_spid
 ,b.program_name AS Blocking_Program
-- ,a.dbid
-- ,a.spid as 'spid_sort'
-- ,  substring( convert(varchar,a.last_batch,111) ,6  ,5 ) + ' '
-- + substring( convert(varchar,a.last_batch,113) ,13 ,8 )
--       as 'Last_batch_char'
FROM 
	master.dbo.sysprocesses a (nolock),
	master.dbo.sysprocesses b (nolock)
	
WHERE a.blocked>0

---

***************************************************************************************
** Thoughs on integration of DB jobs on the TW server 
In a situation where you are doing some background processing inside a stored procedure 
and you want to minimize performance hits on the customer's database, 
Ive found the following tricks help:

1. Use table variables over temp tables 
See: http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
	Ex.
	---------------------------------------------------------------------------
	DECLARE @DocTable TABLE 
	(
  		DocumentId int
	)
	---------------------------------------------------------------------------

2. When doing alot of selects (especially into a temp table) use ROWCOUNT so it all doenst happen in
one long blocking time. Penalty = Takes alot longer to complete query
	Ex.
	---------------------------------------------------------------------------
	SET ROWCOUNT 100    -- Slower tables, like Document, should have low numbers
	LABEL_SELECT_MORE:
		INSERT INTO @DocTable (DocumentID)
		SELECT DocumentID
		FROM [Document] (NOLOCK)
		WHERE interfaceid = 6 
	IF @@ROWCOUNT > 0 GOTO LABEL_SELECT_MORE
	SET ROWCOUNT 0
	---------------------------------------------------------------------------

3. Space out your processing at least with a second or so in between.  Use WAITFOR. Like Rowcount, it makes the overall query take longer.

	Ex.
	---------------------------------------------------------------------------
	FETCH NEXT FROM HCP_DocTable_Cursor  -- Cursors are inherently slow so need alot of breathing room
	INTO 	@DocumentID 
	---------------------------------------------------------------------------
	WHILE @@FETCH_STATUS = 0
	BEGIN
		WAITFOR DELAY '00:00:01' -- Wait 1 sec
		EXEC HCP_RemoveCharsFromDocument @DocumentID
		FETCH NEXT FROM HCP_DocTable_Cursor INTO @DocumentID
	END
	---------------------------------------------------------------------------

4. When testing queries run the following query in a different window to see what is being blocked:
	Ex.
	---------------------------------------------------------------------------

	SELECT  a.spid, a.status, a.hostname, a.program_name, a.cmd, a.cpu
 		,a.physical_io, convert(sysname, rtrim(a.loginame))
		,a.blocked As Blocking_spid, b.program_name AS Blocking_Program
	FROM 
		master.dbo.sysprocesses a (nolock),
		master.dbo.sysprocesses b (nolock)
	WHERE a.blocked>0
	AND b.spid = a.blocked
	---------------------------------------------------------------------------

NOTE: A single query (Rowcount or waitfor) that blocks key tables even for 2 seconds will really annoy users. 
NOTE: Processes that are being WAITFOR DELAY'ed still consume a db process thread.