Benjamin Nevarez Rotating Header Image


High Performance SQL Server

High Performance SQL Server

I am very excited to announce that my new book, High Performance SQL Server, is now available on Amazon and, in this post, I include the Introduction of the book, which covers what this book is about and describes the content of each of its nine chapters.

I’ve been writing and presenting about query tuning and optimization for years. I even wrote a book on the topic called SQL Server 2014 Query Tuning and Optimization. Query tuning and optimization is extremely important to improve the performance of your databases and applications.

Equally important is having a well-designed and configured system in the first place. SQL Server default configuration can work fine for some applications, but mission critical and high-performance applications demand a thoughtful design and configuration. Well-written and tuned queries will not shine if a system is not properly configured. For example, queries will not use processor resources in an optimal way if a maximum degree of parallelism setting is not configured correctly. Database performance will suffer if a database is using the default file auto-growth settings or if the storage is not properly configured. A misconfigured  tempdb  may show contention on many busy systems. Even the query optimizer will struggle with a bad database design or badly written queries. These are just some common configuration problems out there in real production systems.

In addition, even when a well-designed application goes to production, performance tuning does not end there. Monitoring and troubleshooting are an extremely important part of an application and database life cycle since performance problems eventually will arise. Workloads may change, hopefully for the better (for example, an application having to deal with an unexpected increase on business transactions). Sometimes those changes will require a redesign, changes, and perhaps new configurations.

So this is, in fact, an iterative process, consisting of design and configuration, followed by implementation, monitoring, and troubleshooting, which again may lead to new designs or configurations, monitoring, and so on. In addition, collecting performance data, creating a baseline, and performing trend analysis is an important part of a production implementation, not only to troubleshoot problems but also to anticipate issues or understand future growth and additional requirements. It is essential to estimate and trend those changes proactively instead of waking up to a system suddenly having trouble in handling changing workloads or, even worse, to face a downtime that could have been avoided. There are several tools to help with this, and the great news is that SQL Server 2016 offers a very promising one called the Query Store.

I spent a good part of my daily job working on all these items so I decided to write a book about them. I wanted to cover everything you need to know about performance in SQL Server that does not require you to know about query tuning, work with execution plans, or “fight” the query optimizer. There are so many areas to cover and more are being added as new features and technologies appear on SQL Server such as In-Memory OLTP, columnstore indexes, and the aforementioned Query Store.

This book covers all currently supported versions of SQL Server with a major focus on SQL Server 2016. Although this is a performance book from the practical point of view, SQL Server internals are very important too. The best way to troubleshoot something is to know how it works and why things happen. Consequently, I focus on internals when required.

Finally, this book complements my query tuning and optimization book. If you are a database developer or a SQL Server professional who cares about query performance, you could benefit from both books. If you are a database administrator, a database architect, or even a system administrator, and you want to improve the performance of your system without query tuning, you can read only this book.

As mentioned earlier, understanding SQL Server internals is important to better optimize a system and troubleshoot database problems, so this book starts explaining how the SQL Server database engine works and covers everything happening in the system from the moment a connection is made to a database until a query is executed and the results are returned to the client. Chapter 1 includes such topics such as the Tabular Data Stream (TDS) and network protocols used by SQL Server, SQLOS, and the work performed by the SQL Server relational engine, focusing on query processing and the most common query operators.

Waits happen in an SQL Server instance all the time. Chapter 2 introduces the waits performance methodology, which can be used to troubleshoot performance problems, especially when other methods are not able to pinpoint a performance issue.

Chapter 3 covers the Query Store, a very promising query performance feature introduced with SQL Server 2016. The Query Store can help you to collect query and plan information along with their runtime statistics, which you can use to easily identify query-performance-related problems and even force an existing execution plan. The chapter closes by mentioning some related new features such as the Live Query Statistics and the SQL Server Management Studio plan comparison tool.

Chapter 4 explains a number of instance level configuration settings that can greatly impact the performance of your SQL Server implementation. As an interesting fact, it shows how some trace flags originally introduced to solve a particular problem are now implemented as SQL Server configuration defaults.

Chapter 5 covers  tempdb  configuration, which is especially important as such a database is shared between all the user and system databases on a SQL Server instance. Focus on the chapter is given to  tempdb  latch contention of allocation pages and  tempdb  disk spilling, a performance issue that occurs when not enough memory is available for some query processor operations.

In-memory technologies are introduced in Chapter 6, including In-Memory OLTP, which in SQL Server 2016 sees its second release, and columnstore indexes, now on its third mayor implementation. Both features suffered severe limitations with their original release so this chapter covers how these technologies work and what their current improvements are. The chapter ends with Operational Analytics, which combines both technologies to allow analytical queries to be executed in real-time in an OLTP system. In-memory technologies promise to be the future in relational database technologies.

Chapter 7 shows how proactively collecting and persisting performance information could be extremely beneficial to understand how a specific system works, to create a baseline, and to understand when performance is deviating from a desirable or expected behavior. The chapter also covers the most critical performance counters, dynamic management objects, and events, along with some of the tools used to display and collect such data.

Indexing, a required topic for database performance, is covered in Chapter 8. It explains how indexes work and why they are important on both OLTP and Data Warehouse environments. The chapter provides emphasis on using SQL Server tools to help create indexes such as the missing indexes feature and the more sophisticated Database Engine Tuning Advisor.

Finally, SQL Server storage is explained in Chapter 9. Disk has traditionally been the slowest part of a database system, but newer technologies such as flash-based storage offer great performance improvements and are becoming a de facto enterprise standard as their cost continues to decline. The chapter also indicates that storage optimization is not only about using the fastest disk possible but also minimizing its usage by implementing the methods covered in several chapters of the book, such as proper indexing or some query tuning techniques.

The SQL Server Query Store

Have you ever found a plan regression after a SQL Server upgrade and wanted to know what the previous execution plan was? Have you ever had a query performance problem due to the fact that a query unexpectedly got a new execution plan? At the last PASS Summit, Conor Cunningham uncovered a new SQL Server feature, which can be helpful in solving performance problems related to these and other changes in execution plans.

This feature, called the Query Store, can help you with performance problems related to plan changes and will be available soon on SQL Azure and later on the next version of SQL Server. Although it is expected to be available on the Enterprise Edition of SQL Server, it is not yet known if it will be available on Standard or any other editions. To understand the benefits of the Query Store, let me talk briefly about the query troubleshooting process.

Why is a Query Slow?

Once you have detected that a performance problem is because a query is slow, the next step is to find out why. Obviously not every problem is related to plan changes. There could be multiple reasons why a query that has been performing well is suddenly slow. Sometimes this could be related to blocking or a problem with other system resources. Something else may have changed but the challenge may be to find out what. Many times we don’t have a baseline about system resource usage, query execution statistics or performance history. And usually we have no idea what the old plan was. It may be the case that some change, for example, data, schema or query parameters, made the query processor produce a new plan.

Plan Changes

At the session, Conor used the Picasso Database Query Optimizer Visualizer tool, although didn’t mention it by name, to show why the plans in the same query changed, and explained the fact that different plans could be selected for the same query based on the selectivity of their predicates. He even mentioned that the query optimizer team uses this tool, which was developed by the Indian Institute of Science. An example of the visualization:

Picasso Database Query Optimizer Visualizer

Picasso Database Query Optimizer Visualizer

Each color in the diagram is a different plan, and each plan is selected based on the selectivity of the predicates. An important fact is when a boundary is crossed in the graph and a different plan is selected, most of the times the cost and performance of both plans should be similar, as the selectivity or estimated number of rows only changed slightly. This could happen for example when a new row is added to a table which qualifies for the used predicate. However, in some cases, mostly due to limitations in the query optimizer cost model in which it is not able to model something correctly, the new plan can have a large performance difference compared to the previous one, creating a problem for your application. By the way, the plans shown on the diagram are the final plan selected by the query optimizer, don’t confuse this with the many alternatives the optimizer has to consider to select only one.

An important fact, in my opinion, which Conor didn’t cover directly, was the change of plans due to regressions after changes on cumulative updates (CUs), service packs, or version upgrades. A major concern that comes to mind with changes inside the query optimizer is plan regressions. The fear of plan regressions has been considered the biggest obstacle to query optimizer improvements. Regressions are problems introduced after a fix has been applied to the query optimizer, and sometimes referred as the classic “two or more wrongs make a right.” This can happen when, for example, two bad estimations, one overestimating a value and the second one underestimating it, cancel each other out, luckily giving a good estimate. Correcting only one of these values may now lead to a bad estimation which may negatively impact the choice of plan selection, causing a regression.

What Does the Query Store Do?

Conor mentioned the Query Store performs and can help with the following:

  1. Store the history of query plans in the system;
  2. Capture the performance of each query plan over time;
  3. Identify queries that have “gotten slower recently”;
  4. Allow you to force plans quickly; and,
  5. Make sure this works across server restarts, upgrades, and query recompiles.

So this feature not only stores the plans and related query performance information, but can also help you to easily force an old query plan, which in many cases can solve a performance problem.

How to Use the Query Store

You need to enable the Query Store by using the ALTER DATABASE CURRENT SET QUERY_STORE = ON; statement. I tried it in my current SQL Azure subscription, but the statement returned an error as it seems that the feature is not available yet. I contacted Conor and he told me that the feature will be available soon.

Once the Query Store is enabled, it will start collecting the plans and query performance data and you can analyze that data by looking at the Query Store tables. I can currently see those tables on SQL Azure but, since I was not able to enable the Query Store, the catalogs returned no data.

You can analyze the information collected either proactively to understand the query performance changes in your application, or retroactively in case you have a performance problem. Once you identify the problem you can use traditional query tuning techniques to try to fix the problem, or you can use the sp_query_store_force_plan stored procedure to force a previous plan. The plan has to be captured in the Query Store to be forced, which obviously means it is a valid plan (at least when it was collected; more on that later) and it was generated by the query optimizer before. To force a plan you need the plan_id, available in the sys.query_store_plan catalog. Once you look at the different metrics stored, which are very similar to what is stored for example in sys.dm_exec_query_stats, you can make the decision to optimize for a specific metric, like CPU, I/O, etc. Then you can simply use a statement like this:

EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 1;

This is telling SQL Server to force plan 1 on query 1. Technically you could do the same thing using a plan guide, but it would be more complicated and you would have to manually collect and find the required plan in the first place.

How Does the Query Store Work?

Actually forcing a plan uses plan guides in the background. Conor mentioned that “when you compile a query, we implicitly add a USE PLAN hint with the fragment of the XML plan associated with that statement.” So you no longer need to use a plan guide anymore. Also keep in mind that, same as using a plan guide, it is not guaranteed to have exactly the forced plan but at least something similar to it. For a reminder of how plan guides work take a look at this article. In addition, you should be aware that there are some cases where forcing a plan does not work, a typical example being when the schema has changed, i.e. if a stored plan uses an index but the index no longer exists. In this case SQL Server can not force the plan, will perform a normal optimization and it will record the fact that the forcing the plan operation failed in the sys.query_store_plan catalog.


Every time SQL Server compiles or executes a query, a message is sent to the Query Store. This is shown next.


Query Store Workflow Overview

The compile and execution information is kept in memory first and then saved to disk, depending on the Query Store configuration (the data is aggregated according to the INTERVAL_LENGTH_MINUTES parameter, which defaults to one hour, and flushed to disk according to the DATA_FLUSH_INTERVAL_SECONDS parameter). The data can also be flushed to disk if there is memory pressure on the system. In any case you will be able to access all of the data, both in memory and disk, when you run the sys.query_store_runtime_stats catalog.


The collected data is persisted on disk and stored in the user database where the Query Store is enabled (and settings are stored in sys.database_query_store_options. The Query Store catalogs are:

sys.query_store_query_text Query text information
sys.query_store_query Query text plus the used plan affecting SET options
sys.query_store_plan Execution plans, including history
sys.query_store_runtime_stats Query runtime statistics
sys.query_store_runtime_stats_interval Start and end time for intervals
sys.query_context_settings Query context settings information

Query Store views

Runtime statistics capture a whole slew of metrics, including the average, last, min, max, and standard deviation. Here is the full set of columns for sys.query_store_runtime_stats:

runtime_stats_id plan_id runtime_stats_interval_id
execution_type execution_type_desc first_execution_time last_execution_time count_executions
avg_duration last_duration min_duration max_duration stdev_duration
avg_cpu_time last_cpu_time min_cpu_time max_cpu_time stdev_cpu_time
avg_logical_io_reads last_logical_io_reads min_logical_io_reads max_logical_io_reads stdev_logical_io_reads
avg_logical_io_writes last_logical_io_writes min_logical_io_writes max_logical_io_writes stdev_logical_io_writes
avg_physical_io_reads last_physical_io_reads min_physical_io_reads max_physical_io_reads stdev_physical_io_reads
avg_clr_time last_clr_time min_clr_time max_clr_time stdev_clr_time
avg_dop last_dop min_dop max_dop stdev_dop
avg_query_max_used_memory last_query_max_used_memory min_query_max_used_memory max_query_max_used_memory stdev_query_max_used_memory
avg_rowcount last_rowcount min_rowcount max_rowcount stdev_rowcount

Columns in sys.query_store_runtime_stats

This data is only captured when query execution ends. The Query Store also considers the query’s SET options, which can impact the choice of an execution plan, as they affect things like the results of evaluating constant expressions during the optimization process. I cover this topic in a previous post.


This will definitely be a great feature and something I’d like to try as soon as possible (by the way, Conor’s demo shows “SQL Server 15 CTP1” but those bits are not publicly available). The Query Store can be useful for upgrades which could be a CU, service pack, or SQL Server version, as you can analyze the information collected by the Query Store before and after to see if any query has regressed. (And if the feature is available in lower editions, you could even do this in a SKU upgrade scenario.) Knowing this can help you to take some specific action depending on the problem, and one of those solutions could be to force the previous plan as explained before.

New Book: SQL Server 2014 Query Tuning & Optimization



It looks like my blog has been very quiet for a while once again. Yes, I’ve been busy working on some other SQL Server projects since last year. One of those projects was working on Kalen Delaney’s new book SQL Server 2012 Internals where I co-authored two chapters, “Special Databases” and “Query Execution”, which were originally written for previous versions of the book by Kalen and Craig Freedman, respectively. The book was finally released last November.

As soon as I finished my part on Kalen’s book, I immediately started working on my new book, SQL Server 2014 Query Tuning & Optimization, which has been available for pre-order at Amazon for a while now. This book covers everything you need to know about query tuning and optimization for SQL Server to help you design high-performing database applications. As a SQL Server 2014 book it obviously covers new features like Hekaton, the new cardinality estimator, and incremental statistics, among others, but most of the content can be used in previous versions of SQL Server as well. I am currently working on the last items of the book with the great team at McGraw-Hill Education and the help of the amazing Dave Ballantyne (@davebally) as technical reviewer and the book should be available soon.

Also, at the last PASS Summit in Charlotte I was invited by SQL Sentry to blog at their site, which has great content by some of the most amazing bloggers in the SQL Server community. Although I haven’t had much time to blog there as I would wanted, I have started with a few SQL Server 2014 articles that you can read here.

Finally, I have submitted sessions for SQLBits (first time ever submitted) and to the PASS Summit (which I have attended for 11 years so far), and just at the moment I am writing this I got the great news that I got two sessions selected for SQLBits so I am really looking forward to finally attend this conference in the UK this July. I am also planning to attend some other SQL Server events in the US, including our always amazing local SQLSaturdays in Orange County this April 26th, Sacramento on July 12th and San Diego, which is usually scheduled for September. Hope to see you at any of these SQL Server events.

Parameter Sniffing and Plan-reuse-affecting SET Options

One interesting problem I am asked to troubleshoot sometimes is when a developer tells me that a stored procedure is timing out or taking too long to execute on a web application but returning immediately when executed directly in Management Studio. Even for the same parameters. Although there could be a few reasons for a problem like this to happen, including blocking, the most frequent one is related to a behavior in which the plan used by the web application was optimized using a combination of parameters which produced a “bad” plan for some other executions of the same stored procedure with different parameters. Although you may be tempted to just run sp_recompile to force a new optimization and allow the application to continue working, this does not really fix the problem and it may eventually come back. You could have also seen some similar scenarios where you have updated statistics, rebuild an index or changed something else to find out that suddenly the problem seems to be fixed. It is not. Those changes probably just forced a new optimization with the “good” parameter you were just testing. Obviously the best thing to do for this kind of problem is capturing the “bad” plan for further analysis in order to provide a permanent solution. In this post I will show you how to do that.

But first, a little bit of background. Remember that in general query optimization is an expensive operation and, in order to avoid this optimization cost, the plan cache will try to keep the generated execution plans in memory so they can be reused. So, if the stored procedure is executed thousands of times, only one optimization is needed. However, if a new connection running the same stored procedure has different SET options it may generate a new plan instead of reusing the one already on the plan cache. This new plan can be reused by later executions of the same stored procedure with the same connection settings. A new plan is needed as these SET options can impact the choice of an execution plan because they affect the results of evaluating constant expressions during the optimization process (a process known as constant folding and explained here). Another connection setting, FORCEPLAN, acts in a similar way to a hint, requesting the Query Optimizer both to preserve the join order as specified on the query syntax and to use nested loop joins only. As indicated in the Microsoft white paper Plan Caching in SQL Server 2008, the following SET options will affect the reuse of execution plans.


Unfortunately different management or development tools, like Management Studio, ADO.NET, or even sqlcmd, may have different SET options in their default configuration. You will find that mostly the problem is that one of the options, ARITHABORT, is OFF in ADO.NET and ON in Management Studio. So it may be possible that, in our example, Management Studio and the web application are using distinct cached plans and that the web application initially got a good plan for the parameters used during the optimization, but this plan is not good for some other executions of the same stored procedure with different parameters.

But now let us see how to prove that parameter sniffing is in fact the problem for your specific instance of the issue, and how to extract the plans to inspect both the parameters and the SET options used during optimization. Since AdventureWorks does not have the default SET options of a new database, let us create our own and copy some data from AdventureWorks


Create a new table and a stored procedure to test

USE Test
SELECT * INTO dbo.SalesOrderDetail
FROM AdventureWorks.Sales.SalesOrderDetail
ON dbo.SalesOrderDetail(ProductID)
CREATE PROCEDURE test (@pid int)
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @pid

Let us test two different applications, executing the stored procedure from a .NET application (C# code included at the end) and from Management Studio. For the purpose of this test we want to assume that a plan with a table scan is a bad plan and a plan using an index seek/RID lookup is the optimal one.

Start with a clean plan cache by running


Run the .NET application from a command prompt window and provide the value 870 as a parameter (note that this application is only running the test stored procedure)

Enter ProductID: 870

At this moment we can start inspecting the plan cache to see the plans available in memory. Run the following script from the Test database (we will be running this script again later during this exercise)

SELECT plan_handle, usecounts, pvt.set_options
    SELECT plan_handle, usecounts, epa.attribute, epa.value 
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
where pvt.objectid = object_id('dbo.test')

You should get an output similar to this

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    1            251

The output shows that we have one execution plan in the plan cache, it has been used once (as indicated by the usecounts value), and the set_options value, taken from the sys.dm_exec_plan_attributes DMF, is 251. Since this was the first execution of the stored procedure, it was optimized using the parameter 870 which in this case created a plan using a table scan (consider here a “bad” plan). Now run the application again using a parameter that returns only a few records and will benefit from an index seek/RID lookup plan:

Enter ProductID: 898

If you inspect the plan cache again you will notice that the plan has been used twice and unfortunately this time it was not good for the second parameter used

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    2            251

At this moment the developer may try to troubleshoot this problem by running the stored procedure in Management Studio using something like this

EXEC test @pid = 898

Now the developer is surprised to find that SQL Server is returning a good execution plan and the query is returning immediately. Inspecting the plan cache again will show something similar to this

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    2            251
0x05000700210F0207B860650B000000000000000000000000    1            4347

You can see that a new plan was added for the Management Studio execution, with a different value for set_options.

What to do next? It is time to inspect the plans and look at the SET options and parameters used during the optimization. Select the plan_handle of the first plan created (the one with set_options 251 in your own example) and use it to run the following query

select * from sys.dm_exec_query_plan

You can find the SET options at the beginning of the plan

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" 

And the used parameters at the end

    <ColumnReference Column="@pid" ParameterCompiledValue="(870)" />

Do the same for the second plan and you will get the following information for the SET options

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" 

and the following parameter information

    <ColumnReference Column="@pid" ParameterCompiledValue="(898)" />

This information shows that the ARITHABORT SET option has different value on these plans and that the parameter used to optimize the query on the web application was 870. (The same information is available from the Properties window of a graphical plan). You can also verify the operators used in the plan, the first one using a table scan and the second one an index seek/RID lookup combination.

Now that you have captured the plans you can force a new optimization so the application can use a better plan immediately (keeping in mind that this is not a permanent solution). Try this

sp_recompile test

So now you know that you have a problem related to parameter sniffing. What to do next? I have a few recommendations on previous posts here and here. I have another one here but usually you should not be doing this.

Finally, you can use the following script to display SET options for a specific set_options value

declare @set_options int = 251
if ((1 & @set_options) = 1) print 'ANSI_PADDING'
if ((4 & @set_options) = 4) print 'FORCEPLAN'
if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'
if ((16 & @set_options) = 16) print 'ANSI_WARNINGS'
if ((32 & @set_options) = 32) print 'ANSI_NULLS'
if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'
if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'
if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'
if ((512 & @set_options) = 512) print 'NoBrowseTable'
if ((4096 & @set_options) = 4096) print 'ARITH_ABORT'
if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'
if ((16384 & @set_options) = 16384) print 'DATEFIRST'
if ((32768 & @set_options) = 32768) print 'DATEFORMAT'
if ((65536 & @set_options) = 65536) print 'LanguageID'

C# Code

using System;
using System.Data;
using System.Data.SqlClient;

class Test
    static void Main()
        SqlConnection cnn = null;
        SqlDataReader reader = null;
            Console.Write("Enter ProductID: ");
            string pid = Console.ReadLine();

            cnn = new SqlConnection("Data Source=(local);Initial Catalog=Test;
                Integrated Security=SSPI");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cnn;
            cmd.CommandText = "dbo.test";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@pid", SqlDbType.Int).Value = pid;
            reader = cmd.ExecuteReader();
            while (reader.Read())
        catch (Exception e)
            throw e;
            if (cnn != null)
                if (cnn.State != ConnectionState.Closed)

Disabling Parameter Sniffing?

As I mentioned in a previous post, parameter sniffing is a good thing: it allows you to get an execution plan tailored to the current parameters of your query. Of course, sometimes it can also be a problem but there are some solutions available. Some of these solutions are covered in my posts here and here.

However, Microsoft recently released a cumulative update which provides a trace flag to disable parameter sniffing at the instance level. This cumulative update is available for the latest versions of SQL Server as described on the knowledge base article 980653.

Basically this trace flag, 4136, has the effect of disabling the use of histograms, a behavior similar to the use of the OPTIMIZE FOR UNKNOWN hint. There are still three cases where this trace flag has no effect, as described in the previous knowledge base article, which are on queries using the OPTIMIZE FOR or RECOMPILE query hints and on stored procedures using the WITH RECOMPILE option.

In general I would not recommend using this trace flag and would ask you to try the other solutions available instead. But anyway, it is good to know that this choice exists and can be used in cases when you really need it. It should be used carefully and only when enough testing shows that in fact it improves the performance of your application.

But let us test it to see how it works. I am testing it with SQL Server 2008 R2. My original build is 10.50.1600. After the cumulative update is installed the build is 10.50.1720.

Let us use the same example described on my OPTIMIZE FOR UNKNOWN post so perhaps you want to refer to it to better understand the details. Create the following stored procedure on the AdventureWorks database.

CREATE PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

Executing the stored procedure before the cumulative update, or after the cumulative update but without using the flag

EXEC test @pid = 709

shows the following plan


In this case, since the trace flag is not yet in effect, SQL Server uses the statistics histogram to estimate the number of rows which in this case is 188. After I enable the trace flag, restart my SQL Server instance, and run the same stored procedure again I got the following plan where the estimated number of rows is now 456.079. Again, how these values were obtained was explained in my previous post.


Let us try a test using the OPTIMIZE FOR query hint, which ignores the 4136 trace flag (note that it is not the same as the OPTIMIZE FOR UNKNOWN hint) by using the following code.

ALTER PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION (OPTIMIZE FOR (@pid = 709))

If you try this version of the stored procedure, even with the trace flag enabled, it will use the histogram again and will create a plan using the estimated number of rows of 188.

Finally, if you followed this exercise, do not forget to remove the trace flag and restart your SQL Server service.

Rebuilding Indexes vs. Updating Statistics

One of the questions I was asked recently while speaking at the Los Angeles SQL Server Professionals Group and the Orange County SQL Server User Group (*) was regarding the order in which jobs like rebuilding indexes or updating statistics should be executed as part of the database maintenance activities. In general you should consider the following important points, focusing in the fact that there are two kinds of statistics: index and column statistics.

1) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option of this statement will update column statistics only. Using the INDEX option will update index statistics only.

2) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

3) Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement, will update only index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update any column statistics.

4) Reorganizing an index, for example using the ALTER INDEX … REORGANIZE statement, does not update any statistics.

So depending on your maintenance jobs and scripts several scenarios can exist. The simplest scenario is if you want to rebuild all the indexes and update all the statistics. As mentioned before, if you rebuild all your indexes then all the index statistics will also be updated by scanning all the rows on the table. Then you just need to update your column statistics by running an UPDATE STATISTICS WITH FULLSCAN, COLUMNS statement. Since the first job only updates index statistics and the second one only updates column statistics, it does not matter which one you execute first.

Some other more complicated scenarios include when you have jobs which rebuild your indexes based on their fragmentation level. Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time only with a default and smaller sample. Not only you are updating your index statistics twice but you are overwriting the better of the two choices.

Let me show you how these commands work with some examples using the AdventureWorks database. Create a new table dbo.SalesOrderDetail

SELECT * INTO dbo.SalesOrderDetail

FROM sales.SalesOrderDetail

The next query uses the sys.stats catalog view and shows that initially there are no statistics objects for the new table.

SELECT name, auto_created, stats_date(object_id, stats_id) AS update_date FROM sys.stats

WHERE object_id = object_id(‘dbo.SalesOrderDetail’)

Use this query using the sys.stats catalog again when asked to inspect the status of the statistics after each of the following commands. Now run the following query

SELECT * FROM dbo.SalesOrderDetail

WHERE SalesOrderID = 43670 AND OrderQty = 1

Use the previous sys.stats query to verify that two statistics objects were created, one for the SalesOrderID column and another one for the OrderQty column (they both have names starting with _WA_Sys as shown later). Run the following statement to create an index on the ProductID column.

CREATE INDEX ix_ProductID ON dbo.SalesOrderDetail(ProductID)

Run again the query to verify that a new statistics object for the ProductID column has been created. Notice the value of the auto_created column which tells if the statistics were automatically created by the query processor.

name                          auto_created      update_date

_WA_Sys_00000004_7F80E8EA     1                 2010-07-01 23:27:45.953

_WA_Sys_00000001_7F80E8EA     1                 2010-07-01 23:27:46.117

ix_ProductID                  0                 2010-07-01 23:29:41.110

Run the next statement to update the column statistics only. You can validate that only the column statistics were updated by looking at the update_date column which uses the STATS_DATE function to display the last date the statistics were updated.


name                          auto_created      update_date

_WA_Sys_00000004_7F80E8EA     1                 2010-07-01 23:34:13.753

_WA_Sys_00000001_7F80E8EA     1                 2010-07-01 23:34:13.850

ix_ProductID                  0                 2010-07-01 23:29:41.110

This statement will do the same for the index statistics


The next two statements will update both index and column statistics



See how an index rebuild only updates index statistics

ALTER INDEX ix_ProductID ON dbo.SalesOrderDetail REBUILD

Here you can verify that reorganizing an index does not update any statistics

ALTER INDEX ix_ProductID ON dbo.SalesOrderDetail REORGANIZE

Finally, remove the table you have just created

DROP TABLE dbo.SalesOrderDetail

* Updated from an article originally written in October, 2009


One of the questions I have been asked several times is about how OPTIMIZE FOR UNKNOWN works. OPTIMIZE FOR is a query hint introduced with SQL Server 2005 that can help with the parameter sniffing problem and requires from you to specify a value for a parameter. For an introduction to the parameter sniffing problem you can see my previous post here. On the other hand, OPTIMIZE FOR UNKNOWN, which was introduced in SQL Server 2008, does not require from you to specify a value for a parameter.

A traditional way to avoid the parameter sniffing problem, especially in previous versions of SQL Server, was by using local variables. But entirely avoiding parameter sniffing does not mean that it is always a good solution. As I mentioned in my previous article, from the point of view of query optimization, parameter sniffing is a good thing. When the Query Optimizer knows the value of a parameter it can use the statistics histogram to estimate the number of records that can be returned by a query. Using the histogram will give you the best estimate possible. But when you use local variables SQL Server is not able to use the histogram anymore. Instead it uses the information on the density vector of the statistics object. OPTIMIZE FOR UNKNOWN works pretty much in the same way.

To better understand how OPTIMIZE FOR UNKNOWN works let us first see the case when a parameter value is known. Create the following stored procedure

CREATE PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

Running this stored procedure and requesting a plan shows 188 estimated records which can be seen on the following execution plan which uses both an index seek and a key lookup operators.

EXEC test @pid = 709


In this case SQL Server is able to use the histogram and estimate that 188 records would be returned. The Query Optimizer uses that estimate to take a decision about the plan to generate. Use the following statement to inspect the statistics object used by this stored procedure.

DBCC SHOW_STATISTICS(‘Sales.SalesOrderDetail’, IX_SalesOrderDetail_ProductID)

Running the statement shows the following information (for space limitations, only the information needed for this post is displayed, including the first line of the density vector and the first three lines of the histogram).

All density   Average Length Columns

0.003759399   4              ProductID



707          0             3083          0                   

708          0             3007          0                   

709          0             188           0                   

In this case SQL Server used the histogram to find the value of ProductID 709, defined as RANGE_HI_KEY, and finds the estimated number of rows 188, defined as EQ_ROWS.

Let us now change the stored procedure to use local variables

ALTER PROCEDURE test (@pid int)


DECLARE @lpid int

SET @lpid = @pid

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @lpid

Run the procedure again using the same value

EXEC test @pid = 709

This time we get a different plan, using a clustered index scan, as shown next


Local variables are not known at optimization time so the Query Optimizer is not able to use the value 709 for the optimization, as it did before. Actually, this time it does not matter which value you use, you always will get the same estimated number of rows and the same plan. The estimated number of rows is 456.079.

Now use the version of the stored procedure with OPTIMIZE FOR UNKNOWN

ALTER PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid


You will notice that it is behaving the same as with local variables, it is getting an estimated number of rows of 456.079 and getting the same plan, using a clustered index scan.

But now let us see how SQL Server is obtaining the value 456.079 and what the reasoning behind this is.

Density is defined as 1 / number of distinct values. The SalesOrderDetail table has 266 distinct values for ProductID, so the density is calculated as 1 / 266 or 0.003759399 as shown before on the statistics object. One assumption in the statistics mathematical model used by SQL Server is the uniformity assumption. Since in this case SQL Server can not use the histogram, the uniformity assumption tells that for any given value the data distribution is the same. To obtain the estimated number of records SQL Server will multiply the density by the current total number of records, 0.003759399 * 121,317 or 456.079, as shown on the plan. This is also the same as to divide the total number of records by the number of distinct values, 121,317 / 266, which also gives 456.079.

Finally, I would conclude saying that the benefit of using OPTIMIZE FOR UNKNOWN is that you always get the same execution plan, just make sure that the chosen plan benefits most of the instances of your query.

The Parameter Sniffing Problem

SQL Server can use the histogram of a statistics object to estimate the cardinality of a predicate and use this information to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing: getting an execution plan tailored to the current parameters of a query improves the performance of your applications. We also know that the plan cache can store these execution plans so they can be reused the next time the same query needs to be executed again. This saves optimization time and CPU resources as the query does not need to be compiled again.

However, although the Query Optimizer and the plan cache work fine together most of the times, occasionally some performance problems can appear. Since the Query Optimizer can produce more than one execution plan for the same query, depending on the value of its parameters, caching and reusing only one of these plans may be a performance issue for an instance of this query that could benefit from a better plan. This is a known problem with queries using explicit parameterization which is for example the case of the stored procedures. Let me show you an example of what the problem is and a few recommendations on how to fix it. Create the following stored procedure on the AdventureWorks database

CREATE PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

Run the following statement to execute the stored procedure and request to display its execution plan

EXEC test @pid = 897

The Query Optimizer estimates that only a few records will be returned by this query and produces the following execution plan which uses an index seek operator, to quickly find the records using an existing nonclustered index; and a key lookup operator, to search on the base table for the remaining columns requested on the query.


This index seek-key lookup combination is a good choice in this case because the query is highly selective. Let us run the same stored procedure again with a different ProductID value. For the following test, include a SET STATISTICS ION statement to display the amount of disk activity generated by the query



EXEC test @pid = 870


On this second execution SQL Server is performing more than 14,000 reads when the SalesOrderDetail table has only around 1,240 pages. It is using more than 10 times more I/Os than simply scanning the entire table. So why is the second execution performing poorly? When SQL Server was executed the second time, it was using the same execution plan that was generated for the first execution. And for this particular case, the plan generated was optimal for the parameter used in the first query but not for the one on the second query. Performing both an index seek and a key lookup, which are together called a bookmark lookup, is an expensive operation which can have performance benefits but only for a limited number of records.

Now clear the plan cache to remove the current execution plan from memory and run the stored procedure again using the same previous parameter (note that there are better ways to remove a plan from the plan cache so use this statement only on a test environment).



EXEC test @pid = 870


Since this time there was no available plan for this stored procedure on the plan cache, SQL Server optimized it using the ProductID 870 and created a new optimal execution plan for this value. The new plan will use a clustered index scan as shown next.


If you look at the I/O information you will notice that this time it is using only around 1,240 pages, which is the total number of pages on the table. This is a lot smaller compared with the more than 14,000 pages used in the previous execution with the same parameter.

Of course this does not mean that it is something wrong with using stored procedures. This is a problem that you need to be aware of and research especially if you have queries where performance changes dramatically when different parameters are introduced. If you happen to have this problem there are a few choices available.


Optimize for a Typical Parameter

There might be cases when most of the executions of a query can benefit of the same execution plan and you want to minimize the cost of optimizations. For these cases you can use the OPTIMIZE FOR hint, which was introduced with SQL Server 2005. Use this hint when an optimal plan can be generated for the majority of the possible values. Only the few executions using an atypical parameter will not have an optimal plan.

Suppose that almost all of the executions of our stored procedure would benefit from the plan that uses both an index seek and a key lookup operators. You could write the stored procedure this way

ALTER PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION (OPTIMIZE FOR (@pid = 897))

When you run the stored procedure for the first time, it will be optimized for the value 897, no matter what parameter value was specified for the execution. The plan will be stored on the plan cache and used for every execution of the stored procedure after that. Test the following case

EXEC test @pid = 870

You can find the following entry close to the end in the XML plan which shows that the value 897 was in fact used during the optimization


<ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(870)" />


The benefit of using this hint is that you have total control on which plan is stored on the plan cache and reused later. On the original problem you did not have control of which of the two execution plans would be stored on the plan cache.


Optimize on Every Execution

If you can not find a typical parameter o plan for your queries you can optimize for every execution. You will get an optimal plan on every execution but will pay for the optimization cost. To do this use the RECOMPILE hint as shown here.

ALTER PROCEDURE test (@pid int)


SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid


Run the store procedure using

EXEC test @pid = 897

The XML plan for this execution will show


<ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(897)" />


This time the query will be optimized on each execution and will get an optimal plan depending on current value of its parameters.

Some other solution that has been traditionally implemented, especially in older versions of SQL Server, is to simply avoid the parameter sniffing behavior by the use of local variables. Next, I will explain the pros and cons of using local variables along with the OPTIMIZE FOR UNKNOWN hint, which is new in SQL Server 2008. But since I need to go to bed now so I can wake up very early in the morning to see the first game of the 2010 World Cup, I will leave these topics for my next post. By the way, who is your favorite team?