本文為翻譯文章，原文地址：https://www.simple-talk.com/blogs/2009/08/03/stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application/　

　　前言

　　在硬件資源非常有限的SQLServer服務器上進行開發，有一大優勢，就是錯誤是可視化的，即你很快就會因為你的錯誤而受到懲罰。譬如，應用中過多使用ad-hoc動態查詢的產生的問題會很快暴露出來。開發者可能會向你抱怨，數據庫運行的越來越慢。你也將會發現服務器CPU的使用率非常高，甚至接近100%，但是在性能較差的時段，卻沒有阻塞的發生。

　　在極端的情況下，你甚至可能接受到如下的錯誤：

　　Error: 701, Severity: 17, State: 1　　There is insufficient system memory to run this query.

　　或者

　　Msg 8645, Level 17, State 1, PRocedure , Line 1　　A time out occurred while waiting for memory resources to execute the query. Re-run the query.　

　　分析

　　你會發現運行過多的ad-hoc查詢所有造成的不良影響。CPU利用率高是因為查詢優化器需要編譯大量的ad-hoc查詢。內存壓力是因為一些內存用來緩存ad-hoc查詢生成的執行計劃。換句話說，開發者使用了Ad-hoc查詢，而不是使用存儲過程或者參數化的查詢。這是非常愚蠢的。

　　一個編譯好的執行計劃大約占用70KB的空間，而一個存儲過程的執行計劃，根據其復雜度，大約占用2到3倍的空間。區別是每個存儲過程只有一個執行計劃。使用Ad-hoc查詢，你將冒著每個查詢都有一個單獨的執行計劃的風險。

　　執行計劃緩存起來是為了被重用的，SQLServer需要占用內存來存儲執行計劃，這部分內存按照申請方式稱為StolenPages。其他占用StolenPages的對象包括了Connections、Locks 和 Transaction Context等一些內存Consumer以及線程和第三方代碼消耗的內存。這是一個簡單的日常任務分配內存的方式，但是當數據庫接受到非常多的Ad-hoc查詢時，將會產生麻煩。除非SQLServer可以確定查詢可以自動的參數化或者說新的查詢和已緩存的查詢一致，否則SQLServer就會重新生成一個執行計劃。可能不長時間，你就會看到數據緩存產生瓶頸　

　　那么如何確定系統是否存在這種問題

　　1.檢查編譯查詢計劃的數量。SQLServer性能監視器將會顯示SQL Compilations/sec 有比較高的數值。理論上，SQL Recompilations/sec 和 Batch Requests/sec 的比率應該會非常低

　　2.DBCC MemoryStatus 也會顯示出stolen pages的數量會上升

　　確定問題后，如何解決

　　1. 使用存儲過程來執行

　　2. 使用參數化查詢

　　實例演示 略 具體見原網址

　　如有不對的地方，歡迎拍磚，謝謝！O(∩_∩)O