[SQL Server] FIX: Invalid object name 'master.dbo.spt_values'

How to fix: Invalid object name 'master.dbo.spt_values' ?

• It's only need to execute the u_tables.sql file with Dedicated Administrator Connection(DAC).
• No downtime.

For example: 

• Getting error on ALT+F1 in SSMS

---

Symptoms

• Getting error on Database Properties in SSMS
• Getting error in sp_help 'objectname'
• Getting error on ALT+F1 in SSMS

Encounters errors and the following messages:

Msg 208, Level 16, State 1, Procedure sp_help, Line 103 [Batch Start Line 5]
Invalid object name 'master.dbo.spt_values'.

Error 1: Getting error on Database Properties in SSMS

-- figure 11_Getting error on Database Properties in SSMS




-- figure 12_Getting error on Database Properties in SSMS




Error 2: Getting error in sp_help 'objectname'

-- figure 21_Getting error in sp_help 'objname'




Error 3: Getting error on ALT+F1 in SSMS

ALT+F1 Keyboard Shortcuts

-- figure 31_ALT+F1

---

Cause

• From the error it looks like view object: dbo.sp_values is not found in the Resource database(mssqlsystemresource.mdf).
• Don't restore the master database.

---

Resolution

• It's only necessary to use a Dedicated Administrator Connection(DAC), and execute the u_tables.sql file in Query Editor.
• No downtime.

---

FIX: Invalid object name 'master.dbo.spt_values'


Demo Version: SQL Server 2017


To connect to a server using the DAC


01. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

02. In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance.

• For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

03. Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

• The connection is made.
• If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

-- figure 61_DAC




-- figure 62_DAC




Execute the u_tables.sql file in Query Editor

The u_tables.sql file is located in the following folder:

• C: represents the drive where you installed the SQL Server program files
• Version number 14 is SQL Server 2017.

C:\Program Files\Microsoft SQL Server\MSSQL14.\MSSQL\Install

-- figure 41_u_tables.sql file is located in the following folder

Execute the u_tables.sql file in Query Editor, and Fix this issue.

• It has been successfully completed.

-- figure 63_Fix this issue

Starting u_Tables.SQL at  26 Jan 2018 07:51:34:150
This file creates all the system tables in master.
Creating view 'spt_values'.
drop table spt_monitor ....
Creating 'spt_monitor'.
Grant Select on spt_monitor
Insert into spt_monitor ....
Finishing at  26 Jan 2018 07:51:34:277

Verification 

Execute the sp_help.

-- figure 71_sp_help

 

---

Execute the u_tables.sql file without use DAC


Execute the u_tables.sql file without use DAC,  then the query encounters errors and the following messages:

-- figure 51_Error_Execute the u_tables.sql file without use DAC

Starting u_Tables.SQL at  26 Jan 2018 07:49:09:660
This file creates all the system tables in master.
Creating view 'spt_values'.
Msg 208, Level 16, State 1, Procedure spt_values, Line 7 [Batch Start Line 49]
Invalid object name 'sys.spt_values'.
sp_MS_marksystemobject: Invalid object name 'spt_values'
Msg 15151, Level 16, State 1, Line 62
Cannot find the object 'spt_values', because it does not exist or you do not have permission.
drop table spt_monitor ....
Creating 'spt_monitor'.
Grant Select on spt_monitor
Insert into spt_monitor ....
Finishing at  26 Jan 2018 07:49:09:950

---

Sample Code

20180128_spt_values_issue

https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

References

Resource Database
https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

Use the system_health Session
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

Using a Dedicated Administrator Connection
https://technet.microsoft.com/en-us/library/ms189595(v=sql.105).aspx

Diagnostic Connection for Database Administrators
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators

SQL Server Management Studio Keyboard Shortcuts
https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts

[SQL Server] Incorrect Performance Tuning in stored procedure: Local Variables issue

習以為常的 Performance Tuning (效能調教) 方法，卻是不正確的!


試想，有一支龐大的 Stored Procedures 要執行 Performance Tuning

• 某段 SQL Query 可能是 Bottlenecks
• 如何使用 SSMS 對該 SQL Query 執行 Performance Tuning 呢？

請參考以下範例：

-- figure 100_Trap in Local Variables

又是 踩到 "Local Variables / OPTIMIZE FOR UNKNOWN" 這個 雷區。


在執行 Stored Procedure 的 Performance Tuning 時，記得這項規則：

利用 SSMS 管理工具對 Stored Procedure 執行 測試 與 改寫 時，使用

• Literal Values (常值)
• Temporary Stored Procedures
• Stored Procedures

不要額外使用 Local Variables 改寫 SQL Query 。

---

Incorrect Performance Tuning in stored procedure: use Local Variables issue



01. 建立 Stored Procedure: dbo.up_PerformanceTuning_LocalVariables_Issue

假設：此 Stored Procedure 有多行 SQL Query。


-- figure 01_Create Stored Procedure_up_PerformanceTuning_LocalVariables_Issue




初步分析，以下 SQL Query 可能是 Performance Bottlenecks (效能瓶頸)：

SELECT UnitPrice FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID

那該如何使用 SSMS 對此 SQL Query 執行 Performance Tuning 呢？


有一支龐大的 Stored Procedures，若要 Performance Tuning  某一段 SQL Query，流程是：

• Task 1:  先由 Plan Cache 找出 Execution Plan
• Task 2:  找出 Execution Plan 的 Parameter Compiled Value
• Task 3:  使用 Literal Values (常值) 或 Temporary Stored Procedures

---

Find out Parameter Compiled Value


How to find out Execution Plan of Parameter Compiled Value?


找出 Parameter Compiled Value

如何找出 Execution Plan 的 Compiled Value?



01. 先由 Plan Cache 中，找出此 SQL Query 現行的 Execution Plan。

請參考：[SQL Server] Look at parameter from execution plan

• return 2 row
• Index Seek: [IX_SalesOrderDetail_ProductID]

-- figure 02_Get last performance statistics for cached query




02. 檢視此 Execution Plan。

-- figure 03_Show Execution Plan XML





02. 點選 "Show Execution Plan XML..."

• 觀察：ParameterList
• ParameterCompiledValue="(897)"

-- figure 04_Parameter Compiled Value





03. 若使用 SentryOne Plan Explorer，點選  "View with SentryOne Plan Explorer"

• Compiled Value : 897

-- figure 05_View with SentryOne Plan Explorer

---

Incorrect Performance Tuning in stored procedure: use Local Variables issue


Trap in Local Variables

對 Stored Procedure 執行 不正確的 Performance Tuning 


陷入 Local Variables 陷阱之中



01. 若習以為常的使用 Local Variables 來執行 Performance Tuning，將會是以下範例：


-- figure 11_Incorrect Performance Tuning in stored procedure_Local Variables issue





02. 陷入 Local Variables 陷阱之中，對 Stored Procedure 執行 不正確的 Performance Tuning

• Not match Execution Plan, Bad
• return 2 row
• Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

仔細留意：

• 此 Execution Plan 不僅是使用 執行效能差 的 Index Scan。
• 而且，竟然與先前由 Plan Cache 取得的 Execution Plan 不同!

-- figure 12_Execution Plan_Incorrect Performance Tuning





原因是

• 踩到 "Local Variables / OPTIMIZE FOR UNKNOWN" 這個 雷區!

請參考：[SQL Server] Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-local.html

---

Correct Performance Tuning in stored procedure


Solution

• Use Literal Values
• Use Temporary Stored Procedures

Solution 01: Use Literal Values


01. 直接使用 Literal Values (常值)

• 將原本使用 Input Parameter ，直接使用 Literal Values 來取代。

觀察 Execution Plan

• 與先前由 Plan Cache 取得的 Execution Plan 相同。
• Index Seek: [IX_SalesOrderDetail_ProductID]

-- figure 21_Solution 01 Use Literal Values







Solution 02: Use Temporary Stored Procedures


 01. 建立 Temporary Stored Procedures (暫存預存程序)

• 宣告以 # 或 ##  開頭的 Temporary Stored Procedures。

Temporary Stored Procedures 有 2 種：

• Local (區域): single number sign (#)
• Global (全域): two number signs (##)

觀察 Execution Plan

• 與先前由 Plan Cache 取得的 Execution Plan 相同。
• Index Seek: [IX_SalesOrderDetail_ProductID]

-- figure 31_Solution 02 Use Temporary Stored Procedures




在執行 Stored Procedure 的 Performance Tuning 時，記得這項規則：

利用 SSMS 管理工具對 Stored Procedure 執行 測試 與 改寫 時，使用

• Literal Values (常值)
• Temporary Stored Procedures
• Stored Procedures

不要額外使用 Local Variables 改寫 SQL Query 。

---

Sample Code

20180122_IncorrectPerformanceTuning_LocalVariables

https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Constants

常數也稱為常值或純量值，是一個代表特定資料值的符號。
A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value.

• Constant 常數
• Literal 常值
• Scalar value 純量值

Temporary Stored Procedures 

有兩種：Local  區域與 Global 全域。

它們在名稱、可見性和可用性方面有些差異。

Local Temporary Stored Procedures 

• 是以單一數字符號 (#) 作為名稱的第一個字元；
• 只有目前連接的使用者才能看見它們，當使用者中斷與 SQL Server 執行個體的連接時，就會刪除它們。

Global Temporary Stored Procedures 

• 是以兩個數字符號 (##) 作為名稱的前兩個字元；
• 只要一建立好，任何使用者都能看見它們，只有當所有參考這些 Stored Procedures 的使用者都中斷與 SQL Server 執行個體的連接時，才會刪除它們。

---

參考資料

[SQL Server] Ad hoc Query: Local Variable and Without Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-ad-hoc-query-local-variable.html

[SQL Server] Look at parameter from execution plan
http://sharedderrick.blogspot.tw/2017/12/sql-server-look-at-parameter-from.html

Constants (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql

[SQL Server] Ad hoc Query: Local Variable and Without Local Variable

延續前文：[SQL Server] Parameter Sniffing: Local Variable


來個有趣的問題，給 SQL Server Developer：
以下這兩段 SQL Query，有何不同？

What's the difference between 01 and 02?

-- figure 01_Whats the difference between 01 and 02

大相徑庭 的 Execution Plan

• 即便回傳筆數相同
• 但 02. 使用 Local Variable

-- figure 02_Execution_Plan

換成是 Ad hoc Query，也受到 Local Variables / OPTIMIZE FOR UNKNOWN 的影響，停用 Parameter Sniffing 。


評估方式
"Local Variables / OPTIMIZE FOR UNKNOWN" 

Conditions	Query Optimizer only use
equality: =	Rows * All density
inequality: >  ,  < , etc.,	Rows * 30%

當條件式是：

• 使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 Cardinality Estimation，也就是： Rows * All density。
• 使用 Inequality (不相等)，則 Query Optimizer 連 density vector 也不用了，就直接設定資料列總數的 30% 來執行 Cardinality Estimation，也就是： Rows * 30% 。

Compare Match and Not match Execution Plan


以 Local Variables / OPTIMIZE FOR UNKNOWN 為例：


若  ProductID  = 897

• Not match Execution Plan, Bad
• 理想是 Logical Reads: 14，若改用 Local Variable，耗用增加到 1246，差距 89 倍。
• 原本是 CPU Time(sec): 0.000488000，若改用 Local Variable，耗用增加到 0.011253000，差距 23 倍。

---

Ad hoc Query: Local Variable and Without Local Variable


01. 檢視：資料表 Sales.SalesOrderDetail 與 Index 資訊。

• 資料表總筆數是：121,317 rows

-- figure 01_Get Table and Index Information




02. 檢視 依據 ProductID 分組資料

• ProductID = 897,  return 2 rows.
• ProductID = 870,  return 4688 rows.

-- figure 02_Query Data by ProductID




03. 執行查詢 WHERE ProductID = 897，檢視 Execution Plan



WHERE ProductID = 897

• Good Execution Plan

• return 2 row
• Index Seek: [IX_SalesOrderDetail_ProductID]

• Number of Rows Read: 2
• Actual Number of Rows: 2
• Estimated  Number of Rows: 75.6667
• Estimated Number of Rows to be Read: 75.6667

-- figure 11_ProductID_897_Execution_Plan




Actual Number of Rows: 2
Number of Rows Read: 2
Estimated  Number of Rows: 75.6667

-- figure 12_Estimated  Number of Rows



04. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO

• LastElapsedTime(sec): 0.073422000
• LastCPUTime(sec)]: 0.000488000
• LastRows: 2
• EstimatedNumberofRows: 75.6667
• Last_logical_reads: 14

-- figure 13_Performance_Statistics

---

Ad hoc Query with Local Variable


01. 宣告 DECLARE @ProductID INT = 897：

• WHERE ProductID = @ProductID
• Use Local Parameter

觀察 Execution Plan：

@ProductID INT = 897

• Not match Execution Plan, Bad

• return 2 row
• Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
• Suggestion: Missing index ???
• Number of Rows Read: 121317
• Actual Number of Rows: 2
• Estimated  Number of Rows: 456.079
• Estimated Number of Rows to be Read: 121317

-- figure 21_Ad hoc Query with Local Variable

• Actual Number of Rows: 2
• Number of Rows Read: 121317
• Estimated  Number of Rows: 456.079

-- figure 22_Estimated  Number of Rows





02. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO

• LastElapsedTime(sec): 0.028126000
• LastCPUTime(sec)]: 0.011253000
• LastRows: 2
• EstimatedNumberofRows: 456.079
• Last_logical_reads: 1246

-- figure 23_Performance_Statistics

---

Why does Estimated  Number of Rows get 456.079 ?


為什麼是使用 EstimatedNumberofRows: 456.079 來評估建立 Execution Plan 呢?


01. Show Execution Plan XML，可以到相關的資訊。


-- figure 31_Show Execution Plan XML



02. 使用 DBCC SHOW_STATISTICS，顯示目前的 query optimization 的 statistics。

• Query Optimizer 使用 statistics 來預估 Cardinality (基數) 或 查詢結果中的資料列數，如此可讓 Query Optimizer 建立高品質的 Execution Plan。
• 舉例來說，Query Optimizer 可使用 Cardinality Estimates (CE) 估來選擇 Execution Plan 中的 index seek operator，而不是 index scan operator，避免發生 resource-intensive index scan 來提高查詢效能。

Query Optimizer 使用 statistics 物件中的相關資料來計算 Cardinality Estimates (CE) 。

DBCC SHOW_STATISTICS 可以顯示儲存在 statistics 物件中的資料，包含：

• Header (標頭)
• Density Vector (密度向量)
• Histogram (長條圖)

請參考 [SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-option_14.html



觀察 DBCC SHOW_STATISTICS 所顯示的資料。

• 在 Header(標頭)
• Rows: 121317
• 在 Density Vector (密度向量)
• All density: 0.0.003759399

-- figure 41_Displays Query Optimization statistics





03. 謎底揭曉：

• 0.003759399 * 121317 = 456.079008483，約等於 456.079。

-- figure 51_All density






當 Ad hoc Query with Local Variable and Equality Operator ：

• 使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation，也就是： Rows * All density。

---

Ad hoc Query with Local Variable and Inequality Operator


Ad hoc Query and Inequality Operator

Use Inequality Operators, 使用 不相等

01. 執行查詢 WHERE ProductID > 897，檢視 Execution Plan

WHERE ProductID > 9999

• Good Execution Plan

• return 0 row
• Index Seek: [IX_SalesOrderDetail_ProductID]
• Number of Rows Read: 0
• Actual Number of Rows: 0
• Estimated  Number of Rows: 1
• Estimated Number of Rows to be Read: 1

-- figure 61_Ad hoc Query and Inequality Operator

• Actual Number of Rows: 0
• Number of Rows Read: 1

-- figure 62_Estimated  Number of Rows



03. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO

• LastElapsedTime(sec): 0.022990000
• LastCPUTime(sec)]: 0.000421000
• LastRows: 0
• EstimatedNumberofRows: 1
• Last_logical_reads: 2

-- figure 63_Performance_Statistics

---

Ad hoc Query with Local Variable and Inequality Operator


01. 宣告 DECLARE @ProductID INT > 9999：

• WHERE ProductID > @ProductID
• Use Local Parameter
• Use Inequality Operator

觀察 Execution Plan：

WHERE ProductID > @ProductID

• Not match Execution Plan, Bad
• return 0 row
• Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
• Number of Rows Read: 121317
• Actual Number of Rows: 0
• Estimated  Number of Rows: 36395.1
• Estimated Number of Rows to be Read: 121317

-- figure 71_Ad hoc Query with Local Variable and Inequality Operator

• Actual Number of Rows: 0
• Number of Rows Read: 121317
• Estimated  Number of Rows: 36395.1

-- figure 72_Estimated  Number of Rows





02. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO

• LastElapsedTime(sec): 0.031641000
• LastCPUTime(sec)]: 0.009111000
• LastRows: 0
• EstimatedNumberofRows: 36395.1
• Last_logical_reads: 1246

-- figure 73_Performance_Statistics




為什麼是使用 EstimatedNumberofRows: 36395.1 來評估建立 Execution Plan 呢?


03. 觀察 DBCC SHOW_STATISTICS 所顯示的資料

• 在 Header(標頭)
• Rows: 121317

-- figure 81_Displays Query Optimization statistics




-- 04. 謎底就是：

• Inequality: EstimatedNumberofRows is 30% of the rows
• 121317 * 0.3 = 36395.1

-- figure 82_EstimatedNumberofRows is 30




當 Ad hoc Query with Local Variable and Inequality Operator ：

• Query Optimizer 一律都 僅 使用 Rows * 30% 方式來評估

也就是說，輸入什麼 parameter，回傳多少筆數，Query Optimizer 都不列入考量。

---

Sample Code

20180120_Ad hoc Query_Local Variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

參考資料

[SQL Server] Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-local.html

[SQL Server] Parameter Sniffing: Local Variable

延續前文：[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2


使用 "Local Variables / OPTIMIZE FOR UNKNOWN"

• 如果無法識別出 常用 的 parameter 值，或是 Query Optimizer 使用此 parameter 值所 compile 的 Execution Plan 效率不佳(例如：由於 data skew 數據傾斜)。
• SQL 開發人員 設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。
• 至於輸入什麼 parameter，回傳多少筆數，讓 Query Optimizer 都不要列入考量，也不使用 Parameter Sniffing。
• 在此次範例中，甚至連該有的 Missing Index 建議也不見了!
  
• "Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能。
• 先前，若要停用 Parameter Sniffing ，要對每一個 Input Parameter，額外再多宣告對應出 Local Parameter 來承接。
• 在 SQL Server 2008 可以改用 OPTIMIZE FOR UNKNOWN，這是 SQL Statement 陳述式層級方式來 停用 Parameter Sniffing。

使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 建立 Reuse Execution Plan 是個好方法嗎？

• 有提高 執行效能？
• 如何評估 Index 呢？

評估方式
"Local Variables / OPTIMIZE FOR UNKNOWN" 

Conditions	Query Optimizer only use
equality: =	Rows * All density
inequality: >  ,  < , etc.,	Rows * 30%

當條件式是：

• 使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 Cardinality Estimation，也就是： Rows * All density。
• 使用 Inequality (不相等)，則 Query Optimizer 連 density vector 也不用了，就直接設定資料列總數的 30% 來執行 Cardinality Estimation，也就是： Rows * 30% 。

試想：

• 這種方式建立出來的 Execution Plan 會有好的效能嗎？
• 這會是 效能最佳化 的 Execution Plan 嗎？

Compare Match and Not match Execution Plan


以 Local Variables / OPTIMIZE FOR UNKNOWN 為例：


若 @StateProvinceID= 32

• Not match Execution Plan, Bad
• 理想是 Logical Reads: 6，但卻因故 誤用，而耗用到 218，差距 36.33 倍。
• 原本是 CPU Time(sec): 0.000448000，但卻因故 誤用，而耗用到 0.001645000，差距 3.67 倍。

若 @StateProvinceID= 9

• 算是 Match Execution Plan。
• 但該有的 Missing Index 建議 卻不見了!

---

Local Variable and Equality Operator


01. 建立 Stored Procedure: up_Parameter_Local_Variable

• Use Input Parameter
• Use Local Variable
• Use Equality Operator

• SQL 開發人員 為了要 停用 Parameter Sniffing，設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 Cardinality Estimation。
• 至於輸入什麼 parameter，回傳多少筆數，讓 Query Optimizer 都不要列入考量。

要達成 停用 Parameter Sniffing 方法是：

• 額外再宣告 Local Variable。
• 承接來自於 Input Parameter 的值
• 設定條件式僅使用 Local Variable。

-- figure 01_Local Variable




02. 設定 Input Parameter: @StateProvinceID= 32，執行此 Stored Procedure。

• Input Parameter: StateProvinceID= 32
• Use Local Parameter

• Actual Number of Rows 是: 1。
• Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] 
• 但評估 Estimate Number of Rows 卻是: 265.054 ?

Local Variable 與 OPTIMIZE FOR UNKNOWN 兩者是一樣的功能，有著相同的 誤用問題!


-- figure 02_Local Variable_StateProvinceID_32





03. 由 SSMS 的 GUI 介面，觀察 Execution Plan

• StatementEstRows="265.054"
• OptimizeStatsUsage

在 OptimizeStatsUsage，可以看是 Statistics: [IX_Address_StateProvinceID]。

也就是說，Query Optimizer 是使用 Index: [IX_Address_StateProvinceID] 的 statistics (統計資料)，執行 Query Optimization。

-- figure 03_OptimizeStatsUsage





04. Show Execution Plan XML，可以到相關的資訊。

• 在 OptimizeStatsUsage，可以看是 Statistics: [IX_Address_StateProvinceID]。

-- figure 04_XML_OptimizeStatsUsage

---

Changing Parameter Values @StateProvinceID = 9

01. 執行 Stored Procedure，輸入 Parameter: @StateProvinceID= 9

• Input Parameter: StateProvinceID= 9
• Use Local Parameter

改採 Use Local Parameter方式來建立 Execution Plan 後，當 StateProvinceID=9，竟然 沒有 原本該有的 Missing Index 建議。

由 SSMS 的 GUI 介面，觀察 Execution Plan 的 Estimate Number of Rows 也是: 265.054 ?

-- figure 11_Local Variable_StateProvinceID_9




後續的相關測試，請參考先前的文章：

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能，有著相同的 誤用問題!

---

Local Variable and Inequality Operator


Use Inequality Operators, 使用 不相等


01. 建立新的 Stored Procedure: up_Parameter_Local_Variable_Inequality，使用 Inequality Operators。

• Use Local Parameter
• Use < (Less Than) Operators

-- figure 21_inequality





02. 執行 Stored Procedure，輸入 Parameter: @StateProvinceID= 2

• Number of Rows Read: 19614
• Actual Number of Rows: 25
• Estimated  Number of Rows: 5884.2
• Estimated Number of Rows to be Read: 19614

Use Local Parameter，使用 < (Less Than)

• return 25 rows
• 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?

為什麼是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?


-- figure 31_inequality_StateProvinceID_2_Execution_Plan





03. 再度更換 Parameter: @StateProvinceID= 181

• Number of Rows Read: 19614
• Actual Number of Rows: 19583
• Estimated  Number of Rows: 5884.2
• Estimated Number of Rows to be Read: 19614

Use Local Parameter，使用 < (Less Than)

• return 19583 rows
• 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?

怎麼又是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?

-- figure  41_inequality_StateProvinceID_181_Execution_Plan

後續的相關測試，請參考先前的文章：

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能，有著相同的 誤用問題!

---

Sample Code

20180116_Parameter_Sniffing_Local_Variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

參考資料

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-option_14.html