I had an interesting time at the office recently. We were undertaking a performance tuning exercise for the canned reports of an OLTP that were migrated from Crystal Reports to SSRS, and had to come up with some ground rules for all database developers to follow when re-engineering queries.

One of the basic things to be ensured during a performance tuning exercise is that the queries must be written in such a way that once an execution plan is generated, SQL Server reuses a given execution plan for subsequent executions of the same query.

The above statement seems very simple, but there is a lot of between-the-lines reading that needs to be done before the true meaning of the statement can be understood.

Some basics – Execution & Query Plans

When a query is submitted to the SQL Server database engine for the first time, the query optimizer processes the query (query processing is a huge topic, and out-of-scope for this discussion. For more information, you can refer the BOL page on Query processing here) and generates a two execution plans:

1. For serial execution
2. For parallel execution

Each Execution plan (BOL Reference here) consists of:

1. Query Plan
   • This is the bulk of the query plan, and does not contain the Execution context
2. Execution Context
   • The execution context is unique for each user running the plan because it contains the parameter values for each execution per user

The prime concern is the re-use of the query plan portion of the Execution Plan. This is because generation of execution plans takes time & resources – both of which are scarce.

The importance of using the proper case when writing T-SQL Queries

The query plan, is internally stored as a text value – a string, which is what we see if we execute a query with the SHOWPLAN operator.

When a query is submitted, it is parsed and one of the first things that happens afterwards is that Microsoft SQL Server attempts to find a suitable match for the query plan from the plan cache. This is done via a simple, but crucial to understand string comparison. The problem is that most developers fail to realize that this comparison is a case-sensitive comparison.

Let’s understand this via a simple demo.

Demo

We will begin by clearing out the procedure cache.

DBCC FREEPROCCACHE
GO

Next, let us run a simple T-SQL query (twice) to fetch a list of all the employees in an organization:

USE AdventureWorks2008R2
GO

SELECT * FROM HumanResources.Employee
GO 2

Now, let us use the DMVs – sys.dm_exec_cached_plans, sys.dm_exec_query_plan and sys.dm_exec_sql_text to look at the procedure cache:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can see from the usecounts column that Microsoft SQL Server used the query plan it generated twice.

Now, assume that different developers working on the same application used some other variants of the same query:

USE AdventureWorks2008R2
GO

SELECT * FROM HUMANRESOURCES.EMPLOYEE
GO

SELECT * FROM HumanResources.Employee
GO;

Let us take a look at that plan usage again:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can clearly see that SQL Server used a different version of the query plan for each of the two query variants.

Moral of the story

When writing queries, please avoid:

1. Changes to letter case in the text
2. Changes to white space
3. Changes to literal values
4. Changes to text inside comments

Most important: If you are using Microsoft SQL Server 2008 and above – always use intelli-sense to be safe!

Until we meet next time,

Be courteous. Drive responsibly.