SQL Server Performance Analysis-deadlock detection database blocking statements

SQL Server Performance Analysis-deadlock detection database blocking statements

Work in the database often appear in memory, found an article

Refer to Csdn, China Wind (Roy) A deadlock article block: One of the transactions is blocked, other transactions wait for the other party to release their locks, and can cause deadlock problems.

People: China Wind (Roy) refer to Roy_88 's blog

Http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx

Date: 2008.07.20 ************************************************************************************************* ***********************/

Generate a Test table --Generate Test table TA if not object_id ("ta") is null drop table ta go create table ta (id int Primary key,col1 int,col2 nvarchar ()) I Nsert Ta Select 1,101, "A" union ALL Select 2,102, "B" union ALL Select 3,103, "C" go

To generate Data:

/* Table Ta ID Col1 Col2--------------------------------1 101 A 2 102 B 3 103 C (3 rows affected) */

1, reduce processing congestion to a minimum: 2, do not request user input in the transaction 3, read the data in the row version Management 4, try to access the smallest amount of data in the transaction 5, as far as possible to use the low transaction isolation level to block 1 (transaction):

Transaction blocking --Test single table-----------------------Connection window 1 (update\insert\delete)------------------------------begin TRAN--update update Ta set col2= "BB" where id=2--or insert--begin tran--Insert TA values (4,104, "D")--or delete--begin Tran--delete Ta wher E id=1--rollback tran-------------------------Connection window 2 (enquiry form)---------------------------------------------BEGIN Tran select * from TA--rollback tran-----analysis---------------------------------------------------->sql SERVER 2005 query deadlock process s Elect request_session_id as SPID, Resource_type, db_name (resource_database_id) as DbName, Resource_description, RESOURCE_ASSOCIATED_ENTITY_ID, Request_mode as mode, request_status as status from sys.dm_tran_locks--result:/* Process ID resource class Type Database resource Description resource close chain ID lock type process status----------------------------------------------------------------------------------------- -Gepro 0 S Grant Gepro database Gepro 0 S Grant database Gepro 0 S Grant PAGE Gepro 1:1904 72057594039435264 is GRANT PAGE GePRO 1:1904 72057594039435264 IX GRANT object               Gepro 853578079 is Grant, OBJECT Gepro 853578079 IX GRANT, KEY gepro (020068e8b274) 72057594039435264&nbs p;    x      GRANT Gepro (020068e8b274) 72057594039435264 S       WAIT (9 rows affected) */

-->sql SERVER 2000 Query deadlock process

Code Select DISTINCT "Process id" = str (a.spid, 4), "Process id status" = CONVERT (CHAR), a.status), "deadlock process id" = str (a.blocked, 2), "Workstation name" = Convert (char (), a.hostname), "user executing command" = Convert (char), suser_name (A.uid)), "database name" = CONVERT (char), db_name (A. dbid)), "Application Name" = Convert (char), a.program_name), "executing command" = Convert (char (+), a.cmd), "Login" = a.loginame, "EXECUTE statement" = B.text from Master. sysprocesses a cross APPLY sys.dm_exec_sql_text (a.sql_handle) b WHERE a.blocked in (SELECT blocked from master: sysprocesses)--and blocked <> 0 ORDER by STR (spid, 4)--result/* Process id  process id   status   deadlock process Id&nbs P The workstation name executes the command with the user database name of the application name that is executing the command login execution statement------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------sleeping 0 DC91229126 sa Gepro Microsoft awaiting COMMAND dc91229126ff442\administrator SET STATISTICS XML OFF sleeping 0 DC912291 SA Gepro MicRosoft awaiting COMMAND dc91229126ff442\administrator SET STATISTICS XML OFF suspended DC91229126 sa Gepro Microsoft Select Dc91229126ff442\administrator BEGIN TRAN Select * from TA runnable 0 DC91229126 sa gepro Microsoft Select DC912 29126ff442\administrator SELECT DISTINCT sleeping 0 DC91229126 sa Gepro Toad for S awaiting COMMAND Dc91229126ff442\adm Inistrator SET fmtonly OFF; Sleeping 0 DC91229126 sa Gepro Toad for S awaiting COMMAND dc91229126ff442\administrator/--Check connection information (spid:57, Sat) Select Connect_time,last_read,last_write,most_recent_sql_handle from Sys.dm_exec_connections where session_id in (57,58)-- Viewing session information Select Login_time,host_name,program_name,login_name,last_request_start_time,last_request_end_time from Sys.dm_exec_sessions where session_id in (57,58)--view blocking requests being executed select Session_id,blocking_session_id,wait_type,wait_ Time,wait_resource from sys.dm_exec_requests where blocking_session_id>0--is blocking the requested session ID. If this column is NULL, the request/* is not blocked session_id,blocking_session_id, Wait_type,wait_time,wait_resource lck_m_s 2116437 key:6:72057594039435264 (020068e8b274)/--View the SQL statement being executed Select A.session_id,sql.text,a.most_recent_sql_handle from Sys.dm_exec_connections a cross apply Sys.dm_exec_sql_text (a.most_recent_sql_handle) as SQL--can also use function fn_get_sql to get execution statements through Most_recent_sql_handle where a.session_id in (57,58)/* session_id text----------------------------------------------------------STATISTICS SET XML OFF Tran Select * FROM TA */

Treatment method: Law One:

--Connection Window 2 BEGIN TRAN select * from TA with (nolock)-nolock: Business data is constantly changing, as is available when viewing the current month.

Law II: Blocking 2 (Index): Processing method: Index

Code Create INDEX ix_ta_col1 on Ta (Col1)--With the COL1 column on the index, when the update condition: COL1=102 will use the index ix_ta_col1 to get an exclusive range of key lock------------------------ ----Connection Window 1-------------------------------------------------set TRANSACTION isolation Level SERIALIZABLE-set for session TRANSACTION Isolation Level--serializable Phantom Read, non-repeatable read, and dirty read are not allowed to begin TRAN update TA set col2= "BB" where col1=102--rollback t Ran-----------------------------Connection window 2------------------------------------------------begin TRAN Select * FROM TA

Law three: Set the current query isolation level

-----------------------------Connection Window 2------------------------------------------------SET TRANSACTION Isolation Level Read COMMITTED---set session committed reads: Specifies that the statement cannot read data that has been modified by another transaction but has not yet been submitted begin TRAN select * FROM TA

1, the business should be as short as possible

Viewing deadlock victims --View deadlock victim SELECT "process id[spid]" = str (a.spid, 4), "process state" = CONVERT (CHAR (Ten), A.status), "chunked process id" = str (a.blocked, 2) , "server Name" = Convert (char), A.hostname, "execute User" = Convert (char (), Suser_name (A.uid)), "database name" = Convert (char (10 ), Db_name (a.dbid)), "Application Name" = Convert (char (ten), a.program_name), "executing command" = Convert (char (+), a.cmd), "Cumulative CPU Time" = STR (A.CPU, 7), "IO" = str (a.physical_io, 7), "login name" = A.loginame, "Execute SQL" = B.text from Master: sysprocesses a cross APPLY sys.dm_exec_sql_text (a.sql_handle) b WHERE blocked <> 0 ORDER by spid

View Process Health --View Process Health Select "Process id" = str (spid, 4), "Process id status" = CONVERT (CHAR (Ten), status), "chunked process id" = str (blocked, 2 ), "workstation Name" = Convert (char (ten), hostname), "execute User" = Convert (char (ten), Suser_name (UID)), "database name" = Conve       RT (char), db_name (dbid)), "Application Name" = Convert (char), program_name), "executing command" = Convert (char (+), cmd) , "Cumulative CPU time" = str (CPU, 7), "IO" = str (physical_io, 7), "login name" = Loginame from Master. sysprocesses--where blocked = 0 ORDER by spid
--blocked = 0 Indicates a process ID that is not blocked;

Query lock type --Query lock type Select Process Id=a.req_spid, Database =db_name (rsc_dbid), type =case rsc_type when 1 then "NULL resource (not used)" When 2 Then "database" When 3 then "file" when 4 then "index" when 5 then "table" when 6 then "page" when 7 then "key" when 8 then "extents" when 9 Then "RID (Row ID)" When Ten then "Application" end, Object Id=rsc_objid, object name =b.obj_name, Rsc_indid from master. Syslockinfo a LEFT join #t B on a.req_spid=b.req_spid

View SQL executed by SA user ----View the SQL Select "Process id[spid]" = str (a.spid, 4) executed by the SA user, "process state" = CONVERT (CHAR (Ten), A.status), "chunked process id" = str (a.block Ed, 2), "server name" = Convert (char (), a.hostname), "execute User" = Convert (char (ten), Suser_name (A.uid)), "database name" = CONVERT ( Char (Ten), Db_name (a.dbid)), "Application Name" = Convert (char (ten), a.program_name), "executing command" = Convert (char (+), a.cmd), "tired Count CPU Time "= str (A.CPU, 7)," IO "= str (a.physical_io, 7)," login name "= A.loginame," Execute SQL "= B.text from Master: sysprocesses a cross APPLY sys.dm_exec_sql_text (a.sql_handle) b WHERE blocked <> 0 OR a.loginame= ' sa ' ORDER by spid