排錯

　　不論是否錯誤是持續的和可重復的（同樣狀態）或是隨機的（顯示為隨機的不同狀態），當你看到這個錯誤時你需要研究服務器內存分布。當這個錯誤出現時，可能導致診斷查詢失敗。可以開始從外部進行評估。接下來的步驟在一般的內存錯誤排錯步驟中描述。

　　可能的解決方案包括：除去外部內存壓力；增加max server memory設置。使用下列語句釋放緩存dbcc freesystemcache，dbcc freesessioncache，或 dbcc freeproccache。如果問題還是出現，則應減少工作負載。

　　802 - there is insufficient memory available in the buffer pool.

　　原因

　　這個錯誤不是引起內存不足的必要條件。它可能預示緩存池內存被其他的一些組件使用。在sql server 2005中很少出現。

　　排錯

　　使用常規的排錯步驟和701錯誤的建議。

　　8628 - a time out occurred while waiting to optimize the query. rerun the query.

　　原因

　　這個錯誤指出查詢編譯過程失敗因為它不能獲取完成編譯所需的內存。當查詢經歷了這種編譯過程，包括解析，代數求解和優化，它的內存需求會增加。因而查詢將和其他的查詢爭奪內存資源。如果查詢超過的預定的超時時間（查詢增加內存的占用） 當等待資源時，這個錯誤會返回。這種情況的最可能的原因是在服務器上出現很多大型查詢的編譯。

　　排錯

　　1.在常規的排錯步驟后查看是否服務器內存占用正常。

　　2.檢查負載。確認被不同組件占用的內存數量（具體信息請參考內部物理內存壓力）。

　　3.檢查dbcc memorystatus輸出的在每個gateway上的等待數量（這些信息將告訴你是否有其他的查詢在占用大量內存）。

　　4.盡可能減少工作負載。

　　8645 - a time out occurred while waiting for memory resources to execute the query. rerun the query.

　　原因

　　這個錯誤指出在服務器上有很多內存敏感的查詢被同時執行。使用排序（order by）和連接的查詢可以在執行過程中會占用大量的內存。如果有高度并行或如果查詢操作一個非聯合索引的分區表時，查詢內存需求會有很大的增加。無法或取所需內存資源的查詢在預先確定的超時時間時（默認，超時是25倍的評估查詢時間或sp_configure 設置的’query wait’）將收到這個錯誤。通常，收到這個錯誤的查詢占用內存的那個查詢。

　　排錯

　　1.根據一般步驟評估內存的情況。

　　2.識別有問題的查詢：確認有大量的在分區表上的查詢，檢查是否他們使用非聯合索引，檢查是否有大量包括連接和排序的查詢。

　　3.檢查sp_configure參數中degree of parallelism 和min memory per query 。嘗試降低并行度并驗證是否min memory per query沒有設置很高的值。如果設置了很高的值，即使少量的查詢也會占用指定查詢的數量。

　　4.為了找到查詢是否等待resource_semaphore，請查看本白皮書中阻塞的部分。

　　8651 - could not perform the requested operation because the minimum query memory is not available. decrease the configured value for the 'min memory per query' server configuration option.

　　原因

　　原因和8645錯誤類似；它也預示服務器內存過低。min memory per query太高也可能導致這個錯誤。

　　排錯

　　1.根據一般內存錯誤排錯步驟。

　　2.驗證sp_configure min memory per query 選項設置。

　　i/o 瓶頸

　　sql server性能非常依賴于i/o子系統。除非你的數據庫適合物理內存，sql server經常地會有數據庫頁面進出緩存池。這樣就發生了實質的i/o流量。同樣，在事務被明確的提交前，日志記錄需要寫入磁盤。sql server為各種目的可以使用tempdb，例如存儲中間結果，排序，保持行的版本或其他。所以好的i/o子系統對于sql server性能非常重要。

　　除了當事務需要回滾時，訪問日志文件是非常頻繁的，而包括訪問數據文件，包括tempdb，是隨機訪問的。所以作為一個通常的規則，為獲取更好的性能，你最好將日志文分布不到不同的物理磁盤。本文重點于不是在如何配置你的i/o設備，而是描述識別你的i/o瓶頸的方法。一旦識別了i/o瓶頸，你也許需要重新配置你的i/o子系統。

　　如果你有一個慢速的i/o子系統，你的用戶會遇到性能問題，例如很長的響應時間，任務由于超時而中斷。

　　你可以使用下列性能參數來識別i/o瓶頸。注意，如果你的收集間隔過長，這些平均值可能會傾向于過小。例如，很難通過60秒一次的快照獲取正確的i/o值。所以，你不能依賴于一個計數器來確定瓶頸；要考慮多個技術起來反復校驗。

　　◆physicaldisk object: avg. disk queue length表現在采樣周期中所選擇的物理磁盤隊列中的物理讀和寫平均請求數量。如果你的i/o系統過載，更多的讀/寫操作將被等待。如果在很少使用sql server時，你的磁盤隊列長度經常超過2，這樣你可能遇到了i/o瓶頸

　　◆avg. disk sec/read 是平均每次從磁盤讀取數據的時間

　　小于10 ms – 很好

　　在 10 - 20 ms 之間- 正常

　　在20 - 50 ms 之間- 緩慢，需要注意

　　大于 50 ms – 嚴重的i/o 瓶頸

　　◆avg. disk sec/write 是平均每次從磁盤讀取數據的時間。請引用之前的指導數據。

　　◆physical disk: %disk time 是所選磁盤驅動器用于服務于讀或寫請求的總共時間的百分比。一般推薦是如果該值大于50%，則表現為i/o瓶頸。

　　◆avg. disk reads/sec 表現磁盤上讀操作的速度。你需要確認該值小于85%的磁盤設計能力。磁盤訪問時間指數高于能力的85%。

　　◆avg. disk writes/sec表現在磁盤上寫操作的速度。確認該值小于85%的磁盤設計能力。磁盤訪問時間指數高于能力的85%。

　　當使用以上計數器，你在使用raid配置時，你需要使用下列公式調整結果值。

　　raid 0 -- i/os per disk = (reads + writes) / number of disks

　　raid 1 -- i/os per disk = [reads + (2 * writes)] / 2

　　raid 5 -- i/os per disk = [reads + (4 * writes)] / number of disks

　　raid 10 -- i/os per disk = [reads + (2 * writes)] / number of disks

　　例如，你有帶有2個物理磁盤的raid-1系統和下列計數器值。

　　disk reads/sec　　　　　　80

　　disk writes/sec　　　　　 70

　　avg. disk queue length　　5

　　這種情況下，你遇到了(80 + (2 * 70))/2 = 110 i/os per disk，你的磁盤隊列長度為5/2=2.5，表明系統接近于i/o瓶頸。

　　你也可以通過考察鎖等待來識別i/o瓶頸。當數據頁通過讀或寫訪問并且在緩存池中頁不可用時，這些鎖等待占用了大量的物理i/o等待。當頁面沒有在緩存池中找到時，一個異步i/o請求被發出，i/o的狀態是被選中的。如果i/o已經完成，工作進程處理正常。否則，依賴于請求的類型，它會等待pageiolatch_ex 或 pageiolatch_sh。從下列的dmv查詢可以用于找到i/o鎖等待的統計。

　　下劃線的是有興趣的鎖等待。當i/o完成時，工作者被放置到可用隊列中。從i/o完成到工作者被實際的調度的時間在signal_wait_time_ms列說明。如果你得waiting_task_counts and wait_time_ms偏離正常值太多，你可以識別為i/o問題。為了這樣，通過使用性能計數器和關鍵dmv建立正常時運行時的性能基線就十分重要。wait_types可以識別是否你的i/o子系統處在瓶頸狀態，但是他們不能提供任何關于物理磁盤遇到問題的信息。

　　你可以使用下列dmv查詢找到當前等待的i/o請求。你可以周期性的執行這些請求檢查i/o子系統的健康情況，并找到涉及i/o瓶頸的物理磁盤。

　　下面是示例的輸出。它展示當時在給定的數據庫上有3個未決定的i/o 。你可以使用database_id 和 file_id 來找到映射的物理磁盤文件。

　　io_pending_ms_ticks 表現了所有等待在未決定隊列中的個別的i/o。

　　database_idfile_id io_stallio_pending_ms_ticksscheduler_address

　　----------------------------------------------------------------------

　　6　1　10804　78　0x0227a040

　　6　1　10804　78　0x0227a040

　　6　2　10145131　0x02720040

　　解決

　　當你識別到i/o瓶頸時，你可以下列方法解決：

　　◆檢查sql server的內存配置。如果sql server配置的內存不足，將導致更多的i/o開銷。你可以考察下列計數器來識別內存壓力。

　　◆buffer cache hit ratio

　　◆page life expectancy

　　◆checkpoint pages/sec

　　◆lazywrites/sec

　　更多關于內存壓力的信息，請查看內存瓶頸。

　　◆增加i/o帶寬。

　　◆為當前的磁盤陣列添加更多的物理驅動器和/或使用更快的磁盤代替當前的磁盤。這幫助提升讀寫訪問時間。但是添加的磁盤數量不能比當前i/o控制器所支持的數量大。

　　◆添加快速或額外的i/o控制器。考慮為當前的控制器添加更多的緩存（如果有可能）

　　◆考察執行計劃并查看那個計劃占用了更多的i/o。這樣可以找到更好的查詢進化（例如，索引）可以減少i/o。如果缺少索引，你可以運行database engine tuning advisor找到缺失的索引。

　　下列dmv查詢能用于找到那個批處理/請求生成了最多的i/o。你將注意到我們沒有說明物理寫入。如果你了解數據庫如何工作，這也沒有問題。請求中的dml/ddl并不是直接將數據頁寫入磁盤。只有通過提交事務語句才能觸發將頁面物理寫入磁盤。通常物理寫通過checkpoint語句完成或通過sql server lazy writer完成。下列dmv查詢可以用于找到生成最多i/o的5個請求。調整這些查詢，使他們使用較少的邏輯度來完成操作，這樣可以減少在緩存池上的壓力。這允許其他的查詢在緩存池中找到重復執行中所需要的重復數據（而不是通過物理i/o完成）。因此整體系統性能被提高了。

　　當然你可以改變查詢或取數據的不同視角。例如，在一個查詢中聲稱最多i/o請求的5個請求，你可以通過下列表達式排序：

　　(total_logical_reads + total_logical_writes)/execution_count

　　除此之外，你可能希望通過物理i/o排序或其他的操作。然而，邏輯讀/寫數量來幫助決定是否查詢選擇的計劃被優化過。例如，它可以做一個表掃描而不是使用索引。一些查詢，例如使用嵌套循環連接可能有很高的邏輯計數器但是能更好的適應緩存，因為他們重新訪問了同樣的頁面。

　　例如:讓我們考察下列2個有2個sql查詢的批，每個表由1000行并且行大小大于8000（每頁1行）。

　　批-1

　　select

　　c1,

　　c5

　　from t1 inner hash join t2 on t1.c1 = t2.c4

　　order by c2

　　批-2

　　select * from t1

　　為了這個示例，在運行dmv查詢前，我們利用下列命名清理了緩存池和過程的緩存。

　　checkpoint

　　dbcc freeproccache

　　dbcc dropcleanbuffers

　　這是dmv查詢的輸出。你將注意到2行表現的這2個批。

　　你將注意到第2個批只發生了邏輯讀但是沒有物理i/o。這是因為需要的數據已經由第1個查詢緩存到了高速緩存中（有充分的內存）。

　　你可以通過下列查詢或取執行查詢的文本。

　　你也可以通過下列語句找到獨立語句的字符串。

　　statement_start_offest 和statement_end_offset的值需要除以2來補償sql server使用unicode來存儲這種數據的。statement_end_offse值為-1指出語句先前到了批的最后。而substring()函數不識別-1，并將其作為非法值。使用(<statement_end_offset> -<statement_start_offset>)/2,代替-1，一個需要輸入64000的值，來確認語句覆蓋了所有的情況。通過這種方法，長時間運行或資源消費語句可以被過濾出大型的存儲過程或批。

　　同樣的你可以運行下列查詢，找到查詢計劃確定是否當選擇了較差的計劃時有大量的i/o。

　　tempdb

　　tempdb用于全局存儲內部或用戶對象，臨時表，對象和在sql server操作是創建的存儲過程。每個sql server 實例只有1個單一的tempdb。它可能是一個性能和磁盤空間的瓶頸。有限可用空間和過多的ddl/dml會使tempdb超過負載。這能導致運行在同一個服務器中的其他無關應用變得運行緩慢或失敗。

　　下面列出一些tempdb的常規問題：

　　◆tempdb磁盤空間不足

　　◆由于tempdb中的i/o瓶頸，導致查詢運行緩慢。這在i/o瓶頸部分提到過。

　　◆過多ddl操作導致系統表的瓶頸

　　◆分配爭奪

　　before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. it can be grouped into four main categories.當開始調試tempdb問題前，讓我們先看一下在tempdb中如何使用空間。它可以分為4個主要的類別。

　　tempdb使用的總空間等于用戶對象加內部對象加版本存儲+可用空間。

　　這個可用空間等于性能計數器中tempdb的可用空間。

　　監視tempdb空間

　　阻止問題更好的方法是隨后馬上解決它。你可以使用下列性能監視器來監視使用中的tempdb空間。

　　◆free space in tempdb (kb).這個計數器以kb為單位跟蹤空閑空間的數量。管理員可以使用這個計數器確定是否tempdb運行在較低的磁盤空間上。

　　然而，識別前面所定義的不同類別如何使用在tempdb中使用的磁盤空間，是很有興趣的并有效率的。

　　下列查詢返回了tempdb用戶使用的空間和內部組件對象。當前它提供了僅有tempdb中的信息。

　　這里有一些示例的輸出（空間使用kb為單位）。

　　注意這些技術不包括其中的混合擴展。頁面在混合擴展中可以被分配個別用戶和內部對象。

　　磁盤空間問題排錯

　　用戶對象，內部對象和存儲的版本可以都可以導致在tempdb中的空間問題。這節我們會考慮針對每個類別如何排錯。

　　用戶對象

　　因為用戶對象不被特定的會話擁有，你需要理解創建該對象的應用程序的規范并調整需要的tempdb大小。你可以通過執行exec sp_spaceused @objname='<user-object>'來找到個別用戶對象使用的。例如，你可以運行下列腳本枚舉所有tempdb對象。