SQLServer性能优化

最新推荐文章于 2024-07-28 18:23:28 发布

JunChow520

最新推荐文章于 2024-07-28 18:23:28 发布

阅读量774

点赞数

本文链接：https://blog.csdn.net/JunChow520/article/details/103030768

版权

SQLServer语句执行顺序

步骤1. FROM <left_table>
FROM子句，组装来自不同数据源的数据，对FROM子句中前后两表执行笛卡尔积生成虚拟表vt1。
步骤2. ON <join_condition>
对虚拟表vt1应用ON筛选器，只有满足<join_condition>连接条件为真时才被插入新虚拟表vt2
步骤3. <join_type> JOIN <right_table>
如果指定了OUTER JOIN保留表(preserved table)中未找到的行，将行作为外部行添加到新的虚拟表vt2并生成新虚拟表vt3。
如果FROM子句中包含两个以上的表，则对上一个连接生成的结果表和下一个表重复执行步骤1和步骤2，直到结束。
步骤4. WHERE <where_condition>
对虚拟表vt3应用WHERE筛选器，只有<where_condition>条件为真的行才能被插入新的虚拟表vt4。
步骤5. GROUP BY <group_by_list>
按GROUP BY子句中的列对虚拟表vt4中的行进行分组并生成新的虚拟表vt5。
步骤6. WITH {cube | rollup}
将超组(super groups)插入到新生成的虚拟表vt6
步骤7. HAVING <having_condition>
对虚拟表vt6应用HAVING筛选器，当<having_condition>条件为真的组才被插入到虚拟表vt7。
步骤8. SELECT
处理SELECT列列表并生成虚拟表vt8
步骤9. DISTINCT
将虚拟表vt8中重复的行剔除后生成虚拟表vt9
步骤10. ORDER BY <order_by_list>
将虚拟表vt9的行，按ORDER BY子句中的列列表排序生成游标vc10。
步骤11. TOP <top_specification> <select_list>
从游标vc10的开始处选择指定数量或比例的行生成虚拟表vt11并返回调用者。

优化子查询

WITH+ EXISTS

对存在子查询的语句，可使用WITH...AS优化替换，原则上是尽可能避免子查询。
对于IN操作的语句，应尽量使用EXISTS 替换 IN。

-- 原始语句
SELECT * 
FROM WHGameUserDB.dbo.UserAccount 
WHERE 1=1 
AND ChannelID IN(SELECT ChannelID FROM WHTreasureDB.dbo.ChannelConfig WHERE ChannelType=1)

-- 优化替换
WITH tbl(ChannelID) AS (
    SELECT ChannelID FROM WHTreasureDB.dbo.ChannelConfig WHERE ChannelType=1
)

SELECT * 
FROM WHGameUserDB.dbo.UserAccount t1 
WHERE 1=1 
AND EXISTS(SELECT 1 FROM tbl WHERE ChannelID=t1.ChannelID)

优化连表查询

JOIN链接最好不要超过5张表，若存在更新的大数据表，应先放进临时表，然后再使用JOIN连接。

SELECT Channel,PlayDate,Score,UserID INTO #tmp FROM WHTreasureDB.dbo.GameRecordMain WHERE 1=1 AND ClubID=53297316

-- 使用临时表替换大表直接连接
SELECT Channel,PlayDate,Score,UserID INTO #tmp FROM WHTreasureDB.dbo.GameRecordMain WHERE 1=1 AND ClubID=53297316
SELECT * FROM WHGameUserDB.dbo.UserAccount t1, #tmp WHERE 1=1 AND t1.UserID=#tmp.UserID
DROP TABLE #tmp

查看语句执行信息

-- 设置查看语句影响行数
SET NOCOUNT OFF
-- 设置查看执行时间和CPU占用时间
SET STATISTICS TIME ON
-- 设置查询对IO的操作情况
SET STATISTICS IO ON

-- TEST
SELECT * FROM dbo.PropConfig

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(6 行受影响)
Table 'PropConfig'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

查询正在执行的语句

注意：SqlServer数据库时间以微秒为单位，即1秒=1000毫秒(ms)=1000*1000微秒。

SET NOCOUNT OFF;
SET STATISTICS TIME ON;
SET STATISTICS IO  ON;

-- 查询正在执行的语句
SELECT TOP 10   
    st.text AS [执行语句]                 
    ,qs.execution_count [执行次数] 
    ,qs.creation_time AS [执行时间] 
    ,(qs.total_logical_reads + qs.total_logical_writes) AS [逻辑读写]
    ,qs.total_logical_reads AS [逻辑读取]
    ,qs.total_logical_writes AS [逻辑写入]
    ,qs.total_physical_reads [物理读取]
    ,qs.total_elapsed_time AS [执行耗时]
    ,qs.total_worker_time AS [CPU耗时]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.creation_time DESC

查询当前缓存中批处理或存储过程占用CPU资源的情况

-- 查询当前缓存中批处理或存储过程占用CPU资源的情况
SELECT TOP 50   
    qs.sql_handle
    ,COUNT(*) AS [语句个数]
    ,SUM(qs.execution_count) AS [执行次数]
    ,SUM(qs.total_worker_time)/1000.0 AS [CPU耗时]
    ,SUM(qs.total_elapsed_time)/1000.0 AS [执行耗时]
    ,SUM(qs.total_logical_reads) AS [逻辑读取]
    ,SUM(qs.total_logical_writes) AS [逻辑写入]
    ,SUM(qs.total_physical_reads) AS [物理读取]
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY 4 DESC

查询执行耗时的语句

--查询执行耗时的语句
SELECT 
    SS.sum_execution_count
    ,SS.sum_total_elapsed_time
    ,SS.sum_total_worker_time
    ,SS.sum_total_logical_reads
    ,SS.sum_total_logical_writes  
    ,T.text
FROM (
    SELECT 
        S.plan_handle
        ,SUM(S.execution_count) sum_execution_count
        ,SUM(S.total_elapsed_time) sum_total_elapsed_time
        ,SUM(S.total_worker_time) sum_total_worker_time
        ,SUM(S.total_logical_reads) sum_total_logical_reads
        ,SUM(S.total_logical_writes) sum_total_logical_writes
    FROM SYS.dm_exec_query_stats S
    GROUP BY S.plan_handle
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.plan_handle) T
ORDER BY sum_total_logical_reads DESC

查询CPU消耗最高的SQL语句

--查询CPU消耗最高的SQL语句
SELECT TOP 10 
    TEXT AS [SQL]
    ,last_execution_time AS [最后执行时间]
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [IO平均读写次数]
    ,(total_worker_time / execution_count) / 1000000.0 AS [CPU平均执行秒数]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [平均执行秒数]
    ,execution_count AS [执行次数]
    ,qs.total_physical_reads AS [物理读取次数]
    ,qs.total_logical_writes AS [逻辑写入次数]
    ,qp.query_plan AS [查询计划]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

查找执行次数最多的SQL语句

--查找执行次数最多的SQL语句
DECLARE @begin_time DATETIME = '2018-01-01 00:00:00';
DECLARE @execute_count_limit INT = 500;

WITH tbl AS (
    SELECT  
        --执行次数 
        QS.execution_count 
        ,SUBSTRING(
            ST.text
            ,(QS.statement_start_offset / 2) + 1
            ,((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 
        ) AS statement_text
        ,ST.text AS [text]
        ,QS.last_elapsed_time
        ,QS.min_elapsed_time
        ,QS.max_elapsed_time
        ,QS.total_worker_time
        ,QS.last_worker_time
        ,QS.max_worker_time
        ,QS.min_worker_time 
    FROM sys.dm_exec_query_stats QS 
    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST 
    WHERE 1=1
    AND QS.last_execution_time > @begin_time
    AND QS.execution_count > @execute_count_limit
    --AND ST.text LIKE '%%' 
    --ORDER BY QS.execution_count DESC
)

SELECT 
    MAX(execution_count) max_execution_count
    ,[text]
FROM tbl
WHERE 1=1 
AND [text] NOT LIKE '%sp_MSupd_%' 
AND [text] NOT LIKE '%sp_MSins_%' 
AND [text] NOT LIKE '%sp_MSdel_%' 
GROUP BY [text]
ORDER BY 1 DESC

查找逻辑读取最高的存储过程

--查找逻辑读取最高的查询(存储过程)
SELECT TOP 25 
    p.name AS [存储过程]
    ,deps.total_logical_reads AS [逻辑读总次数] 
    ,deps.total_logical_reads / deps.execution_count AS [逻辑读平均次数]
    ,deps.execution_count [总执行次数]
    ,ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS [每秒调用次数] 
    ,deps.total_elapsed_time/1000/1000.0 AS [总消耗时长]
    ,deps.total_elapsed_time/1000/1000.0/deps.execution_count AS [平均消耗时长]
    ,deps.cached_time AS [缓存时间]
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
WHERE 1=1
AND deps.Database_id = DB_ID()
ORDER BY deps.total_elapsed_time DESC;

数据库性能优化

-- 排查历史慢查询
SELECT TOP 50
    DB_NAME(qt.dbid) AS [数据库]
    ,OBJECT_NAME(qt.objectid, qt.dbid) AS [对象名]
    ,qs.creation_time AS [创建时间]
    ,qs.last_execution_time AS [最近执行时间]
    ,qs.last_elapsed_time AS [最近执行耗时]
    ,qs.last_worker_time AS [最近CPU耗时]
    ,qs.last_rows AS [最近影响行数]
    ,qs.execution_count AS [执行次数]
    ,qs.total_elapsed_time AS [累计执行耗时]
    ,(qs.total_elapsed_time / qs.execution_count) AS [平均执行耗时]
    ,qs.max_elapsed_time AS [最大执行耗时]
    ,qs.total_worker_time AS [累计CPU耗时]
    ,(qs.total_worker_time / qs.execution_count) AS [平均CPU耗时]
    ,qs.max_worker_time AS [最大CPU耗时]
    ,(qs.total_logical_reads + qs.total_logical_writes) AS [累计逻辑读写]
    ,(qs.total_logical_reads + qs.total_logical_writes)/qs.execution_count AS [平均逻辑读写]
    ,qs.min_rows AS [最小影响行数]
    ,qs.max_rows AS [最大影响行数]
    ,qs.total_rows AS [累计影响行数]
    ,SUBSTRING(
        qt.text,
        (qs.statement_start_offset/2) + 1,     
        ((
        CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset
        )/2) + 1
    ) AS [独立查询]
    ,qt.text AS [父级查询]
    ,qp.query_plan AS [查询计划]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE 1=1
AND qt.dbid IS NOT NULL
AND DB_NAME(qt.dbid)!='msdb'
ORDER BY 4 DESC

-- 精简版 
SELECT TOP 100
    DB_NAME(qt.dbid) AS [数据库]
    ,OBJECT_NAME(qt.objectid, qt.dbid) AS [对象名]
    ,qs.execution_count AS [执行次数]
    ,qs.total_worker_time/1000/1000 AS [CPU总消耗秒数]
    ,qs.total_worker_time/qs.execution_count/1000/1000.0 AS [CPU平均消耗秒数]
    ,max_worker_time/1000/1000.0 AS [CPU最大消耗秒数]
    ,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 [剔除注释]
    ,qt.text [完整语句]
    ,last_execution_time AS [最后执行时间]
FROM sys.dm_exec_query_stats qs 
WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE 1=1
AND qs.execution_count > 0 
-- AND qs.total_worker_time/qs.execution_count/1000 > 1
AND OBJECT_NAME(qt.objectid, qt.dbid) IS NOT NULL
AND DB_NAME(qt.dbid) != 'msdb'
ORDER BY qs.execution_count DESC

The user does not have permission to perform this action.

排查历史慢查询

建议先优化慢查询，然后根据IOPS、QPS、CPU等指标决定是否升级实例规格。

--排查历史慢查询
SELECT TOP 50
    (qs.total_logical_reads + qs.total_logical_writes) AS [逻辑读写]
    ,(qs.total_logical_reads + qs.total_logical_writes)/qs.execution_count AS [平均读写]
    ,qs.execution_count AS [执行次数]
    ,SUBSTRING(
        qt.text,
        (qs.statement_start_offset/2) + 1,     
        ((
        CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset
        )/2) + 1
    ) AS [独立查询]
    ,qt.text AS [父级查询]
    ,DB_NAME(qt.dbid) AS [数据库]
    ,qp.query_plan AS [查询计划]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE 1=1
AND qt.dbid IS NOT NULL
AND DB_NAME(qt.dbid)!='msdb'
ORDER BY 2 DESC

查询当前正在执行的慢查询

-- 查询当前正在执行的慢查询
SELECT TOP 10
    ST.transaction_id AS TransactionID
    ,ST.session_id
    ,DB_NAME(DT.database_id) AS DatabaseName
    ,SES.host_name
    ,SES.login_name
    ,SES.status
    ,AT.transaction_begin_time AS TransactionStartTime
    ,S.text 
    ,C.connect_time 
    ,DATEDIFF(second, AT.transaction_begin_time, GETDATE()) "exec_time(s)" 
    ,DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time
    ,CASE AT.transaction_type
    WHEN 1 THEN 'Read/Write Transaction'
    WHEN 2 THEN 'Read-Only Transaction'
    WHEN 3 THEN 'System Transaction'
    WHEN 4 THEN 'Distributed Transaction'
    END AS TransactionType 
    ,CASE AT.transaction_state
    WHEN 0 THEN 'Transaction Not Initialized'
    WHEN 1 THEN 'Transaction Initialized & Not Started'
    WHEN 2 THEN 'Active Transaction'
    WHEN 3 THEN 'Transaction Ended'
    WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
    WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
    WHEN 6 THEN 'Transaction Committed'
    WHEN 7 THEN 'Transaction Rolling Back'
    WHEN 8 THEN 'Transaction Rolled Back'
    END AS TransactionState
FROM sys.dm_tran_session_transactions AS ST
INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
LEFT JOIN sys.dm_exec_connections AS C ON st.session_id = C.session_id
LEFT JOIN sys.dm_exec_sessions AS SES ON C.session_id = SES.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_Handle) S
WHERE 1=1
AND DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2

查询最近修改的存储过程

SELECT 
  Name
  ,Create_date
  ,Modify_Date 
FROM sys.objects 
WHERE 1=1
AND TYPE in ('U','P', 'V','F', 'TR', 'FN') 
ORDER BY Modify_Date DESC;

查询每秒死锁数量

--查询每秒死锁数量
SELECT *
FROM sys.dm_os_performance_counters
WHERE 1=1
AND counter_name LIKE 'Number of Deadlocksc%';

通过等待类型分析耗时操作

--查询等待类型
SELECT TOP 10 * FROM SYS.dm_os_wait_stats ORDER BY wait_time_ms DESC
--通过等待类型分析耗时操作

查看内存结构

SELECT
    (physical_memory_in_use_kb/1024) AS MemoryUsed
    ,(locked_page_allocations_kb/1024) AS LockedPagesUsed
    ,(total_virtual_address_space_kb/1024) AS VASTotal
    ,process_physical_memory_low
    ,process_virtual_memory_low 
FROM sys.dm_os_process_memory;

查看数据库连接数

SELECT 
* 
FROM sysprocesses 
WHERE 1=1
AND dbid IN(SELECT dbid FROM sysdatabases WHERE 1=1 AND name='WHGameUserDB')

检索索引碎片

SELECT  
    DB_NAME(ps.database_id) AS [DbName] 
    ,OBJECT_NAME(ps.OBJECT_ID) AS [DbObject]
    ,ps.index_id AS [IndexID]
    ,b.name 
    ,ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
WHERE 1=1
AND ps.database_id = DB_ID('ReportServerTempDB')
ORDER BY ps.avg_fragmentation_in_percent DESC