Improve plan_

jolaSQLDBA 2015-07-06 19:45:17

link textGot this plan from developer and was wondering if there are any thing i can do to help improve this plan. Developers use a lot of functions though, any help will be appreciated.

Thanks

Aaron Bertrand 2015-07-07 17:04:45
That's a very complex plan, one thing that stands out immediately is a whole slew of implicit converts, though I can't tell (since the statement is truncated) how many of them are involved in expressions within the where/group by/join clauses. Any chance you could post the whole query text somewhere? Could you also generate an actual plan from within Plan Explorer? (This will allow us to review runtime metrics like duration, CPU, memory, table I/O, etc. We can't see those in an estimated plan that was generated in Management Studio or pulled from the plan cache.)
jolaSQLDBA 2015-07-07 19:59:12
Thanks for getting back to me. I was able to generate the actual plan from within Plan Explorer and it now should include the actual query as well as other metrics.
Aaron Bertrand 2015-07-07 20:51:27
A lot of the query time is spent waiting on PAGEIOLATCH_SH. This can often mean substandard I/O (I'd have to see if there is actual latency, e.g. has the time taken to read a page into the buffer pool increased), but really the root of the issue is insufficient memory to hold the data. It's not likely that you could add enough memory to the server to support 22 million+ page reads without having to use disk, so for some of this you are at the mercy of the range of performance you can expect from your storage (especially if it is not dedicated).

That said, the plan is full of hash match joins (I count 24), many of them against the same tables repeatedly. I am sure there are ways to consolidate this logic, but my first instinct would be to dump some of this data to #temp tables first, and massage it there, before introducing these parts of the logic into the overarching optimization.

There are a lot of implicit converts on rate_code.rate_unit. What is the underlying data type? Why is it being treated like nvarchar(12)? This isn't happening in the query; I suspect there is an underlying view that is causing this (but I can't see the view definitions to confirm).

There are a series of repeated operations where there is an inequality filter: payment_detail.invoice_module_id not in (412,560). If this is a common filter criteria and not something that just happened to be observed on this single iteration, a covering filtered index might make more sense, and you could eliminate all those rows from the query altogether (I am not sure if rows where all those values are 0 make any sense to include in the report). This could also help eliminate the key lookup, though I'm not sure if this on its own would have a dramatic impact to the overall query performance.

There are also a bunch of expressions that start out with case when 0 = 1 then – I don't understand the purpose of those.

Finally, the report returns 25,000 rows, and orders them by expressions that almost certainly can't be materialized until the query runs. Is there any possibility that (a) the report can consume far fewer rows at once, and implement paging or other techniques to consume more rows, and/or (b) the ordering could be based on underlying columns rather than the resulting expressions (which wouldn't change the ordering directly – NULL and 0 would both sort first).

Those are just a few ideas off the cuff, without having any understanding of the underlying hardware (current or potential), and without seriously trying to reverse engineer the query logic at all (if this is put together by a query builder or ORM of some kind, you may not have much control over that part of it anyway).

jolaSQLDBA 2015-07-08 14:37:36
Thanks Aaron, i really appreciate your detailed explanation on this issue, i will be sure to share with your developers.