使用动态管理视图监视性能 Azure SQL 数据库Monitoring performance Azure SQL Database using dynamic management views

Azure SQL 数据库支持通过一部分动态管理视图来诊断性能问题,这些问题可能由查询受阻或长时间运行、资源瓶颈、不良查询计划等原因造成。Azure SQL Database enables a subset of dynamic management views to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor query plans, and so on. 本主题提供有关如何通过使用动态管理视图检测常见性能问题的信息。This topic provides information on how to detect common performance problems by using dynamic management views.

SQL 数据库部分支持三种类别的动态管理视图:SQL Database partially supports three categories of dynamic management views:

  • 与数据库相关的动态管理视图。Database-related dynamic management views.
  • 与执行相关的动态管理视图。Execution-related dynamic management views.
  • 与事务相关的动态管理视图。Transaction-related dynamic management views.

有关动态管理视图的详细信息,请参阅 SQL Server 联机丛书中的动态管理视图和功能 (Transact-SQL)For detailed information on dynamic management views, see Dynamic Management Views and Functions (Transact-SQL) in SQL Server Books Online.

权限Permissions

在 SQL 数据库中,查询动态管理视图需要 VIEW DATABASE STATE 权限。In SQL Database, querying a dynamic management view requires VIEW DATABASE STATE permissions. VIEW DATABASE STATE 权限返回有关当前数据库中的所有对象的信息。The VIEW DATABASE STATE permission returns information about all objects within the current database. 若要向特定数据库用户授予 VIEW DATABASE STATE 权限,请运行以下查询:To grant the VIEW DATABASE STATE permission to a specific database user, run the following query:

GRANT VIEW DATABASE STATE TO database_user;

在本地 SQL Server 的实例中,动态管理视图会返回服务器状态信息。In an instance of on-premises SQL Server, dynamic management views return server state information. 在 SQL 数据库中,这些视图会返回只与当前逻辑数据库相关的信息。In SQL Database, they return information regarding your current logical database only.

本文包含一组 DMV 查询,你可以使用 SQL Server Management Studio 或 Azure Data Studio 执行这些查询,以检测以下类型的查询性能问题:This article contains a collection of DMV queries that you can execute using SQL Server Management Studio or Azure Data Studio to detect the following types of query performance issues:

识别 CPU 性能问题Identify CPU performance issues

如果 CPU 使用率长时间超过 80%,请考虑以下故障排除步骤:If CPU consumption is above 80% for extended periods of time, consider the following troubleshooting steps:

目前正在发生 CPU 问题The CPU issue is occurring now

如果目前已出现问题,则可能存在两种情况:If issue is occurring right now, there are two possible scenarios:

存在许多单独的查询,它们共同消耗了很多的 CPU 资源Many individual queries that cumulatively consume high CPU

使用以下查询来识别消耗量靠前的查询哈希:Use the following query to identify top query hashes:

PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
SELECT TOP 10 GETDATE() runtime, *
FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"
    FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text
          FROM sys.dm_exec_requests AS req
                CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
    GROUP BY query_hash) AS t
ORDER BY Total_Request_Cpu_Time_Ms DESC;

消耗 CPU 的长时间运行的查询仍在运行Long running queries that consume CPU are still running

使用以下查询来识别这些查询:Use the following query to identify these queries:

PRINT '--top 10 Active CPU Consuming Queries by sessions--';
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;
GO

过去发生了 CPU 问题The CPU issue occurred in the past

如果问题是在过去发生的,你想要执行根本原因分析,请使用查询存储If the issue occurred in the past and you want to do root cause analysis, use Query Store. 拥有数据库访问权限的用户可以使用 T-SQL 对查询存储数据执行查询。Users with database access can use T-SQL to query Query Store data. 查询存储的默认配置使用 1 小时粒度。Query Store default configurations use a granularity of 1 hour. 使用以下查询来查看 CPU 消耗量较高的查询的活动。Use the following query to look at activity for high CPU consuming queries. 此查询将返回 CPU 消耗量最高的 15 个查询。This query returns the top 15 CPU consuming queries. 请记得更改 rsi.start_time >= DATEADD(hour, -2, GETUTCDATE()Remember to change rsi.start_time >= DATEADD(hour, -2, GETUTCDATE():

-- Top 15 CPU consuming queries by query hash
-- note that a query  hash can have many query id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
                       FROM sys.query_store_query_text AS qt
                            JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                            JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                            JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                            JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
                       WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                       GROUP BY q.query_hash), OrderedCPU AS (SELECT query_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
                                                              FROM AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec DESC;

识别出有问题的查询后,可以优化这些查询,以降低 CPU 利用率。Once you identify the problematic queries, it's time to tune those queries to reduce CPU utilization. 如果没有时间优化查询,可以选择升级数据库的 SLO 来解决问题。If you don't have time to tune the queries, you may also choose to upgrade the SLO of the database to work around the issue.

识别 IO 性能问题Identify IO performance issues

识别 IO 性能问题时,与 IO 问题最相关的等待类型包括:When identifying IO performance issues, the top wait types associated with IO issues are:

  • PAGEIOLATCH_*

    数据文件 IO 问题(包括 PAGEIOLATCH_SHPAGEIOLATCH_EXPAGEIOLATCH_UP)。For data file IO issues (including PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). 如果等待类型名称中包含 IO,则此类型与某个 IO 问题相关。If the wait type name has IO in it, it points to an IO issue. 如果页面闩锁等待名称中不包含 IO,则此类型与不同类型的问题(例如 tempdb 争用)相关。If there is no IO in the page latch wait name, it points to a different type of problem (for example, tempdb contention).

  • WRITE_LOG

    事务日志 IO 问题。For transaction log IO issues.

如果目前已经出现了 IO 问题If the IO issue is occurring right now

使用 sys.dm_exec_requestssys.dm_os_waiting_tasks 查看 wait_typewait_timeUse the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time.

识别数据和日志 IO 用量Identify data and log IO usage

使用以下查询来识别数据和日志 IO 用量。Use the following query to identify data and log IO usage. 如果数据或日志 IO 用量超过 80%,则表示用户对 SQL 数据库服务层级使用了可用 IO。If the data or log IO is above 80%, it means users have used the available IO for the SQL DB service tier.

SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

如果已达到 IO 限制,可使用两种选项:If the IO limit has been reached, you have two options:

  • 选项 1:升级计算大小或服务层级Option 1: Upgrade the compute size or service tier
  • 选项 2:识别并优化 IO 消耗量最大的查询。Option 2: Identify and tune the queries consuming the most IO.

对于选项 2,可以针对缓冲区相关 IO 的查询存储使用以下查询,以查看过去两个小时的跟踪活动:For option 2, you can use the following query against Query Store for buffer-related IO to view the last two hours of tracked activity:

-- top queries that waited on buffer
-- note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
                    FROM sys.query_store_query_text AS qt
                         JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                         JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                         JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
                         JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
                    WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                    GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS RN
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO

查看 WRITELOG 等待类型的日志 IO 总计View total log IO for WRITELOG waits

如果等待类型为 WRITELOG,请使用以下查询按语句查看日志 IO 总计:If the wait type is WRITELOG, use the following query to view total log IO by statement:

-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
           MAX(max_logical_io_reads) max_logical_reads,
           COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
           COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Aborted' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Aborted_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Regular' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Regular_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Exception' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Exception_Execution_Count,
           SUM(count_executions) AS total_executions,
           MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
        JOIN sys.query_store_query AS q
            ON qt.query_text_id = q.query_text_id
        JOIN sys.query_store_plan AS p
            ON q.query_id = p.query_id
        JOIN sys.query_store_runtime_stats AS rs
            ON rs.plan_id = p.plan_id
        JOIN sys.query_store_runtime_stats_interval AS rsi
            ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rs.execution_type_desc IN ( 'Regular', 'Aborted', 'Exception' )
          AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE())
    GROUP BY q.query_hash),
     OrderedLogUsed
AS (SELECT query_hash,
           total_log_bytes_used,
           number_of_distinct_plans,
           number_of_distinct_query_ids,
           total_executions,
           Aborted_Execution_Count,
           Regular_Execution_Count,
           Exception_Execution_Count,
           sampled_query_text,
           ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS RN
    FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
       OD.number_of_distinct_plans,
       OD.number_of_distinct_query_ids,
       OD.total_executions,
       OD.Aborted_Execution_Count,
       OD.Regular_Execution_Count,
       OD.Exception_Execution_Count,
       OD.sampled_query_text,
       OD.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO

识别 tempdb 性能问题Identify tempdb performance issues

识别 IO 性能问题时,与 tempdb 问题最相关的等待类型是 PAGELATCH_*(而不是 PAGEIOLATCH_*)。When identifying IO performance issues, the top wait types associated with tempdb issues is PAGELATCH_* (not PAGEIOLATCH_*). 但是,出现 PAGELATCH_* 等待并不总是意味着发生了 tempdb 争用。However, PAGELATCH_* waits do not always mean you have tempdb contention. 这种等待可能还意味着,由于并发请求面向相同的数据页面,发生了用户对象数据页面争用。This wait may also mean that you have user-object data page contention due to concurrent requests targeting the same data page. 若要进一步确认 tempdb 争用,请使用 sys.dm_exec_requests 确认 wait_resource 值是否以 2:x:y 开头,其中,tempdb 是数据库 ID,x 是文件 ID,y 是页 ID。To further confirm tempdb contention, use sys.dm_exec_requests to confirm that the wait_resource value begins with 2:x:y where 2 is tempdb is the database ID, x is the file ID, and y is the page ID.

对于 tempdb 争用,常用的方法是减少依赖于 tempdb 的重写应用程序代码。For tempdb contention, a common method is to reduce or re-write application code that relies on tempdb. 常见的 tempdb 使用区域包括:Common tempdb usage areas include:

  • 临时表Temp tables
  • 表变量Table variables
  • 表值参数Table-valued parameters
  • 版本存储使用(特别是与长时间运行的事务关联的用法)Version store usage (specifically associated with long running transactions)
  • 包含使用排序、哈希联接和 spool 的查询计划的查询Queries that have query plans that use sorts, hash joins, and spools

使用表变量和临时表的最相关查询Top queries that use table variables and temporary tables

使用以下查询来识别使用表变量和临时表的最相关查询:Use the following query to identify top queries that use table variables and temporary tables:

SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
     CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') 'table'
INTO #tmp2
FROM(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
    CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO

SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM(SELECT DISTINCT plan_handle, [Database], [Schema], [table]
     FROM #tmp2
     WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
    JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;

识别长时间运行的事务Identify long running transactions

使用以下查询来识别长时间运行的事务。Use the following query to identify long running transactions. 长时间运行的事务会阻止版本存储的清理。Long running transactions prevent version store cleanup.

SELECT DB_NAME(dtr.database_id) 'database_name',
       sess.session_id,
       atr.name AS 'tran_name',
       atr.transaction_id,
       transaction_type,
       transaction_begin_time,
       database_transaction_begin_time transaction_state,
       is_user_transaction,
       sess.open_transaction_count,
       LTRIM(RTRIM(REPLACE(
                              REPLACE(
                                         SUBSTRING(
                                                      SUBSTRING(
                                                                   txt.text,
                                                                   (req.statement_start_offset / 2) + 1,
                                                                   ((CASE req.statement_end_offset
                                                                         WHEN -1 THEN
                                                                             DATALENGTH(txt.text)
                                                                         ELSE
                                                                             req.statement_end_offset
                                                                     END - req.statement_start_offset
                                                                    ) / 2
                                                                   ) + 1
                                                               ),
                                                      1,
                                                      1000
                                                  ),
                                         CHAR(10),
                                         ' '
                                     ),
                              CHAR(13),
                              ' '
                          )
                  )
            ) Running_stmt_text,
       recenttxt.text 'MostRecentSQLText'
FROM sys.dm_tran_active_transactions AS atr
    INNER JOIN sys.dm_tran_database_transactions AS dtr
        ON dtr.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_tran_session_transactions AS sess
        ON sess.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_exec_requests AS req
        ON req.session_id = sess.session_id
           AND req.transaction_id = sess.transaction_id
    LEFT JOIN sys.dm_exec_connections AS conn
        ON sess.session_id = conn.session_id
    OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt
WHERE atr.transaction_type != 2
      AND sess.session_id != @@spid
ORDER BY start_time ASC;

识别内存授予等待性能问题Identify memory grant wait performance issues

如果最相关等待类型为 RESOURCE_SEMAHPORE,但你未遇到 CPU 使用率偏高的问题,则可能是出现了内存授予等待问题。If your top wait type is RESOURCE_SEMAHPORE and you don't have a high CPU usage issue, you may have a memory grant waiting issue.

确定 RESOURCE_SEMAHPORE 等待是否为最相关的等待Determine if a RESOURCE_SEMAHPORE wait is a top wait

使用以下查询来确定 RESOURCE_SEMAHPORE 等待是否为最相关的等待Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait

SELECT wait_type,
       SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
    JOIN sys.dm_exec_sessions AS sess
        ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;

识别内存消耗量较高的语句Identity high memory-consuming statements

使用以下查询来识别内存消耗量较高的语句:Use the following query to identify high memory-consuming statements:

SELECT IDENTITY(INT, 1, 1) rowId,
    CAST(query_plan AS XML) query_plan,
    p.query_id
INTO #tmp
FROM sys.query_store_plan AS p
    JOIN sys.query_store_runtime_stats AS r
        ON p.plan_id = r.plan_id
    JOIN sys.query_store_runtime_stats_interval AS i
        ON r.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE start_time > '2018-10-11 14:00:00.0000000'
      AND end_time < '2018-10-17 20:00:00.0000000';
GO
;WITH cte
AS (SELECT query_id,
        query_plan,
        m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory
    FROM #tmp AS t
        CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) )
SELECT TOP 50
    cte.query_id,
    t.query_sql_text,
    cte.query_plan,
    CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB
FROM cte
    JOIN sys.query_store_query AS q
        ON cte.query_id = q.query_id
    JOIN sys.query_store_query_text AS t
        ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;

识别最活跃的 10 个内存授予Identify the top 10 active memory grants

使用以下查询来识别最活跃的 10 个内存授予:Use the following query to identify the top 10 active memory grants:

SELECT TOP 10
    CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,
       r.session_id,
       r.blocking_session_id,
       r.cpu_time,
       r.total_elapsed_time,
       r.reads,
       r.writes,
       r.logical_reads,
       r.row_count,
       wait_time,
       wait_type,
       r.command,
       OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',
       LTRIM(RTRIM(REPLACE(
                              REPLACE(
                                         SUBSTRING(
                                                      SUBSTRING(
                                                                   text,
                                                                   (r.statement_start_offset / 2) + 1,
                                                                   ((CASE r.statement_end_offset
                                                                         WHEN -1 THEN
                                                                             DATALENGTH(text)
                                                                         ELSE
                                                                             r.statement_end_offset
                                                                     END - r.statement_start_offset
                                                                    ) / 2
                                                                   ) + 1
                                                               ),
                                                      1,
                                                      1000
                                                  ),
                                         CHAR(10),
                                         ' '
                                     ),
                              CHAR(13),
                              ' '
                          )
                  )
            ) stmt_text,
       mg.dop,                                               --Degree of parallelism
       mg.request_time,                                      --Date and time when this query requested the memory grant.
       mg.grant_time,                                        --NULL means memory has not been granted
       mg.requested_memory_kb / 1024.0 requested_memory_mb,  --Total requested amount of memory in megabytes
       mg.granted_memory_kb / 1024.0 AS granted_memory_mb,   --Total amount of memory actually granted in megabytes. NULL if not granted
       mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.
       max_used_memory_kb / 1024.0 AS max_used_memory_mb,
       mg.query_cost,                                        --Estimated query cost.
       mg.timeout_sec,                                       --Time-out in seconds before this query gives up the memory grant request.
       mg.resource_semaphore_id,                             --Non-unique ID of the resource semaphore on which this query is waiting.
       mg.wait_time_ms,                                      --Wait time in milliseconds. NULL if the memory is already granted.
       CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
           WHEN 1 THEN
               'Yes'
           WHEN 0 THEN
               'No'
           ELSE
               'Memory has been granted'
       END AS 'Next Candidate for Memory Grant',
       qp.query_plan
FROM sys.dm_exec_requests AS r
    JOIN sys.dm_exec_query_memory_grants AS mg
        ON r.session_id = mg.session_id
           AND r.request_id = mg.request_id
    CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC;

计算数据库和对象大小Calculating database and objects sizes

下面的查询将返回数据库的大小(以 MB 为单位):The following query returns the size of your database (in megabytes):

-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO

下面的查询将返回数据库中各个对象的大小(以 MB 为单位):The following query returns the size of individual objects (in megabytes) in your database:

-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO

监视连接Monitoring connections

可以使用 sys.dm_exec_connections 视图检索与特定 Azure SQL 数据库服务器建立的连接的相关信息和每个连接的详细信息。You can use the sys.dm_exec_connections view to retrieve information about the connections established to a specific Azure SQL Database server and the details of each connection. 此外,sys.dm_exec_sessions 视图在检索有关所有活动用户连接和内部任务的信息时非常有用。In addition, the sys.dm_exec_sessions view is helpful when retrieving information about all active user connections and internal tasks. 下面的查询将检索当前连接上的信息:The following query retrieves information on the current connection:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

Note

在执行 sys.dm_exec_requestssys.dm_exec_sessions views 视图时,如果具有对数据库的 VIEW DATABASE STATE 权限,会看到数据库上所有正在执行的会话;否则,只会看到当前会话。When executing the sys.dm_exec_requests and sys.dm_exec_sessions views, if you have VIEW DATABASE STATE permission on the database, you see all executing sessions on the database; otherwise, you see only the current session.

监视资源使用情况Monitor resource use

可以使用 SQL 数据库 Query Performance InsightQuery Store 监视资源使用情况。You can monitor resource usage using SQL Database Query Performance Insight and Query Store.

也可以使用以下两个视图来监视使用情况:You can also monitor usage using these two views:

sys.dm_db_resource_statssys.dm_db_resource_stats

可以在每个 SQL 数据库中使用 sys.dm_db_resource_stats 视图。You can use the sys.dm_db_resource_stats view in every SQL database. Sys.dm_db_resource_stats 视图显示相对于服务层级的最新资源使用数据。The sys.dm_db_resource_stats view shows recent resource use data relative to the service tier. CPU、数据 IO、日志写入以及内存的平均百分比每 15 秒记录一次,并保留 1 小时。Average percentages for CPU, data IO, log writes, and memory are recorded every 15 seconds and are maintained for 1 hour.

由于此视图提供了更精细的资源使用情况,因此首先将 sys.dm_db_resource_stats 用于任何当前状态分析或故障排除。Because this view provides a more granular look at resource use, use sys.dm_db_resource_stats first for any current-state analysis or troubleshooting. 例如,此查询显示过去一小时的当前数据库平均和最大资源使用情况:For example, this query shows the average and maximum resource use for the current database over the past hour:

SELECT  
    AVG(avg_cpu_percent) AS 'Average CPU use in percent',
    MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
    AVG(avg_data_io_percent) AS 'Average data IO in percent',
    MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
    AVG(avg_log_write_percent) AS 'Average log write use in percent',
    MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
    AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
    MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats;  

有关其他查询,请参阅 sys.dm_db_resource_stats 中的示例。For other queries, see the examples in sys.dm_db_resource_stats.

sys.resource_statssys.resource_stats

master 数据库中的 Sys.resource_stats 视图包含可帮助监视 SQL 数据库在特定服务层级和计算大小的性能。The sys.resource_stats view in the master database has additional information that can help you monitor the performance of your SQL database at its specific service tier and compute size. 每 5 分钟收集一次数据,并且会保留大约 14 天。The data is collected every 5 minutes and is maintained for approximately 14 days. 此视图可用于 SQL 数据库使用资源的方式的长期历史分析。This view is useful for a longer-term historical analysis of how your SQL database uses resources.

下图显示一周内每小时的 P2 计算大小高级数据库的 CPU 资源使用情况。The following graph shows the CPU resource use for a Premium database with the P2 compute size for each hour in a week. 此图形从周一开始,显示 5 个工作日,并显示周末(应用程序上很少发生的情况)。This graph starts on a Monday, shows 5 work days, and then shows a weekend, when much less happens on the application.

SQL 数据库资源使用情况

从数据而言,此数据库当前有一个峰值 CPU 负载刚好超过相对于 P2 计算大小的 50% CPU 使用率(星期二中午)。From the data, this database currently has a peak CPU load of just over 50 percent CPU use relative to the P2 compute size (midday on Tuesday). 如果 CPU 是应用程序资源配置文件的决定因素,可以决定 P2 是适当的计算大小以保证工作负荷始终适合。If CPU is the dominant factor in the application's resource profile, then you might decide that P2 is the right compute size to guarantee that the workload always fits. 如果希望应用程序可以随时间增长,最好具有额外的资源缓冲,以便应用程序不会达到性能级别的限制。If you expect an application to grow over time, it's a good idea to have an extra resource buffer so that the application doesn't ever reach the performance-level limit. 如果增加计算大小,则有助于避免当数据库没有足够能力有效处理请求(尤其是在易受延迟影响的环境中)时向客户显示错误。If you increase the compute size, you can help avoid customer-visible errors that might occur when a database doesn't have enough power to process requests effectively, especially in latency-sensitive environments. 一个示例是支持应用程序根据数据库调用结果绘制网页的数据库。An example is a database that supports an application that paints webpages based on the results of database calls.

其他应用程序类型可能以不同的方式解释同一图形。Other application types might interpret the same graph differently. 例如,如果某个应用程序尝试每天处理工资数据并使用相同的图表,则在 P1 计算大小也许就能让此类“批处理作业”模型正常工作。For example, if an application tries to process payroll data each day and has the same chart, this kind of "batch job" model might do fine at a P1 compute size. P1 计算大小有 100 个 DTU,P2 计算大小有 200 个 DTU。The P1 compute size has 100 DTUs compared to 200 DTUs at the P2 compute size. P1 计算大小提供的性能是 P2 计算大小的一半。The P1 compute size provides half the performance of the P2 compute size. 因此,P2 中 50% 的 CPU 使用率相当于 P1 中 100 % 的 CPU 使用率。So, 50 percent of CPU use in P2 equals 100 percent CPU use in P1. 如果应用程序没有超时,则作业耗时 2 小时或 2.5 小时完成并不重要(如果今天完成)。If the application does not have timeouts, it might not matter if a job takes 2 hours or 2.5 hours to finish, if it gets done today. 此类别中的应用程序可能使用 P1 计算大小。An application in this category probably can use a P1 compute size. 可以充分利用一天之中资源使用率较低的时间段,以便“大峰值”可以溢出到当天稍后的某个低谷。You can take advantage of the fact that there are periods of time during the day when resource use is lower, so that any "big peak" might spill over into one of the troughs later in the day. 只要作业可以每天按时完成,P1 计算大小就适用于该类型的应用程序(且节省费用)。The P1 compute size might be good for that kind of application (and save money), as long as the jobs can finish on time each day.

Azure SQL 数据库在每个服务器的 master 数据库的 sys.resource_stats 视图中,公开每个活动数据库的资源耗用信息。Azure SQL Database exposes consumed resource information for each active database in the sys.resource_stats view of the master database in each server. 表中的数据以 5 分钟为间隔收集而得。The data in the table is aggregated for 5-minute intervals. 对于“基本”、“标准”和“高级”服务层级,数据可能需要再耗费 5 分钟才会出现在表中,以使此数据更有利于历史分析而非接近实时的分析。With the Basic, Standard, and Premium service tiers, the data can take more than 5 minutes to appear in the table, so this data is more useful for historical analysis rather than near-real-time analysis. 查询 sys.resource_stats 视图,以查看数据库的最近历史记录和验证你选择的预留是否提供了所需的性能。Query the sys.resource_stats view to see the recent history of a database and to validate whether the reservation you chose delivered the performance you want when needed.

Note

必须连接到 SQL 数据库服务器的 master 数据库,才能查询下面示例中的 sys.resource_stats 。You must be connected to the master database of your SQL Database server to query sys.resource_stats in the following examples.

此示例演示如何公开此视图中的数据:This example shows you how the data in this view is exposed:

SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC

sys.resource_stats 目录视图

下面的示例演示可以用不同方式使用 sys.resource_stats 目录视图以获取有关 SQL 数据库使用资源的方式的信息:The next example shows you different ways that you can use the sys.resource_stats catalog view to get information about how your SQL database uses resources:

  1. 若要查看数据库 userdb1 过去一周的资源使用情况,可以运行此查询:To look at the past week's resource use for the database userdb1, you can run this query:

    SELECT *
    FROM sys.resource_stats
    WHERE database_name = 'userdb1' AND
        start_time > DATEADD(day, -7, GETDATE())
    ORDER BY start_time DESC;
    
  2. 若要评估工作负荷与计算大小的适合程度,需要向下钻取资源指标的每个方面:CPU、读取数、写入数、辅助进程数和会话数。To evaluate how well your workload fits the compute size, you need to drill down into each aspect of the resource metrics: CPU, reads, writes, number of workers, and number of sessions. 下面是使用 sys.resource_stats 的修订查询,用于报告这些资源度量值的平均值和最大值:Here's a revised query using sys.resource_stats to report the average and maximum values of these resource metrics:

    SELECT
        avg(avg_cpu_percent) AS 'Average CPU use in percent',
        max(avg_cpu_percent) AS 'Maximum CPU use in percent',
        avg(avg_data_io_percent) AS 'Average physical data IO use in percent',
        max(avg_data_io_percent) AS 'Maximum physical data IO use in percent',
        avg(avg_log_write_percent) AS 'Average log write use in percent',
        max(avg_log_write_percent) AS 'Maximum log write use in percent',
        avg(max_session_percent) AS 'Average % of sessions',
        max(max_session_percent) AS 'Maximum % of sessions',
        avg(max_worker_percent) AS 'Average % of workers',
        max(max_worker_percent) AS 'Maximum % of workers'
    FROM sys.resource_stats
    WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE());
    
  3. 使用每个资源指标的平均值和最大值信息,可以评估工作负荷与所选计算大小的适合程度。With this information about the average and maximum values of each resource metric, you can assess how well your workload fits into the compute size you chose. 通常情况下,sys.resource_stats 中的平均值提供了一个用于目标大小的良好基准。Usually, average values from sys.resource_stats give you a good baseline to use against the target size. 它应该是主要测量标杆。It should be your primary measurement stick. 例如,你可能正在使用 S2 计算大小的“标准”服务层级。For an example, you might be using the Standard service tier with S2 compute size. CPU 和 IO 读写的平均使用百分比低于 40%,平均辅助角色数低于 50,平均会话数低于 200。The average use percentages for CPU and IO reads and writes are below 40 percent, the average number of workers is below 50, and the average number of sessions is below 200. 工作负荷可能适合 S1 计算大小。Your workload might fit into the S1 compute size. 很容易查看数据库是否在辅助角色和会话限制内。It's easy to see whether your database fits in the worker and session limits. 若要查看数据库是否适合 CPU 和读写数等更小的计算大小,请将更小计算大小的 DTU 数除以当前计算大小的 DTU 数,并将结果乘以 100:To see whether a database fits into a lower compute size with regards to CPU, reads, and writes, divide the DTU number of the lower compute size by the DTU number of your current compute size, and then multiply the result by 100:

    S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40

    结果是以百分比表示的两个计算大小之间的相对性能差异。The result is the relative performance difference between the two compute sizes in percentage. 如果资源使用不超出此量,则工作负荷可能适合更低的计算大小。If your resource use doesn't exceed this amount, your workload might fit into the lower compute size. 但是,需要查看资源用量值的所有范围,并按百分比确定数据库工作负荷适合更小计算大小的频率。However, you need to look at all ranges of resource use values, and determine, by percentage, how often your database workload would fit into the lower compute size. 以下查询会根据此示例中计算得出的 40% 阈值,输出每个资源维度的适合百分比:The following query outputs the fit percentage per resource dimension, based on the threshold of 40 percent that we calculated in this example:

     SELECT
         (COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'CPU Fit Percent',
         (COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'Log Write Fit Percent',
         (COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'Physical Data IO Fit Percent'
     FROM sys.resource_stats
     WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE());
    

    可以根据数据库服务层级的情况来确定工作负荷是否适合更小的计算大小。Based on your database service tier, you can decide whether your workload fits into the lower compute size. 如果数据库工作负荷目标为 99.9%,而上述查询针对所有三个资源维度返回的值大于 99.9%,则工作负荷可能适合更小的计算大小。If your database workload objective is 99.9 percent and the preceding query returns values greater than 99.9 percent for all three resource dimensions, your workload likely fits into the lower compute size.

    查看适合性百分比还可以深入分析是否应转到下一个更大的计算大小以满足目标。Looking at the fit percentage also gives you insight into whether you should move to the next higher compute size to meet your objective. 例如,userdb1 显示过去一周的如下 CPU 使用率:For example, userdb1 shows the following CPU use for the past week:

    平均 CPU 百分比Average CPU percent 最大 CPU 百分比Maximum CPU percent
    24.524.5 100.00100.00

    平均 CPU 大约是计算大小限制的四分之一,这意味着它很适合数据库的计算大小限制。The average CPU is about a quarter of the limit of the compute size, which would fit well into the compute size of the database. 但是,最大值显示该数据库达到了计算大小的限制。But, the maximum value shows that the database reaches the limit of the compute size. 在这种情况下,是否需要转到下一个更大的计算大小?Do you need to move to the next higher compute size? 查看工作负荷达到 100% 的次数,并将这种情况与数据库工作负荷目标进行比较。Look at how many times your workload reaches 100 percent, and then compare it to your database workload objective.

    SELECT
        (COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'CPU fit percent'
        ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'Log write fit percent'
        ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) AS 'Physical data IO fit percent'
        FROM sys.resource_stats
        WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE());
    

    如果对于三个资源维度中的任何一个维度,此查询返回的值小于 99.9%,请考虑转到下一个更大的计算大小,或使用应用程序优化技术来减少 SQL 数据库上的负载。If this query returns a value less than 99.9 percent for any of the three resource dimensions, consider either moving to the next higher compute size or use application-tuning techniques to reduce the load on the SQL database.

  4. 本练习还应将未来预计的工作负荷增加考虑在内。This exercise also considers your projected workload increase in the future.

对于弹性池,可以使用本节中所述的方法来监视池中的单个数据库。For elastic pools, you can monitor individual databases in the pool with the techniques described in this section. 但你还可以在总体上监视池。But you can also monitor the pool as a whole. 有关信息,请参阅监视和管理弹性池For information, see Monitor and manage an elastic pool.

最大并发请求数Maximum concurrent requests

若要查看并发请求数,请在 SQL 数据库中运行以下 Transact-SQL 查询:To see the number of concurrent requests, run this Transact-SQL query on your SQL database:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R;

若要分析本地 SQL Server 数据库的工作负荷,请修改此查询以筛选要分析的特定数据库。To analyze the workload of an on-premises SQL Server database, modify this query to filter on the specific database you want to analyze. 例如,如果有一个名为 MyDatabase 的本地数据库,此 TRANSACT-SQL 查询返回该数据库中的并发请求计数:For example, if you have an on-premises database named MyDatabase, this Transact-SQL query returns the count of concurrent requests in that database:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R
INNER JOIN sys.databases D ON D.database_id = R.database_id
AND D.name = 'MyDatabase';

这只是某一时刻的快照。This is just a snapshot at a single point in time. 要更好地了解工作负荷和并发请求要求,需要在一段时间内收集多个样本。To get a better understanding of your workload and concurrent request requirements, you'll need to collect many samples over time.

最大并发登录数Maximum concurrent logins

可以通过分析用户和应用程序模式来了解登录频率。You can analyze your user and application patterns to get an idea of the frequency of logins. 还可以在测试环境中运行实际负荷,确保不会超过本文所讨论的这样或那样的限制。You also can run real-world loads in a test environment to make sure that you're not hitting this or other limits we discuss in this article. 无法通过单一查询或动态管理视图 (DMV) 了解并发登录计数或历史记录。There isn't a single query or dynamic management view (DMV) that can show you concurrent login counts or history.

如果多个客户端使用相同的连接字符串,该服务也会对每个登录名进行身份验证。If multiple clients use the same connection string, the service authenticates each login. 如果 10 个用户使用相同的用户名和密码同时连接到数据库,将有 10 个并发登录。If 10 users simultaneously connect to a database by using the same username and password, there would be 10 concurrent logins. 此限制仅适用于登录和身份验证期间。This limit applies only to the duration of the login and authentication. 如果相同的 10 个用户按顺序连接到数据库,则并发登录数将不会大于 1。If the same 10 users connect to the database sequentially, the number of concurrent logins would never be greater than 1.

Note

此限制目前不适用于弹性池中的数据库。Currently, this limit does not apply to databases in elastic pools.

最大会话数Maximum sessions

若要查看当前活动会话数,请在 SQL 数据库中运行以下 Transact-SQL 查询:To see the number of current active sessions, run this Transact-SQL query on your SQL database:

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections

如果要分析本地 SQL Server 工作负荷,可以对查询进行修改,使之专注于特定的数据库。If you're analyzing an on-premises SQL Server workload, modify the query to focus on a specific database. 如果考虑将数据库移至 Azure SQL 数据库,此查询可帮助你确定该数据库可能的会话需求。This query helps you determine possible session needs for the database if you are considering moving it to Azure SQL Database.

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections C
INNER JOIN sys.dm_exec_sessions S ON (S.session_id = C.session_id)
INNER JOIN sys.databases D ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase'

同样,这些查询将返回时间点计数。Again, these queries return a point-in-time count. 如果在一段时间内收集多个样本,则可更好地了解会话使用情况。If you collect multiple samples over time, you'll have the best understanding of your session use.

对于 SQL 数据库分析,也可以通过查询 sys.resource_stats 视图并查看 active_session_count 列获取会话的历史统计信息。For SQL Database analysis, you can get historical statistics on sessions by querying the sys.resource_stats view and reviewing the active_session_count column.

监视查询性能Monitoring query performance

缓慢或长时间运行的查询会消耗大量系统资源。Slow or long running queries can consume significant system resources. 本部分演示如何使用动态管理视图来检测一些常见的查询性能问题。This section demonstrates how to use dynamic management views to detect a few common query performance problems. 一个较旧但仍很有帮助的故障排除参考是 Microsoft TechNet 上的排查 SQL Server 2008 中的性能问题这篇文章。An older but still helpful reference for troubleshooting, is the Troubleshooting Performance Problems in SQL Server 2008 article on Microsoft TechNet.

查找前 n 个查询Finding top N queries

下列示例返回了按平均 CPU 时间排名的前五个查询的信息。The following example returns information about the top five queries ranked by average CPU time. 该示例根据查询散列收集了查询,以便逻辑上等值的查询能够根据累积资源消耗分组。This example aggregates the queries according to their query hash, so that logically equivalent queries are grouped by their cumulative resource consumption.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
     MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
        SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1 THEN DATALENGTH(ST.text)
                ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

监视受阻的查询Monitoring blocked queries

缓慢或长时间运行的查询会造成过多的资源消耗并会导致查询受阻。Slow or long-running queries can contribute to excessive resource consumption and be the consequence of blocked queries. 受阻的原因可能是应用程序设计欠佳、查询计划不良、缺乏有用的索引等。The cause of the blocking can be poor application design, bad query plans, the lack of useful indexes, and so on. 可以使用 sys.dm_tran_locks 视图来获取有关 Azure SQL 数据库中当前锁定活动的信息。You can use the sys.dm_tran_locks view to get information about the current locking activity in your Azure SQL Database. 有关示例代码,请参阅 SQL Server 联机丛书中的 sys.dm_tran_locks (Transact-SQL)For example code, see sys.dm_tran_locks (Transact-SQL) in SQL Server Books Online.

监视查询计划Monitoring query plans

低效的查询计划还可能会增加 CPU 占用率。An inefficient query plan also may increase CPU consumption. 下面的示例使用 sys.dm_exec_query_stats 视图来确定哪一查询累积的 CPU 占用率最高。The following example uses the sys.dm_exec_query_stats view to determine which query uses the most cumulative CPU.

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 50
        qs.plan_handle,
        qs.total_worker_time
    FROM
        sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;

另请参阅See also

SQL 数据库简介Introduction to SQL Database