Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to solve the sql server query blocking optimization performance problem". The editor shows you the operation process through the actual case, the operation method is simple and fast, and the practicality is strong. I hope this article "how to solve the sql server query statement blocking optimization performance problem" can help you solve the problem.

In a production environment, sometimes the company's customer service reports that the web page can not be reached for half a day, except after the browser presses the Network response of F12 to check to make sure that the web server is not malfunctioning. You need to check whether the database is blocked.

At that time, in the production environment of the database, the amount of data in the main table was more than 2000w, and the amount of data in the sub-table was more than 100 million, and it was updated and added frequently. Coupled with the synchronous mirroring, it consumes a lot of resources.

At this point, you need to create a new session, and you probably need to know the following:

1. What is the current number of active sessions?

two。 Session elapsed time?

3. Is there any blocking between sessions?

4. Blocking time?

There are many ways to query blocking. Sp_lock with sql 2000 and dmv with sql 2005 and above

one。 Blocking query sp_lock

Execute the following key fields under exec sp_lock

Spid refers to the process ID, which filters out the system process and only shows the user process spid > 50.

Dbid refers to which database under the current instance. Use the DB_NAME () function to identify the database.

The mode in which type requests locking

Request status of the mode lock

GRANT: lock acquired.

CNVRT: the lock is being converted from another mode, but the transformation is blocked by another process that holds the lock (the pattern conflicts).

WAIT: the lock is blocked by another process that holds the lock (mode conflict).

Summary: when mode is not in GRANT state, you need to know the mode of the current lock and find the current sql statement through the process ID

For example, when the current process ID is 416 and the mode status is WAIT, the viewing method is DBCC INPUTBUFFER (416)

Sp_lock queries show very little information, and it's hard to see who's blocked by whom. Therefore, it is not recommended when the database version is 2005 or above.

two。 Blocking query dm_tran_locksSELECT t1.resourceworthy type sys.dm_tran_locks as t1INNER JOIN sys.dm_os_waiting_tasks as t2ON t1.lock_owner_address t1.resourceinformationdatabaseclassiiddirection t1.resourcestaffassociatedaccounentityclassificiddirection t1.requestaccountmentmodered1.requestaccountsessionclassiiddirection t2.blockingaccountsessionclassiidFROMFROM = t2.resource_address

The above query only shows blocked sessions. Pay attention to blocking_session_id, that is, blocked session ID, and also use DBCC INPUTBUFFER to query sql statements

three。 Blocking queries sys.sysprocessesSELECT spid, kpid, blocked, waittime AS 'waitms', lastwaittype, DB_NAME (dbid) AS DB, waitresource, open_tran, hostname, [program_name], hostprocess,loginame, [status] FROM sys.sysprocesses WITH (NOLOCK) WHERE kpid > 0 AND [status]' sleeping' AND spid > 50 AND spid@@SPID

Sys.sysprocesses can show how many session processes, wait time, how many transactions open_tran has, and how many blocking sessions are. The overall content is more detailed.

Key field description:

Spid session ID (process ID), the number of a connection within SQL, generally less than 50

Kipid thread ID

Blocked: blocked process ID. A value greater than 0 indicates blocking, and a value of its own process ID indicates io operation

Waittime: the current wait time in milliseconds.

Open_tran: the number of open transactions of the process

Hostname: name of the client workstation that established the connection

The name of the program_name application.

Hostprocess workstation process ID number.

Loginame login name.

[status]

Running = session is running one or more batches

Background = session is running a background task, such as deadlock detection

Rollback = session has transaction rollback in progress

Pending = session is waiting for worker thread to become available

Runnable = tasks in the session are waiting in an executable queue run by scheduler. (important)

Spinloop = tasks in the session are waiting for the tuning lock to become available.

Suspended = the session is waiting for an event (such as Icano) to complete. (important)

Sleeping = connection idle

The wait resource format is fileid:pagenumber:rid such as (5 1purl 8235440)

Kpid=0, waittime=0 idle connection

Kpid > 0, waittime=0 running statu

Kpid > 0, waittime > 0 need to wait for a resource to continue execution, usually suspended (wait for io)

Kpid=0, waittime=0, but it is still the source of blocking, check that the open_tran > 0 transaction is not committed in time.

If blocked > 0, but the waittime time is very short, the blocking time is not long or serious.

If there are several runnable status tasks on status, you need to take them seriously. Cpu is overloaded and fails to process users' concurrent requests in time.

This is the end of the content on "how to solve the performance problem of sql server query blocking optimization". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.