Complex Execution Plan

@SQLTrooper 2014-04-02 16:10:09

I did not author this, but would like a second set of eyes to see if there are any low hanging fruit I might be missing. It's running quite slow, but I don't have the luxury of redesigning/rewriting it.

Aaron Bertrand 2014-04-02 16:17:36
Individually, the plans seem relatively efficient, though there are some with multiple sort operations that might not be necessary (including the one deemed most costly, at least by estimate). There also seems to be some redundancy or at least some simplification that could occur with all of those IF checks (perhaps dynamic SQL would be more appropriate). I haven't looked through all of the code to see if that is the case. You didn't generate this plan from Plan Explorer – could you? What are the durations, CPU and reads we're talking about here (overall, and the longest individual one)?
@SQLTrooper 2014-04-02 16:37:42
Thanks Aaron – I did generate it from Plan Explorer. Did I miss something? It was my first ever so maybe I did something wrong.
Aaron Bertrand 2014-04-02 17:55:10
Also can you share the definition of the function dbo.SearchEngine? That seems to be where most of the time is being spent.
@SQLTrooper 2014-04-02 16:41:05
I see what you mean – like that I'm assuming.

@SQLTrooper 2014-04-02 16:49:04
If it helps, the problem seems to be that the execution time is all over the board – in the Plan Explorer execution it's showing ~5 seconds, in SSMS currently it's taking 30 seconds, via the web it's taking a similar ~30 seconds.
Aaron Bertrand 2014-04-02 17:51:35
An answer because likely too long for a comment.

There are some typical high-level things to look for when a query runs fast in one application and slow in another.

  1. 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. 🙂
  2. 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.

  3. 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.
  4. 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.

@SQLTrooper 2014-04-02 18:47:46
Wow, the size_in_bytes is 9,617,408 (9MB) for the quick one, 6,971,392 for the slower one. The SET options are different – I'm investigating those. I do realize there will be different plans when different SET options are configured but wasn't sure how to obtain that information. I'm querying for the execution plans to compare them.

How do I get the SET options for a connection other than mine again?

@SQLTrooper 2014-04-02 18:51:24
Is it possible that because this procedure has functions used throughout, that a plan wouldn't stay cached? I'm finding that query_plan is NULL all plan_handles for this objectid – assuming I'm approaching this correctly.
Aaron Bertrand 2014-04-02 20:28:12
Updated my answer with a sample query to start with. You can get the set options in place for a query that's currently running in sys.dm_exec_requests, and for an idle session in sys.dm_exec_sessions (e.g. both have an arithabort column).
@SQLTrooper 2014-04-02 20:34:28
I did have those raw values – 249 v. 4401 (for SET options) – is there a converter? I know how to get my own session SET options in SSMS, but not a remote one (the 4401, for example).
Aaron Bertrand 2014-04-02 20:39:47
@SQLTrooper 2014-04-02 21:05:35
SET ARITHABORT OFF in SSMS took care of that (runs in five seconds versus 30), but doens't address the crux of the performance issue I don't think. I have more homework to do – your patience and help is much appreciated Aaron!
@SQLTrooper 2014-04-02 20:27:11
Why in the Showplan XML would there be no "actual" figures? I ran it in SSMS and captured the trace. There should be actuals as well. I have found some statements that have differing execution plans and am diving in to those.
Aaron Bertrand 2014-04-02 20:29:50
The plan doesn't store runtime metrics (and trace doesn't get them from the plan, either). In fact if you run a plan in Plan Explorer and save as .sqlplan, you lose runtime metrics too. We can only save these values by extending the XML schema in our proprietary .queryanalysis and .pesession files.