5. SQL Server database performance monitoring-current request

Source: Internet
Author: User

Original: 5. SQL Server database performance monitoring-current request

For systems running on-line, current database performance monitoring typically monitors the following points:

(1) Whether there is obstruction (Blocking);

(2) whether there is waiting (waiting), blocking is the lock (locks) waiting;

(3) Whether the running time is too long (long running);

(4) Whether there is a deadlock (Deadlock);

Sys.dm_exec_query_stats and so on, and so on some statistical information, usually not as the real-time alarm content, but in the performance optimization, as a reference.

a . Blocking / wait / Long Running time

1. SQL Server 2005 and later version check

SELECTr.session_id, r.blocking_session_id,db_name(r.database_id) asdatabase_name, R.start_time, R.total_elapsed_time, R.[Status]      , Case  whenr.blocking_session_id<> 0  Then 'Blocking'             whenr.blocking_session_id= 0  andR.wait_type is  not NULL  Then 'Waiting'            ELSE 'long-running'       END  asSlowness_type, R.percent_complete, R.command, R.wait_type, R.wait_time, R.wait_resource , R.last_wait_type, R.cpu_time, R.reads, r.writes, R.logical_reads, T.[text]  asExecuting_batch,SUBSTRING(T.[text], R.statement_start_offset/2,                 ( Case  whenR.statement_end_offset= -1                         Then datalength(T.[text])--LEN (CONVERT (NVARCHAR (MAX), T.text)) * 2                  ELSER.statement_end_offsetEND -R.statement_start_offset)/2 + 1) asExecuting_sql, Bt.[text]  asBlocking_batch,SUBSTRING(BT.[text], Br.statement_start_offset/2,                 ( Case  whenBr.statement_end_offset= -1                         Then datalength(BT.[text])--LEN (CONVERT (NVARCHAR (MAX), Bt.text)) * 2                  ELSEBr.statement_end_offsetEND -Br.statement_start_offset)/2 + 1) asBlocking_sql--, P.query_plan   fromsys.dm_exec_requests R CrossAPPLY sys.dm_exec_sql_text (R.sql_handle) asT CrossAPPLY sys.dm_exec_query_plan (R.plan_handle) asP Left JOINsys.dm_exec_requests BR onr.blocking_session_id=br.session_idOUTERAPPLY sys.dm_exec_sql_text (br.session_id) asBTWHEREr.session_id>  -  andr.session_id<> @ @SPID    andR.total_elapsed_time>  - *  - *  + ORDER  byR.total_elapsed_timeDESC;

The above script returns statements that run for more than 30 minutes, and it is important to note that:

(1) If you return to the execution plan, it will make the above script much slower , can not return, after receiving the alarm to check the statement, then go to view the execution plan;

(2) display text, such as: xp_cmdshell such statements, Start_offset, End_offset are 0, the text is blank, only look at the text to know what the statement There is sometimes a need to know which batch or stored procedure the request came from;

(3) Sometimes it is not enough to display text , also take xp_cmdshell as an example, you need DBCC INPUTBUFFER to see the full SQL statement, but also run the end but have not commit/rollback transactions, No longer in requests, you also need to borrow DBCC INPUTBUFFER to view SQL statements;

DBCC inputbuffer (@ @SPID)

(4) The SQL agent job, which will be checked together here , can also be done through msdb. Sysjobactivity to be inspected separately;

Select *    from  Innerjoin  msdb.dbo.sysjobs b    on =  where  like ' %backup% '

2. methods used by SQL Server

SelectP.dbid, P.spid, p.blocked, P.waittime/1000.0/60.0  asWait_minutes,ISNULL(DATEDIFF(MI, P.last_batch,GETDATE()),0) elapsed_minutes, P.last_batch, P.status, P.program_name, (Select [text]  from:: Fn_get_sql (P.sql_handle)) Sql_text fromMaster.. sysprocesses Pwherespid>  -  andspid<> @ @SPID    and(Status<> 'Sleeping'  and ISNULL(DATEDIFF(MI, P.last_batch,GETDATE()),0)>  -)

The above script returns statements that run for more than 30 minutes, and it is important to note that:

sysprocesses the connection/session/request information in one, where there is no specific time for the start of the request, through Last_batch monitoring the running time is not accurate. The test is as follows:

(1) Connect to SQL Server through isql, if the current connection has not initiated any request, Last_batch time is 1900-01-01 00:00:00, when the request is initiated on this connection, through Last_ Batch calculates that the current request runs at an inaccurate length;

(2) in SQL analyzer/ssms new Query window , when no query is initiated, Last_batch is the same as login_time, not 1900-01-01 00:00:00, by Last_ Batch calculates the current request run time is inaccurate, or the current window initiated request has ended, but the window/connection is not closed, the request is initiated again on this connection, Last_batch is the time of the last request end, and the current request runs with Last_batch to calculate the length is not accurate;

(3) After the SQL agent job runs , the connection to sysprocesses is closed, the connection is re-established the next time it is run, and Last_batch equals login_time in the new connection, through Last_ Batch calculation job run time is accurate;

As an old method, estimate and no longer go into the drill, but with sysprocesses to monitor blocking/waiting or no problem, the runtime of the job can also be monitored, the script changes as follows:

SelectP.dbid, P.spid, p.blocked, P.waittime/1000.0/60.0  asWait_minutes,ISNULL(DATEDIFF(MI, P.last_batch,GETDATE()),0) elapsed_minutes, P.last_batch, P.status, P.program_name, (Select [text]  from:: Fn_get_sql (P.sql_handle)) Sql_text fromMaster.. sysprocesses Pwherespid>  -  andspid<> @ @SPID    and((P.program_name like 'sqlagent-tsql JobStep (Job%'  and ISNULL(DATEDIFF(MI, P.last_batch,GETDATE()),0)>  -)       or(p.blocked<> 0  andP.waittime/1000.0/60.0 >  -)       )

This leaves only the non-blocking but long-running SQL requests not being monitored. If you must be fully monitored, you can choose to turn on tracing and then analyze the trace file.

two . dead Lock

Deadlock monitoring can be done by monitoring SQL Server errorlog, but you need to turn on the deadlock trace flag in advance. The script is as follows:

-- SQL Server DBCC traceon (1204,-1)--SQL Server 2005 +DBCC Traceon (1222,-1)

When a deadlock occurs, the deadlock details are written to the errorlog, and the deadlock or victim keywords are checked for monitoring.

Summary

The runtime/baseline of each statement is not the same, usually not set a uniform threshold, sometimes borrowing third-party tools for different requests to set different time-length thresholds and alarms, so in the database this layer most alarm blocking can, the approximate steps are as follows:

(1) Deploy Database Mail;

(2) Deployment job: Timed check blocking, send email alerts.

5. SQL Server database performance monitoring-current request

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.