1、查询出最近所有耗时最大的SQL语句
返回的是未关联任何特定对象的最耗费资源的查询信息，包括查询的执行SQL、最后一次执行的总耗时、所有执行的总耗时、执行最小耗时、执行最大耗时、执行次数、计划生成次数、所有执行期间总共读取和写的物理磁盘次数以及逻辑磁盘次数等信息。
代码语言：javascript复制SELECT s2.dbid,
s1.sql_handle,
(
SELECT TOP 1
SUBSTRING( s2.text,
statement_start_offset / 2 1,
((CASE
WHEN statement_end_offset = -1 THEN
(LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2)
ELSE
statement_end_offset
END
) - statement_start_offset
) / 2 1
)
) AS 执行SQL,
last_worker_time '最后执行总耗时(毫秒)',
last_execution_time '最后执行时间',
total_worker_time '所有执行总耗时(毫秒)',
min_worker_time '执行最小耗时(毫秒)',
max_worker_time '执行最大耗时(毫秒)',
execution_count,
plan_generation_num,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid IS NULL
ORDER BY last_worker_time DESC,
s1.sql_handle,
s1.statement_start_offset,
s1.statement_end_offset;
说明
列名 | 描述 |
|---|---|
s2.dbid | 执行查询的数据库ID |
s1.sql_handle | SQL语句的唯一标识符 |
执行SQL | 通过sys.dm_exec_sql_text获取的SQL语句文本 |
last_worker_time | 上一次执行查询所花费的总CPU时间（毫秒） |
last_execution_time | 查询最后一次执行的时间 |
total_worker_time | 所有执行查询所花费的总CPU时间（毫秒） |
min_worker_time | 任何单次执行查询所花费的最少CPU时间（毫秒） |
max_worker_time | 任何单次执行查询所花费的最大CPU时间（毫秒） |
execution_count | 查询被执行的次数 |
plan_generation_num | 计划生成次数 |
total_physical_reads | 所有执行期间总共读取的物理磁盘次数 |
last_physical_reads | 上一次执行期间读取的物理磁盘次数 |
min_physical_reads | 任何单次执行期间最少读取的物理磁盘次数 |
max_physical_reads | 任何单次执行期间最多读取的物理磁盘次数 |
total_logical_writes | 所有执行期间总共写的逻辑磁盘次数 |
last_logical_writes | 上一次执行期间写的逻辑磁盘次数 |
min_logical_writes | 任何单次执行期间最少写的逻辑磁盘次数 |
max_logical_writes | 任何单次执行期间最多的逻辑磁盘次数 |
● sys.dm_exec_query_stats AS s1：SQL Server中的系统视图，提供了关于查询统计信息，例如CPU时间、读取次数和写入次数等。
● CROSS APPLY sys.dm_exec_sql_text(s1.sql_handle) AS s2：使用CROSS APPLY运算符将s1.sql_handle传递给s2，以便从sys.dm_exec_sql_text视图中检索实际的SQL文本
2、查询数据库每个数据表存储占用
查询指定数据库的记录数、总工占用空间KB、总共占用空间MB、已使用空间KB、已使用空间MB、未使用空间KB、未使用空间MB，这对于分析磁盘占用非常有用。
代码语言：javascript复制SELECT
t.NAME AS 表名,
s.Name AS 架构,
p.rows AS 记录数,
SUM ( a.total_pages ) * 8 AS 总工占用空间KB,
CAST ( ROUND( ( ( SUM ( a.total_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 总共占用空间MB,
SUM ( a.used_pages ) * 8 AS 已使用空间KB,
CAST ( ROUND( ( ( SUM ( a.used_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 已使用空间MB,
( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 AS 未使用空间KB,
CAST (
ROUND( ( ( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 ) / 1024.00, 2 ) AS NUMERIC ( 36, 2 )
) AS 未使用空间MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 0
GROUP BY t.Name,s.Name,p.Rows
ORDER BY 总共占用空间MB DESC;
3、当前正在执行的最耗时的前10个SQL语句
该语句适合排查生产环境实时SQL语句慢的情况。
代码语言：javascript复制SELECT TOP 10
r.session_id,
r.request_id,
r.start_time AS '开始时间',
r.status AS '状态',
r.command AS '命令',
t.text AS 'sql语句',
DB_NAME(r.database_id) AS '数据库名',
r.blocking_session_id AS '正在阻塞其他会话的会话ID',
w.wait_type AS '等待资源类型',
r.wait_time AS '等待时间',
r.wait_resource AS '等待的资源',
w.waiting_tasks_count AS '当前正在进行等待的任务数',
r.reads AS '物理读次数',
r.writes AS '写次数',
r.logical_reads AS '逻辑读次数',
r.row_count AS '返回结果行数'
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
LEFT JOIN sys.dm_os_wait_stats AS w ON r.wait_type = w.wait_type
WHERE r.session_id > 50 -- 排除系统进程
ORDER BY r.cpu_time DESC;
注意：sys.dm_exec_requests仅包含当前活动的会话信息，获取过去一段时间内已经完成的慢查询，可以使用扩展事件(XEvents)来记录长时间运行的查询，并将这些信息写入一个跟踪文件，然后分析这些文件来找出历史某个时间段的慢查询。
4、SQLServer查看锁表和解锁
如果遇到数据库锁表的情况，可以通过sql语句拿到锁表进程id，然后执行杀掉进程语句，解决数据库卡死的情况。
代码语言：javascript复制-- 第一步查询被锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';
--参数说明 spid 锁表进程 ；tableName 被锁表名
-- 第二步解锁语句 需要拿到spid然后杀掉缩表进程
declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill ' cast(@spid as varchar)
exec(@sql)5、快速清理数据库日志文件
数据库日志文件随着业务系统的长期使用会非常占用存储空间，甚至占用超过几百G甚至上T，如果不需要进行一直保留数据库日志文件，可以建一个数据库作业，定时清理数据库日志文件，脚本如下：
代码语言：javascript复制USE master
ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY SIMPLE --调整为简单模式
USE DB
DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M，可以自行指定
USE master
ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY FULL --还原为完全模式 
