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

---

Neat tricks to reduce lock escalation

NOTE: Its my understanding Sql server will escalate locks from Row, to Page, to Table if you lock things longer and longer at one time. The below examples show how to add breaks in normal operations to reduce the chance the locks escalate. I dont think its healthy for key tables to ever be locked on a TW server.

***************************************************************************************
** 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.