Use an SQL statement that has been running for a long time in the mail, and send an SQL statement in the mail

Use an SQL statement that has been running for a long time in the mail, and send an SQL statement in the mail

ALTER proc [dbo].[usp_EmailLongRunningSQL]asbegindeclare@LongRunningQueries AS TABLE(  lrqId int IDENTITY(1,1) PRIMARY KEY,  spid int NULL,  batch_duration bigintNULL,  program_namenvarchar(500) NULL,  hostname nvarchar(100) NULL,  loginame nvarchar(100) NULL,  sqltext nvarchar(max) NULL)-- variabledeclaratuionsDECLARE @exectime DATETIMEDECLARE @tableHTMLNVARCHAR(MAX)DECLARE @Handle VARBINARY (85)DECLARE @SPID INTDECLARE @sqltext NVARCHAR(MAX)DECLARE @timeLimitsmallintdeclare @Sub as varchar(100)set @Sub = @@servername + 'Long Running  Query found'SET @timeLimit=(3*60) -- minutes -- WAITFOR DELAY'00:01:05' -- uncomment for testing (1min:5sec) -- populate thetable with execution info, you don't have to use top 1INSERT INTO @LongRunningQueries(spid, batch_duration, program_name, hostname, loginame) SELECT top 5    P.spid  , convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000  , P.program_name  , P.hostname  , P.loginameFROM master.dbo.sysprocesses P WITH(NOLOCK)WHERE (P.spid > 50)AND P.status NOT IN('background', 'sleeping')AND P.cmd NOT IN('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')AND convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000 >@timeLImit  -- use a cursorto update the sqltext for each pidDECLARE @lrqId intDECLARE mycur cursor for  SELECT lrqId from @LongRunningQueries  ORDER BY lrqId OPEN mycurFETCH NEXT FROM mycur INTO @lrqId WHILE @@FETCH_STATUS=0BEGIN   SET @SPID =(SELECT spid from @LongRunningQueries WHERElrqId=@lrqId)   -- get the SQL theSPID is executing  SELECT@Handle = sql_handleFROM master.dbo.sysprocesses WHEREspid = @SPID   UPDATE@LongRunningQueries  SET sqltext =(SELECT text FROM sys.dm_exec_sql_text(@Handle))  WHERE lrqId = @lrqId   FETCH NEXT FROM mycur INTO @lrqIdENDCLOSE mycurDEALLOCATE mycur DELETE FROM @LongRunningQueries WHEREsqltext IS NULLOR sqltext='' OR  program_nameLIKE '%DatabaseMail%' IF EXISTS(SELECT * FROM@LongRunningQueries WHERE sqltext IS NOT NULL OR sqltext<>'')BEGIN-- populate atable with it's info and mail itSET @tableHTML=  N'<H1>LongRunning  Querys</H1>' +  N'<tableborder="1">' +  N'<tr><th>SPID</th>'+  N'<th>Duration</th>'+  N'<th>Application</th>'+  N'<th>HostName</th>'+  N'<th>Login</th>'+  N'<th>SQLExecuting</th></tr>' +  CAST(( SELECT td = T.spid, '',  td = T.batch_duration, '',  td = T.[program_name], '',  td = T.hostname, '',  td = T.loginame, '',  td = T.sqltext, ''  FROM  @LongRunningQueries T  FOR XML PATH('tr'), TYPE  ) AS NVARCHAR(MAX) ) +  N'</table>' -- if @tableHTMLis NULL, mail will not get sentEXEC msdb.dbo.sp_send_dbmail,@recipients= 'smith.liu@126.com'@body = @tableHTML,@body_format = 'HTML';END end

How Does oracle get the SQL statement running time?

Run in sqlplus
SQL> set timing on -- display the execution time
SQL> set autelize on? C. display the execution plan
SQL> set autelize on? C. display the execution plan
SQL> set autotrace traceonly? C. Only display the execution plan, that is, not the queried data.
After the settings are complete, the execution SQL statement displays the execution plan information and corresponding statistics (you need to set this option)

How to calculate the SQL statement running time in SQL2000

But I still don't understand ~~~ Method 2: set statistics time displays the number of milliseconds required to analyze, compile, and execute each statement. Syntax set statistics time {ON | OFF}: When set statistics time is ON, the statement time statistics are displayed. If it is OFF, no time statistics are displayed. Set statistics time is SET during execution or running, rather than during analysis. Microsoft ?? SQL Server ?? Accurate statistics cannot be provided in fiber mode, while fiber mode is activated when the lightweight pooling configuration option is enabled. The cpu column in The sysprocesses table is updated only when you use set statistics time on to execute a query. When set statistics time is OFF, 0 is returned. The ON and OFF settings also affect the CPU columns in the SQL Server Enterprise Manager "active process information View.