SQL Server - Payroll query kills database every month

Question

so admittedly I'm not the best at query tuning, and could really use some guidance on how to tune this query. It's a month end payroll query that pulls A LOT of data, but ultimately doesn't need all of it (atleast I don't think). I used Ozar's paste the plan, maybe someone can provide some insight.

I want to communicate with the SWE's what is happening, and need good examples on how to address it. Any help would be greatly appreciated. Thank you!!

The estimated plan is here: https://www.brentozar.com/pastetheplan/?id=0YOij6eghf

The full query is here: https://gist.github.com/gomad3000-hub/e53f0130bc4759b91b5d1e346958449c

Answer 1

darling has a difficulty

There are likely too many problems here to solve via Q&A, but I can make some observations.

First, you're at a significant disadvantage with the server itself

• You're on SQL Server 2012 (Build="11.0.7512.11")
• The hardware is likely quite underpowered (EstimatedAvailableDegreeOfParallelism="2")

I can't see the settings or the hardware, but people don't usually set MAXDOP to 2 when they have an abundance of CPU cores. If MAXDOP is set to 0, then you only have two CPU cores. That ain't good.

In many cases, having an available DOP of 2 will make choosing a parallel plan unattractive because the cost reduction isn't considered significant. With an estimated cost of 1924180 query bucks, this plan should be a very good candidate for a parallel execution plan.

Being on SQL Server 2012 limits many of the newer choices for tuning queries like this, e.g. good support for batch mode execution.

Another thing I can't see is the full query text. The select list is so long that it's cut off before the from clause.

I can see a preview of the overall complexity from a filter operator. The predicate here is quite involved.

NUTS

Queries like this almost always benefit from being broken up into smaller chunks, e.g. putting some intermediate results into #temp tables.

Another good sign that this is a reasonable strategy here is that the compile time alone for this query is about 20 seconds (CompileTime="19889" -- measured in milliseconds for your convenience).

Other things the query plan shows: hardly any of these tables have clustered indexes to support joins. Many of the operators are just table scans:

NUTS

There may be nonclustered indexes on the tables, even nonclustered primary keys, but SQL Server isn't choosing them because of how many rows are estimated to leave them. It becomes prohibitive to seek or scan + lookup.

I can infer that there are a lot of narrow (perhaps only single column) nonclustered indexes on some of these tables because of the amount of lookups that occur at various points in the plan, where the lines are much more narrow, and doing so is a more viable strategy. Here are a couple as an example.

NUTS

At one point, SQL Server creates two indexes for you on the fly:

NUTS

See my post here about how to fix Eager Index Spools. These only tend to happen when things are quite desperate.

Anyway, good luck with this one. You're not in an enviable position dealing with this considering the environmental disadvantages.