March 31, 2015 at 1:19 pm
I have a simple query that is generated från Entity Framework that takes about 55 seconds to complete in an .NET application. When I run the query in SSMS with the same parameters it runs in about 4 seconds.
I'm aware that different set options can generate different query plans. I use Profiler to display "Showplan XML Statistics Profile" and I can verify that the query plans are identical but still Profiler shows very different durations depending on whether the query is run from the application or not. The duration for the slow running query is about 50 seconds.
I have also used DMVs to display execution_count from sys.dm_exec_query_stats to verify that the same query plan is being used in both cases.
The query itself is very simple but it returns almost 100.000 records. If I rewrite the query as a stored procedure it runs fast in both application and SSMS but still using the same simple execution plan.
I would like to understand what causes this behaviour.
What am I missing?
March 31, 2015 at 3:34 pm
Are all the properties on each of the operators in the plan exactly the same?
On a guess, I'd be inclined to suggest differences in the ANSI settings possibly causing differences in how the query is returned... but I'm unsure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2015 at 8:36 am
Are you missing any settings/fine tuning on .net?
.Net application connects remotely? or from the same server?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 13, 2015 at 10:26 am
Find your set options using your query
SELECT plan_handle, usecounts, pvt.set_options,text
FROM (
SELECT plan_handle, usecounts, epa.attribute, epa.value ,text
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
CROSS APPLY sys.dm_exec_sql_text(plan_handle) a
WHERE cacheobjtype = 'Compiled Plan' and a.text like '%MYQUERYTEXT%') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
THEN plug the setoptions for your 2 queries into the below and see what set options differ.
declare @set_options int = 4347
if ((1 & @set_options) = 1) print 'ANSI_PADDING'
if ((4 & @set_options) = 4) print 'FORCEPLAN'
if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'
if ((16 & @set_options) = 16) print 'ANSI_WARNINGS'
if ((32 & @set_options) = 32) print 'ANSI_NULLS'
if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'
if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'
if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'
if ((512 & @set_options) = 512) print 'NoBrowseTable'
if ((4096 & @set_options) = 4096) print 'ARITH_ABORT'
if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'
if ((16384 & @set_options) = 16384) print 'DATEFIRST'
if ((32768 & @set_options) = 32768) print 'DATEFORMAT'
if ((65536 & @set_options) = 65536) print 'LanguageID'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply