Complex Execution Plan
There are some typical high-level things to look for when a query runs fast in one application and slow in another.
- Plan Explorer does this intentionally, by discarding the results, so that your focus is on the aspects of the query/plan that we can highlight. If your network is slow, and it takes a long time to deliver results, or the end user application is very slow at rendering results (poor code, insufficient RAM on the client, what have you), there isn't much we can do to discover that, never mind fix it. So, I would see if – while the 30-second version of the query is running – what you see as the most consistent wait type in sys.dm_exec_requests for that SPID (poll often). If it's a network-related wait, then it's possible you can stop giving this query the stink-eye and go talk to your network folks. 🙂
- Certain settings associated with the execution context of the query could lead to different versions of the plan being stored for different applications. ARITHABORT ON in one app and ARITHABORT OFF in another, for example, means there will be two different plans. You can see what attributes are set for a specific plan using sys.dm_exec_plan_attributes. You can also test if clearing these versions of the plans help (by generating a more appropriate plan, perhaps) by adding OPTION (RECOMPILE) to the query. A lot more information in this article by Erland Sommarskog. Here is a query to get you started, based on your follow-up questions:
SELECT t.[text], p.usecounts, p.cacheobjtype, p.objtype, a.value FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a WHERE t.objectid IN ( OBJECT_ID('dbo.function_name'), OBJECT_ID('dbo.procedure_name') ) AND a.attribute = N'set_options';
If you see the same text with different values for value, you can dig deeper and see which set options are different.
- Parameter sniffing could be an issue – these queries might all be sharing the same plan, one that is optimized for certain parameter values. If the runtime parameters differ from the compiled ones, it could be that SQL Server is using a plan that is far from optimal for the current values (think of a plan compiled to find all the Smiths in the phone book, and now you're searching for all the people whose name ends in a t). Again you can see if you can shake the "bad" plan by using OPTION (RECOMPILE) on the query.
- Be sure you are comparing exactly the same query. Is all of the text in the same case, has the same white space, etc.? SQL Server compares the query text by first converting to binary. So these two queries will be considered different, and generate two different plans, even on a case insensitive collation:
SELECT cols FROM dbo.table; SELECT cols from dbo.table;
Those are just a few ideas you can look into.