34,591
社区成员
发帖
与我相关
我的任务
分享
SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId) AS procName,
DB_NAME(qt.dbId) AS [db_name],
qt.text AS SQL_Full,
SUBSTRING(
qt.text,
(qs.statement_start_offset / 2) + 1,
(
(
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) / 2
) + 1
) AS SQL_Part --统计对应的部分语句
,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.last_elapsed_time / 1000000 AS lastElapsedSeconds,
qs.last_worker_time / 1000000 AS lastCpuSeconds,
CAST(
qs.total_elapsed_time / 1000000.0 / (
CASE
WHEN qs.execution_count = 0 THEN -1
ELSE qs.execution_count
END
) AS DECIMAL(28, 2)
) AS avgDurationSeconds,
CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS
lastLogicReadsMB,
qs.last_logical_reads,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00' --今天8点之后的慢SQL
AND qs.last_elapsed_time >= 3 * 1000 * 1000 --只取执行时间大于 3 秒的记录
AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
qs.last_worker_time DESC
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
d DATETIME NOT NULL
)
GO
SET NOCOUNT ON
INSERT INTO t(d) VALUES('1900-01-01')
INSERT INTO t(d) VALUES('2010-01-01')
GO
SELECT * FROM t WHERE d>''
/*
id d
2 2010-01-01 00:00:00.000
*/
DECLARE @d DATETIME
SET @d=''
SELECT @d AS r
/*
r
1900-01-01 00:00:00.000
*/
因为绝大多数的日期都会>'1900-01-01', 所以消耗必然是很大的。
不过, 你早点用我 #1 的语句, 是可以查到的。现在可能被冲掉了。