Sql-server – why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well

execution-planperformancequery-performancesql server 2014

I am running the following query on MS SQL Server 2014 (and also on 2014SP3)

SET NOCOUNT ON

CREATE TABLE #GUIDs(
  PartyNames_GUID  UNIQUEIDENTIFIER NULL,
  Party_GUID UNIQUEIDENTIFIER  NULL,
  FirstName  NVARCHAR(255)  NULL
)

insert #GUIDs(Party_GUID)
select top 1 Party_GUID 
FROM Party a 
join PartyNames b on a.Party_ID = b.Party_ID 

--Give the optimizer all kinds of choices. 
create  index i1 on #GUIDs(PartyNames_GUID)
create  index i2 on #GUIDs(Party_GUID)
create  index i3 on #GUIDs(Party_GUID, PartyNames_GUID)
create  index i4 on #GUIDs(PartyNames_GUID,Party_GUID)
update statistics #GUIDs


SELECT  PartyNames.PartyNames_ID,       PartyNames.LastName,        PartyNames.FirstName
FROM Party  INNER JOIN PartyNames   ON PartyNames.Party_ID = Party.Party_ID  
            INNER JOIN #GUIDs       ON Party.Party_GUID = #GUIDs.Party_GUID                     --Hard Match on Party_GUID
                                                     AND 
      (#GUIDs.PartyNames_GUID IS NULL OR PartyNames.PartyNames_GUID = #GUIDs.PartyNames_GUID )  --Optional Match
                                                    AND 
            (#GUIDs.FirstName IS NULL OR PartyNames.FirstName = #GUIDs.FirstName )          --Optional Match

drop table #GUIDs

Both the Party and PartyNames tables have a clustered index on their primary _IDs, a nonclustered unique index on their respective GUIDs and PartyNames has a index on the foreign key from Party. The DDL for these table is included at the end not so as to not clutter up the description of the problem. Party has about 1.9M rows and PartyNames 1.3M. PartyNames can at the most have 3 records per Party. The above query takes a few hundred milliseconds to run. But the same query runs in 15ms or less on SQL Server 2012, 2016 and 2019. The schema on all versions is the same, the exact same data is BCP'ed in and statistics updated after the BCP is done and before running the query. Here is how the execution plan looks on 2014

And here is the execution plan on the other SQL Server versions , 2012, 2016, 2019

Why does 2014 generate such a poor plan, one which scans PartyNames' primary key rather than seeking on
[PartyNames].[ix_PartyNames_Party_ID? OR rather how do versions higher and lower than 2014 manage to come up with a good plan while 2014 does not ? The plan generated by the other versions is obviously better as it consumes less CPU and does less IOs. Is it some server setting on 2014 which is causing this ? Is the costing the optimizer of SQL Server 2014 that dramatically different from versions before or after it ? Any help or pointers appreciated. I would hate to think this is a shortcoming in SQL Server 2014. And the interesting thing is that even if the number of records are reduced to a few thousand, SQL Server 2014 continues to generate the same bad plan. But removing the last AND clause ,(#GUIDs.FirstName IS NULL OR PartyNames.FirstName = #GUIDs.FirstName ) causes SQL Server 2014 to generate the same good plan as the other versions. This is of course a contrived reproduction. Customers running our product on MS SQL Server 2014 complained of poor performance while those on other versions did not. Troubleshooting the issue lead to this simple reproduction.

And here is the DDL for the schema generation

CREATE TABLE [dbo].[Party](
    [Party_ID] [int] IDENTITY(1,1) NOT NULL,
    [Party_GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [pk_Party_Party_ID] PRIMARY KEY CLUSTERED ( [Party_ID] ASC),
 CONSTRAINT [uq_Party_Party_GUID] UNIQUE NONCLUSTERED ( [Party_GUID] ASC)
) 
GO
ALTER TABLE [dbo].[Party] ADD  CONSTRAINT [df_Party_Party_GUID]  DEFAULT (newid()) FOR [Party_GUID]
GO




CREATE TABLE [dbo].[PartyNames](
    [PartyNames_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartyNames_GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Party_ID] [int] NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [MiddleName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
 CONSTRAINT [pk_PartyNames_PartyNames_ID] PRIMARY KEY CLUSTERED ([PartyNames_ID] ASC),
 CONSTRAINT [uq_PartyNames_PartyNames_GUID] UNIQUE NONCLUSTERED ([PartyNames_GUID] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PartyNames] ADD  CONSTRAINT [df_PartyNames_PartyNames_GUID]  DEFAULT (newid()) FOR [PartyNames_GUID]
GO

ALTER TABLE [dbo].[PartyNames]  WITH NOCHECK ADD  CONSTRAINT [fk_PartyNames_Party_ID] FOREIGN KEY([Party_ID])
REFERENCES [dbo].[Party] ([Party_ID])
GO

ALTER TABLE [dbo].[PartyNames] CHECK CONSTRAINT [fk_PartyNames_Party_ID]
GO
CREATE NONCLUSTERED INDEX [ix_PartyNames_Party_ID] ON [dbo].[PartyNames]
(
    [Party_ID] ASC
)
GO

Best Answer

You need to enable documented and supported trace flag 4199 (which enables query optimizer fixes) to correct this problem.

You can enable it at the query level to test it out, but that requires sysadmin permissions (which hopefully your application doesn't have). A pretty convincing argument can be made for turning this on globally (as a startup trace flag), you can read some discussion around that here: Trace Flag 4199 - Enable globally?

I was able to reproduce your problem using the following test data:

INSERT INTO dbo.Party
    (Party_GUID)
SELECT TOP (1900000)
    NEWID()
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.PartyNames
    (Party_ID, PartyNames_GUID)
SELECT TOP (1300000)
    Party_ID,
    NEWID()
FROM dbo.Party;

Getting an estimated plan for the test query produces the bad plan:

The optimizer chooses to scan the clustered index on dbo.PartyNames because, for some reason, it thinks that every row in dbo.PartyNames is going to be a match for the one row retrieved from dbo.Party.

Adding OPTION (QUERYTRACEON 4199) to the end of the query corrects this faulty reasoning and produces the good plan:

For more information on TF 4199, see this KB: SQL Server query optimizer hotfix trace flag 4199 servicing model

An alternative solution is to use the legacy cardinality estimator with trace flag 9481, which also produces the good plan.

Related Solutions

Sql-server – Optimize simple query in SQL Server

It has no WHERE clause so it must process and aggregate all 40 million rows. SQL Server will not take advantage of the index order and skip scan ahead to the next IdTag once it has found the MAX for the current group but will continue processing the other rows in that group. Each group has an average of about 30,000 rows.

As you have another table that lists the 1,386 distinct IdTag types then you could try the following instead.

SELECT D.IdTag,
       V.PCTimeStamp,
       V.Now,
       datediff(SECOND, V.PCTimeStamp, V.Now) AS DELAY
FROM   DescriptionTagsOPC D
       CROSS APPLY (SELECT TOP 1 *,
                                 getdate() AS Now
                    FROM   ValuesTagsOPC V
                    WHERE  D.IdTag = V.IdTag
                    ORDER  BY PCTimeStamp DESC) V

To replace the scan of 40 million rows with 1,386 seeks.

If that table was not available then a recursive CTE could be used to achieve similar results.

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 IdTag, PCTimeStamp
        FROM ValuesTagsOPC
        ORDER BY IdTag DESC, PCTimeStamp DESC
        UNION   ALL
        SELECT  R.IdTag, R.PCTimeStamp
        FROM    (
                SELECT  V.*,
                        rn = ROW_NUMBER() OVER (ORDER BY V.IdTag DESC, V.PCTimeStamp DESC)
                FROM    ValuesTagsOPC V
                JOIN    RecursiveCTE R
                        ON  V.IdTag < R.IdTag
                ) R
        WHERE   R.rn = 1
        )
SELECT  IdTag,
        PCTimeStamp,
        getdate()                                 AS NOW,
        datediff(SECOND, PCTimeStamp, getdate()) AS DELAY
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

Sql-server – SHOWPLAN does not display a warning but “Include Execution Plan” does 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 – Optimize simple query in SQL Server

Sql-server – SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

Related Question