July 24, 2017 at 3:20 am
Good morning Experts,
When the developer is running a query from SSMS it completes in seconds. When the developer is running the same query from a reporting application(front end) it is taking time in minutes. I checked for blocking and there is no blocking. Could you please help me in fixing this
July 24, 2017 at 3:52 am
Check the ExecutionLog table in Reporting Services to see how much of the time the report took to execute was query time and how much was rendering time. Also check the settings for SSRS and SSMS. Settings can vary depending on provider and login, and differences in default language and ANSI_NULL settings, for example, can change the way that the query runs? Does the SSRS query run over a slower network link? Compare the execution plans for the SSMS and SSRS queries - do they look the same? Is the query in question the only query that the report runs?
John
July 24, 2017 at 4:32 am
See also Erland Sommarskog's epic post Slow in the Application, Fast in SSMS for other things to investigate.
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 24, 2017 at 7:28 am
I agree with article posted above, it can seem overwhelming though. One of the first things I check is mentioned in section 2.5 of that article, "The Default Settings". Check if the sessions running the query have the same connection properties, such as ARITHABORT which Microsoft says should always be set to ON, as it is in SSMS, even though for .Net and other applications the default is OFF:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql
You can configure an instance to default all connections to ARITHABORT ON using the server properties, connections page, arithmetic abort setting:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page
July 24, 2017 at 9:04 am
It sounds like you're getting different execution plans for the same query, depending whether it's run from SSMS or application. For clues about why, use the DMV script below to return cached plan information. Replace reference to '%SomeTextThatIdentifiesQuery%' as needed to identify your specific query. The plan cache won't tell you what application or login created the plan, but you can refer to column plan_created for clue about when it was created.
SELECT
@@servername as servername
, db_name(qp.dbid) dbname
, object_name( qp.objectid, qp.dbid ) objectname
, qs.creation_time AS plan_created
, qs.last_execution_time AS last_executed
, convert(varchar,dateadd(ms,qs.last_elapsed_time /1000
,getdate())-getdate(),114) as last_exec_time
, convert(varchar,dateadd(ms,qs.last_worker_time /1000
,getdate())-getdate(),114) as last_cpu_time
, qs.last_physical_reads
, qs.last_logical_reads
, qs.execution_count
, convert(varchar,dateadd(ms,qs.total_elapsed_time /qs.execution_count /1000
,getdate())-getdate(),114) as avg_exec_time
, convert(varchar,dateadd(ms,qs.total_worker_time /qs.execution_count /1000
,getdate())-getdate(),114) as avg_cpu_time
, qs.last_physical_reads /qs.execution_count AS avg_physical_reads
, qs.last_logical_reads /qs.execution_count AS avg_logical_reads
, st.[text] as sql_text
, qp.query_plan
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_query_plan ( cp.plan_handle ) as qp
left outer join sys.dm_exec_query_stats AS qs on qs.plan_handle = cp.plan_handle
outer apply sys.dm_exec_sql_text (cp.plan_handle) as st
where st.[text] LIKE '%SomeTextThatIdentifiesQuery%'
order by
statement_start_offset;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply