Sql-server – Speed up query execution. Can we add an index or rewrite the query

index-tuningperformancequery-performancesql serversql-server-2012

I've been working on this query handed to me by a developer who wanted to reduce the query execution time even further. Currently, it takes almost around 40 secs and outputs 262K records. Based on the actual execution the query cost is more towards the clustered index scan on "CacheClients". However, I am not sure if adding a non clustered covering index would benefit it. Also, I see a table scan at the extreme bottom right of the plan and as per the developer they were testing with an index on that table but they weren't able to figure out an appropriate one that can fit the criteria which is basically they would join on the id fields and run queries based on date period.

   USE [ABC]
    GO

    /****** Object:  View [report].[vw_MonthlyDealAllocations]    Script Date: 11/24/2017 9:54:10 PM ******/
    --SET ANSI_NULLS ON
    --GO

    --SET QUOTED_IDENTIFIER ON
    --GO

    --CREATE VIEW [report].[vw_MonthlyDealAllocations] as
    SELECT
      PeriodStartDate,
      PeriodEndDate,
      D.DealID,
      Dl.DealName,
      D.StageID,
      CONVERT(varchar(1), D.StageID) + ' - ' + DS.Stage Stage,
      DP.Platform,
      ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID,
      ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID,
      CASE
        WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 1 THEN 'Investment Advisory'
        WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 2 THEN 'Debt/EP'
        WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 6 THEN 'HFF Securities'
        ELSE DM.Department
      END AS AllocatedDepartment,
      AO.Name AllocatedOffice,
      CASE
        WHEN DP.DisplayWithOffice = 1 THEN AO.Name + ' ' + DP.ShortName
        ELSE AO.Name
      END AllocatedOfficeDept,
      Dl.DepartmentID DealDepartmentID,
      DX.Department DealDepartment,
      Dl.OfficeID DealOfficeID,
      DO.Name DealOffice,
      CASE
        WHEN DP.DisplayWithOffice = 1 THEN DO.Name + ' ' + DP.ShortName
        ELSE DO.Name
      END DealOfficeDept,
      DT.DealType,
      D.OfficeAllocVolumePipeline,
      D.OfficeAllocVolumeCompleted,
      D.OfficeAllocFeePipeline,
      D.OfficeAllocFeeCompleted,
      D.OfficeAllocVolumePipelineGBP,
      D.OfficeAllocVolumeCompletedGBP,
      D.OfficeAllocFeePipelineGBP,
      D.OfficeAllocFeeCompletedGBP,
      PTG.GroupName,
      PT.PropertyType,
      CASE
        WHEN C.CountryID <> 1 THEN 'Foreign'
        ELSE R.Region
      END AS Region,
      AP.Address1,
      C.City,
      S.StateLong,
      AP.Zip,
      Investor.ClientName MSA,
      Cl.ClientName RollupMSA,
      ITI.InvestorType PrimaryInvestor,
      ITC.InvestorType PrimaryClient,
      P.FirstName + ' ' + P.LastName AS PrimaryProducer,
      I.FirstName + ' ' + I.LastName AS IL_Producer,
      D.CountDealCompleted,
      CASE Dl.isSecuritized
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE ''
      END AS isSecuritized,
      CASE Dl.isServiced
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE ''
      END AS isServiced,
      --QCount.QtCt,
      /*IIF (CASE
            WHEN D.isCompleted = 1 THEN D.VolumeCompleted 
            WHEN (D.isPipeline = 1 OR D.StageID = 0) THEN D.VolumePipeline 
            ELSE 0 
        END >= 50000000,1,NULL
      ) AS Over50M, */
      IIF(Dl.PortfolioID IS NOT NULL, 1, NULL) AS isPortfolio,
      --IIF (isMultitransactional.linktype IS NOT NULL,1,NULL) AS isMultiTransactional,
      DP.PlatformID,
      P.EmployeeID AS PrimaryProducerID,
      I.EmployeeID AS IL_ProducerID,
      --report.fnReportGetDealClients(D.DealID) Clients,
      --report.fnReportGetDealInvestors(D.DealID) Investors,
      DFM.FinancingMethod,
      IIF((Dl.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency'), 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorType

    FROM report.MonthlyDealAllocations D
    INNER JOIN dbo.Deals Dl
      ON Dl.DealID = D.DealID
    INNER JOIN DealTypes DT
      ON DT.DealTypeID = Dl.DealTypeID
    INNER JOIN DealPlatforms DP
      ON DP.PlatformID = DT.PlatformID
    INNER JOIN DealStages DS
      ON DS.StageID = D.StageID
    LEFT JOIN Departments DM
      ON DM.DepartmentID = ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID)
    LEFT JOIN Departments DX
      ON DX.DepartmentID = Dl.DepartmentID
    LEFT JOIN Offices AO
      ON AO.OfficeID = ISNULL(D.AllocatedOfficeID, Dl.OfficeID)
    LEFT JOIN Offices DO
      ON DO.OfficeID = Dl.OfficeID
    LEFT JOIN dbo.DealProperties DPP
      ON D.DealID = DPP.DealID
      AND DPP.IsMainProperty = 1
    LEFT JOIN PropertyTypes PT
      ON PT.PropertyTypeID = DPP.PropertyTypeID
    LEFT JOIN PropertyTypeGroups PTG
      ON PTG.GroupID = PT.GroupID
    LEFT JOIN DealProperties AS DPZ
      ON D.DealID = DPZ.DealID
      AND DPZ.isMainProperty = 1
    LEFT JOIN Assets AP
      ON DPZ.AssetID = AP.AssetID
    LEFT JOIN Cities C
      ON C.CityID = AP.CityID
    LEFT JOIN States S
      ON S.StateID = C.StateID
    LEFT JOIN Regions R
      ON ISNULL(C.RCARegionID, ISNULL(S.RCARegionID, S.RegionID)) = R.RegionID
    LEFT JOIN DealsToClients DTCC
      ON D.DealID = DTCC.DealID
      AND DTCC.isPrimary = 1
      AND DTCC.MemberType IN (1, 3)
    LEFT JOIN DealsToClients DTCI
      ON D.DealID = DTCI.DealID
      AND DTCI.isPrimary = 1
      AND DTCI.MemberType IN (2, 4)
    LEFT JOIN CacheClients Cl
      ON DTCC.ClientID = Cl.ClientID
    LEFT JOIN CacheClients Investor
      ON DTCI.ClientID = Investor.ClientID
    LEFT JOIN InvestorTypes ITI
      ON Investor.InvestorTypeID = ITI.InvestorTypeID
    LEFT JOIN InvestorTypes ITC
      ON Cl.InvestorTypeID = ITC.InvestorTypeID
    --LEFT JOIN (SELECT DISTINCT D2D.DealID, D2D.LinkType FROM DealsToDeals D2D WHERE LinkType = 2) isMultitransactional ON D.DealID = isMultitransactional.DealID 
    LEFT JOIN DealsToEmployees EP
      ON D.DealID = EP.DealID
      AND EP.MemberType = 1
      AND EP.isPrimary = 1
    LEFT JOIN DealsToEmployees EI
      ON D.DealID = EI.DealID
      AND EI.MemberType = 2
      AND EI.isPrimary = 1
    LEFT JOIN Employees P
      ON EP.EmployeeID = P.EmployeeID
    LEFT JOIN Employees I
      ON EI.EmployeeID = I.EmployeeID
    LEFT JOIN DealFinancingMethods DFM
      ON Dl.DealFinancingMethodID = DFM.DealFinancingMethodsID
    LEFT JOIN InvestorTypes AS ITID2C
      ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID
    --LEFT JOIN (SELECT DealID, COUNT(QuoteID) AS QtCt FROM Quotes WHERE DateRemoved IS NULL GROUP BY DealID) QCount ON D.dealid = Qcount.DealID 
 go

Below is the index they created.

USE [ABC]
GO

/****** Object:  Index [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID]    Script Date: 11/24/2017 11:43:00 PM ******/
CREATE NONCLUSTERED INDEX [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID] ON [report].[MonthlyDealAllocations]
(
    [DealID] ASC,
    [AllocatedDepartmentID] ASC,
    [AllocatedOfficeID] ASC
)
INCLUDE (   [PeriodStartDate],
    [PeriodEndDate],
    [CountDealCompleted],
    [StageID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Execution plan link
https://www.brentozar.com/pastetheplan/?id=SJu_XK8lM

SQL Script file (Includes schema and index DDL)
https://pastebin.com/BB8p9hqa

Best Answer

If you run your query with SET STATISTICS IO ON; that'll give you information about IO required for all of your joined tables. Based on estimated plan costs you'll get the most benefit from adding a covering index on the CacheClients table. That table has around 50 columns but you only use three columns in your query. Creating an index on ClientID, ClientName, and InvestorTypeID will reduce IO requirements for that table because SQL Server will be able to scan the index that contains only those three columns instead of the full table. You will see benefits even if the join type doesn't change.

Other than that there's not a lot going here. Your cardinality estimate starting at MonthlyDealAllocations never changes, so it's not like you should be joining to a specific table first to improve performance. If possible, changing some of the joins expected to change cardinality to LEFT OUTER JOINS or defining unique indexes on some of the tables could improve your estimates to keep them at a constant 262336 throughout the plan. This could have a minor benefit in avoiding the tempdb spill at node id 61.

If you're really desperate you could try a MAXDOP 1 hint or disabling MERGE JOINS via OPTION (LOOP JOIN, HASH JOIN) just to see what happens to the query's runtime. As is you're asking for data from a bunch of different tables and SQL Server is going to take time to do all of those joins. There's no magic query rewrite that's going to change that. I recommend approaching this problem by defining a minimum acceptable query performance time target instead of just wanting to make it faster without a concrete goal in mind.

Related Solutions

Sql-server – Index not making execution faster, and in some cases is slowing down the query. Why is it so

Even though the index is suggested by the SQL Server, why does it slow things down by a significant difference?

Index suggestions are made by the query optimizer. If it comes across a logical selection from a table which is not well served by an existing index, it may add a "missing index" suggestion to its output. These suggestions are opportunistic; they are not based on a full analysis of the query, and do not take account of wider considerations. At best, they are an indication that more helpful indexing may be possible, and a skilled DBA should take a look.

The other thing to say about missing index suggestions is that they are based on the optimizer's costing model, and the optimizer estimates by how much the suggested index might reduce the estimated cost of the query. The key words here are "model" and "estimates". The query optimizer knows little about your hardware configuration or other system configuration options - its model is largely based on fixed numbers that happen to produce reasonable plan outcomes for most people on most systems most of the time. Aside from issues with the exact cost numbers used, the results are always estimates - and estimates can be wrong.

What is the Nested Loop join which is taking most of the time and how to improve its execution time?

There is little to be done to improve the performance of the cross join operation itself; nested loops is the only physical implementation possible for a cross join. The table spool on the inner side of the join is an optimization to avoid rescanning the inner side for each outer row. Whether this is a useful performance optimization depends on various factors, but in my tests the query is better off without it. Again, this is a consequence of using a cost model - my CPU and memory system likely has different performance characteristics than yours. There is no specific query hint to avoid the table spool, but there is an undocumented trace flag (8690) that you can use to test execution performance with and without the spool. If this were a real production system problem, the plan without the spool could be forced using a plan guide based on the plan produced with TF 8690 enabled. Using undocumented trace flags in production is not advised because the installation becomes technically unsupported and trace flags can have undesirable side-effects.

Is there something that I am doing wrong or have missed?

The main thing you are missing is that although the plan using the nonclustered index has a lower estimated cost according to the optimizer's model, it has a significant execution-time problem. If you look at the distribution of rows across threads in the plan using the Clustered Index, you will likely see a reasonably good distribution:

Scan plan

In the plan using the Nonclustered Index Seek, the work ends up being performed entirely by one thread:

Seek plan

This is a consequence of the way work is distributed among threads by parallel scan/seek operations. It is not always the case that a parallel scan will distribute work better than an index seek - but it does in this case. More complex plans might include repartitioning exchanges to redistribute work across threads. This plan has no such exchanges, so once rows are assigned to a thread, all related work is performed on that same thread. If you look at the work distribution for the other operators in the execution plan, you will see that all work is performed by the same thread as shown for the index seek.

There are no query hints to affect row distribution among threads, the important thing is to be aware of the possibility and to be able to read enough detail in the execution plan to determine when it is causing a problem.

With the default index (on primary key only) why does it take less time, and with the non clustered index present, for each row in the joining table, the joined table row should be found quicker, because join is on Name column on which the index has been created. This is reflected in the query execution plan and Index Seek cost is less when IndexA is active, but why still slower? Also what is in the Nested Loop left outer join that is causing the slowdown?

It should now be clear that the nonclustered index plan is potentially more efficient, as you would expect; it is just poor distribution of work across threads at execution time that accounts for the performance issue.

For the sake of completing the example and illustrating some of the things I have mentioned, one way to get a better work distribution is to use a temporary table to drive parallel execution:

SELECT
    val1,
    val2
INTO #Temp
FROM dbo.IndexTestTable AS ITT
WHERE Name = N'Name1';

SELECT 
    N'Name1',
    SUM(T.val1),
    SUM(T.val2),
    MIN(I2.Name),
    SUM(I2.val1),
    SUM(I2.val2)
FROM   #Temp AS T
CROSS JOIN IndexTestTable I2
WHERE
    I2.Name = 'Name1'
OPTION (FORCE ORDER, QUERYTRACEON 8690);

DROP TABLE #Temp;

This results in a plan that uses the more efficient index seeks, does not feature a table spool, and distributes work across threads well:

Optimal plan

On my system, this plan executes significantly faster than the Clustered Index Scan version.

If you're interested in learning more about the internals of parallel query execution, you might like to watch my PASS Summit 2013 session recording.

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

Best Answer

Related Solutions

Sql-server – Index not making execution faster, and in some cases is slowing down the query. Why is it so

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

Related Question