昨天一開發(fā)同事反饋一個存儲過程很慢，但是重編譯后，存儲過程就很快了。了解基本情況后，初步判斷是參數(shù)嗅探問題。那么如何診斷定位、分析問題呢？下面簡單介紹一下這次參數(shù)嗅探問題定位的流程過程。

首先查看該存儲過程的執(zhí)行計劃相關(guān)信息：

如下截圖所示，此存儲過程是2018-09-12 9:03：01緩存的，最后一次執(zhí)行是2018-09-14 08:58，而且自上次緩存后，執(zhí)行了24875次。從這里我們基本判斷該存儲過程一直在重用緩存的執(zhí)行計劃，而且沒有產(chǎn)生重編譯現(xiàn)象。

SELECT  d.object_id ,
        d.database_id ,
        OBJECT_NAME(object_id, database_id) 'proc name' ,
        d.cached_time ,
        d.last_execution_time ,
        d.total_elapsed_time ,
        d.total_elapsed_time / d.execution_count AS [avg_elapsed_time] ,
        d.last_elapsed_time ,
        d.execution_count
FROM    sys.dm_exec_procedure_stats AS d
WHERE   OBJECT_NAME(object_id, database_id) = 'sp_GetOTList'
ORDER BY [total_worker_time] DESC;

然后我們使用下面腳本找到該存儲過程的實際執(zhí)行計劃，將存儲過程的執(zhí)行計劃的XML內(nèi)容拷貝到Plan Explorer工具。生成比較清晰、詳細(xì)的執(zhí)行計劃圖。?

SELECT
        d.object_id ,
        DB_NAME(d.database_id) DBName ,
        OBJECT_NAME(object_id, database_id) 'SPName' ,
        d.cached_time ,
        d.last_execution_time ,
        d.total_elapsed_time/1000000    AS total_elapsed_time,
        d.total_elapsed_time / d.execution_count/1000000 
                                        AS [avg_elapsed_time] ,
        d.last_elapsed_time/1000000        AS last_elapsed_time,
        d.execution_count ,
        d.total_physical_reads ,
        d.last_physical_reads ,
        d.total_logical_writes ,
        d.last_logical_reads ,
        et.text SQLText ,
        eqp.query_plan executionplan
FROM    sys.dm_exec_procedure_stats AS d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et
CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp
WHERE   OBJECT_NAME(object_id, database_id) = 'sp_GetOTList'
ORDER BY [total_worker_time] DESC;

如下截圖所示，我們可以清晰的找到Est Cost、 Est Cpu Cost、 Est IO Cost等高的SQL語句（其實這個是實際執(zhí)行計劃，而不是預(yù)估的執(zhí)行計劃），

然后重點研究、對比， 然后使用WITH(RECOMPILE)重新執(zhí)行該存儲過程，生成新的執(zhí)行計劃，然后按照上面方式將存儲過程執(zhí)行計劃的XML拷貝到Plan Explorer工具里面。 然后我們可以對比、研究看看到底出現(xiàn)了什么情況?

舊的實際執(zhí)行計劃

如上截圖所示，開銷最大的SQL語句的實際執(zhí)行計劃如上所示，注意開銷占比最大的地方。 下面截圖是Nested Loops里面循環(huán)的次數(shù)（迭代次數(shù)20次），也是我們

對比執(zhí)行計劃需要重點關(guān)注的地方

新的實際執(zhí)行計劃

新的執(zhí)行計劃中，可以看到舊執(zhí)行計劃開銷最大的SQL語句在整體開銷的占比減少了很多，但是該語句的新舊執(zhí)行計劃是一樣的。唯一不同的就是兩個Nested Loops里面循環(huán)的次數(shù)不一樣。這個就是產(chǎn)生性能差異的地方，如果對嵌套循環(huán)連接不太熟悉，可以參考一下下面這段內(nèi)容:

Nested Loops也稱為嵌套迭代，它將一個聯(lián)接輸入用作外部輸入表（顯示為圖形執(zhí)行計劃中的頂端輸入），將另一個聯(lián)接輸入用作內(nèi)部（底端）輸入表。外部循環(huán)逐行消耗外部輸入表。內(nèi)部循環(huán)為每個外部行執(zhí)行，在內(nèi)部輸入表中搜索匹配行。最簡單的情況是，搜索時掃描整個表或索引；這稱為單純嵌套循環(huán)聯(lián)接。如果搜索時使用索引，則稱為索引嵌套循環(huán)聯(lián)接。如果將索引生成為查詢計劃的一部分（并在查詢完成后立即將索引破壞），則稱為臨時索引嵌套循環(huán)聯(lián)接。

舊執(zhí)行計劃：?

• 嵌套循環(huán)次數(shù)：20* 30
• 嵌套循環(huán)次數(shù)：20*20

新執(zhí)行計劃：

• 嵌套循環(huán)次數(shù)： 1* 1?
• 嵌套循環(huán)次數(shù)： 1* 1

那么為什么產(chǎn)生這個差異，就是因為存儲過程里面一段SQL語句使用了存儲過程參數(shù)，而恰巧里面那個表按照這個字段的數(shù)據(jù)分布很不均衡。所以當(dāng)存儲過程按照第一次傳入的參數(shù)生成執(zhí)行計劃并緩存下來，而按照那個參數(shù)生成的執(zhí)行計劃并不是一直都是最優(yōu)執(zhí)行計劃，那么就導(dǎo)致了性能問題出現(xiàn)了，這也就是參數(shù)嗅探問題。?

解決方法

在SQL語句后面使用HINT提示來解決參數(shù)嗅探，本想在對應(yīng)的SQL語句后面使用OPTION (RECOMPILE)?,但是考慮此存儲過程調(diào)用頻繁，而且同事極力推薦使用提示OPTION?(OPTIMIZE?FOR?UNKNOWN).修改過后，性能測試效果也確實顯著。

標(biāo)簽： 腳本 搜索

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。