主页 / dba / 问题 / 112124
Accepted
pshore73
Asked: 2015-08-26 05:13:23 +0800 CST

将 SQL Server 查询与数据库相关联

  • 772

有没有办法通过 T-SQL 和 DMV 显示针对哪个数据库运行了查询？

目标是通过数据库查询查看 CPU 和 I/O，以便我可以在重新配置环境时重新分配数据库。

如果可以的话，我不想使用跟踪、分析器或扩展事件。如果可以避免的话，我也不想分解 XML。我正在寻找历史，而不仅仅是活跃的。

sql-server performance

1 个回答

  1. Best Answer

    您必须专注于内存、磁盘 IO 和 CPU 利用率以平衡您的环境。

    最好的办法是开始收集结果并将其转储到实用程序数据库中，并定期使用 SQL 代理为您提供良好的基线。

    或者，您可以使用性能数据收集器（适用于 sql server 2008 及更高版本）

    记忆：

    您可以查看缓冲池使用情况（哪些数据库在缓冲池中占用更多空间？）

    来自：浪费缓冲池内存的性能问题

    SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

    磁盘输入输出：

    在磁盘 IO 方面，您需要将具有最多磁盘 IO 的 mdf/ldf 文件移至更好/更快的 LUN/阵列：

    来自：如何从 SQL Server 中检查 IO 子系统延迟

    SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

    另请参阅利用 sys.dm_io_virtual_file_stats

    中央处理器：

    来自：布伦特的回答 

    SELECT total_worker_time/execution_count AS AvgCPU  
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration  
, total_elapsed_time AS TotalDuration  
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, (total_logical_reads+total_physical_reads) AS TotalReads
, 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 txt  
, query_plan
FROM sys.dm_exec_query_stats AS qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp 
ORDER BY 1 DESC

    DMV 相关查询的金矿：2014 年 9 月的 SQL Server 诊断信息查询

    为了完整起见，如果您使用的是 sql server 2012，则可以使用系统健康报告仪表板来可视化 sp_server_diagnostics 结果。

    • 3

相关问题