Sql Server-執行計畫

標籤：style   blog   http   color   os   使用   ar   strong   sp   

1.每次執行sql語句都會產生執行計畫並緩衝起來，因為產生執行計畫也需要時間開銷，因此重用執行計畫將能提高效能，並節省緩衝區空間。我們可以使用sys.dm_exec_cached_plans、sys.dm_exec_sql_text、sys.dm_exec_query_plan來查詢快取的執行計畫。

以下實驗語句每次執行前需要執行DBCC freeproccache來清空計畫快取

2.查詢執行計畫語句

select     b.[text],    a.usecounts,    a.cacheobjtype,    a.objtype,    c.query_plan,    a.size_in_bytesfrom sys.dm_exec_cached_plans aCROSS APPLY sys.dm_exec_sql_text(a.plan_handle) bCROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

說明：

　　text：代碼sql語句

　　usecounts：緩衝使用次數

　　objtype：

　　　　Prepared 預定義語句，使用參數化查詢的sql語句

           Adhoc 即席查詢，沒有參數話的查詢

3.測試

    3.1 執行3次下面語句

select * from Person.Address where AddressID = 1

     結果如下，看第2、3條，Adhoc和Prepared分別被執行3次和1次，說明執行計畫被重用了。

     3.2 執行下面3個語句

select * from Person.Address where AddressID = 1goselect * from Person.Address where AddressID = 2goselect * from Person.Address where AddressID = 3go

    結果如下，分別為每個Adhoc查詢產生了一個計畫快取

     3.3 執行下面參數話查詢2次

exec sp_executesql N‘select * from Person.Address where AddressID = @AddressID‘,N‘@AddressID int‘,@AddressID=1

    結果日下，計畫快取被使用了2次

    3.4  執行下面語句

exec sp_executesql N‘select * from Person.Address where AddressID = @AddressID‘,N‘@AddressID int‘,@AddressID=1goexec sp_executesql N‘select * from Person.Address where AddressID = @AddressID‘,N‘@AddressID int‘,@AddressID=2go

    結果如下，當查詢參數值不同時，計劃也被重用了

    3.5 執行下面語句

select * from Person.Address where City = ‘San Francisco‘goselect * from Person.Address where City = ‘Dallas‘go

    結果如下，adhoc計劃重建了。所以在後台代碼裡寫sql語句的時，如果是拼接語句的時候，如果是字串，且長度不一致時，則不會重用執行計畫，原因是對sql語句進行hash計算，根據計算結果去找是否存在執行計畫，所以一個字元差別就會導致計劃不能重用。

    3.6 執行下面語句

exec sp_executesql N‘select * from Person.Address where City = @City‘,N‘@City nvarchar(100)‘,@City=‘San Francisco‘goexec sp_executesql N‘select * from Person.Address where City = @City‘,N‘@City nvarchar(100)‘,@City=‘Dallas‘go

    結果如下，當使用參數話查詢的時候，計劃被重用了。

Sql Server-執行計畫