When you observe performance difference between the application layer executing a stored procedure, v/s running the same exact call via SSMS, I found there actually exist 2 distinct query plans in the procedure cache. On running this query you will find identical values for the query_hash but different values for the query_plan_hash.
SELECT
cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.plan_handle AS PlanHandle,
execution_count,
query_hash,
query_plan_hash
FROM sys.dm_exec_cached_plans
AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
join sys.dm_exec_query_stats
qs on qs.plan_handle = cp.plan_handle
WHERE OBJECT_NAME(st.objectid,st.dbid) = 'CustomerOriginGet'
ORDER BY cp.usecounts DESC
ObjectType ObjectName ExecutionCount execution_count query_hash query_plan_hash
Proc CustomerOriginGet 10 10 0x27CBF0C0A5FFAC05 0x7FC50685222DDBF8
Proc CustomerOriginGet 8 8 0x27CBF0C0A5FFAC05 0xD1990C7B57DB8B71
I had to dig further to find the reason why 2 different plans were being generated for the same stored procedure. Using the above query to retrieve only the QueryPlan as "Saving As..." I saved the XML plan in a *.sqlplan file. Open this in SSMS for the graphical format.
On retrieving the properties of the root node select of the query plan, expand the Set Options.
Application Query Plan
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True
SSMS Query Plan
ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True
The connection SET settings affect the query plan that is created.
As warned by this documentation of SET ARITHABORT
SET ARITHABORT
http://msdn.microsoft.com/en-us/library/ms190306.aspx
Caution
|
|---|
| The default ARITHABORT setting for SQL Server
Management Studio is ON. Client applications setting ARITHABORT to OFF
can receive different query plans making it difficult to troubleshoot
poorly performing queries. That is, the same query can execute fast in
management studio but slow in the application. When troubleshooting
queries with Management Studio always match the client ARITHABORT
setting. Additional Sources that helped me gather this information. |
Use this DMV to query long XML query plans:
http://msdn.microsoft.com/en-us/library/windowsazure/hh977104.aspx
Slow in the Application, Fast in SSMS?
Understanding Performance Mysteries
http://www.sommarskog.se/query-plan-mysteries.htmlSET Options That Affect Results
http://technet.microsoft.com/en-us/library/ms175088%28v=sql.105%29.aspx
Determining SET Options for a Current Session in SQL Server
http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/
same query showing different execution plans on the same server
http://ask.sqlservercentral.com/questions/95689/same-query-showing-different-execution-plans-on-th.html
