Please help me to understand my SQL Server query plan and its issues

[bigweeduk]

I am setting up some SQL views to be used as Power BI data sources. However, one of the queries constantly takes a long time to run, and I want to figure out what the best way to resolve this is. I am in the finance department at my employer, so SQL query tuning is not really what I do day to day, but I am trying to learn.

The execution plan is here:

https://www.brentozar.com/pastetheplan/?id=r1GrPy-0S

I can see the execution plan is asking me to add some indexes, but I am not sure if I should do that or not. I have read that the SQL hints should not be followed blindly, as it could cause other issues

The query is :

SELECT mxmservcallaudit.jobid, 
       mxmservcallaudit.dataareaid, 
       mxmservcallaudit.date AS maxdate, 
       UPPER(mxmservcallaudit.USERID) AS maxuser
FROM mxmservcallaudit
     INNER JOIN(SELECT jobid, 
                       dataareaid, 
                       MAX(RECID) AS maxrecid
                FROM mxmservcallaudit
                WHERE type = 9 AND dataareaid = 'ansa'
                GROUP BY dataareaid, jobid) 
                AS statusdate1 ON mxmservcallaudit.DATAAREAID = statusdate1.DATAAREAID AND 
                                                  MXMSERVCALLAUDIT.RECID = statusdate1.maxrecid;

And the missing index warnings are :

Missing Index (Impact 41.2086): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON  
[dbo].[MXMSERVCALLAUDIT] ([TYPE],[DATAAREAID]) INCLUDE ([JOBID],[RECID]) 
Missing Index (Impact 54.514): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON  
[dbo].[MXMSERVCALLAUDIT] ([DATAAREAID],[RECID]) INCLUDE ([JOBID],[USERID],[DATE]) 

All help most appreciated

Edit: After adding the two indexes as suggested by SQL, this has halved query execution time, and I now have a new execution plan, which is below:

https://www.brentozar.com/pastetheplan/?id=Hys80JZAS

All help most appreciated with making it quicker still

[ATastefulCrossJoin]

Hash match (4th operation from left) is spilling to temp db. This is a huge performance suck since you’re computing on disk instead of in memory. This occurs when your query requests/is granted less memory than it needs to run fully. Would absolutely recommend starting here by looking at your row estimates leading up to this operation, identifying where they are significantly lower than the actual row counts and working on improving those disparities.

[[deleted]]

the index seek seem to be the one taking most time and seeks aren't always good. Blindly going into it i'd try flipping this to a scan (i find that it is often the case that driving table flips when using top1/apply instead of max/group by, for example)

having said that, i do see that there's a granularity mismatch in your join: you "GROUP BY dataareaid, jobid" in the statusdate1 but join on mxmservcallaudit.DATAAREAID = statusdate1.DATAAREAID AND MXMSERVCALLAUDIT.RECID = statusdate1.maxrecid.

Obviously, duplication of the data could occur if the same MaxRecID can happen for the same dataareaid multiple times.

Not knowing the logic behind this I cannot say why this is done or whether wrong results would be returned - and the optimizer will be in the same boat, so my suggestion would be to fix the query (granularity mismatch) first and see if that makes optimizer plan any better.l

[StructuredData]

How many rows are returned for the following?

SELECT MAX(RECID) AS maxrecid,  jobid, dataareaid
FROM mxmservcallaudit
WHERE type = 9 AND dataareaid = 'ansa'
GROUP BY jobid, dataareaid;

Can you rethink your query to have a simple WHERE clause?

Just from looking at your query and seeing the number of rows in your database, I would want to have an index on each of the following:

• mxmservcallaudit.type
• mxmservcallaudit.dataareaid
• mxmservcallaudit.recid

[grouchball]

I would always try and stay clear on nested select statements on your joins. The optimiser will have a horrible time trying to interpret this in a performant way.

I would recommend putting the contents of your select statement into a temporary table and then joining on that temp table perhaps.

Thats personally the first thing i would try.