數據庫防止sql注入，預編譯sql查詢語句_頻繁查詢重新編譯– SQL查詢性能殺手–檢測

預編譯sql查詢語句

There are certain scenarios when the query is automatically removed from cache, so the next time the query is called, SQL Server will not find its query execution plan in cache and will have to recompile it.

在某些情況下，查詢會自動從緩存中刪除，因此，下次調用查詢時，SQL Server將不會在緩存中找到其查詢執行計劃，而必須重新編譯它。

?
CREATE PROCEDURE TestRecomp
AS
BEGIN-- DDL statementCREATE TABLE TestTab( id int, cola int, colb nvarchar(30))SELECT *FROM TestTab -- DML statement - the first recompilationALTER TABLE TestTabADD colc datetime -- Another DDL statement - the second recompilationSELECT idFROM TestTab -- DML - the third recompilationEND

?
EXEC TestRecomp

When a SET option is changed
????It’s recommended not to save any of SET options (ANSI_NULLS, Quoted_identifier, etc.) in a stored procedure, as they ????cause recompilation

更改SET選項時
建議不要在存儲過程中保存任何SET選項（ANSI_NULLS，Quoted_identifier等），因為它們會導致重新編譯

Since SQL Server 2005, only the statement that causes a recompilation is recompiled. This is a statement-level recompilation. In the earlier versions, all statements in the stored procedure were recompiled, which affected SQL Server performance even more.

sql無法預編譯， 從SQL Server 2005開始，僅重新編譯導致重新編譯的語句。 這是語句級的重新編譯。 在早期版本中，存儲過程中的所有語句都被重新編譯，這對SQL Server的性能影響更大。

使用Windows Performance Monitor檢測重新編譯 (Detecting recompilations using Windows Performance Monitor)

Windows Performance Monitor can be used to track both compilations and recompilations.

Windows Performance Monitor可用于跟蹤編譯和重新編譯。

The metric that tracks the number of compilations per second is SQL Compilations/Sec. It’s located in the SQL Server Statistics section. The metric shows the number of query and procedure compilations in a second. It’s expected that the value is high on SQL Server start up, but is should fall and stabilize after a while.

跟蹤每秒編譯數量的指標是SQL Compilations / Sec 。 它位于“ SQL Server統計信息”部分。 該指標在一秒鐘內顯示查詢和過程編譯的數量。 預計該值在SQL Server啟動時會很高，但應該會下降并在一段時間后穩定下來。

mysql sql注入，The metric that shows the number of query and procedure recompilations in a second is SQL Re-Compilations/Sec. A high value is a clear indication of frequent recompilations that lead to processor stress and SQL Server performance degradation.

每秒顯示查詢和過程重新編譯的數量的指標是SQL Re-Compilations / Sec 。 較高的值清楚地表明頻繁的重新編譯會導致處理器壓力和SQL Server性能下降。

The ideal value for the number of recompilations per second is zero. There is no specific threshold value. You should watch for the metric trend and set a baseline. However, it is also recommended to have the Compilations/sec value to be less than 10% of the Batch Requests/sec value. Another recommendation is that the number of Recompilations/sec should be less than 10% of Compilations/sec.

每秒重新編譯次數的理想值為零。 沒有具體的閾值。 您應該注意指標趨勢并設置基線。 但是，還建議“ 編譯/秒”值小于“ 批處理請求/秒”值的10％。 另一項建議是， 重新編譯的數/秒應匯編/秒的低于10％。

If the values are constantly high, further investigation is required.

sqlserver日志、 如果值一直很高，則需要進一步調查。

1. Windows Performance Monitor, using one of the following methods: Windows Performance Monitor ：
   • Start (Windows + C for Windows 8), select 開始” （適用于Windows 8的Windows + C），選擇“ Search, type 搜索” ，鍵入perfmon, and press perfmon ，然后按EnterEnter鍵。
   • Start, 開始” ， Run (Windows + R for Windows 8), type “運行” （對于Windows 8為Windows + R），鍵入perfmon, and press perfmon ，然后按EnterEnter鍵。
   • Control Panel, select 控制面板 ，選擇“ System and Security, Administrative Tools, and click 系統和安全性”，“管理工具” ，然后單擊“ Performance Monitor性能監視器”。
2. Monitoring Tools, Performance Monitor in the left pane監視工具”，“性能監視器 ”
3. Add icon in the menu添加圖標
4. Add counters dialog select the machine name in the drop-down list添加計數器”對話框中，在下拉列表中選擇計算機名稱。
5. MSSQL$<sql server instance>: SQL StatisticsMSSQL $ <sql服務器實例>：SQL Statistics

6. Expand the list, select SQL Re-Compilations/sec, and click Add. To track the recompilations as well, select SQL Compilations/sec

   展開列表，選擇“ SQL Re-Compilations / sec” ，然后單擊“ 添加” 。 要也跟蹤重新編譯，請選擇“ SQL編譯/秒”

   

   關于sql優化。

7. Click OK

   點擊確定

   

   以下說明哪個是錯誤的，

   The Performance Monitor graph will show the number of compilations and recompilations per second.

   性能監視器圖將顯示每秒的編譯和重新編譯次數。

The advantages of Performance Monitor are that it is available in Windows Server 2003 and later and easy to use. It shows the graphs in real-time, adds little overhead, and you can monitor only specific counters. Although it clearly shows the number of recompilations, it doesn’t show the queries and stored procedures recompiled, which is necessary for troubleshooting.

Performance Monitor的優點是它在Windows Server 2003和更高版本中可用，并且易于使用。 它實時顯示圖形，幾乎不增加開銷，并且您只能監視特定的計數器。 盡管它清楚地顯示了重新編譯的次數，但沒有顯示查詢和存儲過程的重新編譯，這對于故障排除是必需的。

使用sys.dm_os_performance_counters檢測重新編譯 (Detecting recompilations using sys.dm_os_performance_counters)

db2 sql3114？The SQL Compilations/Sec and SQL Re-Compilations/Sec metrics are also available in the sys.dm_os_performance_counters dynamic management view.

sys.dm_os_performance_counters動態管理視圖中還提供了SQL編譯/秒和SQL重新編譯/秒指標。

As it’s useful to compare the number of recompilations to the number of compilations and batch requests, select all three metrics.

由于將重新編譯次數與編譯次數和批處理請求次數進行比較非常有用，因此請選擇所有三個指標。

?
SELECT *FROM sys.dm_os_performance_countersWHERE counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')

批量執行sql失敗跳過。The counter type value 272696576 indicates these are cumulative values since the last SQL Server restart, and should be recalculated to get the number per second. We’ll use a 10 second sampling.

計數器類型值272696576指示這些是自上次SQL Server重新啟動以來的累積值，應重新計算以獲取每秒的數量。 我們將使用10秒的采樣時間。

?
DECLARE @CountVal BIGINT;SELECT @CountVal = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Re-Compilations/sec';WAITFOR DELAY '00:00:10';SELECT (cntr_value - @CountVal) / 10 AS 'SQL Re-Compilations/sec'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Re-Compilations/sec';

Again, there’s no information about the queries and stored procedures that are recompiled, which is necessary for troubleshooting

同樣，沒有有關重新編譯的查詢和存儲過程的信息，這對于故障排除是必需的

使用SQL Server Profiler檢測重新編譯 (Detecting recompilations using SQL Server Profiler)

Recompilations are captured in SQL Server traces. To find out whether a recompilation has occurred, use SQL Server Profiler to read the traces.

sqlserver查詢歷史sql執行記錄？ 重新編譯在SQL Server跟蹤中捕獲。 若要確定是否發生了重新編譯，請使用SQL Server Profiler讀取跟蹤。

1. SQL Server ProfilerSQL Server Profiler
2. File menu, select 文件菜單上，選擇New Trace新建跟蹤
3. Connect to Server dialog, select the SQL Server instance and provide credentials連接到服務器”對話框中，選擇SQL Server實例并提供憑據
4. Connect連接

5. In the General tab, specify the trace name

   在“ 常規”選項卡中，指定跟蹤名稱

   

   預編譯sql依然有注入？

6. Open the Events Section tab

   打開事件部分選項卡

   Note that recompilations are not captured into SQL traces by default. To capture them, you have to select them manually.

   請注意，默認情況下，重新編譯不會捕獲到SQL跟蹤中。 要捕獲它們，您必須手動選擇它們。

7. Show All Events checkbox顯示所有事件復選框
8. Events grid, scroll down to the 事件”網格中，向下滾動到“ Stored Procedures node and expand it存儲過程”節點并將其展開

9. sql一直在執行查詢？Select the SP:Recompile row

   選擇“ SP：Recompile”行

   

   This option is only for tracking recompilations on stored procedures

   此選項僅用于跟蹤存儲過程中的重新編譯

10. To track recompilations on ad hoc queries, expand the TSQL node and select the SQL:Stmt Recompile row

    要跟蹤臨時查詢的重新編譯，請展開TSQL節點并選擇SQL：Stmt Recompile行

    

11. Run運行

The stored procedure recompilations are shown as SP:Recompile event class. The statement that is recompiled is shown in the text data column, as well as in the lower pane of the SQL Server Profiler window.

存儲過程的重新編譯顯示為SP：Recompile事件類。 文本數據列以及“ SQL Server Profiler”窗口的下部窗格中顯示了重新編譯的語句。

Ad hoc query recompilations are shown as the SQL:Stmt Recompile event class.

臨時查詢重新編譯顯示為SQL：Stmt Recompile事件類。

SQL Server Profiler offers the Find option to search for the specific events, but it doesn’t enable filtering by a specific event class which would make recompilation analysis easier. The statements recompiled are shown, so that can be used to find the most often recompiled queries and stored procedures. The downside of this method is that running SQL Server traces can add overhead to SQL Server performance.

SQL Server Profiler提供了“查找”選項來搜索特定事件，但是它沒有啟用按特定事件類進行的篩選，這會使重新編譯分析更加容易。 顯示了重新編譯的語句，因此可用于查找最經常重新編譯的查詢和存儲過程。 這種方法的缺點是運行SQL Server跟蹤會增加SQL Server性能的開銷。

In this article, we showed when the recompilation occurs automatically. We also presented three different methods that can be used for monitoring recompilations. Windows Performance Monitor and sys.dm_os_performance_counters view show the number or recompilations in a second, but cannot be used for deeper analysis and troubleshooting. SQL Server Profiler shows the recompiled statements, therefore provides enough information for further investigation.

在本文中，我們顯示了何時自動進行重新編譯。 我們還介紹了可用于監視重新編譯的三種不同方法。 Windows Performance Monitor和sys.dm_os_performance_counters視圖可以在一秒鐘內顯示數量或重新編譯，但不能用于更深入的分析和故障排除。 SQL Server Profiler顯示了重新編譯的語句，因此提供了足夠的信息以供進一步研究。

翻譯自: https://www.sqlshack.com/frequent-query-recompilations-sql-query-performance-killer-detection/

預編譯sql查詢語句