SQL Server 数据库查询非常实用的功能(不断更新中，2020.06.15)

骚团长

已于 2023-11-09 18:51:03 修改

阅读量1.1k

点赞数

分类专栏： SQL小技巧小小笔录文章标签： sql sqlserver 数据库

于 2020-06-15 09:17:43 首次发布

本文链接：https://blog.csdn.net/u010944388/article/details/106611791

版权

小小笔录同时被 2 个专栏收录

12 篇文章 2 订阅

订阅专栏

SQL小技巧

9 篇文章 0 订阅

订阅专栏

首先对各位原创作者表示感谢！

由于以前收藏在自己本地电脑的记事本中，所以无法找到原文出处！

为方便一目了然的查看查询结果，我对脚本查询结果做了适当调整，

现将“笔记”发布CSDN blog，部分敏感数据，我会适当马赛克，体验不佳，敬请谅解！

1>：查看所有文件所在数据库、路径、状态、大小：

/*查看所有文件所在数据库、路径、状态、大小*/
USE [master]
SELECT 
db_name(database_id)AS 数据库名,  
CASE WHEN type_desc='ROWS' THEN N'数据' WHEN type_desc='LOG' THEN N'日志' ELSE type_desc END AS 文件类型,  
NAME AS 文件名称 , 
state_desc AS 文件状态,  
CAST (size * 8.0/1024 AS VARCHAR(50))+'(MB)' AS [数据大小(MB)],
CAST (size * 8.0/1024000 AS VARCHAR(50))+'(GB)' AS [数据大小(GB)],
physical_name AS 文件物理路径   
FROM sys.master_files

2>：查看数据库的最近备份信息

/*查看数据库的最近备份信息*/
USE OA3LN/*运行数据库不限，系统数据库与非系统数据库均可*/
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type
/*说明：D 表示全备份，i 表示差异备份，L 表示日志备份*/

3>：查询实例上的每个数据库的大小

--查询实例上的每个数据库的大小
SELECT
DB_NAME(db.database_id) AS 数据库名,
CAST((CAST(mfrows.RowSize AS FLOAT)*8)/1024 AS VARCHAR(50))+'(MB)' AS [数据大小(MB)],
CAST(ROUND((CAST(mfrows.RowSize AS FLOAT)*8)/1024000,2)AS VARCHAR)+'(GB)' AS [数据大小(GB)],
'|' AS [分割线],
CAST((CAST(mflog.LogSize AS FLOAT)*8)/1024 AS VARCHAR(50))+'(MB)' AS [日志大小(MB)],
CAST(ROUND((CAST(mflog.LogSize AS FLOAT)*8)/1024000,2)AS VARCHAR)+'(GB)' AS [日志大小(GB)],
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

4>：查询总耗CPU最多的前30个SQL，且最近5天出现过

--查询总耗CPU最多的前30个SQL，且最近5天出现过
USE [master]
SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())
ORDER BY total_worker_time DESC

5>：查询平均耗CPU最多的前30个SQL，且最近5小时出现过

--查询平均耗CPU最多的前30个SQL，且最近5小时出现过
USE [master]
SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

6>：查询计划任务中的作业情况

--查询计划任务中的作业情况
SELECT 
TOP 100 
jobs.name AS 作业名,
CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) AS 日志日期 
FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs  jobs 
ON jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1 
--and jobs.name='LSRestore_*****1.11,**00_T***' --这里可以AND 任务名来单独查询
ORDER BY 2 DESC

7>：查看当前最耗资源的30个SQL及其spid

--查看当前最耗资源的30个SQL及其spid
USE OA3LN
SELECT TOP 30
session_id,request_id,start_time AS '开始时间',
status AS '状态',
command AS '命令',d_sql.text AS 'sql语句',
 DB_NAME(database_id) AS '数据库名',
blocking_session_id AS '正在阻塞其他会话的会话ID',
wait_type AS '等待资源类型',
wait_time AS '等待时间',
wait_resource AS '等待的资源',
reads AS '物理读次数',
writes AS '写次数',
logical_reads AS '逻辑读次数',
row_count AS '返回结果行数'
FROM sys.dm_exec_requests AS d_request
OUTER APPLY
sys.dm_exec_sql_text(d_request.sql_handle) AS d_sql
WHERE session_id>50
ORDER BY cpu_time DESC
-- 说明：前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background

8>：逻辑读最高的前30条语句

--逻辑读最高的前30条语句
USE OA3LN
select top 30
p.name as [SP Name],
deps.total_logical_reads as [TotalLogicalReads],
deps.total_logical_reads / deps.execution_count as [AvgLogicalReads],
deps.execution_count,
ISNULL(deps.execution_count/DATEDIFF(Second,deps.cached_time,Getdate()),0) as [Calls/Second],
deps.total_elapsed_time,
deps.total_elapsed_time / deps.execution_count as [avg_elapsed_time] ,
deps.cached_time
from sys.procedures as p
inner join sys.dm_exec_procedure_stats as deps on p.[object_id] = deps.[object_id]
where deps.database_id = DB_ID()
order by deps.total_logical_reads desc

9>：最耗时的前100个存储过程

--最耗时的前100个存储过程
USE LXOA3
SELECT TOP 100 a.object_id AS 对象名称,
	a.database_id AS 数据库ID,
	DB_NAME(a.database_id) AS 数据库名称,
	OBJECT_NAME( object_id, database_id ) AS 存储过程名称,
	a.cached_time AS 存储过程添加到缓存的时间,
	a.last_execution_time AS 上次执行时间,
	a.total_elapsed_time AS 总耗时,
	a.total_elapsed_time/ a.execution_count AS 平均耗时,
	a.execution_count AS 运行次数,
	a.total_physical_reads/ a.execution_count AS 平均物理读取数,
	a.total_logical_writes AS 总逻辑写入次数,
	a.total_logical_writes/ a.execution_count AS 平均逻辑写入次数,
	a.last_elapsed_time AS 上次运行耗时,
	a.[total_worker_time] AS[此存储过程自编译以来执行所用的CPU时间总量(微秒)]
	FROM sys.dm_exec_procedure_stats a order by [平均耗时] desc

10>：Top 30 耗时任务

--Top 30 耗时任务	
USE [master]
SELECT TOP 30 creation_time, last_execution_time, total_elapsed_time,
total_elapsed_time/execution_count AS [Avg Elapsed Time us], last_elapsed_time,
execution_count
, ( SELECT SUBSTRING(text, statement_start_offset/2,
( CASE 
WHEN statement_end_offset = -1 
THEN LEN(CONVERT(nvarchar(max),text)) * 2 
ELSE statement_end_offset END 
- statement_start_offset
)
)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats 
ORDER BY [Avg Elapsed Time us] DESC;

以下待续......

11>：

SQL Server基于T-SQL 查看所有表大小，所占空间


SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
    总共占用空间 desc