Complex Execution Plan

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.

avatar image By @SQLTrooper 86 asked Apr 02, 2014 at 04:10 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 02, 2014 at 04:17 PM

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)?

avatar image @SQLTrooper Apr 02, 2014 at 04:37 PM

Thanks Aaron - I did generate it from Plan Explorer. Did I miss something? It was my first ever so maybe I did something wrong.

avatar image Aaron Bertrand ♦ Apr 02, 2014 at 05:55 PM

Also can you share the definition of the function dbo.SearchEngine? That seems to be where most of the time is being spent.

10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 02, 2014 at 05:51 PM
more ▼
(comments are locked)
avatar image @SQLTrooper Apr 02, 2014 at 06:47 PM

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?

avatar image @SQLTrooper Apr 02, 2014 at 06:51 PM

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.

avatar image Aaron Bertrand ♦ Apr 02, 2014 at 08:28 PM +

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).

avatar image @SQLTrooper Apr 02, 2014 at 08:27 PM

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.

avatar image Aaron Bertrand ♦ Apr 02, 2014 at 08:29 PM

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.

10|10000 characters needed characters left

I see what you mean - like that I'm assuming.

avatar image By @SQLTrooper 86 answered Apr 02, 2014 at 04:41 PM
more ▼
(comments are locked)
avatar image @SQLTrooper Apr 02, 2014 at 04:49 PM

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.