SQL Server Performance Analysis

--Blocking/**********************************************************************************************************     Blocking: One of the transactions is blocked and other transactions are waiting for the other party to release their locks, which can cause deadlock problems. Finishing Man: China Wind (Roy) Date: 2008.07.20 *************************************************************************************  /--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 (ten)) Insert Ta Select 1,101, ' A ' union ALL Select 2,102, ' B ' UNION ALL           Select 3,103, ' C ' go generate data:/* table Ta ID Col1 Col2--------------------------------1 101 A 2 102 B 3 103 C (3 rows affected) */minimizes processing congestion: 1, transaction should be as short as 2, do not Request user input 3 in a transaction, use row version management 4 for read data, try to access the smallest amount of data in a transaction 5, use the low transaction isolation level as much as possible go block 1 (transaction):--Test a 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 whe Re id=1--rollback tran------------------------------------------Connection window 2--------------------------------begin TR A select * from TA--rollback tran--------------analysis-----------------------Select 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/* s PID resource_type dbName resource_description resource_associated_entity_id mode Status-----------------------                    -------------------------------------------------------------------DATABASE Test 0                             S GRANT NULL si DATABASE Test 0 S GRANT NULL 53 DATABASE Test 0 S GRANT NULL-PAGE T                EST 1:201 72057594040483840 is GRANT si PAGE Test 1:201                            72057594040483840 IX GRANT OBJECT Test 1774629365 is          Grant NULL, OBJECT Test 1774629365 IX GRANT NULL 54 KEY Test (020068e8b274) 72057594040483840 X GRANT--(SPID:54 request exclusive lock)     EY Test (020068e8b274) 72057594040483840 S wait-(spid:55 shared lock + wait state) (9 rows affected) */ --Check connection information (spid:54, Connect_time,last_read,last_write,most_recent_sql_handle) Select from Sys.dm_exec_connections w Here session_id in (54,55)--View 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 (54,55)--view blocking requests being executed select Session_id,blocking_session_id,wait_typ E,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--view the SQL statement being executed select A.session_id,sql.text,a.most_recent_sql_handle from SYS.D M_exec_connections a cross apply sys.dm_exec_sql_text (A.most_recent_sql_handle) as SQL--also available function fn_get_sql via Most_r Ecent_sql_handle gets the EXECUTE statement where a.session_id in (54,55)//session_id text------------------------------------ ----------------------si begin tran Update ta set col2= ' BB ' where id=2 a BEGIN TRAN SELECT * F             Rom TA/* Processing method:--Connection Window 2 BEGIN TRAN select * from TA with (nolock)-nolock: Business data is constantly changing, such as sales when viewing the current month is available. Block 2 (Index):-----------------------Connection window 1 set TRANSACTION isolation Level SERIALIZABLE--set TRANSACTION Isol for session ATION level begin tran UpDate ta set col2= ' BB ' where col1=102--rollback tran------------------------Connection window 2 insert into TA (id,col1,co L2) VALUES (5,105, ' E ') Processing method: 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 a Exclusive range Lock blocking 3 (Session settings):-------------------------------Connection window 1 begin Tran--update update TA set col2= ' B B ' where id=2 select col2 from Ta where id=2--rollback tran--------------------------------Connection window 2 SET T          Ransaction Isolation LEVEL Read COMMITTED--set Session Read Committed: 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 How to:--------------------------------Connect Windows 2 (use session settings: Business data is constantly changing, as is available when sales view the month) Set TRANSACTION isolation level read uncom Mitted--SET SESSION UNCOMMITTED read: Specifies that a statement can read a row that has been modified by another transaction but has not yet been submitted begin TRAN select * FROM Ta <pre></pre>

[Go from]:http://blog.csdn.net/roy_88/article/details/2682044
Two, deadlock detection database blocking statement

--View deadlock condition SELECTDISTINCT ' process ID ' =str (a.spid, 4), ' Process ID State ' =convert (CHAR (Ten), a.status), ' Deadlock process ID ' =str (a.blocked, 2), ' Workstation name ' =convert (char (Ten), a.hostname), ' User executing command ' =convert (char (), Suser_name (A.uid)), ' Database name ' =convert (char), db_ Name (a.dbid)), ' Application name ' =convert (char (Ten), a.program_name), ' Executing command ' =convert (char (+), a.cmd), ' login name ' = A.loginame, ' Execute statement ' = B.textfrom master. sysprocesses a cross applysys.dm_exec_sql_text (A.sql_handle) bwhere a.blocked in (SELECT blockedfrom master: sysprocesses)--and blocked <> 0ORDERBYSTR (spid, 4)--Check connection information (spid:57, +) Select Connect_time,last_read,last_ Write,most_recent_sql_handle from Sys.dm_exec_connections where session_id in (57,58)--View 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 executing requests 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--also available function fn_get_sql via Most_ Recent_sql_handle get EXECUTE statement where a.session_id in (57,58)--Query lock type Select Process Id=a.req_spid, Database =db_name (rsc_dbid), type =case RSC _type when1then ' NULL resource (not used) ' When2then ' database ' when3then ' file ' when4then ' index ' when5then ' table ' when6then ' page ' when7then ' key ' whe N8then ' extents ' when9then ' RID (row ID) ' When10then ' Application ' End, Object Id=rsc_objid, object name =b.obj_name, Rsc_indidfrom master: Syslockinfo a leftjoin #t b on a.req_spid=b.req_spid----View SA user executed sqlselect ' process id[spid] ' =str (A.spid, 4), ' Process state ' =convert (char (), a.status), ' chunked process id ' =str (a.blocked, 2), ' Server name ' =convert (char (Ten), a.hostname), ' Execute user ' =convert (char), SU Ser_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), ' Cumulative CPU Time ' =str (A.CPU, 7), ' IO ' =str (A.P Hysical_io, 7), ' login name ' = a.loginame, ' Execute SQL ' = B.textfrom master. sysprocesses a cross APPLY sys.dm_exec_sql_text (a.sql_handle) bwhere blocked <>0or a.loginame= ' sa ' for SPID master To manage views dynamically: Sys.sysprocesses (compatible SQL2K) sys.dm_exec_connectionssys.dm_exec_sessionssys.dm_exec_requests Source:/http Www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html

SQL Server Performance Analysis