將 SQL Server 查詢與數據庫關聯

有沒有辦法通過 T-SQL 和 DMV 來顯示查詢是針對哪個數據庫執行的？

目標是通過數據庫查詢來查看 CPU 和 I/O，以便我可以在重新配置環境時重新分配數據庫。

如果可以的話，我不想使用跟踪、分析器或擴展事件。如果可以避免的話，我也不想粉碎 XML。我尋找的不僅僅是活躍的歷史。

您必須專注於記憶體、磁碟 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 方面，您需要將具有最多磁碟 IO 的 mdf/ldf 文件移至更好/更快的 LUN/Array ：

來自：如何從 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 結果。

引用自：https://dba.stackexchange.com/questions/112124