SQL SERVER query performance optimization -- Analysis of transaction and lock (t


The SQL SERVER query performance optimization of — — analysis of transaction and lock (a)


The second step, the SQL statement locks

Because the SQL SERVER 2008 as compared to SQL SERVER 2005 “ activity monitor ” has a bigger change, So we use SQL statements to analysis, Use the SQL statement analysis through SP_WHO, SP_WHO2, SP_LOCK and other system stored procedures, Master.sys.sysprocesses system view, Or from SQL 2005 (2008) dynamic view management provides (DMV) sys.dm_exec_session, sys.dm_tran_locks and other related information.

Find out the first lock resources through the master.sys.sysprocesses view and the cause behind a series of process was forced to stop waiting.

Here we give an example, the following code can be used in SQL SERVER 2005/2008:

--1 create table test

CREATE TABLE [dbo].[Book](

[bookid] [int] NOT NULL,

[Name] [varchar](60) NULL,

[category] [varchar](10) NULL,

[numberofcopies] [int] NULL,

[AuthorID] [int] NULL,

 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 

(

[bookid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (1, N'SQL 2008', N'MS', 4, 1)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (2, N'SharePoint 2007', N'MS', 3, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (3, N'SharePoint 2010', N'MS', 5, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (5, N'DB2', N'IBM', 10, 3)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (7, N'SQL 2012', N'MS', 7, 1)

 

--2 test examples

--List the first lock resource, causing a series of other processes are source of origin. 

An example of a

if exists(select * from master.sys.sysprocesses where spid in (select blocked from master.sys.sysprocesses))

begin

--Determining the process by other resources.

select The SPID process, STATUS state, the login account=SUBSTRING(SUSER_SNAME(sid),1,30)

,The user name of the machine=SUBSTRING(hostname,1,12)

,Whether it is locked=convert(char(3),blocked)

,The database name=SUBSTRING(db_name(dbid),1,20),The CMD command,waittype as Wait type

,Last_batch the last batch processing time, the number of open_tran uncommitted transactions

from master.sys.sysprocesses

--List the lock others (in other process blocked appear in the field values) but not locked(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

end

else 

begin

select 'No locked process'

end 

 

--The next us through the following code to construct a process is also a process of lock. 

--In two cases

Use test

Go

Begin tran

update book set Name='MS SQL 2008'

where bookid=1

--Switching to another query interface, execute the following code

--In three cases

Use test

Go

select * from Book where bookid=1

go

Open a new query interface in SQL 3 SERVER 2005/2008 SQL Server Management Microsoft Studio, exec SP_LOCK. Results as shown below.

Figure 1

From Figure 1 can be observed in the interaction of the two processes, the process “ 53” &ldquo exclusive mode; ” (X), has been obtained to allow “ GRANT” “ process; 56” requirement model for “ sharing (S) &rdquo (WAIT) processing waiting;.

As shown in Figure 1 in the process of “ 56” (SELECT statement execution query) is the process “ 53” (UPDATE statement execution query) blockade phenomenon, And data from the Book table lock can be seen as “ exclusive &rdquo lock; one of the index key values (for type KEY), To process “ 56” placed shared locks (requirements model“S”), While waiting for the state (state WAIT).

Open a new query interface in SQL 4 SERVER 2005/2008 SQL Server Management Microsoft Studio, by another connection to perform code (a), the execution results as below.

Figure 2

In the above 2 see example two query code to open after the transaction, not close the transaction, so the state (status) for sleeping, but not by other processes (BLK), so the lock is locked by the “ ” column data is 0, no orders, no waiting for a resource. As the query a database connection is Test, so the database name is Test.

5 query sysprocesses system view rendering problem trading phenomenon may have many kinds, but one of the most common is the status field is equal to sleeping, the waittype field is equal to 0x0000, the last_batch field has a distance from the last batch execution time, and the open_tran field is greater than 0. For example, direct code execution in two cases, then the transaction is already open, but has not been completed, may be the program did not do a good job in business management.

Open the following statement is executed a new query interface in SERVER 2005/2008 SQL SQL Server Management Microsoft Studio, connected to the query in question

select The SPID process, STATUS state, the login account=SUBSTRING(SUSER_SNAME(sid),1,30)

,The user name of the machine=SUBSTRING(hostname,1,12)

,Whether it is locked=convert(char(3),blocked)

,The database name=SUBSTRING(db_name(dbid),1,20),The CMD command,waittype as Wait type

,Last_batch the last batch processing time, the number of open_tran uncommitted transactions

from master.sys.sysprocesses

Where  status='sleeping' and waittype=0x0000 and open_tran>0

The following diagram.

Figure 3

The status field is equal to the sleeping said no indicator is executed, waittype field is equal to the 0x0000 represents the connection without waiting for any resource, the last_batch field represents the last SQL statement execution time, if this time there is a period of time from now, and the open_tran word segment is greater than 0, is a problem. A period of time in the past, without waiting for any resource, also not executing any SQL statement, so why open transaction?

In addition to the above query sysprocesses view of the system, SQL SERVER 2005/2008 can “ sys.dm_tran_locks dynamic management view ” present the use of related locking information. Each record represents a return authorization or authorization pending lock. In the data rows in the result set, mainly divided into “ ” “ &rdquo request; two, the field respectively by resource and request as the prefix. Resource group description is locked or waiting for resources, and requests the group described has acquired or lock waiting request.

--In four cases

select t1.resource_type [Resource locking type],DB_NAME(resource_database_id) as The database name

,T1.resource_associated_entity_id lock object,t1.request_mode as Wait for lock pattern request

,T1.request_session_id for SID

,T2.wait_duration_ms wait time

,(select TEXT from sys.dm_exec_requests r cross apply

sys.dm_exec_sql_text(r.sql_handle) where r.session_id=t1.request_session_id) as Waiting to execute SQL

,(select SUBSTRING(qt.text,r.statement_start_offset/2+1,

(case when r.statement_end_offset=-1 then DATALENGTH(qt.text) else r.statement_end_offset end -r.statement_start_offset)/2+1

)

from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle)qt

where r.session_id=t1.request_session_id) Waiting was executed statement

,t2.blocking_session_id [Locking SID]

,(select TEXT from sys.sysprocesses p cross apply

sys.dm_exec_sql_text(p.sql_handle)

where p.spid=t2.blocking_session_id

) Lock the execute statement

from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2

where t1.lock_owner_address=t2.resource_address

In the Sql 2005 TEST executes the code above, the results as below. (this is according to the Wbk_pde_list data sheet)

Figure 4

Implementation of the above code in Sql 2008 on the TEST database, the results as below. (this is according to the BOOK data sheet)

Figure 5

Remarks:

The second step is to query and analysis the lock by executing code in the Microsoft SQL Server Management Studio, and in the SQL SERVER 2005 is available through the Microsoft SQL Server Management Studio management gets quite a lot of information, convenient and allows you to decide at present we should take what kind of steps.

And the second step in the acquisition of information can be in the SQL SERVER 2005 SQL Server Management by Microsoft Studio “ &rdquo acquisition activity monitor;.

For example by “ the activity monitor: "according to the lock &rdquo object classification;, corresponding object selection drop-down menu. (the following chart)

Posted by Lawrence at November 22, 2013 - 11:04 PM