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

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

如下截圖所示，此存儲(chǔ)過(guò)程是2018-09-12 9:03：01緩存的，最后一次執(zhí)行是2018-09-14 08:58，而且自上次緩存后，執(zhí)行了24875次。從這里我們基本判斷該存儲(chǔ)過(guò)程一直在重用緩存的執(zhí)行計(jì)劃，而且沒(méi)有產(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;

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

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語(yǔ)句（其實(shí)這個(gè)是實(shí)際執(zhí)行計(jì)劃，而不是預(yù)估的執(zhí)行計(jì)劃），

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

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

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

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

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

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

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

舊執(zhí)行計(jì)劃：?

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

新執(zhí)行計(jì)劃：

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

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

解決方法

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

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

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