THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

  • The Query Optimizer and Parameter Sniffing


    As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query 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 may produce different execution plans for the same query, depending on its parameters, caching and reusing only one of these plans may be a performance issue for some other instance of this query that could benefit from a better plan.


    This is a known problem with queries using explicit parameterization like in, for example, stored procedures. So let us see an example of what the problem is and a few recommendations on how to fix it.


    1) The problem


    Let us write a simple stored procedure using the Sales.SalesOrderDetail table on the AdventureWorks database


    create procedure test (@pid int)


    select * from Sales.SalesOrderDetail

    where ProductID = @pid

    Run this to display the amount of disk activity generated by the query


    set statistics io on

    and execute the stored procedure


    exec test @pid = 897

    The Query Optimizer estimates that only a few records will be returned by this query and decides to use an existing index, so if you look at the actual execution plan you will see an Index Seek and a Key Lookup operators. The I/O information will be similar to this


    logical reads 10, physical reads 0, read-ahead reads 0


    Now clear the plan cache to remove this plan and run the stored procedure again using a new parameter (Note: Be careful not to clear the plan cache of a production environment)


    dbcc freeproccache

    exec test @pid = 870

    This time you will get 4,688 rows and the execution plan will show a Clustered Index Scan. The I/O information will be similar to this


    logical reads 1240, physical reads 0, read-ahead reads 0


    In this example each execution created its own optimal execution plan. Now see what happen when the plan cache is not cleared before the second execution so they both use the same cached plan


    dbcc freeproccache

    exec test @pid = 870

    exec test @pid = 897

    This time the Query Optimizer will compile the first execution of the stored procedure and will create an optimal execution plan for the value 870. This will use a Clustered Index Scan and around 1,240 logical reads. Since this plan is cached, it will also be used for the second execution, using the value 897 and it will also show a Clustered Index Scan and around 1,240 logical reads. This second execution is using 124 times more reads than its optimal plan, as shown previously.


    You can try the other combination as well, clearing the plan cache, and running these two executions of the stored procedure, but this time using the value 897 on the first one and 870 on the second one.


    2) Optimize for a typical parameter


    There might be cases when most of the executions of a stored procedure use the same execution plan and/or you want to avoid the optimization cost. For these cases you can use the OPTIMIZE FOR hint. Use this hint when an optimal plan is generated for the majority of values used for the parameter. 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 previous plan using an Index Seek and a Key Lookup.  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 it will be optimized for the value 897, no matter what parameter value was specified for the execution. Test the following case


    exec test @pid = 870

    You can find this in the XML plan



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


    This clearly shows which value was used during optimization and which one during execution. In this case the stored procedure is optimized only once, and the plan is stored on the plan cache and reused as many times as needed. The benefit of using this hint is that you have total control on which plan is stored on the plan cache.


    3) Optimize on every execution


    If you want the best performance for every query the solution might be to 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

    option (recompile)

    The XML plan for this execution


    exec test @pid = 897

    will show



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


    Some other solution that has been traditionally implemented before has been using local variables but usually this is not a good idea. By doing this you are not only disabling parameter sniffing but also disabling the choice of the Query Optimizer to use the statistics histogram to find an optimal plan for the query. This solution will use the same execution plan for all the executions but may not be the optimal plan for any of them.

  • The Phases of Query Optimization

    One of the most interesting tools that you can use to gain additional knowledge on how the Query Optimizer works is the sys.dm_exec_query_optimizer_info DMV. This view contains cumulative query optimizer statistics since the SQL Server instance was started and it can also be used to get optimization information for a specific query or workload.


    In this post I will show you how you can use this DMV to get information regarding the phases of query optimization used by SQL Server. Unfortunately, all the optimizer events shown in this section are undocumented and marked as “Internal only” in Books Online.


    As shown in the SQL Server documentation, this view has three fields: counter, which is the name of the optimizer event; occurrence, which is the number of occurrences of the optimization event for this counter; and value, which is the average property value per event occurrence.


    To obtain the optimization information for a specific query you can take snapshots of this DMV before and after the query is executed and compare them to find the events that have changed. Keep in mind that if you execute a query that it is already on the plan cache, it may not cause a new optimization and may not be shown in this view. This DMV may also capture some other optimization events happening on the SQL Server instance at the same time that your query is executing.


    To start, run the following code to create three tables


    create table table1 (a int)

    create table table2 (a int)

    create table table3 (a int)


    Trivial Plan


    The SQL Server query optimizer is a cost-based optimizer but this cost-based optimization has an expensive startup cost. To avoid this cost for the simplest queries where cost-based optimization is not needed, SQL Server uses the trivial plan optimization. The next example shows a query that takes benefit of a trivial plan. The DMV output shows one trivial plan optimization of a query accessing one table with a maximum DOP of 1.


    select * from table1



    Of course, you can also find out if a trivial plan was used during optimization by looking at the properties of the graphical plan, shown as Optimization Level TRIVIAL, or by looking at the XML plan, shown as StatementOptmLevel="TRIVIAL". If a query does not qualify for a trivial plan both of these properties will be shown as FULL instead.


    If a trivial plan is not found, the Query Optimizer will start the cost-based optimization.


    Many SQL Server users believe that it is the job of the Query Optimizer to search for all the possible plans for a query and to finally select the most efficient one. Because some queries may have a huge number of possible query plans, this may not be possible or may take too long to complete. Instead, the Query Optimizer uses three search phases and the optimization process can finish if a good enough plan is found at the end of any of these phases. If at the end of a phase the best plan is still very expensive the Query Optimizer will run the next phase. These phases are shown as search 0, search 1 and search 2 on the sys.dm_exec_query_optimizer_info DMV.


    Phase 0 – Transaction Processing


    The first phase is called the transaction processing phase and it is used for small queries typically found on transaction processing systems. The following example shows an optimization on phase 0, using 233 tasks for a query accessing 3 tables.


    select * from table1

    join table2 on (table1.a = table2.a)

    join table3 on (table1.a = table3.a)



    Phase 1 – Quick Plan


    The next phase is called Quick Plan and it is appropriate for more complex queries. This phase may also consider parallelism. Note that, as shown in the next example, not every query qualifies for phase 0, so depending on the number of tables some queries may start directly on phase 1.


    select * from table1

    join table2 on (table1.a = table2.a)



    Phase 2 – Full Optimization


    The last phase, called Full Optimization, is used for complex to very complex queries. This phase applies more sophisticated transformations than the previous ones.




    The DMV can also show a timeout event. When a timeout is found, the Query Optimizer stops the optimization process and returns the least expensive plan it has found so far. This timeout event is also shown on the properties of a graphical plan as Reason For Early Termination of Statement Optimization or on an XML plan as StatementOptmEarlyAbortReason.


    For example, the following output shows a timeout in phase 0, after 1,616 tasks on a query joining 12 tables.


    To keep this post simple I have provided very small queries only, but you can experiment yourself with more complex and interesting queries. By the way, in Chapter 2 of Inside SQL Server 2005: T-SQL Querying, Lubor Kollar provides an excellent script to automatically extract the optimization information for a specific query from the sys.dm_exec_query_optimizer_info DMV.

  • The Query Optimizer and Computed Columns

    In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until SQL Server 2005. In this post I will show you how this feature works and explain how computed columns can help you improve the performance of your queries.


    A problem faced by queries using scalar expressions is that they usually can not take benefit of column statistics. Statistics provide cardinality estimation to help the Query Optimizer to create better execution plans. Without statistics, the Query Optimizer will use a 30% selectivity on inequality comparisons which may produce inefficient execution plans.


    A solution to this problem is the use of computed columns as SQL Server can automatically create and update statistics on these columns. The great benefit of this solution is that you do not need to specify the name of the computed column in your queries for SQL Server to use its statistics. The Query Optimizer automatically matches the computed column definition to an existing scalar expression in a query, so your applications do not need to be changed.


    Indexes can also be created on computed columns but be aware of the requirements described on the Books Online entry ‘Creating Indexes on Computed Columns’.


    I will show you one example; run this query against the AdventureWorks database


    select * from Sales.SalesOrderDetail

    where OrderQty * UnitPrice > 10000




    The estimated number of rows is 36,395.1, which is 30% of the total number of rows, 121,317. But the query only returns 772 records.


    Now create a computed column


    alter table Sales.SalesOrderDetail

    add cc as OrderQty * UnitPrice


    Run the previous query again. Note that this time the estimated number of rows has changed and it is close to the actual number of rows returned by the query. Replace the 10000 with some other values like 10, 100, 1000 and 5000 and compare the actual and the estimated number of rows.



    Note that creating the computed column does not create statistics. These statistics are created only until the query is executed. You can run this to see information about its statistics, which name starts with _WA_Sys_.


    select * from sys.stats

    where object_id = object_id('Sales.SalesOrderDetail')


    Also note that the auto_created field is 1 which means that the statistics were automatically created by SQL Server. Use the following command to display these statistics


    dbcc show_statistics ('Sales.SalesOrderDetail', _WA_Sys_00000013_2645B050)


    Unfortunately, for automatic matching to work the expression must be exactly the same, if I change the query to UnitPrice * OrderQty, instead of OrderQty * UnitPrice, it will show an estimated number of rows of 30% again.


    select * from Sales.SalesOrderDetail

    where UnitPrice * OrderQty > 10000


    Finally, drop the created computed column


    alter table Sales.SalesOrderDetail

    drop column cc

  • The Query Optimizer and Contradiction Detection

    One of the many interesting topics I found while working as technical editor of the SQL Server 2008 Internals book was the concept of contradiction detection. So, in this post I will to try to expand on this topic and provide some additional examples.



    Contradiction detection is performed by SQL Server during the early steps of the query optimization process. During this process the Query Optimizer tries to find contradictions that can be removed and can make the query perform better. Since these parts of the query are not executed at all, SQL Server saves resources like I/O, locks, memory and CPU, making the query to be executed faster. For example, the Query Optimizer may know that no records can satisfy a predicate even before touching any page of data.



    A contradiction may be related to a check constraint, or may be related to the way the query is written, that is, the query itself contains a contradiction. Let us explore both scenarios and analyze the execution plans that are generated on each case.


    First, I need to a table with a check constraint. The following code creates a table that stores information about classic rock albums and its check constraint validates that every record entered was released before 1990.


    -- create table

    create table albums (

    name varchar(40),

    band varchar(40),

    album_year smallint,

    constraint check_year check (album_year < 1990))

    -- add some records

    insert into albums values ('Machine Head', 'Deep Purple', 1972)

    insert into albums values ('A Night at the Opera', 'Queen', 1975)

    insert into albums values ('British Steel', 'Judas Priest', 1980)

    So, if I run a query to list all the records released, let us say before 1980, the execution plan uses a Table Scan operator, which is exactly what I expected for this scenario.

    select * from albums

    where album_year < 1980



    Check Constraint

    But what about if somebody tries to request all the records released, for example, after 1995, like in the following query? Does the Query Optimizer know that no records will be returned even before looking at the data on the table?

    select * from albums

    where album_year > 1995

    In this case the Query Optimizer should know that because of the existing check constraint the previous query returns no records. So if I execute this query there is no need for SQL Server to access the data on the table, right? But surprisingly, the resulting execution plan still shows the Table Scan operator. Why?

    Actually, there is another optimization called autoparameterization, which is closely related to the trivial plan feature and that overrides contradiction detection. To avoid this optimization I can use some features like query hints, joins, etc. like in the following example


    select * from albums

    where album_year > 1995

    option (maxdop 1)

    Once I change the query to use a query hint, the execution plan does not access the table at all, and instead it is just using a Constant Scan operator.



    Now, see what happens if I disable the check constraint


    -- disable constraint

    alter table albums nocheck constraint check_year

    This time running the last query uses a Table Scan operator again as the optimizer can not longer use the check constraint.

    Finally, there are two choices to enable back the existing check constraint. If I ask SQL Server to enable the constraint and validate the existing data, the constraint will be enabled only if all the data complies with the check constraint definition. If I ask not to validate the existing data, the check constraint will be enabled but only the new records will be validated.


    Of course the resulting execution plan of my query will be different depending on the selected choice. If the check constraint is enabled validating the existing data, running the same query again (and including the query hint) will use the Constant Scan operator. But enabling the constraint without validating the existing data will again result in a Table Scan even when no records are returned. Basically, in this last case the query optimizer can not trust the check constraint.


    You can test yourself the resulting execution plans of the query after enabling the check constraint with any of these two commands


    -- enable constraint validating existing data

    alter table albums with check check constraint check_year

    -- enable constraint without validating existing data

    alter table albums with nocheck check constraint check_year

    By the way, you can use the following query to see if a check constraint is not trusted, that is, if it has not been verified for all the records on the table.

    select name, is_not_trusted from sys.check_constraints

    Contradicting Predicate

    The second type of contradiction case is when the query itself contains a contradiction. Take a look at this query

    select * from albums

    where album_year < 1970 and album_year > 1980

    option (maxdop 1)

    Same as before the execution plan for this query will use a Table Scan when the query hint is not used even when it is so obvious that there is a contradiction and no records should be returned.

    Once I add the query hint, the Table Scan again is gone and only a Constant Scan operator is used.

  • Are You Using Multiserver Queries?


    If you administer multiple instances of SQL Server and you have not used multiserver queries, you are going to love this new SQL Server 2008 feature.


    Multiserver queries allows you to run T-SQL statements against multiple SQL Server instances at the same time, returning the results in the same or separate result sets. In order to run multiserver queries you need to create server groups using the Registered Servers window, and register servers to become members of these groups. This can be accomplished by selecting either Local Server Groups or Central Management Servers.


    Once you have created your groups and registered your servers you can right-click on any of the configured groups and select ‘New Query’. You can also create a query to be executed against all your groups and servers by selecting Local Server Groups or your Central Management Server. To verify that you are about to run a query against multiple servers make sure that the Status bar of the Query Editor is pink and that shows the name of the server group (or DatabaseEngineServerGroup if you have selected all the groups).




    The registered servers could be any combination of SQL Server 2008 or SQL Server 2005 instances. I even successfully tested it with SQL Server 2000 instances.


    Once in the Query Editor there is an endless list of interesting things you can do. A few examples follow.


    Do you need a report of all the databases in all your servers? Just run a simple


    select * from sys.databases


    Notice how the results sets for each instance (shown in the next picture) by default are concatenated and the final result set includes an additional ‘Server Name’ column indicating which instance these records are coming from (you can change this and two other configuration options by selecting Tools, Options, Query Results, SQL Server and Multiserver Results in Management Studio).




    How about getting a report of when each of your SQL Server instances was started? Try


    select start_time from sys.dm_exec_requests where session_id = 1




    select login_time from sysprocesses where spid = 1


    or if you have SQL Server 2008 instances only


    select sqlserver_start_time from sys.dm_os_sys_info


    Or maybe a report of the disk space available on every drive on every server? Just run




    Of course you can also query user databases, but the query needs to be valid on all the instances. For example, if you are like me and have a performance data collection database on each SQL Server instance, you can run a query to obtain some specific performance counters that you collect periodically, like in


    use <db_name>


    select * from cpu_history

    where cpu_usage > 80

  • Getting CPU Utilization Data from SQL Server


    When I installed the SQL Server Performance Dashboard for the first time, perhaps a couple of years ago, the first thing that caught my attention was the nice graph in the main screen showing the system CPU utilization. Then I immediately wanted to know where this report was getting this CPU information from. A few minutes later I found the undocumented sys.dm_os_ring_buffers DMV.




    Even when the sys.dm_os_ring_buffers DMV returns information about memory management, error handling data, and some other data about the state of the server, it is its scheduler monitor health records which provide the system CPU utilization information.


    To get this data the ring_buffer_type field needs to be used to filter on the RING_BUFFER_SCHEDULER_MONITOR value as shown in the following query, which works on both SQL Server 2005 and SQL Server 2008.


    select timestamp, convert(xml, record) as record

    from sys.dm_os_ring_buffers

    where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

    and record like '%<SystemHealth>%'


    The information is stored in XML format and some sample output is shown here




    One record is stored every minute up to a maximum of 256 records (if the instance has been running long enough, that is, more than 4 hours). Clicking on any of the links will take you to the XML editor and will show an entry similar to this


    <Record id="1434" type="RING_BUFFER_SCHEDULER_MONITOR" time="244409400">














    The fields that show the CPU information are ProcessUtilization, which is the amount of CPU used by SQL Server, and SystemIdle, which is amount of idle CPU. The remaining percentage is the amount of CPU used by the other processes running on the server.


    This is the query used by the Performance Dashboard using the XQuery value() method to get the required information from the XML record


    declare @ts_now bigint

    select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info


    select record_id,

          dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,



          100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

    from (


                record.value('(./Record/@id)[1]', 'int') as record_id,

                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,


          from (

                select timestamp, convert(xml, record) as record

                from sys.dm_os_ring_buffers

                where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

                and record like '%<SystemHealth>%') as x

          ) as y

    order by record_id desc


    Note that this query also uses the sys.dm_os_sys_info DMV. While testing on SQL Server 2008 I found that the cpu_ticks_in_ms column is no longer available on this DMV. So, if you want to run this code in this version of SQL Server just replace the second line of the code with this line


    select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;


    Running the query will show something similar to this output




    Finally, you could use this query (or the msdb.MS_PerfDashboard.usp_Main_GetCPUHistory stored procedure installed by the Performance Dashboard) to collect this CPU information periodically, for example, as a job running every 15 minutes.


    Keep in mind that the Performance Dashboard is not needed in SQL Server 2008 as this version includes the new Data Collector, a feature that allows you to store the performance and diagnostics historic information of your SQL Server instances.

More Posts « Previous page
Privacy Statement