Sqlserver2008 deadlock resolution method and performance optimization method

About Deadlocks:

sp_who active– See which caused the deadlock, blk inside that is the blocked SPID;

DBCC INPUTBUFFER (@blk)-can see the deadlock caused by that SQL statement;

sp_lock– look at the lock of that resource, ObjID is the resource ID that has been locked;

Select object_name (ObjID) can get the affected table name;

Combining the above functions, we can write a stored procedure that automatically finds deadlocks, as follows:

Find deadlocks. sql

Set ANSI_NULLS on Go Set QUOTED_IDENTIFIER on go to CREATE PROCEDURE find_lock as BEGIN SET NOCOUNT on; declare @spid int, @blk int DECLARE cur CURSOR for select 0, blocked from (select * from sysprocesses where blocked>0) A Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b where a.blocked=spid) union select SPI d,blocked from sysprocesses where blocked>0 OPEN cur FETCH NEXT from cur to @spid, @blk while @ @FETCH_STATUS = 0 BEGIN If @spid =0 Select ' Causes the deadlock process number to be: ' + cast (@blk as VARCHAR (10) + '), which executes the SQL syntax as follows ' ELSE select ' Process number spid: ' + cast (@spid As varchar (10)) + ' by ' + ' process number SPID: ' + CAST (@blk as varchar (10) + ' block, whose current process executes the SQL syntax as follows ' DBCC InputBuffer (@blk) F ETCH NEXT from cur to @spid, @blk end CLOSE cur deallocate cur end GO

SQL Server Performance Optimizations

Go

exec sp_configure "awe enabled", "1″– memory can support 64G

exec sp_configure "lightweight pooling", "0″– not using NT fiber

EXEC sp_configure "priority boost", "1″– increase SQL Server precedence"

EXEC sp_configure "network packet size (b)", "8192″– increase the size of the SQL Server network packet

Reconfigure with override

– Optimize Database Settings

declare @currentdatabase sysname

Select @currentdatabase = db_name ((select dbid from master.dbo.sysprocesses wherespid = @ @spid))

exec sp_dboption @currentdatabase, ' select Into/bulkcopy ', ' true ' – Log not logged for bulk data operations

exec sp_dboption @currentdatabase, ' trunc. Log on chkpt. ', ' true ' – automatic truncation of logs

exec sp_dboption @currentdatabase, ' Auto create statistics ', ' true ' – Automatically create statistics

exec sp_dboption @currentdatabase, ' Auto update statistics ', ' true ' – Automatic Update

Go

If you do not do routine maintenance, please start immediately.

Often use DBCC SHOWCONTIG (table name) to view the fragmentation of the table, mainly look at the scan density, if the main large table scan density < 40%, means you have serious fragments can be used DBCC INDEXDEFRAG (database name, table name) to defragment or with DBCC DBREIN DEX (table name) rebuilds index.

Sqlserver2008 deadlock resolution method and performance optimization method