SQL Server changes execution plan - Part 2

Question

Previous question: SQL Server changes execution plan

We are using SQL Server 2014 Developer Edition.

SQL Server changes execution plan on identical query and the same database and SQL Server (I checked several times).

If I connect with Management Studio from my dev computer with AD account, it takes 18 seconds to finish the query (most of the time). If I go with remote connection to server and execute query in Management Studio it takes 2 seconds to finish. On my colleague's machine it takes 2 second with Management Studio and 18 if he connects with MVC application (Ado.Net).

Things we did

• Restart SQL Server
• DBCC FREEPROCCACHE
• sp_updatestats
• try with different users AD and SQL users

Slow execution plan

Fast execution plan

Query

SET ANSI_NULLS ON
GO

SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @ccode varchar(500)
DECLARE @PageSize int
DECLARE @PageNumber int
SET @ccode = '%skd%'
SET @PageSize = 20
SET @PageNumber = 1

SELECT DISTINCT CLASSIFICATIONS.ABBREV_TEXT,
            CLASSIFICATIONS.TITLE_TEXT,             
            CLASSIFICATIONS.CFN_UID
    FROM klasje.dbo.CLASSIFICATIONS
    inner join Klasje.dbo.CFN_VERSIONS on CLASSIFICATIONS.CFN_UID = CFN_VERSIONS.CFN_UID
    inner join Klasje.dbo.VERSION_CATEGORY_XREFS on CFN_VERSIONS.CVN_UID = VERSION_CATEGORY_XREFS.CVN_UID
    left outer join Klasje.dbo.VELJAVNOST on CFN_VERSIONS.LIFE_CYCLE_CODE = VELJAVNOST.RV_LOW_VALUE
    WHERE CATEGORY_CODE like @ccode OR DESCRIPTOR_TEXT like @ccode OR DEFINITION_TEXT like @ccode
    ORDER BY ABBREV_TEXT
    OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY

Both execution plans on gist.

SELECT @@OPTIONS returns 5496 in in both cases (slow and fast).

Answer 1

The most likely explanation is that your sessions have different settings. SQL Server has various session settings that can affect the execution plan selected (and the results!)

The values for these settings can depend on how you connect to SQL Server, since different tools set the options different ways when they connect, and some (like SQL Server Management Studio) allow you to override the defaults as well.

For example:

The image above is reproduced from Erland Sommarskog's definitive article on this topic:

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

The whole thing is well worth reading, but you should definitely read the section titled, "The Default Settings"

If you make sure all the settings have the same value on all connections, you should get the same execution plans.

For maximum compatibility with features like indexed views, you should ensure your settings are as follows:

Many of these settings are maintained for backward compatibility only. It is strongly recommended you set them as shown in the table above, or use a tool that sets them the right way automatically.

Books Online references:

• SET Statements (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)
• Create Indexed Views

Update after plans were provided

The slow plan includes:

CardinalityEstimationModelVersion="70"

...whereas the fast plan says:

CardinalityEstimationModelVersion="120"

So the explanation is that one of you is using the original cardinality estimator, and other is using the new SQL Server 2014 CE. The difference in estimated row counts is enough for the new CE to choose a parallel execution plan. Under the original CE, the estimated cost for the serial plan is below the cost threshold for parallelism.

As to why different estimators are being used, I would guess that you have different context databases when the statements are run. One where the compatibility level of the database defaults to the new CE, and one where the original CE is used. The database you are "in" when the query executes determines the CE model, not the database(s) used in the query.

For example, you may have different default databases associated with your logins. If you USE Klasje; before running the statements, both connections should use the same CE model.

Final update: it turned out the target database was indeed set to an older compatibility level. Running the query with master as the context database produced the better plan. Be aware that changing to use the new CE for all queries may cause regressions. You will need to test your workload before changing the database compatibility level in production.

Answer 2

Just to discount the difference with the MVC app - Have you checked the query it is executing using SQL Profiler?

I had a similar problem recently, and it turns out the query executed through my MVC app (using Entity Framework 6) was executing the SQL statement through sp_executesql, which was causing SQL Server to use a different execution plan compared to running pure SQL in Management Studio.

We changed it to use a stored procedure rather than LINQ.