Karthick P.K on SQL Server

Archive for the ‘Optimizer’ Category

SQL Server Parameter sniffing

Posted by Karthick P.K on October 8, 2012

When a stored procedure, prepared queries and queries submitted via sp_executesql  is compiled for the first time, the values of the parameters supplied with the execution call are used for cardinality estimation, to optimize the statements within that stored procedure and create the query plan. This is known as parameter sniffing because the optimizer sniffs the current parameter value during compilation.

If these values are typical and the data distribution is even in the underlying tables, all the calls to the stored procedure will benefit from this query plan since the plan is reused. However, parameter sniffing can cause problems if the "sniffed" parameter value is not typical of the values which are actually used during a typical execution or the data in underlying tables are very skewed, because plan generated for “sniffed” parameter value may not be optimal for current parameter passed and since the plan is reused there can be performance degradation.

Consider the following scenario we have a table with two columns (country and some column ). This table has 10001 rows.  10000 rows has USA in country column and 1 row has brazil in country column.


This table has NONCLUSTERED INDEX called NC on country column.



create table data(country char(10),somecolumn char(10))


insert into data values (‘BRAZIL’,‘somedata’)


insert into data values (‘USA’,‘somedata’)

go 10000




[country] ASC




create proc sniffing @p1 char(10)



select country,somecolumn from data where country=@p1






–Let us execute stored procedure sniffing with the with parameter brazil.


exec sniffing ‘BRAZIL’








Optimizer picked up Index-seek in Non-clustered index and Row-ID lookup on table.


What happens when we execute the same procedure with parameter ‘USA’. Since the plan is already created and cached for ‘BRAZIL’ it is reused and plan which is generated for BRAZIL is Not an optimal plan for parameter USA.

exec sniffing ‘USA’ 








How to identify if the optimizer is using plan which compiled for sniffed parameter values  and not the current parameters value.


Let us enable statistics xml on



set statistics xml on


exec sniffing ‘USA’ 



Look at the XML plan for the ParameterCompiledValue and ParameterRuntimeValue.


Below is extract from XML plan and this output proves that the plan is compiled for parameter BRAZIL (ParameterCompiledValue) and it is used for parameter USA (ParameterRuntimeValue)


<ColumnReferenceColumn="@p1"ParameterCompiledValue="‘BRAZIL    ‘"ParameterRuntimeValue="‘USA       ‘" />





We will also see a huge difference in estimated and actual rows count if the parameter sniffing is impacting the plan

(Remember out dated stats can also cause optimizer to estimate incorrect rows so difference in estimate and actual rows doesn’t mean it is because of parameter sniffing ). 


What would have been the optimal plan if the parameter ‘USA’?



Let us execute the same procedure with recompile option



exec sniffing ‘USA’ with recompile









How to fix Parameter sniffing?


1. USE RECOMPILE: when you create the stored procedure. so the parameter is compiled every time it is called. This method can be used if the compile time is very less compared to execution time of bad plan

   Ex: create proc sniffing @p1 char(10) with recompile


2.  OPTION (RECOMPILE): for the statement which impacted by the parameter sniffing.  If the procedure has multiple statements recompile will impact only the particular statement.


3.  OPTIMIZE FOR HINT: Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized (or)  OPTIMIZE FOR  UNKNOWN WHICH Instructs the query optimizer to use statistical data


instead of the initial values for all local variables when the query is compiled and optimized.  This value is used only during query optimization and actual values are used during execution.


alter proc sniffing @p1 char(10)  as
select country,somecolumn from data where country=@p1
option (optimize for (@p1 = ‘USA’))

–option (optimize for (@p1 unknown))


4.   Assign the incoming parameter values to local variables and use the local variables in the query.  If you are in SQL Server2000 in which we don’t have OPTIMIZE FOR hint.

      Ken Henderson has blogged about it in


5.   Trace Flag 4136 which is introduced in SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable  the "parameter sniffing" process  more details on




If you liked this post, do like us on Facebook at and join our Facebook group!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Optimizer, Performance, SQL General, SQL Server Engine | Tagged: , , , , , , , , , , , , , , , , , | 6 Comments »

Troubleshooting SQL Server high CPU usage

Posted by Karthick P.K on October 4, 2012

Troubleshooting SQL Server high CPU usage


First thing to determine when there is High CPU on systems is, if SQL server is consuming the CPU resource or other applications/service.


Use query in  THIS LINK to get CPU usage history (or) Task manager (or) Perfmon counter to determine that. In Perfmon, Process %Process time can also be used. Remember this counter is not based on 100%.  It is based on number of processor.  If you see 200 for sqlservr.exe and the system has 8 CPU, CPU consumed by sqlservr.exe is 200 out of 800 (only 25%).)


If the CPU spike is caused by other application involve application team.


Next step is to determine if the CPU consumed is kernel time or user time.


We can use Process %Privileged  time and %user Time counters in perfmon. Task manager will show kernel times which will also help us understand


Kernel CPU:  In general, if kernel CPU remains below 10%, it’s normal.  But if you see sustained kernel CPU at 30% or above, you should start looking at system drivers , Antivirus etc.  some known issues which can increase Kernel CPU time are

1.       Few Anti-virus software’s can cause high kernel time.  Temporarily disable anti-virus software to rule this out


2.       We have seen high resolution timer in SQL 2008 or SQL 2005 SP3 caused high kernel time in Virtual Machines because of outdated BIOS .  Temporarily disabling high resolution timer by turning on trace flag 8038 (configure as startup parameter) to prove this. Check for BIOS update and do not use 8038 in long term.


High user CPU: Some of the most common causes for High CPU in SQL Server  are

1.       Query execution causing CPU spike (Most commonly caused by optimizer picking bad plan).


2.       High compiles and recompiles. (schema, Stats change, Use of Temp table, Recompile hint).


3.       System threads spiking CPU (Ghost cleanup, Lazy writer, Resource monitor).


4.       Running many traces.



1. Query execution causing CPU spike:


Query execution  takes long times and spikes CPU commonly because of in-correct cardinality estimates caused by outdated statistics, Lack of Index, Server configuration, Distributed queries, etc.


When the server is experiencing this problem run the query in below link to list all the queries which are executing in the server order by CPU time desc along with plan.


Get SQL Text and Query Plan for statements which are executing now


 It could be one query which is driving the majority CPU time or Multiple queries each driving the CPU. Look at the CPU time of the above query output.


If it is single query/Store procedure which is driving the majority of CPU.


1.        Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will  be approximately

same in execution plan .If there is huge difference stats are out dated and requires update) .


2.       Identify if the query has used bad plan because of parameter sniffing (If the ParameterCompiledValue and ParameterRuntimeValue is different in XML plan). Refer THIS LINK to know more about Parameter Sniffing


3.        If updating the stats and fixing the parameter sniffing doesn’t resolve the issue it is more likely optimizer is not able to create efficient plan because of lack of indexes and correct statistics. Run the query which is driving the CPU in database tuning advisor and apply the recommendations. (You will find missing index detail in xml plan but DTA is more efficient). You can follow the steps in Tune queries using SQL Server Database tuning advisor .


4.       If the query which is spiking the CPU is linked server query try changing the security of linked server to ensure linked server user has ddl_admin or dba/sysadmin on the remote server. More details regarding the issue in THIS LINK.


5.       Ensure optimizer is not aborting early and creating bad plan. For details refer THIS LINK.


6.       Ensure the query which is spiking the CPU doesn’t have plan guides (xml plan will have PlanGuideDB attribute.  Also sys.plan_guides will have entries) and query hints(index= or (option XXX join) or inner (Join Hint) join).


7. Ensure that SET options are not changed.


If it is Multiple queries/Store procedure are driving the CPU together.


1.       Update the stats of all the tables and indexes in the database. Using the query in below link Rebuild index and update statistics for all the tables in database


2.       If updating stats doesn’t help and rebuilding the indexes doesn’t bring down the CPU we have to tune the queries 1 by 1.


3.       Ensure Large amount of RAM is not causing optimizer to choose inefficient plan


4.       Ensure that we do not run many traces at same time (commonly from monitoring tools). Use query in below link to list all the active traces.


Find all the profiler traces running on SQL Server



2. If the system thread is consuming most of the CPU.


1.       If none of the SQL queries are consuming majority of CPU,  we can identify if the back ground threads is consuming the majority of CPU by looking at  sysprocesses output for background threads. select * from sys.sysprocesses where spid<51.


2.       Check if you are hitting any of the known issues.


Resource Monitor may consume high CPU:

The Ghost Cleanup task uses 100% of the CPU on an idle system in SQL Server 2008 or in SQL Server 2005:



3. High compiles and recompiles: I will blog about high compiles and recompiles shortly


4. Other factors which can impact SQL Server query performance

1.       Maximum degree of parallelism. Ensure MAX DOP is set properly (you can follow the steps in How to set Max degree of parallelism (MAXDOP)


2.       Priority boost. (Do not enable priority boot)


3.       Do not enable Fiber mode.


4.       Tweaking affinity mask (Spikes few CPU).


5.       TokenAndPermUserStore.


6.       CPU power plan degrade the server performance


7.       SQL Server that’s uses  .Net Framework can cause high CPU Refer THIS LINK



If you liked this post, do like us on Facebook at and join our Facebook group

Related blogs: 

Tuning SQL Server query

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter


The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Configuration, Optimizer, Performance, SQL Server Engine | Tagged: , , , , , , , , , , , , , , , , , , , , | 28 Comments »

How to rebuild index and update statistics for all the tables in database.

Posted by Karthick P.K on September 26, 2010

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'   --  {can be run anytime}

Exec sp_MSforeachtable "dbcc dbreindex('?')"      --- {Always run this on a off-peak hour on any SQL Server instance}



Posted in Optimizer, Performance, SQL General, SQL Query | Tagged: , , , , | 3 Comments »

%d bloggers like this: