Difference in performance via Pofiler to SSMS

Question

Post reply

Difference in performance via Pofiler to SSMS

Maddave

SSCertifiable

Points: 6828
More actions
May 29, 2014 at 8:30 am

#287061

Our devs have an application which is performing a large query on the database. It is taking over 30 seconds to complete which is resulting in the standard ODBC timeout value killing the connection and the query fails.
If I run the query in SSMS it is taking 4 to 11 seconds to run.
I have used SQL Profiler to see if anything else is being run from the application, but can see the query being executed, taking over 30 seconds to complete and an abort is then registered when the query times out.
Copying the query text from SQL profiler and running in SSMS it again takes only 11 seconds to complete.
I thought seeing the query in SQL Profiler would show that the query is being run on the database engine and so it should perform at a similar rate as a query executed in the same way through SSMS, but this obviously isn't the case.
What could be the issue here? Can anyone point me in the right direction of things to investigate?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AronB Mr or Mrs. 500 Points: 599 More actions · Answer 1

Might be caused by parameter sniffing, http://www.sommarskog.se/query-plan-mysteries.html

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 2

You can check to see if the query plans match up, by running this query while the application is running the query:

select p.query_plan

from sys.dm_exec_requests r cross apply

sys.dm_exec_query_plan(r.plan_handle) p

where r.session_id = ??? The query plan should show up as a link in the (grid) results of SSMS. You will need to identify the session_id that the application is running the query on, but you'll have 30 seconds ;-). Once you have the plan from the application's run, you can compare that to the plan generated by your connection with SSMS.

I suspect there is some difference in the connection attributes (quoted_identifiers, ansi_nulls, arithabout, etc.) of the two connections that is causing a change in the plan. If the plans are the same, I am stumped.

Maddave SSCertifiable Points: 6828 More actions · Answer 3

Thanks for the replies.

I checked the query plans and they were both the same.

However, I've managed to find why the times were different! The query looks at a view, which has a section about only showing results for certain users. I wasn't set up in the user tables, so no results were returned. I have now been added to the user table and the query now takes over a minute to complete so reflects what the application user is seeing!

Now, I have to work out if I can improve the performance of this query!!

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

At this point, I recommend that you read the article at the second link under "Helpful Links" in my signature line below so that we may help you with the performance of this query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)