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

When the SQL Server engine receives the query request issued by the user, the SQL Server execution optimizer binds the query request (Request) with the Task and assigns a Workder,SQL Server application operating system process (Thread) to the Task to execute the Worker. If you execute Request,SQL Server in parallel based on the Max DOP (Maximum Degree Of Parallelism) configuration option to create a new Child Tasks,SQL Server to bind Request to multiple Task; for example, if Max DOP=8, there will be one Master Task and eight Child Tasks. Each Task is bound to a Worker, and the SQL Server engine allocates the appropriate number of Worker to execute the Tasks.

First, check the Request that is being executed

Use sys.dm_exec_requests to return the query script, blocking, and resource consumption associated with the query request (Request) being executed.

1. View the query statement being executed by SQL Server

Sql_handle,statement_start_offset,statement_end_offset, which can be used to view the query statement being executed

Field plan_handle, used to view the execution plan of the query statement

The field command is used to indicate the current type of Command being processed: SELECT,INSERT,UPDATE,DELETE,BACKUP LOG, BACKUP DATABASE,DBCC,FOR

2. Check the Block statements.

Field wait_type: if Request is being blocked, field wait_type returns the current Wait Type

Field last_wait_type: the Wait Type that was last blocked

Field wait_resource: the currently blocked Request is waiting for the resource

Field blocking_session_id: the Session that blocks the current Request

3. Memory and IO,CPU consumption statistics

Field granted_query_memory: size of memory granted, Number of pages allocated to the execution of a query on the request

Field cpu_time,total_elapsed_time: CPU time consumed and total time consumed

Field reads,writes,logical_reads: the number of physical Read, logical Write, and logical Read

Second, view the SQL query statement currently being executed by SQL Server

When troubleshooting, use DMV:sys.dm_exec_requests to view the query statement that SQL Server is currently executing:

Select r.session_id, r.blocking_session_id as blocking, r.wait_type as current_wait_type, r.wait_resource, r.last_wait_type, r.wait_time/1000 as wait_s, r.status, r.command, r.cpu_time,r.reads,r.writes,r.logical_reads, r.total_elapsed_time Substring (st.text, r.statement_start_offset/2+1,) (case when r.statement_end_offset =-1 then len (convert (nvarchar (max)) St.text)) else (r.statement_end_offset-r.statement_start_offset) / 2 end)) as individual_query -, db_name (r.database_id) as dbname,r.percent_complete,r.estimated_completion_time R.granted_query_memoryfrom sys.dm_exec_requests router APPLY sys.dm_exec_sql_text (r.sql_handle) as stwhere ((r.waitworthy typewriter MISCELLANEUS' and r.waitworthy typepe` DISPATCHER_QUEUE_SEMAPHORE') or r.wait_type is null) and r.session_id > 50 and r.session_id@@spidorder by r.session_id asc

1. When troubleshooting, you can filter out some useless wait type and current Session:

@ @ SPID indicates the current spid. Generally speaking, SPID50 is User Session.

When WaitType is' MISCELLANEOUS', it is not used to identify any valid Wait, only as the default Wait

When WaitType is' DISPATCHER_QUEUE_SEMAPHORE', the current Thread is waiting for more Work to be processed. If Wait Time increases, the Thread scheduler (Dispatcher) is very idle.

For more information about WaitType, see The SQL Server WaitType Repository

2. View the SQL query statement executed by request

The sql_handle field represents the handle of the current query statement. Pass this field to the sys.dm_exec_sql_text function to get the SQL statement executed by Request. SQL Server automatically parameterizes some query statements containing constants ("Auto-parameterized"). The format of the obtained SQL query statement is as follows. SQL Server adds a parameter declaration at the beginning of the query statement:

(@ P1 int,@P2 int,@P3 datetime2 (7), @ P4 datetime2 (7)) WITH CategoryIDs AS (SELECT B.CategoryID,.

Two fields, stmt_start and stmt_end, are used to identify the beginning and end of the parameter declaration, which are used to split the parameter declaration and return the query statement executed by SQL Server.

3, blocking

Field blocking_session_id: blocks the Session of the current Request, but excludes the four ID values of 0mai 2mai 3mai 4:

If this column is 0, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

Third, view the active Task in the SQL Server instance

Use DMV:sys.dm_os_tasks to view the active Task in the current instance

1. Field task_state, which identifies the status of the Task

PENDING: Waiting for a worker thread.

RUNNABLE: Runnable, but waiting to receive a quantum.

RUNNING: Currently running on the scheduler.

SUSPENDED: Has a worker, but is waiting for an event.

DONE: Completed.

SPINLOOP: Stuck in a spinlock.

2, pending IO (Pending)

Pending_io_count

Pending_io_byte_count

Pending_io_byte_average

3, associated Request and Worker (associated)

Request_id: ID of the request of the task.

Worker_address: Memory address of the worker that is running the task. NULL = Task is either waiting for a worker to be able to run, or the task has just finished running.

4, Task Hierarchy

Task_address: Memory address of the object.

Parent_task_address: Memory address of the task that is the parent of the object.

5. Monitor concurrent Request (Monitoring parallel requests)

For requests that are executed in parallel, you will see multiple rows for the same combination of (,).

SELECT session_id, request_id, task_state, pending_io_count, pending_io_byte_count, pending_io_byte_average, scheduler_id, context_switches_count, task_address, worker_address, parent_task_addressFROM sys.dm_os_tasksORDER BY session_id, request_id

Or use Task Hierarchy to query

Select tp.session_id, tp.task_state as ParentTaskState, tc.task_state as ChildTaskStatefrom sys.dm_os_tasks tpinner join sys.dm_os_tasks tc on tp.task_address=tc.parent_task_address

Fourth, wait for the Task (waiting) of the resource

Use DMV:sys.dm_os_waiting_tasks to view the Task of waiting resources in the system

Waiting_task_address: Task that is waiting for this resouce.

Blocking_task_address: Task that is currently holding this resource

Resource_description: Description of the resource that is being consumed. Refer to sys.dm_os_waiting_tasks (Transact-SQL)

When troubleshooting congestion, look at the Block and contended resources:

Select wt.waiting_task_address, wt.session_id,-- Wait and Resource wt.wait_duration_ms, wt.wait_type, wt.resource_address, wt.resource_description, wt.blocking_task_address, wt.blocking_session_idfrom sys.dm_os_waiting_tasks wt

Fifth, use dbcc inputbuffer (spid) to get the last SQL statement executed by spid

Dbcc inputbuffer (spid)

Appendix:

Referring to "How to isolate the current running commands in SQL Server", this article describes how to separate the query statements executed by Request:

View Code

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.