使用邮件发送运行时间久的SQL语句，邮件发送s

使用邮件发送运行时间久的SQL语句，邮件发送sql

ALTER proc [dbo].[usp_EmailLongRunningSQL]
as
begin
declare@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
)
-- variabledeclaratuions
DECLARE @exectime DATETIME
DECLARE @tableHTMLNVARCHAR(MAX)
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
DECLARE @sqltext NVARCHAR(MAX)
DECLARE @timeLimitsmallint
declare @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 1
INSERT 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.loginame
FROM 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 pid
DECLARE @lrqId int
DECLARE mycur cursor for
  SELECT lrqId from @LongRunningQueries
  ORDER BY lrqId
 
OPEN mycur
FETCH NEXT FROM mycur INTO @lrqId
 
WHILE @@FETCH_STATUS=0
BEGIN
 
  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 @lrqId
END
CLOSE mycur
DEALLOCATE 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 it
SET @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 sent
EXEC msdb.dbo.sp_send_dbmail,
@recipients= 'smith.liu@126.com'
@body = @tableHTML,
@body_format = 'HTML';
END
 
end

在sqlplus中执行
SQL>set timing on --显示执行时间
SQL>set autorace on ?C显示执行计划
SQL>set autorace on ?C显示执行计划
SQL>set autotrace traceonly ?C只显示执行计划即不显示查询出来的数据
设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)
 

但我还是不懂~~~ 第二种方法：SET STATISTICS TIME显示分析、编译和执行各语句所需的毫秒数。语法SET STATISTICS TIME { ON | OFF }注释当SET STATISTICS TIME 为 ON 时，显示语句的时间统计。为 OFF 时，不显示时间统计。SET STATISTICS TIME 的设置是在执行或运行时设置，而不是在分析时设置。Microsoft?? SQL Server?? 不能在纤程模式下提供准确的统计，而纤程模式在启用 lightweight pooling 配置选项时激活。只有当使用 SET STATISTICS TIME ON 执行查询时才更新 sysprocesses 表中的 cpu 列。当 SET STATISTICS TIME 为 OFF 时，将返回 0。ON 和 OFF 设置也影响 SQL Server 企业管理器内的"当前活动的进程信息视图"中的 CPU 列。