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
