什麼會導致查詢持續時間過長而資源使用率不高？
提前：抱歉問題的長度……無法在細節和簡潔之間取得適當的平衡。
我們的 Web 應用程序的數據庫伺服器存在問題，其中應該（並且通常會）在很短的時間（< 10 毫秒）內執行的查詢，在隨機情況下，需要 1 到 30 秒的時間來執行 - 沒有明顯的圖案。根據我們的分析器跟踪，其中一些甚至是“無所事事”的查詢，例如
"exec sp_reset_connection"（通常在 0ms 內執行；觀察到的峰值為 3~6s），"SET NO_BROWSETABLE ON"等等。一些例子是：SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'Where
TimeZoneRule在 5 列中有大約 500,000 行。具有代理主鍵和Name. 通常需要 0.97ms，在 11s 達到峰值。永遠不會寫入表（在上線之前預先填充）。Profiler 將其記錄為佔用 0-15 CPU、18-25 讀取、0-1 寫入（不知道為什麼要寫入）。UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'Where
User在大約 10 列（其中之一是 Xml 列）上有大約 30,000 行。Id是聚集的主鍵。表被定期寫入和讀取。通常需要 10~20ms，在 26s 達到峰值。Profiler 將其記錄為佔用 0 CPU、15-36 讀取、0-1 寫入。INSERT INTO [Log].[Session] (ASPSessionId, Start, ClientAddress, ClientSoftware, ProxyAddress, ProxySoftware) VALUES(<number>, GETUTCDATE(), '<ipv4address>', '<User agent string>', '<ipv4address>', '<proxy software name (if present)>')其中
Session大約 8 列有大約 1,000,000 行。有一個代理主鍵（身份）和一個索引ASPSessionId。表定期寫入，但很少讀取（僅由我們直接從 SSMS 讀取）。通常需要15~150ms，5s達到峰值。我手頭沒有它的配置文件記錄，但從記憶體來看，CPU 大約為 0，讀取和寫入分別在 0 到 100 之間。我們使用的設置是鏡像設置，以戴爾 2950 為原則（2 個 4 核至強 2.6、16Gb RAM）和戴爾 6850 作為鏡像（4 個 HT Xeon 3.2、8Gb RAM）。兩者都執行 SQL 2005 SP4 64 位。有問題的數據庫不是特別大，大小約為 16Gb。主有 6 個 SAS 磁碟，分為 3 個 RAID-1 卷；一份用於 System + Page + TempDB，一份用於數據庫的 MDF，一份用於事務日誌 + 每小時日誌備份 + 每日 DB 備份。我知道日誌情況遠不是最好的——就磁碟 IO（見下文）和數據安全而言。
到目前為止，我們認為我們已經消除了：
- 鏡子。我們分離了伺服器，並使用其中一個執行（然後切換到另一個），但性能問題仍然存在。
- 由於鎖（*）而阻塞。
TimeZoneRule永遠不會被寫入，並且據我估計，永遠不應該對它有排他鎖。此外，我們已經檢查了跟踪，並且在許多情況下，“問題查詢”是唯一執行的 - 唯一的其他活動是其他連接斷開- 索引不佳。由於讀取和 CPU 的數據較低，這表明 SQL Server 正在有效地使用索引。
- 磁碟 IO。PerfMon 表示數據文件驅動器（但僅該驅動器）的一些奇怪數字 - 雖然數據讀取/寫入速率似乎很少超過 32KB/s，但目前磁碟隊列長度在大約 45 秒的 2-5 秒內飆升至大約 215 -60 分鐘間隔，沒有固定模式。但是，這些與查詢性能不佳的時間無關。其他兩個驅動器的磁碟隊列長度$$ system + page + tempdb $$和$$ log + backups $$ 從不超過3。
(*) 我們嘗試讓分析器擷取與鎖定獲取相關的事件，但跟踪膨脹到無法讀取的比例，更糟糕的是，Web 應用程序停止執行。
不是 DBA，我們的想法很快就會枯竭。誰能想到我接下來應該考慮看的任何東西或我愚蠢地錯過的任何東西？
在執行 SQL 2005 時，您可以獲取 SQL Profiler 數據並將其與 Perfmon 數據進行比較，以查看是否可以看到相關性。這是通過使用正常技術將跟踪數據和性能數據保存到文件中來完成的。然後在 profiler 中打開 SQL Profiler 跟踪，然後文件菜單中的選項之一將是 Import Performance Data。這將讓您選擇一個查詢並查看當時計數器在做什麼（或接近它，具體取決於您的 perfmon 收集間隔）。
磁碟隊列峰值從來都不是好事。尤其是那麼高。當隊列變得如此之高時，您推送到磁碟的 IO 是多少？基本上，您不希望磁碟隊列高於 (2*n) ，其中 n 是陣列中的磁碟數。由於您使用的是 2 磁碟 RAID 1 n=1 （因為您只能獲得單個磁碟的速度）。
在 perfmon 中有一個計數器，它是每次讀取的秒數和每次寫入的秒數。當查詢開始需要很長時間才能執行時，這些計數器會是什麼樣子。平時呢？（任何超過 0.02 秒的時間都是不好的。）預計的頁面預期壽命是多少？（任何低於 300 秒的時間通常都不好，但這可能會有所不同。） SQL Server 記憶體命中率是多少？（低於 97% 的任何東西通常都不好。我喜歡高於 99.9% 的東西。）