SQLServer語句執(zhí)行順序

• 步驟1. FROM <left_table>
FROM子句，組裝來自不同數(shù)據(jù)源的數(shù)據(jù)芥吟，對FROM子句中前后兩表執(zhí)行笛卡爾積生成虛擬表vt1讲冠。
• 步驟2. ON <join_condition>
  對虛擬表vt1應(yīng)用ON篩選器券膀，只有滿足<join_condition>連接條件為真時才被插入新虛擬表vt2
• 步驟3. <join_type> JOIN <right_table>
  如果指定了OUTER JOIN保留表(preserved table)中未找到的行，將行作為外部行添加到新的虛擬表vt2并生成新虛擬表vt3谷扣。
  如果FROM子句中包含兩個以上的表土全，則對上一個連接生成的結(jié)果表和下一個表重復(fù)執(zhí)行步驟1和步驟2，直到結(jié)束。
• 步驟4. WHERE <where_condition>
  對虛擬表vt3應(yīng)用WHERE篩選器裹匙，只有<where_condition>條件為真的行才能被插入新的虛擬表vt4瑞凑。
• 步驟5. GROUP BY <group_by_list>
  按GROUP BY子句中的列對虛擬表vt4中的行進(jìn)行分組并生成新的虛擬表vt5。
• 步驟6. WITH {cube | rollup}
  將超組(super groups)插入到新生成的虛擬表vt6
• 步驟7. HAVING <having_condition>
  對虛擬表vt6應(yīng)用HAVING篩選器概页，當(dāng)<having_condition>條件為真的組才被插入到虛擬表vt7籽御。
• 步驟8. SELECT
  處理SELECT列列表并生成虛擬表vt8
• 步驟9. DISTINCT
  將虛擬表vt8中重復(fù)的行剔除后生成虛擬表vt9
• 步驟10. ORDER BY <order_by_list>
  將虛擬表vt9的行，按ORDER BY子句中的列列表排序生成游標(biāo)vc10惰匙。
• 步驟11. TOP <top_specification> <select_list>
  從游標(biāo)vc10的開始處選擇指定數(shù)量或比例的行生成虛擬表vt11并返回調(diào)用者篱蝇。

優(yōu)化子查詢

WITH+ EXISTS

對存在子查詢的語句，可使用WITH...AS優(yōu)化替換徽曲，原則上是盡可能避免子查詢零截。
對于IN操作的語句，應(yīng)盡量使用EXISTS 替換 IN秃臣。

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

-- 優(yōu)化替換
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)

優(yōu)化連表查詢

JOIN鏈接最好不要超過5張表涧衙，若存在更新的大數(shù)據(jù)表，應(yīng)先放進(jìn)臨時表奥此，然后再使用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

查看語句執(zhí)行信息

-- 設(shè)置查看語句影響行數(shù)
SET NOCOUNT OFF
-- 設(shè)置查看執(zhí)行時間和CPU占用時間
SET STATISTICS TIME ON
-- 設(shè)置查詢對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.

查詢正在執(zhí)行的語句

注意：SqlServer數(shù)據(jù)庫時間以微秒為單位，即1秒=1000毫秒(ms)=1000*1000微秒稚虎。

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

-- 查詢正在執(zhí)行的語句
SELECT TOP 10   
    st.text AS [執(zhí)行語句]                 
    ,qs.execution_count [執(zhí)行次數(shù)] 
    ,qs.creation_time AS [執(zhí)行時間] 
    ,(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 [執(zhí)行耗時]
    ,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

查詢當(dāng)前緩存中批處理或存儲過程占用CPU資源的情況

-- 查詢當(dāng)前緩存中批處理或存儲過程占用CPU資源的情況
SELECT TOP 50   
    qs.sql_handle
    ,COUNT(*) AS [語句個數(shù)]
    ,SUM(qs.execution_count) AS [執(zhí)行次數(shù)]
    ,SUM(qs.total_worker_time)/1000.0 AS [CPU耗時]
    ,SUM(qs.total_elapsed_time)/1000.0 AS [執(zhí)行耗時]
    ,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

查詢執(zhí)行耗時的語句

--查詢執(zhí)行耗時的語句
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 [最后執(zhí)行時間]
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [IO平均讀寫次數(shù)]
    ,(total_worker_time / execution_count) / 1000000.0 AS [CPU平均執(zhí)行秒數(shù)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [平均執(zhí)行秒數(shù)]
    ,execution_count AS [執(zhí)行次數(shù)]
    ,qs.total_physical_reads AS [物理讀取次數(shù)]
    ,qs.total_logical_writes AS [邏輯寫入次數(shù)]
    ,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

查找執(zhí)行次數(shù)最多的SQL語句

--查找執(zhí)行次數(shù)最多的SQL語句
DECLARE @begin_time DATETIME = '2018-01-01 00:00:00';
DECLARE @execute_count_limit INT = 500;

WITH tbl AS (
    SELECT  
        --執(zhí)行次數(shù) 
        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 [邏輯讀總次數(shù)] 
    ,deps.total_logical_reads / deps.execution_count AS [邏輯讀平均次數(shù)]
    ,deps.execution_count [總執(zhí)行次數(shù)]
    ,ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS [每秒調(diào)用次數(shù)] 
    ,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;

數(shù)據(jù)庫性能優(yōu)化

-- 排查歷史慢查詢
SELECT TOP 50
    DB_NAME(qt.dbid) AS [數(shù)據(jù)庫]
    ,OBJECT_NAME(qt.objectid, qt.dbid) AS [對象名]
    ,qs.creation_time AS [創(chuàng)建時間]
    ,qs.last_execution_time AS [最近執(zhí)行時間]
    ,qs.last_elapsed_time AS [最近執(zhí)行耗時]
    ,qs.last_worker_time AS [最近CPU耗時]
    ,qs.last_rows AS [最近影響行數(shù)]
    ,qs.execution_count AS [執(zhí)行次數(shù)]
    ,qs.total_elapsed_time AS [累計執(zhí)行耗時]
    ,(qs.total_elapsed_time / qs.execution_count) AS [平均執(zhí)行耗時]
    ,qs.max_elapsed_time AS [最大執(zhí)行耗時]
    ,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 [最小影響行數(shù)]
    ,qs.max_rows AS [最大影響行數(shù)]
    ,qs.total_rows AS [累計影響行數(shù)]
    ,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 [數(shù)據(jù)庫]
    ,OBJECT_NAME(qt.objectid, qt.dbid) AS [對象名]
    ,qs.execution_count AS [執(zhí)行次數(shù)]
    ,qs.total_worker_time/1000/1000 AS [CPU總消耗秒數(shù)]
    ,qs.total_worker_time/qs.execution_count/1000/1000.0 AS [CPU平均消耗秒數(shù)]
    ,max_worker_time/1000/1000.0 AS [CPU最大消耗秒數(shù)]
    ,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 [最后執(zhí)行時間]
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.

排查歷史慢查詢

建議先優(yōu)化慢查詢撤嫩，然后根據(jù)IOPS、QPS蠢终、CPU等指標(biāo)決定是否升級實例規(guī)格序攘。

--排查歷史慢查詢
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 [執(zhí)行次數(shù)]
    ,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 [數(shù)據(jù)庫]
    ,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

查詢當(dāng)前正在執(zhí)行的慢查詢

-- 查詢當(dāng)前正在執(zhí)行的慢查詢
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;

查詢每秒死鎖數(shù)量

--查詢每秒死鎖數(shù)量
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
--通過等待類型分析耗時操作 

查看內(nèi)存結(jié)構(gòu)

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;

查看數(shù)據(jù)庫連接數(shù)

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