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.