如何找到 SQL Server 数据库的性能瓶颈

在开发和维护应用程序时，数据库的性能是非常重要的。如果数据库性能不佳，应用程序的响应速度将变慢，用户体验将受到影响。因此，找到数据库的性能瓶颈并解决它们是非常重要的。本文将介绍如何找到 SQL Server 数据库的性能瓶颈。

监视 SQL Server 数据库的性能

要找到 SQL Server 数据库的性能瓶颈，首先需要监视其性能。SQL Server 提供了多种监视工具，包括 SQL Server Profiler、SQL Server Management Studio 和动态管理视图（DMV）等。以下是一些常用的 DMV：

• sys.dm_os_performance_counters：提供有关操作系统性能计数器的信息。
• sys.dm_os_wait_stats：提供有关等待类型和等待时间的信息。
• sys.dm_exec_requests：提供有关当前执行的查询的信息。
• sys.dm_exec_sessions：提供有关当前会话的信息。

这些 DMV 可以帮助您了解数据库的性能状况，并找到性能瓶颈。

找到查询性能瓶颈

查询通常是数据库性能瓶颈的主要原因之一。以下是一些常见的查询性能问题：

• 查询缺乏适当的索引。
• 查询中使用了不必要的 JOIN。
• 查询中使用了子查询。
• 查询中使用了函数。
• 查询中使用了大量的 OR 运算符。

要找到查询性能瓶颈，可以使用 SQL Server Profiler 或 DMV。以下是一些示例代码：

找到查询缺乏适当的索引

// javascriptcn.com 代码示例
SELECT TOP 10
    t.name AS TableName,
    i.name AS IndexName,
    user_seeks + user_scans + user_lookups AS TotalReads,
    user_updates AS TotalWrites,
    user_seeks AS Seeks,
    user_scans AS Scans,
    user_lookups AS Lookups,
    user_updates AS Updates
FROM
    sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
    INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE
    (user_seeks + user_scans + user_lookups + user_updates) > 0
    AND t.is_ms_shipped = 0
ORDER BY
    TotalReads DESC

找到查询中使用了不必要的 JOIN

// javascriptcn.com 代码示例
SELECT TOP 10
    t.name AS TableName,
    p.rows AS Rows,
    SUM(a.total_worker_time) / SUM(a.execution_count) AS AvgCPUTime,
    SUM(qs.total_logical_reads + qs.total_logical_writes) / SUM(qs.execution_count) AS AvgIO,
    SUM(qs.total_logical_reads) / SUM(qs.execution_count) AS AvgReads,
    SUM(qs.total_logical_writes) / SUM(qs.execution_count) AS AvgWrites,
    MAX(qs.creation_time) AS LastExecutionTime
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    INNER JOIN sys.tables AS t ON t.object_id = st.objectid
    INNER JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id <= 1
    INNER JOIN sys.dm_exec_cached_plans AS cp ON cp.plan_handle = qs.plan_handle
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qs2(stmt)
    CROSS APPLY stmt.nodes('.//RelOp') AS ro(rel)
    CROSS APPLY ro.rel.nodes('.//IndexScan/Object[@Schema=\'dbo\']/@Name') AS ix(IndexName)
    CROSS APPLY qs2.stmt.nodes('.//Object[@Schema=\'dbo\']/@Name') AS tbl(TableName)
    CROSS APPLY qs2.stmt.nodes('.//ColumnReference[@Schema=\'dbo\']/@Name') AS col(ColumnName)
    OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp2
    OUTER APPLY qp2.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qs3(stmt2)
    OUTER APPLY stmt2.nodes('.//RelOp') AS ro2(rel2)
    OUTER APPLY ro2.rel2.nodes('.//IndexScan/Object[@Schema=\'dbo\']/@Name') AS ix2(IndexName2)
    OUTER APPLY qs3.stmt2.nodes('.//Object[@Schema=\'dbo\']/@Name') AS tbl2(TableName2)
    OUTER APPLY qs3.stmt2.nodes('.//ColumnReference[@Schema=\'dbo\']/@Name') AS col2(ColumnName2)
WHERE
    ix.IndexName IS NULL
    AND ix2.IndexName2 IS NOT NULL
    AND tbl.TableName = tbl2.TableName2
    AND col.ColumnName = col2.ColumnName2
GROUP BY
    t.name,
    p.rows
ORDER BY
    AvgIO DESC

找到查询中使用了子查询

// javascriptcn.com 代码示例
SELECT TOP 10
    st.text AS QueryText,
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.total_logical_reads / qs.execution_count AS AvgReads,
    qs.total_logical_writes / qs.execution_count AS AvgWrites,
    qs.execution_count AS ExecutionCount,
    qs.creation_time AS LastExecutionTime
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE
    st.text LIKE '%SELECT%FROM%WHERE%(%SELECT%'
    AND qs.total_worker_time / qs.execution_count > 100
ORDER BY
    AvgCPUTime DESC

找到查询中使用了函数

// javascriptcn.com 代码示例
SELECT TOP 10
    st.text AS QueryText,
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.total_logical_reads / qs.execution_count AS AvgReads,
    qs.total_logical_writes / qs.execution_count AS AvgWrites,
    qs.execution_count AS ExecutionCount,
    qs.creation_time AS LastExecutionTime
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE
    st.text LIKE '%FUNCTION_NAME%'
    AND qs.total_worker_time / qs.execution_count > 100
ORDER BY
    AvgCPUTime DESC

找到查询中使用了大量的 OR 运算符

// javascriptcn.com 代码示例
SELECT TOP 10
    st.text AS QueryText,
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.total_logical_reads / qs.execution_count AS AvgReads,
    qs.total_logical_writes / qs.execution_count AS AvgWrites,
    qs.execution_count AS ExecutionCount,
    qs.creation_time AS LastExecutionTime
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE
    st.text LIKE '%OR%'
    AND qs.total_worker_time / qs.execution_count > 100
ORDER BY
    AvgCPUTime DESC

找到磁盘 I/O 瓶颈

磁盘 I/O 通常也是数据库性能瓶颈的原因之一。以下是一些常见的磁盘 I/O 性能问题：

• 数据库文件和日志文件位于相同的磁盘上。
• 数据库文件和日志文件位于相同的分区上。
• 数据库文件和日志文件位于相同的卷上。
• 数据库文件和日志文件位于相同的 RAID 组上。
• 磁盘被过度使用。

要找到磁盘 I/O 瓶颈，可以使用 SQL Server Profiler 或 DMV。以下是一些示例代码：

找到数据库文件和日志文件位于相同的磁盘上

// javascriptcn.com 代码示例
SELECT
    DB_NAME(database_id) AS DatabaseName,
    mf.physical_name AS DatabaseFile,
    vfs.num_of_writes AS Writes,
    vfs.num_of_bytes_written AS BytesWritten,
    vfs.io_stall_write_ms AS WriteStall,
    vfs.num_of_reads AS Reads,
    vfs.num_of_bytes_read AS BytesRead,
    vfs.io_stall_read_ms AS ReadStall,
    vfs.io_stall AS TotalStall
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
WHERE
    (mf.type_desc = 'ROWS' OR mf.type_desc = 'LOG')
    AND vfs.num_of_writes > 0
    AND vfs.num_of_reads > 0
    AND vfs.io_stall > 0
    AND (vfs.num_of_writes * 1024 / vfs.io_stall_write_ms) < (vfs.num_of_reads * 1024 / vfs.io_stall_read_ms)
ORDER BY
    TotalStall DESC

找到数据库文件和日志文件位于相同的分区上

// javascriptcn.com 代码示例
SELECT
    DB_NAME(database_id) AS DatabaseName,
    mf.physical_name AS DatabaseFile,
    vfs.num_of_writes AS Writes,
    vfs.num_of_bytes_written AS BytesWritten,
    vfs.io_stall_write_ms AS WriteStall,
    vfs.num_of_reads AS Reads,
    vfs.num_of_bytes_read AS BytesRead,
    vfs.io_stall_read_ms AS ReadStall,
    vfs.io_stall AS TotalStall
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    INNER JOIN sys.dm_os_volume_stats(NULL, NULL) AS vs ON mf.physical_name LIKE vs.volume_mount_point + '%'
WHERE
    (mf.type_desc = 'ROWS' OR mf.type_desc = 'LOG')
    AND vfs.num_of_writes > 0
    AND vfs.num_of_reads > 0
    AND vfs.io_stall > 0
    AND vs.logical_volume_name LIKE vs.physical_volume_name
ORDER BY
    TotalStall DESC

找到数据库文件和日志文件位于相同的卷上

// javascriptcn.com 代码示例
SELECT
    DB_NAME(database_id) AS DatabaseName,
    mf.physical_name AS DatabaseFile,
    vfs.num_of_writes AS Writes,
    vfs.num_of_bytes_written AS BytesWritten,
    vfs.io_stall_write_ms AS WriteStall,
    vfs.num_of_reads AS Reads,
    vfs.num_of_bytes_read AS BytesRead,
    vfs.io_stall_read_ms AS ReadStall,
    vfs.io_stall AS TotalStall
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    INNER JOIN sys.dm_os_volume_stats(NULL, NULL) AS vs ON mf.physical_name LIKE vs.volume_mount_point + '%'
WHERE
    (mf.type_desc = 'ROWS' OR mf.type_desc = 'LOG')
    AND vfs.num_of_writes > 0
    AND vfs.num_of_reads > 0
    AND vfs.io_stall > 0
    AND vs.logical_volume_name = vs.physical_volume_name
ORDER BY
    TotalStall DESC

找到数据库文件和日志文件位于相同的 RAID 组上

// javascriptcn.com 代码示例
SELECT
    DB_NAME(database_id) AS DatabaseName,
    mf.physical_name AS DatabaseFile,
    vfs.num_of_writes AS Writes,
    vfs.num_of_bytes_written AS BytesWritten,
    vfs.io_stall_write_ms AS WriteStall,
    vfs.num_of_reads AS Reads,
    vfs.num_of_bytes_read AS BytesRead,
    vfs.io_stall_read_ms AS ReadStall,
    vfs.io_stall AS TotalStall
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    INNER JOIN sys.dm_os_volume_stats(NULL, NULL) AS vs ON mf.physical_name LIKE vs.volume_mount_point + '%'
WHERE
    (mf.type_desc = 'ROWS' OR mf.type_desc = 'LOG')
    AND vfs.num_of_writes > 0
    AND vfs.num_of_reads > 0
    AND vfs.io_stall > 0
    AND vs.physical_drive_type_desc = 'RAID'
ORDER BY
    TotalStall DESC

找到磁盘被过度使用

// javascriptcn.com 代码示例
SELECT TOP 10
    vs.logical_volume_name AS LogicalVolumeName,
    vs.physical_volume_name AS PhysicalVolumeName,
    vs.total_bytes AS TotalBytes,
    vs.available_bytes AS AvailableBytes,
    vs.reads AS Reads,
    vs.writes AS Writes,
    vs.read_bytes AS ReadBytes,
    vs.write_bytes AS WriteBytes,
    vs.io_stall AS TotalStall
FROM
    sys.dm_os_volume_stats(NULL, NULL) AS vs
ORDER BY
    TotalStall DESC

总结

本文介绍了如何找到 SQL Server 数据库的性能瓶颈。通过监视数据库的性能，并找到查询性能瓶颈和磁盘 I/O 瓶颈，可以帮助您优化数据库性能，提高应用程序的响应速度。希望本文对您有所帮助。

来源：JavaScript中文网 ，转载请注明来源 本文地址：https://www.javascriptcn.com/post/6557340ad2f5e1655d1a22f1