Sqlserver性能调优、死锁、常用语句

深海接盘侠

已于 2024-07-04 08:42:46 修改

阅读量605

点赞数 1

文章标签：数据库 sql

于 2021-08-10 11:10:25 首次发布

本文链接：https://blog.csdn.net/qq_38358143/article/details/119564614

版权

常用SQL Server Management Studio (ssms)调优

查询死锁语句

原理：将sp_who和sp_lock查询结果放入两个临时表方便查看

--开始
--创建两个临时表
CREATE Table #Who(
    spid int,
    ecid int,
    status nvarchar(50),
    loginname nvarchar(50),
    hostname nvarchar(50),
    blk int,
    dbname nvarchar(50),
    cmd nvarchar(50),
    request_ID int);
CREATE Table #Lock(
    spid int,
    dbid int,
    objid int,
    indld int,
    [Type] nvarchar(20),
    Resource nvarchar(50),
    Mode nvarchar(10),
    Status nvarchar(10)
);
 
delete #Who;delete #Lock;
INSERT INTO #Who exec sp_who;--查看进程对应的的用户
INSERT INTO #Lock exec sp_lock;--查看当前阻塞的数据表的进程
 
--查看哪台客户端造成锁，记下spid
SELECT #Who.spid , --进程id,
       hostname ,--计算机名,
       [type],
       #Who.cmd ,
       mode ,
       #Who.blk,--阻塞了进程的spid
       objid,-- 锁定的对象id
       Object_name(objid) objName,-- 锁定的对象名
       #Who.status
FROM   #Lock,
       #Who
WHERE  #Who.spid = #Lock.spid
       AND objid <> 0
       AND dbname = 'QPIS'; -- 按需更改数据库名
 
--可以忽略下方查找死锁原因的语句，直接跳到杀进程
 
exec sp_who2;--查看链接数据库的有什么程序
 
dbcc inputbuffer(97);--查看该spid的进程正在执行的语句
 
select * from Sys.dm_exec_connections a where a.session_id=97;--session_id=spid，查看该进程的主机的ip
 
select * from Sys.dm_exec_Sessions a where a.session_id=97;--session_id=spid，查看该进程的主机什么程序在使用数据库
 
kill 97; --杀进程
drop table #Who;drop table #Lock;

模糊查询函数和存储过程的代码

select *
from sysobjects o, syscomments s
where o.id = s.id
and s.text like '%代码内容%'

查看语句执行性能耗时（使用活动和监视器也可）

/*SELECT (q.total_elapsed_time / q.execution_count) / 1000 平均时间ms,
       q.total_elapsed_time / 1000 总花费时间ms,
       q.total_worker_time / 1000 所用的CPU总时间ms,
       q.total_physical_reads 物理读取总次数,
       q.total_logical_reads / execution_count 每次逻辑读次数,
       q.total_logical_reads 逻辑读取总次数,
       q.total_logical_writes 逻辑写入总次数,
       q.execution_count 执行次数,
       q.creation_time 语句编译时间,
       q.last_execution_time 上次执行时间,
       s.text
FROM sys.dm_exec_query_stats q
    CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) s 
    --使用不了cross apply语法需要兼容性级别COMPATIBILITY_LEVEL为90或以上
    --右键数据库属性->选项->兼容性级别，调整到sqlserver 2005(90)以上
ORDER BY q.total_elapsed_time / q.execution_count DESC;
*/
弃用CROSS APPLY，非广泛使用的SQL语法
 
SELECT (q.total_elapsed_time / q.execution_count) / 1000 平均时间ms,
       q.total_elapsed_time / 1000 总花费时间ms,
       q.total_worker_time / 1000 所用的CPU总时间ms,
       q.total_physical_reads 物理读取总次数,
       q.total_logical_reads / execution_count 每次逻辑读次数,
       q.total_logical_reads 逻辑读取总次数,
       q.total_logical_writes 逻辑写入总次数,
       q.execution_count 执行次数,
       q.creation_time 语句编译时间,
       q.last_execution_time 上次执行时间,
       (SELECT text FROM  sys.dm_exec_sql_text(q.sql_handle)) SQL_TEXT
FROM sys.dm_exec_query_stats q
WHERE q.last_execution_time>'2022-05-26 07:00:00.000'--上次执行时间
ORDER BY q.total_elapsed_time / q.execution_count DESC;

纵向列出表字段

SELECT
    b.name AS tablename,
    a.name AS columnname,
    c.name AS typename,
    a.max_length AS typelength
FROM
    sys.columns a
    INNER JOIN sys.tables b ON b.object_id = a.object_id
    INNER JOIN sys.types c ON c.system_type_id= a.system_type_id
WHERE
    b.name= '' ;--表名，纵向列出字段

横向列出表字段

SELECT c.columnname + ','
FROM   (SELECT a.name AS columnname
        FROM   sys.columns a
               INNER JOIN sys.tables b
                       ON b.object_id = a.object_id
        WHERE  b.name = '' --表名，横向列出字段
       ) c
FOR XML PATH ( '' );

索引碎片查询和重建重组（ssms右键查看表属性也可以）

--查询数据库下所有索引碎片率
--登录的用户要有master权限，在对应数据库下操作，$DatabaseName换成对应数据库名
--行索引，所有sql版本通用
SELECT CASE
         WHEN t.[type] = 'U' THEN '表'
         WHEN t.[type] = 'V' THEN '视图'
       END                                               AS '类型',
       Schema_name(t.schema_id) + '.' + t.[name]         AS '(表/视图)名称',
       i.[name]                                          AS '索引名称',
       d.column_names AS '列名',
       CASE i.[type]
         WHEN 0 THEN '堆索引'
         WHEN 1 THEN '聚集索引'
         WHEN 2 THEN '非聚集索引'
         WHEN 3 THEN 'XML索引'
         WHEN 4 THEN '空间索引'
         WHEN 5 THEN '聚簇列存储索引'
         WHEN 6 THEN '非聚集列存储索引'
         WHEN 7 THEN '非聚集哈希索引'
       END                                               AS '索引类型',
       CASE
         WHEN i.is_unique = 1 THEN '唯一'
         ELSE '不唯一'
       END                                               AS '索引是否唯一',
       case s.alloc_unit_type_desc when 'IN_ROW_DATA' THEN '索引' when 'LOB_DATA' then '大数据字段索引' else s.alloc_unit_type_desc end ,
       s.avg_fragmentation_in_percent '碎片率',
       s.fragment_count,
       s.avg_fragment_size_in_pages '一个碎片的平均页数',/*根据函数最后一个参数而变化,参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15#evaluating-index-fragments*/
       s.page_count
FROM   $DatabaseName.sys.objects t
       INNER JOIN $DatabaseName.sys.indexes i
               ON t.object_id = i.object_id
       INNER JOIN sys.dm_db_index_physical_stats(Db_id('$DatabaseName'), NULL, NULL, NULL,NULL) s--最后一个参数NULL代表LIMITED，改成DETAILED可以更详细但查询很慢
               ON t.object_id = s.object_id
                  AND i.index_id = s.index_id
       CROSS APPLY (SELECT col.[name] + ', '
                    FROM   #DatabaseName.sys.index_columns ic
                           INNER JOIN #DatabaseName.sys.columns col
                                   ON ic.object_id = col.object_id
                                      AND ic.column_id = col.column_id
                    WHERE  ic.object_id = t.object_id
                           AND ic.index_id = i.index_id
                    ORDER  BY col.column_id
                    FOR XML PATH('')) d(column_names)
WHERE  t.is_ms_shipped <> 1--排除系统索引
       AND i.index_id > 0 --排除堆索引（表没有聚合索引就会产生堆索引）
ORDER  BY t.schema_id;
 
--列索引，sql2016以上可用
select * from sys.dm_db_column_store_row_group_physical_stats
 
--可选
--数据库用户权限下
declare @table_id int;
set @table_id=object_id('用户名.dbo.表名');--表名
dbcc showcontig(@table_id);

avg_fragmentation_in_percent范围:
大于5% 且小于 30%,需要重组,
ALTER INDEX #IndexName ON #TableName REORGANIZE;
30%,需要重建,
ALTER INDEX #IndexName ON #TableName REBUILD;

简单总结：
1、重建重组消耗大量资源，数据库空闲时候操作。
2、碎片多或者页面密度低的时候重建重组才有效果。
3、自 SQL Server 2016 (13.x) 起，通常不需要重新生成列存储索引

自动重组重建索引存储过程

--作用：索引页数大于20的情况下，碎片率大于30的索引重建，碎片率大于5小于30的索引重组
--$DatabaseName替换为需要的数据库名
CREATE PROCEDURE [dbo].[index_rebuild_reorganize] AS
SET NOCOUNT ON;
DECLARE
@s_tableName VARCHAR(64),
@s_indexName VARCHAR(128),
@f_fragmentationInPercent FLOAT,
@s_execSql NVARCHAR(200),
@s_action VARCHAR(10);
DECLARE c_indexes CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id)+'.'+t.name tableName, i.name indexName,
    s.avg_fragmentation_in_percent fragmentationInPercent
FROM $DataBaseName.sys.objects t
     INNER JOIN $DatabaseName.sys.indexes i ON t.object_id=i.object_id
     INNER JOIN sys.dm_db_index_physical_stats(DB_ID('$DatabaseName'), NULL, NULL, NULL, NULL) s ON t.object_id=s.object_id
                                                                AND i.index_id=s.index_id
WHERE t.is_ms_shipped<>1 --排除系统索引
      AND i.index_id>0 --排除堆索引（表没有聚合索引就会产生堆索引）
      AND i.type IN (1, 2) --聚合索引和非聚合索引
      AND t.type='U' --U=表 V=视图
      AND s.avg_fragmentation_in_percent>5 --碎片率大于5%
      AND s.page_count>20 --索引页数大于20
     -- AND t.name='XXXXX'
ORDER BY s.avg_fragmentation_in_percent DESC;
OPEN c_indexes;
FETCH NEXT FROM c_indexes
INTO @s_tableName,@s_indexName,@f_fragmentationInPercent;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @s_execSql= 'ALTER INDEX '+@s_indexName +' ON '+ @s_tableName+' ' +@s_action;
    IF @f_fragmentationInPercent >= 30
        SET @s_action='REBUILD';
    ELSE
        SET @s_action='REORGANIZE';
    EXEC (@s_execSql)
    FETCH NEXT FROM c_indexes
    INTO @s_tableName,@s_indexName,@f_fragmentationInPercent;
END;
CLOSE c_indexes;
DEALLOCATE c_indexes;

查看语句执行性能耗时

--弃用CROSS APPLY，非广泛使用的sql语法
 
SELECT (q.total_elapsed_time / q.execution_count) / 1000 平均时间ms,
       q.total_elapsed_time / 1000 总花费时间ms,
       q.total_worker_time / 1000 所用的CPU总时间ms,
       q.total_physical_reads 物理读取总次数,
       q.total_logical_reads / execution_count 每次逻辑读次数,
       q.total_logical_reads 逻辑读取总次数,
       q.total_logical_writes 逻辑写入总次数,
       q.execution_count 执行次数,
       q.creation_time 语句编译时间,
       q.last_execution_time 上次执行时间,
       (SELECT text FROM  sys.dm_exec_sql_text(q.sql_handle)) SQL_TEXT
FROM sys.dm_exec_query_stats q
WHERE q.last_execution_time>'2022-05-26 07:00:00.000'--上次执行时间
ORDER BY q.total_elapsed_time / q.execution_count DESC;

求中位数

（仅用于少量数据统计，大量数据请使用存储将下列语句拆分）

SELECT AVG(x.quantity)
FROM(SELECT $column quantity, COUNT(*) OVER () total,
         CEILING(CAST(COUNT(*) OVER () AS DECIMAL)/ 2) mid, --向上取整，例：CEILING(11.0/2)=6
         ROW_NUMBER() OVER (ORDER BY $column) rowNo
     FROM $tablename a) x
WHERE(total % 2=0 AND rowNo IN (mid, mid+1))OR(total % 2=1 AND rowNo=mid);

sqlserver 2000：_WA_Sys开头的索引

sqlserver查询优化器自动创建的索引，当表数据列过多时，会超过255个索引，导致无法手动创建索引，需要先执行删除_WA_Sys索引语句后立即执行创建其他索引的语句

--sqlserver 2000 查询索引
SELECT
    i.name AS IndexName,
    i.*,
    i.indid AS IndexID
FROM
    sysindexes i
JOIN
    sysobjects o ON i.id = o.id AND o.xtype = 'U' AND o.name = 'TPATIENTVISIT'
WHERE
    i.indid NOT IN (0, 255)