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