Difference between revisions of "Tricks to prevent excessive table locks"
Jump to navigation
Jump to search
Erobertson (talk | contribs) |
Erobertson (talk | contribs) |
||
Line 66: | Line 66: | ||
--------------------------------------------------------------------------- | --------------------------------------------------------------------------- | ||
− | 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. | + | 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. | Ex. |
Latest revision as of 16:48, 11 July 2007
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.