SQL server 使用优化脚本
创始人
2024-05-30 09:42:55
0

查询每个数据库上的Session数量是多少

sys.sysprocesses :包含有关在 SQL Server 实例上运行的进程的信息。 这些进程可以是客户端进程或系统进程。 若要访问 sysprocesses,您必须位于 master 数据库上下文中,或者必须使用由三部分构成的名称 master.dbo.sysprocesses

sys.sysprocesses

-- 每个数据库上的Session数量是多少
SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame AS LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

查询表现最差的前10名

sys.dm_exec_query_stats : 返回 SQL Server 中缓存查询计划的聚合性能统计信息。 缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。 在从缓存删除计划时,也将从该视图中删除对应行。
sys.dm_exec_query_stats

sys.dm_exec_sql_text : 返回由指定的 sql_handle 标识的 SQL 批处理的文本。 该表值函数将替换系统函数 fn_get_sql
sys.dm_exec_sql_text

SELECT TOP 10 ProcedureName    = t.text,ExecutionCount   = s.execution_count,AvgExecutionTime = isnull(s.total_elapsed_time / s.execution_count, 0),AvgWorkerTime    = s.total_worker_time / s.execution_count,TotalWorkerTime  = s.total_worker_time,MaxLogicalReads  = s.max_logical_reads,MaxPhysicalReads = s.max_physical_reads,MaxLogicalWrites = s.max_logical_writes,CreationDateTime = s.creation_time,CallsPerSecond   = isnull(s.execution_count / datediff(second, s.creation_time, getdate()), 0)
FROM sys.dm_exec_query_stats sCROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_physical_reads DESC

查询是否由于连接没有释放引起CPU过高

select *
from master.dbo.sysprocesses
where spid > 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -10, getdate())and login_time < dateadd(minute, -10, getdate())

强行释放空连接

select 'kill ' + rtrim(spid)
from master.dbo.sysprocesses
where spid > 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -60, getdate())and login_time < dateadd(minute, -60, getdate())

查询当前占用 cpu 资源最高的会话和其中执行的语句

SELECT total_worker_time/execution_count AS avg_cpu_cost, plan_handle,execution_count,(SELECT SUBSTRING(text, statement_start_offset/2 + 1,(CASE WHEN statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max), text)) * 2ELSE statement_end_offsetEND - statement_start_offset)/2)FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

查询缓存中重用次数少,占用内存大的查询语句(当前缓存中未释放的)

SELECT TOP 100 usecounts, objtype, p.size_in_bytes, [sql].[text]
FROM sys.dm_exec_cached_plans pOUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql
ORDER BY usecounts, p.size_in_bytes descSELECT top 25 qt.text, qs.plan_generation_num, qs.execution_count, dbid, objectid
FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num > 1
ORDER BY qs.plan_generation_numSELECT top 50 qt.text                                             AS SQL_text,SUM(qs.total_worker_time)                           AS total_cpu_time,SUM(qs.execution_count)                             AS total_execution_count,SUM(qs.total_worker_time) / SUM(qs.execution_count) AS avg_cpu_time,COUNT(*)                                            AS number_of_statements
FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
-- ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

查询是否有阻塞

sys.dm_exec_requests 返回有关在 SQL Server 中正在执行的每个请求的信息
sys.dm_exec_requests

SELECT [session_id],[request_id],[start_time]           AS '开始时间',[status]               AS '状态',[command]              AS '命令',dest.[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 derCROSS APPLYsys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id] > 50
ORDER BY [cpu_time] DESC

查看死锁

sys.dm_tran_locks 返回有关SQL Server中当前活动的锁管理器资源的信息。 向锁管理器发出的已授予锁或正等待授予锁的每个当前活动请求分别对应一行。

结果集中的列大体分为两组:资源组和请求组。 资源组说明正在进行锁请求的资源,请求组说明锁请求。
sys.dm_tran_locks

sys.dm_os_waiting_tasks 返回有关正在等待某些资源的任务的等待队列的信息
sys.dm_os_waiting_tasks

use masterselect        spid         进程,        STATUS       状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid), 1, 30), 用户机器名称=SUBSTRING(hostname, 1, 12), 是否被锁住=convert(char(3), blocked), 数据库名称=SUBSTRING(db_name(dbid), 1, 20),        cmd          命令,        waittype  as 等待类型,        last_batch   最后批处理时间,        open_tran    未提交事务的数量,        getdate() as 执行时间
from master.sys.sysprocesses
Where status = 'sleeping'and waittype = 0x0000and open_tran > 0select t1.resource_type                                [资源锁定类型], DB_NAME(resource_database_id)                as 数据库名, t1.resource_associated_entity_id                锁定对象, t1.request_mode                              as 等待者请求的锁定模式, t1.request_session_id                           等待者SID, t2.wait_duration_ms                             等待时间, (select TEXTfrom sys.dm_exec_requests rcross applysys.dm_exec_sql_text(r.sql_handle)where r.session_id = t1.request_session_id) as 等待者要执行的SQL, (select SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,(casewhen r.statement_end_offset = -1 then DATALENGTH(qt.text)else r.statement_end_offset end - r.statement_start_offset) / 2 + 1)from sys.dm_exec_requests rcross apply sys.dm_exec_sql_text(r.sql_handle) qtwhere r.session_id = t1.request_session_id)    等待者正要执行的语句, t2.blocking_session_id                          [锁定者SID], (select TEXTfrom sys.sysprocesses pcross applysys.dm_exec_sql_text(p.sql_handle)where p.spid = t2.blocking_session_id
)                                                      锁定者执行语句, getdate()                                    as 执行时间
from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2
where t1.lock_owner_address = t2.resource_address

查询表空间大小

sp_spaceused 显示当前数据库中表、索引视图或 Service Broker 队列使用的行数、保留磁盘空间和磁盘空间,或显示整个数据库保留和使用的磁盘空间。

create table #tb
(表名     sysname,记录数    int,保留空间   varchar(100),使用空间   varchar(100),索引使用空间 varchar(100),未用空间   varchar(100)
)
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select *
from #tb
go
SELECT 表名,记录数,cast(ltrim(rtrim(replace(保留空间, 'KB', ''))) as int) / 1024           保留空间MB,cast(ltrim(rtrim(replace(使用空间, 'KB', ''))) as int) / 1024           使用空间MB,cast(ltrim(rtrim(replace(使用空间, 'KB', ''))) as int) / 1024 / 1024.00 使用空间GB,cast(ltrim(rtrim(replace(索引使用空间, 'KB', ''))) as int) / 1024         索引使用空间MB,cast(ltrim(rtrim(replace(未用空间, 'KB', ''))) as int) / 1024           未用空间MB
FROM #tb
WHERE cast(ltrim(rtrim(replace(使用空间, 'KB', ''))) as int) / 1024 > 0
--order by 记录数 desc
ORDER BY 使用空间MB DESC
DROP TABLE #tb;

查询哪些表的Index 需要改进

sys.dm_db_missing_index_group_stats 返回缺失索引组的摘要信息,不包括空间索引。
sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups 返回有关特定索引组中缺少的索引的信息
sys.dm_db_missing_index_groups

sys.dm_db_missing_index_details 返回有关缺失索引的详细信息。
sys.dm_db_missing_index_details

-- 找出哪些表的Index 需要改进
SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], migs.last_user_seek, mid.[statement]                                                                      AS [Database.Schema.Table], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
ORDER BY index_advantage desc

查询 Index 碎片化指数

sys.dm_db_index_physical_stats 返回SQL Server中指定表或视图的数据和索引的大小和碎片信息。 对于索引,针对每个分区中的 B 树的每个级别,返回与其对应的一行。 对于堆,针对每个分区的 IN_ROW_DATA 分配单元,返回与其对应的一行。 对于大型对象 (LOB) 数据,针对每个分区的 LOB_DATA 分配单元返回与其对应的一行。 如果表中存在行溢出数据,则针对每个分区中的 ROW_OVERFLOW_DATA 分配单元,返回与其对应的一行。 不返回有关 xVelocity 内存优化的列存储索引的信息。
sys.dm_db_index_physical_stats

sys.indexes 每个表格对象(例如,表、视图或表值函数)的索引或堆都包含一行。
sys.indexes

--  查看Index 碎片化指数
SELECT DB_NAME(ps.database_id)     AS [Database Name], OBJECT_NAME(ps.[object_id]) AS [Object Name], i.[name]                    AS [Index Name], ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS psINNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent desc;

查询Index 的Statistics 最后更新时间

sys.objects 包含数据库中创建的每个用户定义的架构范围对象(包括本机编译的标量用户定义函数)的行。
sys.objects

sys.stats 对于数据库中SQL Server中表、索引和索引视图存在的每个统计信息对象,包含一行。 每个索引都有一个具有相同名称和 ID 的对应统计信息行 (index_id = stats_id) ,但并非每个统计信息行都有相应的索引。
sys.stats

--   查询Index 的Statistics 最后更新时间
SELECT SCHEMA_NAME(o.[schema_id]) + N'.' + o.[name] AS [Object Name], o.type_desc                                  AS [Object Type], i.[name]                                     AS [Index Name], STATS_DATE(i.[object_id], i.index_id)        AS [Statistics Date], s.auto_created, s.no_recompute, s.user_created, st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]AND i.index_id = s.stats_idINNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]AND i.[index_id] = st.[index_id]
WHERE o.[type] IN ('U', 'V')AND st.row_count > 0
ORDER BY STATS_DATE(i.[object_id], i.index_id) desc;

优化指定表

ALTER INDEX 通过禁用、重新生成或重新组织索引,或通过设置索引选项,修改现有的表索引或视图索引(行存储、列存储或 XML)。
ALTER INDEX

UPDATE STATISTICS 更新表或索引视图的查询优化统计信息。 默认情况下,查询优化器已根据需要更新统计信息以改进查询计划;但在某些情况下,可以通过使用 UPDATE STATISTICS 或存储过程 sp_updatestats 来比默认更新更频繁地更新统计信息,提高查询性能。
UPDATE STATISTICS

对表或索引启用压缩功能

declare @tableName nvarchar(100),@sqlStr nvarchar(300) --声明变量:待优化物理表名,优化语句。set @tableName = ?-- 重建索引,整理碎片
set @sqlStr = 'ALTER INDEX ALL ON [' + @tableName + '] REBUILD'
exec @sqlStr-- 更新统计信息
set @sqlStr = 'UPDATE STATISTICS [' + @tableName + ']'
exec @sqlStr-- 压缩索引
set @sqlStr = 'ALTER INDEX ALL ON [' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=ROW)'
exec @sqlStr-- 压缩物理表
set @sqlStr = 'ALTER TABLE [' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=ROW)'
exec @sqlStr

相关内容

热门资讯

安卓系统用的华为应用,探索智能... 你知道吗?在安卓系统里,华为的应用可是个宝库呢!它们不仅功能强大,而且使用起来超级方便。今天,就让我...
安卓变ios系统魅蓝 你知道吗?最近有个朋友突然告诉我,他要把自己的安卓手机换成iOS系统,而且还是魅蓝品牌的!这可真是让...
幻书启世录安卓系统,安卓世界中... 亲爱的读者们,你是否曾在某个夜晚,被一本神奇的书所吸引,仿佛它拥有着穿越时空的力量?今天,我要带你走...
电脑安装安卓系统进不去,安卓系... 电脑安装安卓系统后竟然进不去,这可真是让人头疼的问题啊!你是不是也遇到了这种情况,心里直呼“怎么办怎...
用键盘切换控制安卓系统,畅享安... 你有没有想过,用键盘来控制你的安卓手机?是的,你没听错,就是那个我们每天敲敲打打的小玩意儿——键盘。...
小米安卓镜像系统在哪,小米安卓... 你有没有想过,你的小米手机里有一个隐藏的宝藏——安卓镜像系统?没错,就是那个可以让你的手机瞬间变身成...
安卓手机下载排班系统,高效排班... 你有没有想过,每天忙碌的工作中,有没有什么好帮手能帮你轻松管理时间呢?今天,就让我来给你介绍一个超级...
桌面组件如何弄安卓系统,桌面组... 亲爱的桌面爱好者们,你是否曾梦想过将安卓系统搬到你的电脑桌面上?想象那些流畅的动画、丰富的应用,还有...
安卓13系统介绍视频,新功能与... 亲爱的读者们,你是否对安卓13系统充满好奇?想要一探究竟,却又苦于没有足够的时间去研究?别担心,今天...
车机安卓7.1系统,功能升级与... 你有没有发现,现在的车机系统越来越智能了?尤其是那些搭载了安卓7.1系统的车机,简直就像是个贴心的智...
安卓系统下如何读pdf,And... 你有没有遇到过这种情况:手机里存了一大堆PDF文件,可是怎么也找不到一个能顺畅阅读的工具?别急,今天...
安卓系统全国通用的吗,畅享智能... 你有没有想过,为什么你的手机里装的是安卓系统呢?安卓系统,这个名字听起来是不是有点神秘?今天,就让我...
假苹果手机8安卓系统,颠覆传统... 你有没有想过,如果苹果手机突然变成了安卓系统,会是怎样的景象呢?想象那熟悉的苹果外观,却运行着安卓的...
安卓12.0系统vivo有吗,... 你有没有听说最近安卓系统又升级啦?没错,就是那个让手机焕然一新的安卓12.0系统!那么,咱们国内的手...
核心芯片和安卓系统,探索核心芯... 你知道吗?在科技的世界里,有一对“黄金搭档”正悄悄改变着我们的生活。他们就是——核心芯片和安卓系统。...
如何调安卓系统屏幕颜色,安卓系... 亲爱的手机控们,你是否曾觉得安卓系统的屏幕颜色不够个性,或者是因为长时间盯着屏幕而感到眼睛疲劳?别担...
旧台式电脑安装安卓系统,轻松安... 你那台旧台式电脑是不是已经服役多年,性能逐渐力不从心,却又不忍心让它退役呢?别急,今天就来教你怎么给...
美国要求关闭安卓系统,科技霸权... 美国要求关闭安卓系统:一场技术革新还是政治博弈?在数字化时代,智能手机已经成为我们生活中不可或缺的一...
安卓系统日记本 你有没有发现,手机里的安卓系统日记本,简直就是记录生活点滴的宝藏库呢?想象每天忙碌的生活中,有没有那...
安卓手机广告最少的系统,探索安... 你有没有发现,用安卓手机的时候,广告总是无处不在,让人烦得要命?不过别急,今天我要给你揭秘一个秘密—...