Same query, different execution plans

Have you ever found out an ad-hoc query that is fast (or slow) when is being executed from one client, and way slower (of faster) in other client applications or in SSMS? If so, this post is for you.

The most probably reason for this inconsistent behavior is the most obvious one — SQL Server used different execution plans for this query. And apparently one of those plans was better than others. Now, the real question is why this happened?

First reason — queries weren’t exactly the same

When SQL Server executes an ad-hoc query for the first time, it optimizes it, run the founded execution plan for this query and store this plan in procedure cache for future reusing. This way when the same query is going to be executed again, the costly optimization and compilation phases can be skipped.

But to reuse existing plan SQL Server has to be sure that the ad-hoc query haven’t changed at all. So it computes a query text hash ad store it. When another query is going to be executed, SQL Server compute a hash of it and compare with already kept ones. Those hashes have to be identical, otherwise a brand new query plan will be optimized and compiled for a query.

This means that ad-hoc queries have to be identically written in order to reuse an existing execution plan. White spaces, comments, letter cases, end of lines delimiters — they all matter.

Let me show you this behavior.

First, to simplify analysis, I will clear the entire procedure cache:

USE AdventureWorks2008
GO

DBCC FREEPROCCACHE
GO

 

Then six very similar query are going to be executed. Note, that all of those queries return exactly the same results:

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] = '1999-02-15';
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''9990215';
GO

SELECT LoginID --Dummy comment
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

select LoginID
from [HumanResources].[Employee]
where [HireDate] ='1999-02-15';
GO

SELECT LoginID FROM [HumanResources].[Employee] WHERE [HireDate] ='1999-02-15';
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='19990215';
GO

 

But if we check the procedure cache we will find that six different execution plans (the additional one is for the diagnostic query):

SELECT COUNT(*)
FROM sys.dm_exec_cached_plans
WHERE objtype ='Adhoc';
GO
------
7

 

To check where the problem is, we can query sys.dm_exec_query_stats dynamic memory view:

SELECT qt.text,qs.sql_handle,qs.plan_handle,qs.query_hash, qs.query_plan_hash
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT LoginID%';
GO
-----------
text    sql_handle    plan_handle    query_hash    query_plan_hash

select LoginID
from [HumanResources].[Employee]
where [HireDate] =''1999-02-15'';
    0x02000000800AD215CA6B798B3E103F8345BD1555450026060000000000000000000000000000000000000000    0x06001C00800AD215305877E10400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] = ''1999-02-15'';
    0x02000000EC9D830E95209F9A3A147B1E96C22CC863415E9B0000000000000000000000000000000000000000    0x06001C00EC9D830EB02F59F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID --Dummy comment
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x020000000F2203384B445BA21A8CFBF9FB90EB15CEF526C90000000000000000000000000000000000000000    0x06001C000F220338503F59F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID FROM [HumanResources].[Employee] WHERE [HireDate] =''1999-02-15'';
    0x020000005CEA0813F3805AC2438E867C3E9926A2314E6A1B0000000000000000000000000000000000000000    0x06001C005CEA0813E05E77E10400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4
SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000B0DB8211E5B7EF1B21FDFDE1D7B285148F9B2A440000000000000000000000000000000000000000    0x06001C00B0DB8211502259F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''19990215'';
    0x0200000008CD4D105D53C5DE1D15F9A10A0B13C22E814C760000000000000000000000000000000000000000    0x06001C0008CD4D10103D59F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

 

As you can see, all six queries have different sql handles, so plan handles also have to be different. This is the case even though SQL Server is well aware that those queries are similar to each other — the query_hash column returns “Binary hash value calculated on the query and used to identify queries with similar logic”. Even more, in this case all six execution plans are also similar enough to produce the same query plan hashes.

Lesson learned — if for whatever reason you have to send ad-hoc queries to SQL Server, at least try to standardize the way they are written. And pay special attention to any ORM tools being used.

Second reason — queries were exactly the same, but some session settings don’t

Even if queries text are exactly the same, some session option could have changed between executions. And some of SET options are plan-reuse-affecting, which means that if any of following options is different, the execution plan will not be reused: ANSI_DEFAULTS, ANSI_NULL_DFLT_OFF, DATEFIRST, ANSI_NULL_DFLT_ON, DATEFORMAT, ANSI_NULLS, FORCEPLAN, ANSI_PADDING, LANGUAGE, ANSI_WARNINGS, NO_BROWSETABLE, ARITHABORT, NUMERIC_ROUNDABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER.

This behavior is also easy to see, all we have to do is:

1. Clear procedure cache to simplify analysis:

DBCC FREEPROCCACHE
GO

 

2. Change one of those options and execute the identical query twice:

SET LANGUAGE Polish
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

SET LANGUAGE English
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

 

3. Check cached query statistics:

SELECT qt.text,qs.sql_handle,qs.plan_handle,qs.query_hash, qs.query_plan_hash
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT LoginID%';
GO
-----
text    sql_handle    plan_handle    query_hash    query_plan_hash

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000680B98034033280706A06C41D19E5CC337D9EC900000000000000000000000000000000000000000    0x06001C00680B9803A0F78AE90400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000680B98034033280706A06C41D19E5CC337D9EC900000000000000000000000000000000000000000    0x06001C00680B9803B07C67E90400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

 

Note that this time sql handles are also identical (SQL Server treats both queries as exactly the same ones), but plan handles are still different.

Lesson learned — do not change session settings if not necessary.

Third reason — queries and sessions settings were exactly the same, but databases don’t

Finally, even though procedure cache is one per SQL Server instance (which means it’s shared across databases), if the same queries are executing in different databases, they will not reuse an execution plan. This is the case even if one of those databases is a snapshot of another one.

To demonstrate it, let me create a snapshot of an example database:

CREATE DATABASE AdvWorks2008_snp
ON (NAME = AdventureWorks2008_Data,
    FILENAME = 'C:\sql\AdventureWorks2008_Data.ss')
AS SNAPSHOT OF [AdventureWorks2008];
GO

 

Then clear the procedure cache:

DBCC FREEPROCCACHE
GO

 

And execute the same query in an original database, in the snapshot and in different database which holds the same table:

USE [AdventureWorks2008]
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

USE AdvWorks2008_snp
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

USE [AdventureWorks2008R2]
GO

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] ='1999-02-15';
GO

 

Again, the query handles are the same, but plan handles are different:

SELECT qt.text,qs.sql_handle,qs.plan_handle,qs.query_hash, qs.query_plan_hash
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT LoginID%';
GO
------
text    sql_handle    plan_handle    query_hash    query_plan_hash

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000680B98034033280706A06C41D19E5CC337D9EC900000000000000000000000000000000000000000    0x06001C00680B9803502259F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000680B98034033280706A06C41D19E5CC337D9EC900000000000000000000000000000000000000000    0x06000C00680B9803103D59F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0x521B8BB4A835B109

SELECT LoginID
FROM [HumanResources].[Employee]
WHERE [HireDate] =''1999-02-15'';
    0x02000000680B98034033280706A06C41D19E5CC337D9EC900000000000000000000000000000000000000000    0x06001D00680B9803B02F59F70400000001000000000000000000000000000000000000000000000000000000    0x933A9A98FDF13AFA    0xBC52AEBAD8A1BEE4

 

Lesson learned — there is a hidden cost (in fact, there are more than this one) of database snapshots. Namely, queries executed in a snapshot database will have to be compiled and their execution plans will be stored along with plans of the same queries executed in the origin database.

See you again

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Performance, T-SQL and tagged , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required