Slow in the Application, Fast in SSMS?

Understanding Performance Mysteries

An SQL text by Erland Sommarskog, SQL Server MVP. Last revision: 2020-08-21.

Copyright applies to this text. See here for font conventions used in this article.

This article is also available in Russian, translated by Dima Piliugin.

Introduction

When I read various forums about SQL Server, I frequently see questions from deeply mystified posters. They have identified a slow query or slow stored procedure in their application. They take the SQL batch from the application and run it in SQL Server Management Studio (SSMS) to analyse it, only to find that the response is instantaneous. At this point they are inclined to think that SQL Server is all about magic. A similar mystery is when a developer has extracted a query in his stored procedure to run it stand-alone only to find that it runs much faster – or much slower – than inside the procedure.

No, SQL Server is not about magic. But if you don't have a good understanding of how SQL Server compiles queries and maintains its plan cache, it may seem so. Furthermore, there are some unfortunate combinations of different defaults in different environments. In this article, I will try to straighten out why you get this seemingly inconsistent behaviour. I explain how SQL Server compiles a stored procedure, what parameter sniffing is and why it is part of the equation in the vast majority of these confusing situations. I explain how SQL Server uses the cache, and why there may be multiple entries for a procedure in the cache. Once you have come this far, you will understand how come the query runs so much faster in SSMS.

To understand how to address that performance problem in your application, you need to read on. I first make a short break from the theme of parameter sniffing to discuss a few situations where there are other reasons for the difference in performance. This is followed by two chapters how to deal with performance problems where parameter sniffing is involved. The first is about gathering information. In the second chapter I discuss some scenarios – both real-world situations and more generic ones – and possible solutions. Next comes a chapter where I discuss how dynamic SQL is compiled and interacts with the plan cache and why there are more reasons you may experience differences in performance between SSMS and the application with dynamic SQL. In the last chapter I look at how you can use the Query Store that was introduced in SQL 2016 for your troubleshooting. At the end there is a section with links to Microsoft white papers and similar documents in this area.

Presumptions

The essence of this article applies to all versions of SQL Server, but the focus is on SQL 2005 and later versions. The article includes several queries to inspect the plan cache; these queries run only on SQL 2005 and later. SQL 2000 and earlier versions had far less instrumentation in this regard. Beware that to run these queries you need to have the server-level permission VIEW SERVER STATE.

For the examples in this article, I use the Northwind sample database. This database shipped with SQL 2000. For later versions of SQL Server you can download it from Microsoft's web site.

This is not a beginner-level article, but I assume that the reader has a working experience of SQL programming. You don't need to have any prior experience of performance tuning, but it certainly helps if you have looked a little at query plans and if you have some basic knowledge of indexes. I will not explain the basics in depth, as my focus is a little beyond that point. This article will not teach you everything about performance tuning, but at least it will be a start.

Caveat: In some places, I give links to the online version of Books Online. Beware that the URL may lead to Books Online for a different version of SQL Server than you are using. In the top left there is a drop-down where you can select a different SQL Server version. Or more precisely, as of this writing there is – they tend to redesign the pages every now and then, and it is difficult for me to keep up.

How SQL Server Compiles a Stored Procedure

In this chapter we will look at how SQL Server compiles a stored procedure and uses the plan cache. If your application does not use stored procedures, but submits SQL statements directly, most of what I say this chapter is still applicable. But there are further complications with dynamic SQL, and since the facts about stored procedures are confusing enough I have deferred the discussion on dynamic SQL to a separate chapter.

What is a Stored Procedure?

That may seem like a silly question, but the question I am getting at is What objects have query plans on their own? SQL Server builds query plans for these types of objects:

Stored procedures.

Scalar user-defined functions.

Multi-step table-valued functions.

Triggers.

With a more general and stringent terminology I should talk about modules, but since stored procedures is by far the most widely used type of module, I prefer to talk about stored procedures to keep it simple.

For other types of objects than the four listed above, SQL Server does not build query plans. Specifically, SQL Server does not create query plans for views and inline-table functions. Queries like:

SELECT abc, def FROM myview SELECT a, b, c FROM mytablefunc(9)

are no different from ad-hoc queries that access the tables directly. When compiling the query, SQL Server expands the view/function into the query, and the optimizer works with the expanded query text.

There is one more thing we need to understand about what constitutes a stored procedure. Say that you have two procedures, where the outer calls the inner one:

CREATE PROCECURE Outer_sp AS ... EXEC Inner_sp ...

I would guess most people think of Inner_sp as being independent from Outer_sp, and indeed it is. The execution plan for Outer_sp does not include the query plan for Inner_sp, only the invocation of it. However, there is a very similar situation where I've noticed that posters on SQL forums often have a different mental image, to wit dynamic SQL:

CREATE PROCEDURE Some_sp AS DECLARE @sql nvarchar(MAX), @params nvarchar(MAX) SELECT @sql = 'SELECT ...' ... EXEC sp_executesql @sql, @params, @par1, ...

It is important to understand that this is no different from nested stored procedures. The generated SQL string is not part of Some_sp, nor does it appear anywhere in the query plan for Some_sp, but it has a query plan and a cache entry of its own. This applies, no matter if the dynamic SQL is executed through EXEC() or sp_executesql.

How SQL Server Generates the Query Plan

Overview

When you enter a stored procedure with CREATE PROCEDURE (or CREATE FUNCTION for a function or CREATE TRIGGER for a trigger), SQL Server verifies that the code is syntactically correct, and also checks that you do not refer to non-existing columns. (But if you refer to non-existing tables, it lets get you away with it, due to a misfeature known as deferred named resolution.) However, at this point SQL Server does not build any query plan, but merely stores the query text in the database.

It is not until a user executes the procedure, that SQL Server creates the plan. For each query, SQL Server looks at the distribution statistics it has collected about the data in the tables in the query. From this, it makes an estimate of what may be best way to execute the query. This phase is known as optimisation. While the procedure is compiled in one go, each query is optimised on its own, and there is no attempt to analyse the flow of execution. This has a very important ramification: the optimizer has no idea about the run-time values of variables. However, it does know what values the user specified for the parameters to the procedure.

Parameters and Variables

Consider the Orders table in the Northwind database, and these three procedures:

CREATE PROCEDURE List_orders_1 AS SELECT * FROM Orders WHERE OrderDate > '20000101' go CREATE PROCEDURE List_orders_2 @fromdate datetime AS SELECT * FROM Orders WHERE OrderDate > @fromdate go CREATE PROCEDURE List_orders_3 @fromdate datetime AS DECLARE @fromdate_copy datetime SELECT @fromdate_copy = @fromdate SELECT * FROM Orders WHERE OrderDate > @fromdate_copy go

Note: Using SELECT * in production code is bad practice. I use it in this article to keep the examples concise.

Then we execute the procedures in this way:

EXEC List_orders_1 EXEC List_orders_2 '20000101' EXEC List_orders_3 '20000101'

Before you run the procedures, enable Include Actual Execution Plan under the Query menu. (There is also a toolbar button and Ctrl-M is the normal keyboard shortcut.) If you look at the query plans for the procedures, you will see the first two procedures have identical plans:

That is, SQL Server seeks the index on OrderDate, and uses a key lookup to get the other data. The plan for the third execution is different:

In this case, SQL Server scans the table. (Keep in mind that in a clustered index the leaf pages contain the data, so a clustered index scan and a table scan is essentially the same the thing.) Why this difference? To understand why the optimizer makes certain decisions, it is always a good idea to look at what estimates it is working with. If you hover with the mouse over the two Seek operators and the Scan operator, you will see the pop-ups similar to those below.

List_orders_1 List_orders_2 List_orders_3

Note: the exact appearance depends on which version of SSMS and SQL Server you are using. The samples above were captured with SSMS 2008 running against SQL 2008. If you use a later version of SSMS, you may see the items in a somewhat different order, and there are also more items listed. None of those extra items are relevant for the example, which is why I have preferred to keep my original screen shots.

The interesting element is Estimated Number of Rows. For the first two procedures, SQL Server estimates that one row will be returned, but for List_orders_3, the estimate is 249 rows. This difference in estimates explains the different choice of plans. Index Seek + Key Lookup is a good strategy to return a smaller amount of rows from a table. But if more rows match the seek criteria, the cost increases, and there is a increased likelihood that SQL Server will need to access the same data page more than once. In the extreme case where all rows are returned, a table scan is much more efficient than seek and lookup. With a scan, SQL Server has to read every data page exactly once, whereas with seek + key lookup, every page will be visited once for each row on the page. The Orders table in Northwind has 830 rows, and when SQL Server estimates that as many as 249 rows will be returned, it (rightly) concludes that the scan is the best choice.

Where Do These Estimates Come From?

Now we know why the optimizer arrives at different execution plans: because the estimates are different. But that only leads to the next question: why are the estimates different? That is the key topic of this article.

In the first procedure, the date is a constant, which means that the SQL Server only needs to consider exactly this case. It interrogates the statistics for the Orders table, which indicates that there are no rows with an OrderDate in the third millennium. (All orders in the Northwind database are from 1996 to 1998.) Since statistics are statistics, SQL Server cannot be sure that the query will return no rows at all, why it makes an estimate of one single row.

In the case of List_orders_2, the query is against a variable, or more precisely a parameter. When performing the optimisation, SQL Server knows that the procedure was invoked with the value 2000-01-01. Since it does not any perform flow analysis, it can't say for sure whether the parameter will have this value when the query is executed. Nevertheless, it uses the input value to come up with an estimate, which is the same as for List_orders_1: one single row. This strategy of looking at the values of the input parameters when optimising a stored procedure is known as parameter sniffing.

In the last procedure, it's all different. The input value is copied to a local variable, but when SQL Server builds the plan, it has no understanding of this and says to itself I don't know what the value of this variable will be. Because of this, it applies a standard assumption, which for an inequality operation such as > is a 30 % hit-rate. 30 % of 830 is indeed 249.

Here is a variation of the theme:

CREATE PROCEDURE List_orders_4 @fromdate datetime = NULL AS IF @fromdate IS NULL SELECT @fromdate = '19900101' SELECT * FROM Orders WHERE OrderDate > @fromdate

In this procedure, the parameter is optional, and if the user does not fill in the parameter, all orders are listed. Say that the user invokes the procedure as:

EXEC List_orders_4

The execution plan is identical to the plan for List_orders_1 and List_orders_2. That is, Index Seek + Key Lookup, despite that all orders are returned. If you look at the pop-up for the Index Seek operator, you will see that it is identical to the pop-up for List_orders_2 but in one regard, the actual number of rows. When compiling the procedure, SQL Server does not know that the value of @fromdate changes, but compiles the procedure under the assumption that @fromdate has the value NULL. Since all comparisons with NULL yield UNKNOWN, the query cannot return any rows at all, if @fromdate still has this value at run-time. If SQL Server would take the input value as the final truth, it could construct a plan with only a Constant Scan that does not access the table at all (run the query SELECT * FROM Orders WHERE OrderDate > NULL to see an example of this). But SQL Server must generate a plan which returns the correct result no matter what value @fromdate has at run-time. On the other hand, there is no obligation to build a plan which is the best for all values. Thus, since the assumption is that no rows will be returned, SQL Server settles for the Index Seek. (The estimate is still that one row will be returned. This is because SQL Server never uses an estimate of 0 rows.)

This is an example of when parameter sniffing backfires, and in this particular case it may be better to write the procedure in this way:

CREATE PROCEDURE List_orders_5 @fromdate datetime = NULL AS DECLARE @fromdate_copy datetime SELECT @fromdate_copy = coalesce(@fromdate, '19900101') SELECT * FROM Orders WHERE OrderDate > @fromdate_copy

With List_orders_5 you always get a Clustered Index Scan.

Key Points

In this section, we have learned three very important things:

A constant is a constant, and when a query includes a constant, SQL Server can use the value of the constant with full trust, and even take such shortcuts to not access a table at all, if it can infer from constraints that no rows will be returned.

For a parameter, SQL Server does not know the run-time value, but it "sniffs" the input value when compiling the query.

For a local variable, SQL Server has no idea at all of the run-time value, and applies standard assumptions. (Which the assumptions are depends on the operator and what can be deduced from the presence of unique indexes.)

And there is a corollary of this: if you take out a query from a stored procedure and replace variables and parameters with constants, you now have quite a different query. More about this later.

Putting the Query Plan into the Cache

If SQL Server would compile a stored procedure – that is, optimise and build a query plan – every time the procedure is executed, there is a big risk that SQL Server would crumble from all the CPU resources it would take. I immediately need to qualify this, because it is not true for all systems. In a big data warehouse where a handful of business analysts runs complicated queries that take a minute on average to execute, there would be no damage if there was compilation every time – rather it could be beneficial. But in an OLTP database where plenty of users run stored procedures with short and simple queries, this concern is very much for real.

For this reason, SQL Server caches the query plan for a stored procedure, so when the next user runs the procedure, the compilation phase can be skipped, and execution can commence directly. The plan will stay in the cache, until some event forces the plan out of the cache. Examples of such events are:

SQL Server's buffer cache is fully utilised, and SQL Server needs to age out buffers that have not been used for some time from the cache. The buffer cache includes table data as well as query plans.

Someone runs ALTER PROCEDURE on the procedure.

on the procedure. Someone runs sp_recompile on the procedure.

on the procedure. Someone runs the command DBCC FREEPROCCACHE which clears the entire plan cache.

which clears the entire plan cache. SQL Server is restarted. Since the cache is memory-only, the cache is not preserved over restarts.

Changing of certain configuration parameters (with sp_configure or through the Server Properties pages in SSMS) evicts the entire plan cache.

If such an event occurs, a new query plan will be created the next time the procedure is executed. SQL Server will anew "sniff" the input parameters, and if the parameter values are different this time, the new query plan may be different from the previous plan.

There are other events that do not cause the entire procedure plan to be evicted from the cache, but which trigger recompilation of one or more individual statements in the procedure. The recompilation occurs the next time the statement is executed. This applies even if the event occurred after the procedure started executing. Here are examples of such events:

Changing the definition of a table that appears in the statement.

Dropping or adding an index for a table appearing in the statement. This includes rebuilding an index with ALTER INDEX or DBCC DBREINDEX .

or . New or updated statistics for a table in the statement. Statistics can be created and updated by SQL Server automatically. The DBA can also create and update statistics with the commands CREATE STATISTICS and UPDATE STATISTICS . However, changed statistics do not always cause recompilation. The basic rule is that there should have been a change in the data for recompilation to be triggered. See this blog post from Kimberly Tripp for more details.

and . However, changed statistics do not always cause recompilation. The basic rule is that there should have been a change in the data for recompilation to be triggered. See this blog post from Kimberly Tripp for more details. Someone runs sp_recompile on a table referred to in the statement.

Note: In SQL Server 2000, there is no statement recompilation, but the entire procedure is always recompiled.

These lists are by no means exhaustive, but you should observe one thing which is not there: executing the procedure with different values for the input parameters from the original execution. That is, if the second invocation of List_orders_2 is:

EXEC List_orders_2 '19900101'

The execution will still use the index on OrderDate, despite the query now retrieves all orders. This leads to a very important observation: the parameter values of the first execution of the procedure have a huge impact for subsequent executions. If this first set of values for some reason is atypical, the cached plan may not be optimal for future executions. This is why parameter sniffing is such a big deal.

Note: for a complete list of what can cause plans to be flushed or statements to be recompiled, see the white paper on Plan Caching listed in the Further Reading section.

Different Plans for Different Settings

There is a plan for the procedure in the cache. That means that everyone can use it, or? No, in this section we will learn that there can be multiple plans for the same procedure in the cache. To understand this, let's consider this contrived example:

CREATE PROCEDURE List_orders_6 AS SELECT * FROM Orders WHERE OrderDate > '12/01/1998' go SET DATEFORMAT dmy go EXEC List_orders_6 go SET DATEFORMAT mdy go EXEC List_orders_6 go

If you run this, you will notice that the first execution returns many orders, whereas the second execution returns no orders. And if you look at the execution plans, you will see that they are different as well. For the first execution, the plan is a Clustered Index Scan (which is the best choice with so many rows returned), whereas the second execution plan uses Index Seek with Key Lookup (which is the best when no rows are returned).

How could this happen? Did SET DATEFORMAT cause recompilation? No, that would not be smart. In this example, the executions come one after each other, but they could just as well be submitted in parallel by different users with different settings for the date format. Keep in mind that the entry for a stored procedure in the plan cache is not tied to a certain session or user, but it is global to all connected users.

Instead the answer is that SQL Server creates a second cache entry for the second execution of the procedure. We can see this if we peek into the plan cache with this query:

SELECT qs.plan_handle, a.attrlist FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est CROSS APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + ' ' FROM sys.dm_exec_plan_attributes(qs.plan_handle) epa WHERE epa.is_cache_key = 1 ORDER BY epa.attribute FOR XML PATH('')) AS a(attrlist) WHERE est.objectid = object_id ('dbo.List_orders_6') AND est.dbid = db_id('Northwind')

Reminder: You need the server-level permission VIEW SERVER STATE to run queries against the plan cache.

The DMV (Dynamic Management View) sys.dm_exec_query_stats has one entry for each query currently in the plan cache. If a procedure has multiple statements, there is one row per statement. Of interest here is sql_handle and plan_handle. I use sql_handle to determine which procedure the cache entry relates to (later we will see examples where we also retrieve the query text) so that we can filter out all other entries in the cache. Most often you use plan_handle to retrieve the query plan itself, and we will see an example of this later, but in this query I access a DMV that returns the attributes of the query plan. More specifically, I return the attributes that are cache keys. When there is more than one entry in the cache for the same procedure, the entries have at least one difference in the cache keys. A cache key is a run-time setting, which for one reason or another calls for a different query plan. Most of these settings are controlled with a SET command, but not all.

The query above returns two rows, indicating that there are two entries for the procedure in the cache. The output may look like this:

plan_handle attrlist ------------------------------- ------------------------------------------------- 0x0500070064EFCA5DB8A0A90500... compat_level=100 date_first=7 date_format=1 set_options=4347 user_id=1 0x0500070064EFCA5DB8A0A80500... compat_level=100 date_first=7 date_format=2 set_options=4347 user_id=1

To save space, I have abbreviated the plan handles and deleted many of the values in the attrlist column. I have also folded that column into two lines. If you run the query yourself, you can see the complete list of cache keys, and they are quite a few of them. If you look up the topic for sys.dm_exec_plan_attributes in Books Online, you will see description for many of the plan attributes, but you will also note that far from all cache keys are documented. In this article, I will not dive into all cache keys, not even the documented ones, but focus only on the most important ones.

As I said, the example is contrived, but it gives a good illustration to why the query plans must be different: different date formats may yield different results. A somewhat more normal example is this:

EXEC sp_recompile List_orders_2 go SET DATEFORMAT dmy go EXEC List_orders_2 '12/01/1998' go SET DATEFORMAT mdy go EXEC List_orders_2 '12/01/1998' go

(The initial sp_recompile is to make sure that the plan from the previous example is flushed.) This example yields the same results and the same plans as with List_orders_6 above. That is, the two query plans use the actual parameter value when the respective plan is built. The first query uses 12 Jan 1998, and the second 1 Dec 1998.

A very important cache key is set_options. This is a bit mask that gives the setting of a number of SET options that can be ON or OFF. If you look further in the topic of sys.dm_exec_plan_attributes, you find a listing that details which SET option each bit describes. (You will also see that there are a few more items that are not controlled by the SET command.) Thus, if two connections have any of these options set differently, the connections will use different cache entries for the same procedure – and therefore they could be using different query plans, with possibly big difference in performance.

One way to translate the set_options attribute is to run this query:

SELECT convert(binary(4), 4347)

This tells us that the hex value for 4347 is 0x10FB. Then we can look in Books Online and follow the table to find out that the following SET options are in force: ANSI_PADDING, Parallel Plan, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON and ARITHABORT.

You can also use this table-valued function that I have written and run:

SELECT Set_option FROM setoptions (4347) ORDER BY Set_option

Note: You may be wondering what Parallel Plan is doing here, not the least since the plan in the example is not parallel. When SQL Server builds a parallel plan for a query, it may later also build a non-parallel plan if the CPU load in the server is such that it is not defensible to run a parallel plan. It seems that for a plan that is always serial that the bit for parallel plan is nevertheless set in set_options.

To simplify the discussion, we can say that each of these SET options – ANSI_PADDING, ANSI_NULLS etc – is a cache key on its own. The fact that they are added together in a singular numeric value is just a matter of packaging.

The Default Settings

About all of the SET ON/OFF options that are cache keys exist because of legacy reasons. Originally, in the dim and distant past, SQL Server included a number of behaviours that violated the ANSI standard for SQL. With SQL Server 6.5, Microsoft introduced all these SET options (save for ARITHABORT, which was in the product already in 4.x), to permit users to use SQL Server in an ANSI-compliant way. In SQL 6.5, you had to use the SET options explicitly to get ANSI compliance, but with SQL 7, Microsoft changed the defaults for clients that used the new versions of the ODBC and OLE DB APIs. The SET options still remained to provide backwards compatibility for older clients.

Note: In case you are curious what effect these SET options have, I refer you to Books Online. Some of them are fairly straight-forward to explain, whereas others are just too confusing. To understand this article, you only need to understand that they exist, and what impact they have on the plan cache.

Alas, Microsoft did not change the defaults with full consistency, and even today the defaults depend on how you connect, as detailed in the table below.

Applications using

ADO .Net, ODBC or OLE DB SSMS SQLCMD,

OSQL, BCP,

SQL Server Agent ISQL,

DB-Library ANSI_NULL_DFLT_ON ON ON ON OFF ANSI_NULLS ON ON ON OFF ANSI_PADDING ON ON ON OFF ANSI_WARNINGS ON ON ON OFF CONACT_NULLS_YIELD_NULL ON ON ON OFF QUOTED_IDENTIFIER ON ON OFF OFF ARITHABORT OFF ON OFF OFF

You might see where this is getting at. Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but by far the most common reason for slow in the application, fast in SSMS is parameter sniffing and the different defaults for ARITHABORT. (If that was all you wanted to know, you can stop reading. If you want to fix your performance problem – hang on! And, no, putting SET ARITHABORT ON in the procedure is not the solution.)

Besides the SET command and the defaults above, ALTER DATABASE permits you to say that a certain SET option always should be ON by default in a database and thus override the default set by the API. However, while the syntax may indicate so, you cannot specify than an option should be OFF this way. Also, beware that if you test these options from Management Studio, they may not seem to work, since SSMS submits explicit SET commands, overriding any default. There is also a server-level setting for the same purpose, the configuration option user options which is a bit mask. You can set the individual bits in the mask from the Connection pages of the Server Properties in Management Studio. Overall, I recommend against controlling the defaults this way, as in my opinion they mainly serve to increase the confusion.

It is not always the run-time setting of an option that applies. When you create a procedure, view, table etc, the settings for ANSI_NULLS and QUOTED_IDENTIFIER, are saved with the object. That is, if you run this:

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF go CREATE PROCEDURE stupid @x int AS IF @x = NULL PRINT "@x is NULL" go SET ANSI_NULLS, QUOTED_IDENTIFIER ON go EXEC stupid NULL

It will print

@x is NULL

(When QUOTED_IDENTIFIER is OFF, double quote( " ) is a string delimiter on equal basis with single quote( ' ). When the setting is ON, double quotes delimit identifiers in the same way that square brackets ( [] ) do and the PRINT statement would yield a compilation error.)

In addition, the setting for ANSI_PADDING is saved per table column where it is applicable (The data types varchar and varbinary).

All these options and different defaults are certainly confusing, but here are some pieces of advice. First, remember that the first six of these seven options exist only to supply backwards compatibility, so there is little reason why you should ever have any of them OFF. Yes, there are situations when some of them may seem to buy a little more convenience if they are OFF, but don't fall for that temptation. One complication here, though, is that the SQL Server tools spew out SET commands for some of these options when you script objects. Thankfully, they mainly produce SET ON commands that are harmless. (But when you script a table, scripts may still in have a SET ANSI_PADDING OFF at the end. You can control this under Tools -> Options -> Scripting where you can set Script ANSI_PADDING commands to False, which I recommend.)

Next, when it comes to ARITHABORT, you should know that in SQL 2005 and later versions, this setting has zero impact as long as ANSI_WARNINGS is ON. (To be precise: it has no impact as long as the compatibility level is 90 or higher.) Thus, there is no reason to turn it on for the sake of the matter. And when it comes to SQL Server Management Studio, you might want do yourself a favour, and open this dialog and uncheck SET ARITHABORT as highlighted:

This will change your default setting for ARITHABORT when you connect with SSMS. It will not help you to make your application to run faster, but you will at least not have to be perplexed by getting different performance in SQL Server Management Studio.

For reference, below is how the ANSI page should look like. A very strong recommendation: never change anything on this page!

When it comes to SQLCMD and OSQL, make the habit to always use the -I option, which causes these tools to run with QUOTED_IDENTIFIER ON. The corresponding option for BCP is -q . (To confuse, -q has one more effect for BCP which I discuss in my article Using the Bulk-Load Tools in SQL Server.) It's a little more difficult in Agent, since there is no way to change the default for Agent – at least I have not found any. Then again, if you only run stored procedures from your job steps, this is not an issue, since the saved setting for stored procedures takes precedence. But if you would run loose batches of SQL from Agent jobs, you could face the problem with different query plans in the job and SSMS because of the different defaults for QUOTED_IDENTIFER. For such jobs, you should always include the command SET QUOTED_IDENTIFIER ON as the first command in the job step.

We have already looked at SET DATEFORMAT, and there are two more options in that group: LANGUAGE and DATEFIRST. The default language is configured per user, and there is a server-wide configuration option which controls what is the default language for new users. The default language controls the default for the other two. Since they are cache keys, this means that two users with different default languages will have different cache entries, and may thus have different query plans.

My recommendation is that you should try to avoid being dependent on language and date settings in SQL Server altogether. For instance, in as far as you use date literals at all, use a format that is always interpreted the same, such as YYYYMMDD. (For more details about date formats, see the article The ultimate guide to the datetime datatypes by SQL Server MVP Tibor Karaszi.) If you want to produce localised output from a stored procedure depending on the user's preferred language, it may be better to roll your own than rely on the language setting in SQL Server.

Note: I said above that ARITHABORT has no impact at all as long as ANSI_WARNINGS is on. Yet, the topic for SET ARITHABORT ON has this passage: You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. I have checked older versions of this topic, and the passage first appeared in the SQL 2012 version of the topic. Since I don't know the optimizer internals, I can't say for sure the passage is nonsense, but it certainly does not make sense to me. Thus, I stand to what I said above: the setting has zero impact on compatibility level 90 and higher. I you feel compelled to follow the recommendation, please bear in mind that if you start to submit an extra batch for every connection you make, you will add an extra network roundtrip, which can be a performance issue in itself.

The Effects of Statement Recompile

To get a complete picture how SQL Server builds the query plan, we need to study what happens when individual statements are recompiled. Above, I mentioned a few situations where it can happen, but at that point I did not go into details.

The procedure below is certainly contrived, but it serves well to demonstrate what happens.

CREATE PROCEDURE List_orders_7 @fromdate datetime, @ix bit AS SELECT @fromdate = dateadd(YEAR, 2, @fromdate) SELECT * FROM Orders WHERE OrderDate > @fromdate IF @ix = 1 CREATE INDEX test ON Orders(ShipVia) SELECT * FROM Orders WHERE OrderDate > @fromdate go EXEC List_orders_7 '19980101', 1

When you run this and look at the actual execution plan, you will see that the plan for the first SELECT is a Clustered Index Scan, which agrees with what we have learnt this far. SQL Server sniffs the value 1998-01-01 and estimates that the query will return 267 rows which is too many to read with Index Seek + Key Lookup. What SQL Server does not know is that the value of @fromdate changes before the queries are executed. Nevertheless, the plan for the second, identical, query is precisely Index Seek + Key Lookup and the estimate is that one row is returned. This is because the CREATE INDEX statement sets a mark that the schema of the Orders table has changed, which triggers a recompile of the second SELECT statement. When recompiling the statement, SQL Server sniffs the value of the parameter which is current at this point, and thus finds the better plan.

Run the procedure again, but with different parameters (note that the date is two years earlier in time):

EXEC List_orders_7 '19960101', 0

The plans are the same as in the first execution, which is a little more exciting than it may seem at first glance. On this second execution, the first query is recompiled because of the added index, but this time the scan is the "correct" plan, since we retrieve about one third of the orders. However, since the second query is not recompiled now, the second query runs with the Index Seek from the previous execution, although now it is not an efficient plan.

Before you continue, clean up:

DROP INDEX test ON Orders DROP PROCEDURE List_orders_7

As I said, this example is contrived. I made it that way, because I wanted a compact example that is simple to run. In a real-life situation, you may have a procedure that uses the same parameter in two queries against different tables. The DBA creates a new index on one of the tables, which causes the query against that table to be recompiled, whereas the other query is not. The key takeaway here is that the plans for two statements in a procedure may have been compiled for different "sniffed" parameter values.

When we have seen this, it seems logical that this could be extended to local variables as well. But this is not the case:

CREATE PROCEDURE List_orders_8 AS DECLARE @fromdate datetime SELECT @fromdate = '20000101' SELECT * FROM Orders WHERE OrderDate > @fromdate CREATE INDEX test ON Orders(ShipVia) SELECT * FROM Orders WHERE OrderDate > @fromdate DROP INDEX test ON Orders go EXEC List_orders_8 go DROP PROCEDURE List_orders_8

In this example, we get a Clustered Index Scan for both SELECT statements, despite that the second SELECT is recompiled during execution and the value of @fromdate is known at this point.

However, there is one exception, and that is table variables. Normally SQL Server estimates that a table variable has a single row, but when there are recompiles in sway, the estimate may be different:

CREATE PROCEDURE List_orders_9 AS DECLARE @ids TABLE (a int NOT NULL PRIMARY KEY) INSERT @ids (a) SELECT OrderID FROM Orders SELECT COUNT(*) FROM Orders O WHERE EXISTS (SELECT * FROM @ids i WHERE O.OrderID = i.a) CREATE INDEX test ON Orders(ShipVia) SELECT COUNT(*) FROM Orders O WHERE EXISTS (SELECT * FROM @ids i WHERE O.OrderID = i.a) DROP INDEX test ON Orders go EXEC List_orders_9 go DROP PROCEDURE List_orders_9

When you run this, you will get in total four execution plans. The two of interest are the second and fourth plans that come from the two identical SELECT COUNT(*) queries. I have included the interesting parts of the plans here, together with the pop-up for the Clustered Index Scan operator over the table variable.

In the first plan, there is a Nested Loops Join operator together with a Clustered Index Seek on the Orders table, which is congruent with the estimate of the number of rows in the table variable: one single row, the standard assumption. In the second query, the join is carried out with a Merge Join together with a table scan of Orders. As you can see, the estimate for the table variable is now 830 rows, because when recompiling a query, SQL Server "sniffs" the cardinality of the table variable, even if it is not a parameter.

And with some amount of bad luck this can cause issues similar to those with parameter sniffing. I once ran into a situation where a key procedure in our system suddenly was slow, and I tracked it down to a statement with a table variable where the estimated number of rows was 41. Unfortunately, when this procedure runs in the daily processing it's normally called on one-by-one basis, so one row was a much better estimate in that case.

Note: what I said above is true up to SQL 2017. If you run List_orders_9 on SQL 2019 or later, you will find that the first SELECT COUNT(*) also runs with a Merge Join and has a correct estimate of 830 rows. Starting with SQL 2019, SQL Server defers compilation of a statement referfing to a table variable until execution reaches that statement. In this way, the statement will be compiled with the actual cardinality of the table instead of a blind assumption of one row. This is intended to improve performance, and certainly it should in many cases. But as I noted above, there are situations where this can backfire.

Speaking of table variables, you may be curious about table-valued parameters, introduced in SQL 2008. They are handled very similar to table variables, but since the parameter is populated before the procedure is invoked, it is now a common situation that SQL Server will use an estimate of more than one row. Here is an example:

CREATE TYPE temptype AS TABLE (a int NOT NULL PRIMARY KEY) go CREATE PROCEDURE List_orders_10 @ids temptype READONLY AS SELECT COUNT(*) FROM Orders O WHERE EXISTS (SELECT * FROM @ids i WHERE O.OrderID = i.a) go DECLARE @ids temptype INSERT @ids (a) SELECT OrderID FROM Orders EXEC List_orders_10 @ids go DROP PROCEDURE List_orders_10 DROP TYPE temptype

The query plan for this procedure is the same as for the second SELECT query in List_orders_9, that is Merge Join + Clustered Index Scan of Orders, since SQL Server sees the 830 rows in @ids when the query is compiled.

The Story So Far

In this chapter, we have looked at how SQL Server compiles a stored procedure and what significance the actual parameter values have for compilation. We have seen that SQL Server puts the plan for the procedure into cache, so that the plan can be reused later. We have also seen that there can be more than one entry for the same stored procedure in the cache. We have seen that there is a large number of different cache keys, so potentially there can be very many plans for a single stored procedure. But we have also learnt that many of the SET options that are cache keys are legacy options that you should never change.

In practice, the most important SET option is ARITHABORT, because the default for this option is different in an application and in SQL Server Management Studio. This explains why you can spot a slow query in your application, and then run it at good speed in SSMS. The application uses a plan which was compiled for a different set of sniffed parameter values than the actual values, whereas when you run the query in SSMS, it is likely that there is no plan for ARITHABORT ON in the cache, so SQL Server will build a plan that fits with your current parameter values.

You have also understood that you can verify that this is the case by running this command in your query window:

SET ARITHABORT OFF

and with great likelihood, you will now get the slow behaviour of the application also in SSMS. If this happens, you know that you have a performance problem related to parameter sniffing. What you may not know yet is how to address this performance problem, and in the following chapters I will discuss possible solutions, before I return to the theme of compilation, this time for ad-hoc queries, a.k.a. dynamic SQL.

Note: There are always these funny variations. The application I mainly work with actually issues SET ARITHABORT ON when it connects, so we should never see this confusing behaviour in SSMS. Except that we do. Some part of the application also issues the command SET NO_BROWSETABLE ON on connection. I have never been able to understand the impact of this undocumented SET command, but I seem to recall that it is related to early versions of "classic" ADO. And, yes, this setting is a cache key.

It's Not Always Parameter Sniffing...

Before we delve into how to address performance problems related to parameter sniffing, which is quite a broad topic, I would first like to give some coverage to a couple of cases where parameter sniffing is not involved, but where you nevertheless may experience different performance in the application and SSMS.

Replacing Variables and Parameters

I have already touched at this, but it is worth expanding on a bit.

Occasionally, I see people in the forums telling me that their stored procedure is slow, but when they run the same query outside the procedure it's fast. After a few posts in the thread, the truth is revealed: the query they are struggling with refer to variables, be that local variables or parameters. To troubleshoot the query on its own, they have replaced the variables with constants. But as we have seen, the resulting stand-alone query is quite different, and SQL Server can make more accurate estimates with constants instead of variables, and therefore it arrives at a better plan. Furthermore, SQL Server does not have to consider that the constant may have a different value next time the query is run.

A similar mistake is to make the parameters into variables. Say that you have:

CREATE PROCEDURE some_sp @par1 int AS ... -- Some query that refers to @par1

You want to troubleshoot this query on its own, so you do:

DECLARE @par1 int SELECT @par1 = 4711 -- query goes here

From what you have learnt here, you know that this is very different from when @par1 really is a parameter. SQL Server has no idea about the value for @par1 when you declare it as a local variable and will make standard assumptions.

But if you have a 1000-line stored procedure, and one query is slow, how do you run it stand-alone with great fidelity, so that you have the same presumptions as in the stored procedure?

One way to tackle this is to embed the query in sp_executesql:

EXEC sp_executesql N'-- Some query that refers to @par1', N'@par1 int', 4711

You will need to double any single quotes in the query to be able to put it in a character literal. If the query refers to local variables, you should assign them in the block of dynamic SQL and not pass them as parameters so that you have the same presumptions as in the stored procedure.

Another option is to create dummy procedure with the problematic statement; this saves from doubling any quotes. To avoid litter in the database, you could create a temporary stored procedure:

CREATE PROCEDURE #test @par1 int AS -- query goes here.

As with dynamic SQL, make sure that local variables are locally declared also in your dummy. I will need to add the caveat I have not investigated whether SQL Server have special tweaks or limitations when optimising temporary stored procedures. Not that I see why there should be any, but I have been burnt before...

Blocking

You should not forget that one possible reason that the procedure ran slow in the application was simply a matter of blocking. When you tested the query three hours later in SSMS, the blocker had completed its work. If you find that no matter how you run the procedure in SSMS, with or without ARITHABORT, the procedure is always fast, blocking is starting to seem a likely explanation. Next time you are alarmed that the procedure is slow, you should start your investigation with some blocking analysis. That is a topic which is completely outside the scope for this article, but for a good tool to investigate locking, see my beta_lockinfo.

Database Settings

Let's say that you run a query like this in two different databases:

SELECT ... FROM dbo.sometable JOIN dbo.someothertable ON ... JOIN dbo.yetanothertable ON ... WHERE ...

You find that the query performs very differently in the two databases. There can be a whole lot of reasons for these differences. Maybe the data sizes are entirely different in one or more of the tables, or the data is distributed differently. It could be that the statistics are different, even if the data is identical. It could also be that one database has an index that the other database has not.

Say that the query instead goes:

SELECT ... FROM thatdb.dbo.sometable JOIN thatdb.dbo.someothertable ON ... JOIN thatdb.dbo.yetanothertable ON ... WHERE ...

That is, the query refers to the tables in three-part notation. And yet you find that the query runs faster in SSMS than in the application or vice versa. But when you get the idea to change the database in SSMS to be the same as in the application, you get slow performance in SSMS as well. What is going on? It can certainly not be anything of what I discssused above, since the tables are the same, no matter the database you issue the query from.

The answer is that it may be parameter sniffing, because if you look at the output of the query I introduced in the section Different Plans for Different Settings, you will see that dbid is one of the cache keys. That is, if you run the same query against the same tables from two different databases, you get different cache entries, and thus there can be different plans. And, as we have learnt, one of the possible reasons for this is parameter sniffing. But it could also be that the settings for the two databases are different:

Thus, f this occurs to you, run this query:

SELECT * FROM sys.databases WHERE name IN ('slowdb', 'fastdb')

(Obviously, you should replace slowdb and fastdb with the names of the actual databases you ran from.) Compare the rows, and make note of the differences. Far from all the columns you see affect the plan choice. The by far the most important one is the compatibility level. When Microsoft makes enhancements to the optimiser in a new version of SQL Server, they only make these enhancements available in the latest compatibility level, because although they are intended to be enhancements, there will always be queries that will be negatively affected by the change and run a lot slower.

If you are on SQL 2016 or later, you also need to look in sys.database_scoped_configurations and compare the settings between the two databases. (This view does not hold the database id or the name, but each database has its own version.) Quite a few of these settings affects the work of the optimizer, which could lead to different plans.

What you would do once you have identified the difference depends on the situation. But generally, I recommend against changing away from the defaults. (Starting with SQL 2017, sys.database_scoped_configurations has a column is_value_default which is 1, if the current setting is the default setting.)

For isntance, if you find that the slow plan comes from a database with a lower compatibility level, consider changing the compatitiblity level for that database. But if the slow plan occurs with the database with the higher compatibility level, you should not change the setting, but rather work with the query and available indexes to see what can be done. In my experience, when a query regresses when going to a newer version of the optimiser, there is usually something that is problematic, and you were only lucky that the query ran fast on the earlier version.

There is one quite obvious exception to the rule of sticking with the defaults: if you find that in the fast database, the database-scoped configuration QUERY_OPTIMIZER_HOTFIXES is set to 1, you should consider to enable this setting for the other database as well, as this will give you access to optimiser fixes released after the original release of the SQL Server version you are using.

Just to make it clear: the database settings do not only apply to queries with tables in three-part notation, but they can also explain why the same query or stored procedure with tables in one- or two-part notation gets different plans and performance in seemingly similar databases.

Indexed Views and Indexed Computed Columns

This is a problem which is far more common on SQL 2000 than on later versions. Or to be precise, it applies to databases which are in compatibility level 80, which is why this can occur also on SQL 2005 and SQL 2008. (Later versions do not support this compat level.)

As long as the database is compatibility level 90 or later, for SQL Server 2005 to consider indexed views and indexes on computed columns (as well as filtered indexes added in SQL 2008) when compiling a query, these settings must be ON: QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL. Furthermore, NUMERIC_ROUNDABORT must be OFF. But in compat level 80, there is one more option that must be ON, and, yes, you guessed it: ARITHABORT. (The reason for this is that in compatibility level 80 there is one type of error – domain errors, e.g. sqrt(-1) – that is covered by ARITHABORT, but not by ANSI_WARNINGS.)

Thus, with compatibility level 80, an application using the default SET options will not be able to take benefit of an index on a computed column or an indexed view, even if that would be the optimal query plan. But when you run the query in SSMS, performance will be a lot better, even if no parameter sniffing is involved, because there ARITHABORT is ON by default.

There is a second phenomenon you can run into with indexed computed columns and indexed views which also is related to SQL 2000, but because of legacy can persist into even recent versions. You have a stored procedure that is slow. You take out the statement and run it on its own, and now it's lightning fast, even if you package it nice and cleanly in a temporary stored procedure as above. Why? Run this statement:

SELECT objectproperty(object_id('your_sp'), 'IsQuotedIdentOn'), objectproperty(object_id('your_sp'), 'IsAnsiNullsOn')

Most likely it will return 0 it at least one of the two columns. As I noted previously, these two settings, QUOTED_IDENTIFIER and ANSI_NULLS are saved with the procedure, and thus the saved settings apply when the procedure runs, not the settings of the connection.

But why would these options be OFF? I would guess that this mainly happens with databases originating from SQL 2000 or earlier versions. To wit, in SQL 2000, you could create stored procedures from Enterprise Manager, and Enterprise Manager always submitted SET QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF prior to creating the objects and this was not configurable. The database may have moved on to newer versions of SQL Server, but when you script the procedure from SSMS, SSMS will add SET OFF commands to retain the old settings. There is all reason to consider to change the SET commands while you are at it!

If only QUOTED_IDENTIFIER is OFF, but ANSI_NULLS is ON for a procedure, one can suspect that it has been created through SQLCMD which as we have seen run with QUOTED_IDENTIFIER OFF by default. (Always run these tools with the -I option to override.)

To find all bad modules in a database, you can use this SELECT:

SELECT o.name FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id WHERE (m.uses_quoted_identifier = 0 or m.uses_ansi_nulls = 0) AND o.type NOT IN ('R', 'D')

An Issue with Linked Servers

This section concerns an issue with linked servers which mainly occurs when the remote server is earlier than SQL 2012 SP1, but it can appear with later versions as well under some circumstances.

Consider this query:

SELECT C.* FROM SQL_2008.Northwind.dbo.Orders O JOIN Customers C ON O.CustomerID = C.CustomerID WHERE O.OrderID > 20000

I ran this query twice, logged in as two different users. The first user is sysadmin on both servers, whereas the second user is a plain user with only SELECT permissions. To ensure that I would get different cache entries, I used different settings for ARITHABORT.

When I ran the query as sysadmin I got this plan:

When I ran the query as the plain user, the plan was different:

How come the plans are different? It's certainly not parameter sniffing because there are no parameters. As always when a query plan has an unexpected shape or operator, it is a good idea to look at the estimates. Here are the pop-ups for the Remote Query operators in the two plans, with the pop-up for the first plan to the left:

You can see that the estimates are different. When I ran as sysadmin, the estimate was 1 row, which is a correct number, since there are no orders in Northwind where the order ID exceeds 20000. (Recall that the optimizer never assumes zero rows from statistics.) But when I ran the query as a plain user, the estimate was 249 rows. We recognize this particular number as 30 % of 830 orders, or the estimate for an inequality operation when the optimizer has no information. Previously, this was due to an unknown variable value, but in this case there is no variable that can be unknown. No, it is the statistics themselves that are missing.

As long as a query accesses tables in the local server only, the optimizer can always access the statistics for all tables in the query. This happens internally in SQL Server and there are no extra checks to see whether the user has permission to see the statistics. But this is different with tables on linked servers. When SQL Server accesses a linked server, the optimizer needs to retrieve the statistics on the same connection that is used to retrieve the data, and it needs to use T‑SQL commands which is why permissions come into play. And, unless login mapping has been set up, those are the permissions of the user running the query.

By using Profiler or Extended Events you can see that the optimizer retrieves the statistics in two steps. First it calls the procedure sp_table_statistics2_rowset which returns information about which column statistics there are, as well as the cardinality and density information of the columns. In the second step, it runs DBCC SHOW_STATISTICS to get the full distribution statistics. (We will look closer at this command later in this article.)

Note: to be accurate, the optimizer does not talk to the linked server at all, but it interacts with the OLE DB provider for the remote data source and requests the provider to return the information the optimizer needs.

For sp_table_statistics2_rowset to run successfully the user must have the permission VIEW DEFINITION on the table. This permission is implied if the user has SELECT permission on the table (without which the user would not be able to run the query at all). So, unless the user has been explicitly denied VIEW DEFINITION, this procedure is not so much a concern.

DBCC SHOW_STATISTICS is a different matter. For a long time, running this command required membership in the server role sysadmin or in one of the database roles db_owner or db_ddladmin. This was changed in SQL 2012 SP1, so starting with this version, only SELECT permission is needed.

And this is why I got different results. As you can tell from the server name, my linked server was an instance running SQL 2008. Thus, when I was connected as a user that was sysadmin on the remote instance, I got the full distribution statistics which indicated that there are no rows with order ID > 20000, and the estimate was one row. But when running as the plain user, DBCC SHOW_STATISTICS failed with a permission error. This error was not propagated, but instead the optimizer accepted that there were no statistics and used default assumptions. Since it did get cardinality information from sp_table_statistics2_rowset, it learnt that the remote table has 830 rows, whence the estimate of 249 rows.

From what I said above, this should not be an issue if the linked server runs SQL 2012 SP1 or later, but there can still be obstacles. If the user does not have SELECT permission on all columns in the table, there may be statistics the optimizer is not able to retrieve. Furthermore, according to Books Online, there is a trace flag (9485) which permits the DBA to prevent SELECT permission to be sufficient for running DBCC SHOW_STATISTICS. And more importantly, if row-level security (a feature added in SQL 2016) has been set up for the table, only having SELECT permission is not sufficient as this could permit users to see data they should not have access to. That is, to run DBCC SHOW_STATISTICS on a table with row-level filtering enabled, you need membership in sysadmin, db_owner or db_ddladmin. (Interesting enough, one would expect the same to apply if the table has Dynamic Data Masking enabled, but that does seem to be the case.)

Thus, when you encounter a performance problem where a query that accesses a linked server is slow in the application, but it runs fast when you test it from SSMS (where you presumably are connected as a power user), you should always investigate the permissions on the remote database. (Keep in mind that the access to the linked server may not be overt in the query, but could be hidden in a view.)

If you determine that permissions on the remote database is the problem, what actions could you take? Granting users more permissions on the remote server is of course an easy way out, but absolutely not recommendable from a security perspective. Another alternative is to set up login-mapping so that users log on the remote server with a proxy user with sufficient powers. Again, this is highly questionable from a security perspective.

Rather you would need to tweak the query. For instance, you can rewrite the query with OPENQUERY to force evaluation on the remote server. This can be particularly useful, if the query includes several remote tables, since for the query that runs on the remote server, the remote optimizer has full access to the statistics on that server. (But it can also backfire, because the local optimizer now gets even less statistics information from the remote server.) You can also use the full battery of hints and plan guides to get the plan you want.

I would also recommend that you ask yourself (and the people around you): is that linked-server access needed? Maybe the databases could be on the same server? Could data be replicated? Some other solution? Personally, linked servers is something I try to avoid as much as possible. Linked servers often mean hassle, in my experience.

Before I close this section, I like to repeat: what matters is the permissions on the remote server, not the local server where the query is issued. I also like to point out that I have given a blind eye to what may happen with other remote data sources such as Oracle, MySQL or Access as they have different permission systems of which I'm entirely ignorant. You may or may not see similar issues when running queries against such linked servers.

Could it Be MARS?

I got this mail from a reader:

We recently found a SQL Server 2016 select query against CCIs that was slow in the application, and fast in SSMS. Application execution time was 3 to 4 times that of SSMS – looking a bit deeper it was seen that the ratio of CPU time was similar. This held true whether the query was executed in parallel as typical or in serial by forcing MAXDOP 1. An unusual facet was that Query Store was accounting the executions against the same row in sys.query_store_query and sys.query_store_plan, whether from the app or from SSMS. The application was using a connection string enabling MARS, even though it didn’t need to. Removing that clause from the connection string resulted in SSMS and the application experiencing the same CPU time and elapsed time. We’ll keep looking to determine the mechanics of the difference. I suspect it may be due to locking behavior difference – perhaps MARS disables lock escalation.

Note: MARS = Multiple Active Result Sets. If you set this property on a connection string, you can run multiple queries on the same connection in an interleaved fashion. It's mainly intended to permit you to submit UPDATE statements as you are iterating through a result set.

The observations from Query Store make it quite clear to me that the execution plan was the same in both cases. Thus, it cannot be a matter of parameter sniffing, different SET options or similar. Interesting enough, some time later after I had added this section to the article, I got a mail from a second reader who had experienced the same thing. That is, access to a clustered columnstore index was significantly slower with MARS enabled.

This kept me puzzled for a while, and there was too little information to make it possible for me to make an attempt to reproduce the issue. But then I got a third mail on this theme, and Nick Smith was kind to provide a simple example query:

SELECT TOP 10000 SiteId FROM MyTable

That is, it is simply a matter of a query that returns many rows. Note that in this case there was no columnstore index involved, but just a plain-vanilla table. I built a small C# program on this theme and measured execution time with and without MARS. At first I could not discern any difference at all, but I was running against my local instance. Once I targeted my database in Windows Azure it was a difference of a factor ten. When I connected to a server on the other side of town the difference was a factor of three or four.

It seems quite clear to me that it is a matter of network latency. I assume that the interleaved nature of MARS introduces chattiness on the wire, so the slower and longer the network connection is, the more is that chattiness going to affect you.

Thus, if you find that a query that returns a lot of data runs slow in the application and a lot faster in SSMS, there is all reason to see whether the application specifies MultipleActiveResultSets=true in the connection string. If it does, ask yourself if you need it, and if not take it out.

Could MARS make an application go slower for some other reason? I see no reason to believe so, but it is still a little telling that my first two correspondents mentiond columnstore indexes. Also, the network latency does not really explain the difference in CPU mentioned in the quote above. So, if you encounter a situation where you conclude that MARS slows things and that there is no network latency, I would be very interested in hearing from you.

Getting Information to Solve Parameter Sniffing Problems

We have learnt how it may come that you have a stored procedure that runs slow in the application, and yet the very same call runs fast when you try it in SQL Server Management Studio: Because of different settings of ARITHABORT you get different cache entries, and since SQL Server employs parameter sniffing, you may get different execution plans.

While the secret behind the mystery now has been unveiled, the main problem still remains: how do you address the performance problem? From what you read this far, you already know of a quick fix. If you have never seen the problem before and/or the situation is urgent, you can always do:

EXEC sp_recompile problem_sp

As we have seen, this will flush the procedure from the plan cache, and next time it is invoked, there will be a new query plan. And if the problem never comes back, consider case closed.

But if the problem keeps reoccurring – and unfortunately, this is the more likely outcome – you need to perform a deeper analysis, and in this situation you should not use sp_recompile, or in some other way alter the procedure. You should keep that slow plan around, so that you can examine it, and not the least find what parameter values the bad plan was built for. This is the topic for this chapter.

Note: the advice in the paragraph above does not apply if you are on SQL 2016 or later and you have enabled the Query Store for your database. In this case, you can find all information about the plans in the Query Store views, even after the plans have been flushed. In the last chapter, I present Query Store versions of the queries that follows below.

Before I go on, a small observation: above I recommended that you should change your preferences in SSMS, so that you by default connect with ARITHABORT OFF to avoid this kind of confusion. But there is actually a small disadvantage with having the same settings as the application: you may not observe that the performance problem is related to parameter sniffing. But if you make it a habit when investigating performance issue to run your problem procedure with ARITHABORT both ON and OFF, you can easily conclude whether parameter sniffing is involved.

Getting the Necessary Facts

All performance troubleshooting requires facts. If you don't have facts, you will be in the situation that Bryan Ferry describes so well in the song Sea Breezes from the first Roxy Music album:

We've been running round in our present state

Hoping help will come from above

But even angels there make the same mistakes

If you don't have facts, not even the angels will be able to help you. The base facts you need to troubleshoot performance issues related to parameter sniffing are:

Which is the slow statement? What are the different query plans? What parameter values did SQL Server sniff? What are the table and index definitions? How do the distribution statistics look like? Is it up to date?

Almost all of these points apply to about any query-tuning effort. Only the third point is unique to parameter-sniffing issues. That, and the plural in the second point: you want to look at two plans, the good plan and the bad plan. In the following sections, we will look at these points one by one.

Which is the Slow Statement?

First on the list is to find the slow statement – in most cases, the problem lies with a single statement. If the procedure has only one statement, this is trivial. Else, you can use Profiler to find out; the Duration column will tell you. Either just trace the procedure from the application, or run the procedure from Management Studio (with ARITHABORT OFF!) and filter for your own spid.

Yet another option is to use the stored procedure sp_sqltrace, written by Lee Tudor and which I am glad to host on my web site. sp_sqltrace takes an SQL batch as parameter, starts a server-side trace, runs the batch, stops the trace and then summarises the result. There are a number of input parameters to control the procedure, for instance how to sort the output. This procedure is particularly useful to determine the slow statement in a loop, if you would happen to have such in your stored procedure.

Getting the Query Plans and Parameters with Management Studio

In many cases, you can easily find the query plans by running the procedure in Management Studio, after first enabling Include Actual Execution Plan (you find it under the Query menu). This works well, as long as the procedure does not include a multitude of queries, in which case the Execution Plan tab gets too littered to work with. We will look at alternative strategies in coming sections.

Typically, you would run the procedure like this:

SET ARITHABORT ON go EXEC that_very_sp 4711, 123, 1 go SET ARITHABORT OFF go EXEC that_very_sp 4711, 123, 1

The assumption here is that the application runs with the default options, in which case the first call will give the good plan – because the plan is sniffed for the parameters you provide – and the second call will run with the bad plan which already is in the plan cache. To determine the cache keys in sway, you can use the query in the section Different Plans for Different Settings to see the cache-key values for the plan(s) in the cache. (If you already have tried the procedure in Management Studio, you may have two entries. The column execution_count in sys.dm_exec_query_stats can help you to discern the entries from each other; the one with the low count is probably your attempt from SSMS.)

Once you have the plans, you can easily find the sniffed parameter values. Right-click the left-most operator in the plan – the one that reads SELECT, INSERT, etc – select Properties, which will open a pane to the right. (That is the default position; the pane is detachable.) Here is an example how it can look like:

The first Parameter Compiled Value is the sniffed value which is causing you trouble one way or another. If you know your application and its usage pattern, you may get an immediate revelation when you see the value. Maybe you do not, but at least you know now that there is a situation where the application calls the procedure with this possibly odd value.

Note also that you can see the settings of some of the SET options that are cache keys. However, if you are still on SQL 2005, beware that there is a bug, so that that the SET options will be always be reported as False. This is an engine bug, and thus your version of SSMS does not matter. (This bug is fixed in SQL 2008 and later.)

While Management Studio provides a very good interface to examine query plans, I still want to put in a plug for a more versatile alternative, to wit Plan Explorer, a free tool from SentryOne, a tool vendor in the SQL Server space. Plan Explorer permits you to view the plan in different ways, and it is also easier to navigate among the queries if your batch has a lot of them.

When you look at a query plan, it is far from always apparent what part of the plan that is really costly. But the thickness of the arrows is a good lead. The thicker the arrow, the more rows are passed to the next operator. And if you are looking at an actual execution plan, the thickness is based on the actual number of rows. On the other hand, I usually don't give any attention to the percentages at all. They are always estimates, and they can be way off, particular if there is a gross misestimate somewhere in the plan. And that is often the case when you have a performance problem related to parameter sniffing.

Getting the Query Plans and Parameters Directly from the Plan Cache

It is not always feasible to use SSMS to get the query plans and the sniffed parameter values. The bad query maybe runs for more minutes than your patience can accept, or the procedure includes so many statements that you get a mess in SSMS. Not the least this can be an issue if the procedure includes a loop that is executed many times, in which case not even SentryOne's Plan Explorer may be workable.

One option to get hold of the query plan and the sniffed parameters is to retrieve it directly from the plan cache. This is quite convenient with help of the query below, but there is an obvious limitation with this method: you only get the estimates. The actual number of rows and actual number of executions, two values that are very important to understand why a plan is bad, are missing.

The Query

This query will return the statements, the sniffed parameter values and the query plans for a stored procedure:

DECLARE @dbname nvarchar(256), @procname nvarchar(256) SELECT @dbname = 'Northwind', @procname = 'dbo.List_orders_11' ; WITH basedata AS ( SELECT qs.statement_start_offset/2 AS stmt_start, qs.statement_end_offset/2 AS stmt_end, est.encrypted AS isencrypted, est.text AS sqltext, epa.value AS set_options, qp.query_plan, charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>') AS paramstart, charindex('</ParameterList>', qp.query_plan) AS paramend FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa WHERE est.objectid = object_id (@procname) AND est.dbid = db_id(@dbname) AND epa.attribute = 'set_options' ), next_level AS ( SELECT stmt_start, set_options, query_plan, CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED' WHEN stmt_start >= 0 THEN substring(sqltext, stmt_start + 1, CASE stmt_end WHEN 0 THEN datalength(sqltext) ELSE stmt_end - stmt_start + 1 END) END AS Statement, CASE WHEN paramend > paramstart THEN CAST (substring(query_plan, paramstart, paramend - paramstart) AS xml) END AS params FROM basedata ) SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement, CR.c.value('@Column', 'nvarchar(128)') AS Parameter, CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value], CAST (query_plan AS xml) AS [Query plan] FROM next_level n CROSS APPLY n.params.nodes('ColumnReference') AS CR(c) ORDER BY n.set_options, n.stmt_start, Parameter

If you have never worked with these DMVs before, I appreciate if this is mainly mumbo-jumbo to you. To keep the focus on the main subject of this article, I will defer to a later section to explain this query. The only thing I like to give attention to here and now is that you specify the database and the procedure you want to work with in the beginning. You may think this would better be a stored procedure, but it is quite likely that you want to add or remove columns, depending on what you are looking for.

The Output

To see the query in action, you can use this test batch (and, yes, the examples get more and more contrived as we move on):

CREATE PROCEDURE List_orders_11 @fromdate datetime, @custid nchar(5) AS SELECT @fromdate = dateadd(YEAR, 2, @fromdate) SELECT * FROM Orders WHERE OrderDate > @fromdate AND CustomerID = @custid IF @custid = 'ALFKI' CREATE INDEX test ON Orders(ShipVia) SELECT * FROM Orders WHERE CustomerID = @custid AND OrderDate > @fromdate IF @custid = 'ALFKI' DROP INDEX test ON Orders go SET ARITHABORT ON EXEC List_orders_11 '19980101', 'ALFKI' go SET ARITHABORT OFF EXEC List_orders_11 '19970101', 'BERGS'

When you have executed this batch, you can run the query above. When I do this I see this result in SSMS:

These are the columns:

SET – The set_options attribute for the plan. As I discussed earlier, this is a bit mask. In this picture, you see the two most likely values. 251 is the default settings and 4347 is the default settings + ARITHABORT ON. If you see other values, you can use the function setoptions to translate the bit mask.

Pos – This is the position for the query in the procedure, counted in characters from the start of the batch that created the procedure, including any comments preceding CREATE PROCEDURE. Not terribly useful in itself, but serves to sort the statements in the order they appear in the procedure.

Statement – The SQL statement. Note that the statements are repeated once for each parameter in the query.

Parameter – The name of the parameter. Only parameters that appear in this statement are listed. As a consequence of this, statements that do not refer to any parameters are not included in the output at all.

Sniffed Value – The compile-time value for the parameter, that is, the value that the optimizer sniffed when it built the plan. In difference to the Properties pane for the plan, you don't see any actual parameter value here. As I discussed previously, the sniffed value for a parameter can be different for different statements in the procedure, and you see an example of this in the picture above.

Query Plan – The query plan. You can double-click the XML document to see the graphical plan directly. (This does not always work. Typically, it does not work if your version of SSMS is lower than the SQL Server version. This feature was also broken in some versions of SSMS 2008 R2. And the feature did not exist at all in SSMS 2005.) As I noted above, this is only the estimated plan. You cannot get any actual values from the cache.

The Query Explained

This query refers to some DMVs which not all readers may be acquainted with. It also uses some query techniques that you may not be very familiar with, for instance XQuery. It would take up too much space and distract you from the main topic to dive into the query in full, so I will explain it only briefly. If the query and the explanation goes over your head, don't feel too bad about it. As long you understand the output, you can still have use for the query.

The query uses two CTEs (Common Table Expression). The first CTE, basedata, includes all access to DMVs. We have already seen all of them but sys.dm_exec_text_query_plan. There are two more columns we retrieve from sys.dm_exec_query_stats, to wit statement_start_offset and statement_end_offset. They delimit the statement for this row, and we pass them to sys.dm_exec_text_query_plan to get the plan for this statement only. (Recall that the procedure is a single cache entry with a single plan_handle.) sys.dm_exec_text_query_plan returns the column query_plan which contrary to what you may expect is nvarchar(MAX). The reason for this is that the XML for a query plan may be so deeply nested, that it cannot be represented with SQL Server's built-in xml data type. The CTE returns the query plan as such, but it also extracts the positions for the part in the document where the parameter values appear.

In the next CTE, next_level, I go on and use the values obtained in basedata. The CASE expression extracts the statement from the text returned by sys.dm_exec_sql_text. The way to do this is fairly clunky, not the least with those long column names. Since there is little reason to modify that part of the query, I say no more but refer you to Books Online. Or just believe me when I say it works. :-) The next column in the CTE, params, performs the actual extraction of the parameter values from the query-plan document and converts that element to the xml data type.

In the final SELECT, I shred the params document, so that we get one row per parameter. It can certainly be argued that it is better to have all parameters on a single row, since in this case each statement will only appear once, and here is a variation of the final SELECT that uses more XML functionality to achieve the string aggregation:

SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement, (SELECT CR.c.value('@Column', 'nvarchar(128)') + ' = ' + CR.c.value('@ParameterCompiledValue', 'nvarchar(512)') + ' ' FROM n.params.nodes('ColumnReference') AS CR(c) FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), CAST (query_plan AS xml) AS [Query plan] FROM next_level n ORDER BY n.set_options, n.stmt_start

In the final SELECT, I also convert the query-plan column to XML, but as noted above, this could fail because of limitations with the xml data type. If you get such an error, just comment out that column, or change CAST to TRY_CAST if you are on SQL 2012 or higher. (TRY_CAST returns NULL if the conversion fails.)

Beside the alterations I have already mentioned, there are several ways you could modify the query to retrieve information you find interesting. For instance, you could add more columns from sys.dm_exec_query_stats or more plan attributes. I opted to include the set_options attribute only, since this is the cache key which is most likely to vary. If you would like to include all statements in the procedure, including those that do not refer to any of the input parameters, just change CROSS APPLY on the next-to-last line to OUTER APPLY.

Live Query Plan

Returning to Management Studio, there is one more option to see query plans and that is a Live Query Plan. The situation when you would use a live query plan is when the query does not complete in reasonable time and you want more information than the estimated plan gives. With a live query plan, the actual values in the plan are updated as the query progresses. You can see both solid lines and dotted lines. I believe the theory is that solid lines represents parts of the plan that are supposed to be completed, while dotted ones are where data is still flowing. But looking at a live query plan for a big a slow query, I can't really piece it together. Certainly an operator is still working if the row count below it is increasing. Nevertheless, hovering over the operators, you can inspect the current actual values, and you can see if there are gross deviations from the estimates.

Live Query Plan is a relatively new feature. It was introduced in SSMS 16, that is the first free version of SSMS that is available as a separate download. (Which I recommend you to use, even if you are on an older version of SQL Server.) Furthermore, there are some requirements on the SQL Server side as noted below.

There are two ways you can see a live query plan in SSMS. One is to run a query from a query window and enable Include Live Query Statistics from the Query menu. This requires that you are connected to SQL 2014 or later.

If you did not think of enabling live query statistics before starting the query, or you have the query running in the application right now, a second option is to use Activity Monitor (found in Object Explorer, by right-clicking the Server node itself.) Open it and find the pane Active Expensive Queries. You are likely to find your slow query here. You can right-click the query, and the option Show Execution Plan is always there. That gives you the estimated execution plan. If the lightweight execution profiling infrastructure is enabled, the option Show Live Execution Plan is also available. This infrastructure is available if any of these are true:

You are on SQL 2019 or later.

You are on SQL 2017 or SQL 2016 SP1 and up, and trace flag 7412 is enabled. (It's not a bad idea to have this trace flag set as a startup parameter for SQL Server.)

You are SQL 2017, SQL 2016 SP1, or SQL 2014 SP2 or later and there is an active Extended Events session that includes the event query_thread_profile .

Finally, I like to mention that you can use my beta_lockinfo in this situation. It will return with the actual values so far when lightweight execution profiling infrastructure is enabled.

Getting Query Plans and Parameters from a Trace

Yet another alternative to get hold of the query plans is to run a trace against the application or against your connection in SSMS. There are several Showplan events you can include in a trace. The most versatile is Showplan XML Statistics Profile which gives you the same information as you see in SSMS when you enable Include Actual Execution Plan.

However, for several reasons a trace is rarely a very good alternative. To start with, enabling query-plan information in a trace adds a lot of overhead to generate that XML information. And observe that this applies even if you narrow your filter to a single spid. The way the trace engine works, all processes still have to generate the event, so this can have severe impact on a busy server.

Next, if you run the trace in Profiler, you are likely to find it very difficult to set up a good filter that captures what you want to see but hides all the noise. One possibility, once the trace has completed, is to save the trace to a table in the database, which permits you to find the interesting information through queries. (But don't ask Profiler to save to a table while the trace is running. The overhead for that is awful.) The plan is in the TextData column. Cast it to xml and then you can view it as I described in the previous section.

A slightly better alternative is to use Lee Tudor's sp_sqltrace that I mentioned earlier. It has a parameter to request that query plans should be collected, and you can opt to collect only estimated plans or actual plans. The overall performance on the server is still impacted, but at least you can find the plan you are looking for easily. However, sp_sqltrace will not work for you if you want to look at an application that is using multiple spids.

You can also use Extended Events to get hold of the query plan by capturing the event query_post_execution_showplan, but it is not any better than Trace. Even if you filter you event session for a specific spid, SQL Server activates this event for all processes, so the overall performance is affected in this case as well.

Note: Starting with SQL 2016 SP2 CU3 and SQL 2017 CU11, there is an extended event query_plan_profile which does not have this problem. However, this event only fires if the query has the hint QUERY_PLAN_PROFILE. Which of course your random slow statement in your application will not have. You can inject it with a plan guide, something I discuss later in this text. But it goes without saying that this is a horrendeously complicated approach, and I have not tried it myself.

Getting Table and Index Definitions

I assume that you are already acquainted with ways to find out how a table is defined, either with sp_help or through scripting, so I jump directly to the topic of indexes. They too can be scripted or you can use sp_helpindex. But scripting is bulky in my opinion, and sp_helpindex does not support features added in SQL 2005 or later. This query can be helpful:

DECLARE @tbl nvarchar(265) SELECT @tbl = 'Orders' SELECT o.name, i.index_id, i.name, i.type_desc, substring(ikey.cols, 3, len(ikey.cols)) AS key_cols, substring(inc.cols, 3, len(inc.cols)) AS included_cols, stats_date(o.object_id, i.index_id) AS stats_date, i.filter_definition FROM sys.objects o JOIN sys.indexes i ON i.object_id = o.object_id CROSS APPLY (SELECT ', ' + c.name + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('')) AS ikey(cols) OUTER APPLY (SELECT ', ' + c.name FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH('')) AS inc(cols) WHERE o.name = @tbl AND i.type IN (1, 2) ORDER BY o.name, i.index_id

As listed, the query will not run on SQL 2005, but just remove the final column, filter_definition, from the result set. This column applies to filtered indexes, a feature added in SQL 2008. As for the column stats_date, see the next section.

The query only lists regular relational indexes, not XML indexes or spatial indexes. Problems related to searches in XML documents or spatial columns are beyond the scope for this article anyway. Nor have I mustered the energy to support columnstore indexes, but that is left as an exercise to the reader.

Finding Information About Statistics

To view all statistics for a table, you can use this query:

DECLARE @tbl nvarchar(265) SELECT @tbl = 'Orders' SELECT o.name, s.stats_id, s.name, s.auto_created, s.user_created, substring(scols.cols, 3, len(scols.cols)) AS stat_cols, stats_date(o.object_id, s.stats_id) AS stats_date, s.filter_definition FROM sys.objects o JOIN sys.stats s ON s.object_id = o.object_id CROSS APPLY (SELECT ', ' + c.name FROM sys.stats_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE sc.object_id = s.object_id AND sc.stats_id = s.stats_id ORDER BY sc.stats_column_id FOR XML PATH('')) AS scols(cols) WHERE o.name = @tbl ORDER BY o.name, s.stats_id

As with the query for indexes, the query does not run for SQL 2005 as listed, but just remove filter_definition from the result set. auto_created refers to statistics that SQL Server creates automatically when it gets the occasion, while user_created refers to indexes created explicitly with CREATE STATISTICS. If both are 0, the statistics exists because of an index.

The column stats_date returns when the statistics most recently was updated. If the date is way back in the past, the statistics may be out of date. The root cause to parameter-sniffing-related problems is usually something else than outdated statistics, but it is always a good idea to look out for this. One thing to keep in mind is that statistics for columns with monotonically increasing data – e.g. id and date columns – quickly go out of date, because queries are often for the most recently inserted data, which is always beyond the last slot in the histogram (more about histograms later).

If you believe statistics are out of date for a table, you can use this command:

UPDATE STATISTICS tbl

This will give you sampled statistics. Often this gives you statistics that are good enough, but sometimes the sampling does not work out well. In this case, it can be worth forcing a full scan of the data. This may be best done with this command:

UPDATE STATISTICS tbl WITH FULLSCAN, INDEX

By adding INDEX to the command, the FULLSCAN update is only performed for statistics for indexes. This can reduce the execution time for UPDATE STATISTICS since for non-index statistics, UPDATE STATISTICS scans the entire table for each statistics. (Whereas for the indexes, it scans the leaf level of the index which is typically a lot smaller.)

You can also update the statistics for a single index. The syntax for this is not what you may expect:

UPDATE STATISTICS tbl indexname WITH FULLSCAN

Note: there is no period between the table name and the index name, just space.

Note that after updating statistics, you may see an immediate performance improvement in the application. This does not necessarily prove that outdated statistics was the problem. Since the updated statistics causes recompilation, the parameters may be re-sniffed and you get a better plan for this reason.

To see the distribution statistics for an index use DBCC SHOW_STATISTICS. This command takes two parameters. The first is the table name, whereas the second can be the name of an index or statistics. For instance:

DBCC SHOW_STATISTICS (Orders, OrderDate)

This displays three result sets. I will not cover all of them here, but only say that the last result set is the actual histogram for the statistics. The histogram reflects the distribution that SQL Server has recorded about the data in the table. Here is how the first few lines look in the example:

This tells us that according to the statistics there is exactly one row with OrderDate = 1996-07-04. Then there is one row in the range from 1996-07-05 to 1996-07-07 and two rows with OrderDate = 1996-07-08. (Because RANGE_ROWS is 1 and EQ_ROWS is 2.) The histogram then continues, and there is in total 188 steps for this particular statistics. There are never more than 200 steps in a histogram. For full details on the output, please see the topic for DBCC SHOW_STATISTICS in Books Online. One of the white papers listed in the section Further Reading has more valuable information about statistics and the histogram.

Note: In SQL 2005 there is a bug, so if there is a step for NULL values, DBCC SHOW_STATISTICS fails to show this row. This is a display error, and the value is still there in the histogram.

You do not typically run DBCC SHOW_STATISTICS for all statistics to have the information just in case, but only when you think that the information may be useful to you. We will look at such an example in the next chapter.

For auto-created statistics the name is something like _WA_Sys_00000003_42E1EEFE. This is somewhat cumbersome to use, so in this case DBCC SHOW_STATISTICS permits you to specify the column name instead. Note that you cannot use the column name if there are user-created statistics on the column (through CREATE INDEX or CREATE STATISTICS). The above example appears to contradict that, since there is an index on the OrderDate column. But that index is also called OrderDate!

Examples of How to Fix Parameter-Sniffing Issues

It is important to understand that parameter sniffing in itself is not a problem; au contraire, it is a feature, since without it SQL Server would have to rely on blind assumptions, which in most cases would lead to less optimal query plans. But sometimes parameter sniffing works against you. We can identify three typical situations:

The query usage is such that parameter sniffing is entirely inappropriate. That is, a plan which is good for a certain execution may be poor for the next. There is a specific pattern in the application where one group of calls is very different from the main bulk. Often this is a call the application performs on start-up or at the beginning of a new day. The index structure for one or more tables is such that there is no perfect index for the query, but there are several half-good indexes, and it is haphazard which index the optimizer chooses.

It can be difficult to say beforehand which applies to your situation, and that is why you need to make a careful analysis. In the previous section I discussed what information you need, although I did not always make it clear what for. In addition, there is one more thing I did not list but which is immensely helpful: intimate knowledge about how the application works and its usage pattern.

Since there are several possible reasons why parameter sniffing could give you a headache, this means that there is no single solution that you can apply. Rather there is a host of them, depending on where the root cause lies. In the following I will give some examples of parameter-sniffing related issues and how to address them. Some are real-life examples, others are more generic in nature. Some of the examples focus more on the analysis, others take a straight look at the solution.

A Non-Solution

Before I go into the real solutions, let me first point out that adding SET ARITHABORT ON to your procedure is not a solution. It will seem to work when you try it. But that is only because you recreated the procedure which forced a new compilation and then the next invocation sniffed the current set of parameters. SET ARITHABORT ON is only a placebo, and not even a good one. The problem will most likely come back. It will not even help you avoid the confusion with different performance in the application and SSMS, because the overall cache entry will still have ARITHABORT OFF as its plan attribute.

So, don't put SET ARITHABORT ON in your stored procedures. Overall, I strongly discourage from you using any of the SET commands that are cache keys in your code.

Best Index Depends on Input

Consider this procedure:

CREATE PROCEDURE List_orders_12 @custid nchar(5), @fromdate datetime, @todate datetime AS SELECT * FROM Orders WHERE CustomerID = @custid AND OrderDate BETWEEN @fromdate AND @todate

There is a non-clustered index on CustomerID and another one on OrderDate. Assume that the order activity among customers varies vividly. Many customers make just a handful a orders per year. But some customers are more active, and some real big guys may place several orders per day.

In the Northwind database, the most active customer is SAVEA with 31 orders, whereas CENTC has only one order. Run the below:

EXEC List_orders_12 'SAVEA', '19970811', '19970811' go sp_recompile List_orders_12 go EXEC List_orders_12 'CENTC', '19960101', '19961231'

That is, for SAVEA we only look at the orders for a single day, but for CENTC we look at the orders for an entire year. As you may sense, these two invocations are best served by different indexes. Here is the plan for the first invocation:

SQL Server here uses the index for OrderDate which is the most selective. The plan for the second invocation is different:

Here CustomerID is the most selective column, and SQL Server uses the index on CustomerID.

One solution to address this is to force recompilation every time with the RECOMPILE query hint:

CREATE PROCEDURE List_orders_12 @custid nchar(5), @fromdate datetime, @todate datetime AS SELECT * FROM Orders WHERE CustomerID = @custid AND OrderDate BETWEEN @fromdate AND @todate OPTION (RECOMPILE)

With this hint, SQL Server will compile the query every time and since it knows that the plan is not to be reused, it handles the values of the parameters and local variables as constants.

Note: This is true if you have SQL 2012 or the latest service pack of SQL 2008 and SQL 2008 R2. On SQL 2005 and early builds of SQL 2008 and SQL 2008 R2, OPTION (RECOMPILE) does not handle the variables as constants. However, in this particular example, OPTION (RECOMPILE) serves the purposes also on the earlier versions.

In many cases, forcing recompilation every time is quite alright, but there are a few situations where it is not:

The procedure is called with a very high frequency, and the compilation overhead hurts the system. The query is very complex and the compilation time has a noticeable negative impact on the respon