1、查询出最近所有耗时最大的SQL语句

返回的是未关联任何特定对象的最耗费资源的查询信息，包括查询的执行SQL、最后一次执行的总耗时、所有执行的总耗时、执行最小耗时、执行最大耗时、执行次数、计划生成次数、所有执行期间总共读取和写的物理磁盘次数以及逻辑磁盘次数等信息。

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_writesFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2WHERE s2.objectid IS NULLORDER 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，这对于分析磁盘占用非常有用。

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 tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idLEFT 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语句慢的情况。

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 rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tLEFT JOIN sys.dm_os_wait_stats AS w ON r.wait_type = w.wait_typeWHERE r.session_id > 50 -- 排除系统进程ORDER BY r.cpu_time DESC;

注意：sys.dm_exec_requests仅包含当前活动的会话信息，获取过去一段时间内已经完成的慢查询，可以使用扩展事件(XEvents)来记录长时间运行的查询，并将这些信息写入一个跟踪文件，然后分析这些文件来找出历史某个时间段的慢查询。

4、SQLServer查看锁表和解锁

如果遇到数据库锁表的情况，可以通过sql语句拿到锁表进程id，然后执行杀掉进程语句，解决数据库卡死的情况。

-- 第一步查询被锁表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，如果不需要进行一直保留数据库日志文件，可以建一个数据库作业，定时清理数据库日志文件，脚本如下：

USE masterALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAITALTER DATABASE DB SET RECOVERY SIMPLE --调整为简单模式USE DBDBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M，可以自行指定USE masterALTER DATABASE DB SET RECOVERY FULL WITH NO_WAITALTER DATABASE DB SET RECOVERY FULL --还原为完全模式