Monthly Archives: October 2013


In this article I will discuss about the ‘Optimize for Unknown’ query hint that was introduced in SQL Server 2008 and how we can use it.

So what is ‘OPTIMIZE for UNKNOWN’?
‘OPTIMIZE for UNKNOWN’ directs the query optimizer to use the standard rules it uses if no parameter values have been passed.The optimizer will use statistical data instead of the initial values for all variables when the query is compiled and optimized.

Now the big question is how does this help? Well ‘OPTIMIZE for UNKNOWN’ is one of the mechanisms which helps us to avoid parameter sniffing.

So what is parameter sniffing?
I will explain this with an example. I will use the Adventureworks2012 database for demonstration purpose on SQL Server 2012. However you can use any other version but not lower than SQL Server 2008.

Let us run the below query on the database and observe the output and execution plan

USE AdventureWorks2012
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744

Row Count: 13
ofu1Now lets take a look at one of the aspects of the execution plan of the above query. Make a note that the actual number of rows is 13 and the estimated number of rows is 44.
ofu2Let us have a look at the plan xml for the run time values and compiled time values:
ofu3Now let us run another query with a different value.

USE AdventureWorks2012
SELECT * FROM Sales.SalesOrderDetail
Where ProductID = 707

Let us observe the row count, execution plan and plan xml values as we did for the previous query
Row Count:
ofu4Execution plan: Make a note that the estimated number of rows is still 44.5 which was for the parameter used in the previous query i.e 744
ofu5Plan XML for Compiled and Runtime values: Make a note here that the compiled value used by the query optimizer here to create the plan is 744 which was the value that the previous query used.
ofu6This is called parameter sniffing where the optimizer sniffs the current parameter value during compilation.

Can this cause trouble?
Yes, it can. When a non-similar parameter is passed when a plan is compiled for the first time, the plan that the optimizer will find in the memory may not be the optimal one for that parameter passed. This will can result in a plan that is suboptimal and can cause a devastating effect on performance.

So how can we use ‘OPTIMIZE for UNKNOWN’ to avoid parameter sniffing. let us understand that with the below example.

We are going to run the above 2 queries here also but with the Optimize query hint in the second query:

USE AdventureWorks2012
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 707

Observations post execution of the two queries:
1)  It creates 2 different plans:
ofu72) Estimated and Actual row calculation for the two queries respectively:
Query 1:
ofu8Query 2:
ofu93) Compiled value and RunTime value from Plan xml:
Query 1: With parameter value 744
ofu10  Query 2: With parameter value 707 and query hint (Optimize for Unknown)
ofu11 So with the use of the query hint the optimizer does not use the value that it got from the cache but generates a new plan based on the available stats in the database.

What are the kind of stored procedures that can be a victim of Parameter Sniffing?
A)  SP’s which has optional parameters
B)  SP’s which has parameters in range operators.

How to find the last execution details of a stored procedure in SQL Server

In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT) AS qp
WHERE st.text like '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%Name of Stored Proc%'

How to increase the number of SQL Server logs

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
EL5Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
EXEC dbo.sp_cycle_agent_errorlog

This will re-initialize the current log and start a new log with the current date time stamp
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
b) Click on OK

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

Find the Buffer Pool usage per database in SQL Server

In this short article i will share a simple query that i wrote to find the number of pages of a database in the buffer pool and there usage of the buffer pool

SELECT CASE database_id WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS [Database],COUNT(*) AS PageCount ,
( COUNT(*) * 8.0 ) / 1024 AS [SpaceOccupiedInBuffer-Mb]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id


How to find and remove a query plan from the plan cache in SQL Server

In this article I will try to explain how we can obtain the plan of a SQL query from the plan cache and also how we can remove the plan from the cache keeping other plans intact. Before we see how we can achieve the above we will understand few basic things on plan cache.
1) What is a query plan: A query plan is an organized and ordered set of steps used by the SQL engine to fetch data. Every query has a query plan cached in the SQL memory
2) What is a plan cache: Apart from Buffer pool which is the biggest consumer of SQL Server memory, SQL Server allocates a proportion of this memory for use of storing the query plans. This storage of query plans in the SQL memory is called plan cache. The storage is a volatile storage and if the SQL server is restarted then all the plans are removed from this cache.
3) What is a plan handle: A plan handle is an unique identifier of the plan.

Let us now see how we can locate the query plan of an already executed query:
Step 1: In this example I have executed the below query on the Adventureworks2012 database.

SELECT [BusinessEntityID],[Title]
FROM [AdventureWorks2012].[HumanResources].[vEmployee]
WHERE PostalCode = '98104'

Step 2: Now let us find the query in the plan cache. We will use the below query to find the plan of the query from the plan cache. Observe carefully that in line number 11 we have used a filter to search all the queries in the plan cache which has the word ‘PostalCode’ which is also a part of our executed code on the Adventureworks2012 database.

select a.plan_handle,query_plan,text,
from sys.dm_exec_cached_plans a
inner join sys.dm_exec_query_stats b
on a.plan_handle=b.plan_handle
cross apply sys.dm_exec_sql_text(b.sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(a.plan_handle) as query_plan
where text LIKE '%PostalCode%'

Snapshot of the output below
Step 3: The highlighted text is the query that we ran and you can see the the query plan associated with it on the second column. Click on the plan and it will open on a new window.
So now we know how we can find the plan of an already executed query from the cache. In short use the above mentioned query and use a string from the executed query as filter in the above given query.

Now let us see how we can remove the query plan from the plan cache
Step 1: Find the plan handle of the query plan.
Step 2: Execute the below code to drop the plan from the plan cache

DBCC freeproccache(0x06000100A8CEE12640BB20D50200000001000000000000000000000000000000000000000000000000000000)

This will only drop that particular plan from the plan cache.Be very careful that you do not practice this on a production environment without understanding the implications of the absence of the query plan which you dropped.

I hope this article was helpful in finding and removing query plans from the plan cache without impacting other plans.

Modified DMV’s in SQL Server 2008 R2 and SQL Server 2012

In this post i will discuss about two dynamic management views that has been enhanced with new columns in SQL Server 2008 R2 and SQL Server 2012. The DMV’s are

1) sys.dm_exec_query_stats
2) sys.dm_exec_sessions

Let us begin with [sys.dm_exec_query_stats]
So what does this view reflect. It reflects performance data for stored query plans in SQL Server plan cache. The data is aggregated and is helpful for performance analysis. When a query plan is eliminated from the plan cache, the related row is also removed. In SQL Server 2008 R2 onwards, 3 new columns have been added to this DMV. They are as follows as explained on Technet.

a) last_rows: The number of rows returned by the last execution of the query.
b) min_rows: Minimum number of rows returned by the query since last recompilation of the query and execution of the plan.
c) max_rows: Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.

The next dmv that was modified with new columns are [sys.dm_exec_sessions]
This view returns a row per authenticated session on SQL Server. It shows information about all the active user connections and internal tasks that are being performed. The new columns that have been added to this view are as follows:

a) Database_id: iD of the current database for each session.
b) authenticating_database_id:
i) When the source of the session is a contained database, then the value is the db id of the authenticating database.
ii) The value will be 1 if it is a server scoped principal i.e a windows or SQL login.
iii) This will be Null if the session is that of an internal task.
c) open_transaction_count: Number of open transactions per session.

%d bloggers like this: