Query Plan's Compile Time Parameter Values

Most of you would know how to get the query plan from the cache via "sys.dm_exec_cached_plans" or "sys.dm_exec_query_stats" DMV or via a "sys.dm_exec_requests" with the plan_handle. But one interesting thing which many not have noticed is the ability to check the "compile time" parameter values of the query or stored procedure via the query plan.

 What is compile time parameter value?

Let’s say we have the following stored procedure

       EXEC usp_get_income @start_income, @end_income;

@start_income, @end_income are the parameters. For the first call, the query optimizer generates the query plans using the parameter values passed initially. For the subsequent calls, the query plan is reused and it is based on the values passed for the first call. Till the plan moves out of the cache or till it is recompiled, the query plan generated via the initial call is reused. The parameter values based on which the query plan is generated is referred as the compile time values

Consider the following example

EXEC usp_get_income 1000, 2000;

Open up another query window and pull out the plan of the

call "EXEC usp_get_income 1000, 2000" using the following

query

select cp.objtype, cp.size_in_bytes,

       cp.cacheobjtype,

       cp.usecounts,

       st.text,

       qp.query_plan,cp.plan_handle

from sys.dm_exec_cached_plans cp

outer apply sys.dm_exec_sql_text(cp.plan_handle) st

outer apply sys.dm_exec_query_plan(cp.plan_handle) qp

where st.text like '%usp_get_income%'

and st.text not like '%dm_exec_cached_plans%';

Use count value 1 indicates it is the first time execution. Click

on the value in the query plan column

Right click on the right most operator and click properties as

shown below.

Expand parameter list section and observe the "parameter

compile value" section as shown below.

"parameter compile value" indicates the inputs based on

which the plan was prepared and shows 1000,2000 as values.

Re run the query with different values

EXEC usp_get_income 5000, 6000;

Check the query's plan reuse count via

"sys.dm_exec_cached_plans" script provided earlier. Check

the compile time value again. It indicates old values

1000,2000 which were used to generate the plan initially at

first compilation.

The compile time parameter values of a

poor performing query can be extremely useful in

troubleshooting query regressions / parameter sniffing

issues. More about the same in subsequent posts.

Sp_recompile doesn't recompile!!!!

Many of us use the stored procedure "sp_recompile" to mark the stored procedure for recompilation, so that next time it runs we get a new plan. But does it actually recompile? Lets check.

Consider the stored procedure "usp_people_income"

Step 1: Lets mark the stored proc for recompilation

sp_recompile 'usp_people_income'

Step 2: Start a extended event trace for the event "sql_statement_recompile" to see if our call to the stored procedure actually results in recompilation

Step 3: EXEC usp_people_income 5000,5010

You would notice that the extended event trace didn't return any result indicating that the stored procedure didn't recompile. 

So, does "sp_recompile" really work?

Yes. It does.

How???

Though, it doesn't actually force a recompilation, it actually achieves the same effect by removing the plan from the cache. At a subsequent call, new plan is actually inserted to the cache. You may check the same in the following way.


Step 1: Start a extended event trace for the events

Turn on the extended events sp_cache_insert,sp_cache_remove events

Step 2: Mark the stored proc for recompilation again

EXEC sp_recompile 'usp_people_income'

Step 3: Run EXEC usp_people_income 5000,5010

You may notice the following events fired in order

1) sp_cache_remove event immediately after "sp_recompile" is executed
2) sp_cache_insert event just before the stored procedure is executed.

Refer to screenshot provided below.

SQL Server 2014 - Cardinality Estimator - Part 1

It has been a while since I blogged. Just moved to few other mediums like speaking at user group and SQL Saturday events in the last few months and it has kept me away from blogging for a while. Now back to blogging again. Hope to do a better balancing act :)

Starting from where I left, which was on Statistics and Histograms, I now move on to Cardinality Estimator, one of the major changes of SQL Server 2014

What is Cardinality Estimator?

Cardinality Estimator is responsible for providing the estimates on number of rows returned for each operator. If you have observed a query plan before, the estimated row count is provided by the cardinality estimator. What happens behind the scene is

1. Query optimizer instructs cardinality estimator (CE) to find the estimated rows for each operation ( read a table, filter, join etc )
2.  Cardinality estimator checks statistics and histogram to find the number of rows returned
3.  Query optimizer based on the inputs given by CE, picks the right operator and subsequently the right plan

Obviously, CE plays a key role in getting right plan. Incorrect estimates would lead to poor plan and spiral out into poor performance.

CE has remained unchanged since SQL Server 7.0 and SQL Server 2014 brought about changes on the way estimates are made and on the algorithm used in making estimates. 

Run the following query with actual plan ( Query ->Include Actual Execution Plan ) on "Adventureworks2012". 

SELECT * FROM 

[Production].[TransactionHistory]

WHERE ProductID < 100

Right click on the select operator and click on properties as shown below

Observe the property "CardinalityEstimatonModelVersion". Value equal to 70 indicates it is using Old CE.

Query uses old CE as the compatibility level of "Adventureworks2012" database is 110 (SQL 2012). Switch the compatibility level to 2014 or higher,

rerun the query and you will notice the "CardinalityEstimatorModelVersion" to be 120, which indicates it uses the new cardinality estimator.

In a series of posts ( expect around 10 ), I would be covering the various changes in new cardinality estimator.

Hekaton Part 11 :- Finding row count in memory table

Finding a row count in a "in memory" Hekaton table can be a tricky task as the regular commands and DMVs don't give the desired result.

• sp_spaceused - doesn't work and returns a blank result
• sys.dm_db_partition_stats - doesn't track in memory tables.

So the  option we are left with is
    
      SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS  
     [Schema Name],

      OBJECT_NAME(Ind.OBJECT_ID) As [Table Name],

      ISNULL([rows],0) as [Total Records]

      FROM sys.dm_db_xtp_index_stats AS Ind

      CROSS APPLY sys.dm_db_stats_properties

      (Ind.object_id,Ind.index_id)

      WHERE Ind.index_id =2

      ORDER BY OBJECT_NAME(Ind.OBJECT_ID)

 

However, please note that the above query will give correct row counts if and only if statistics are updated for the table. For in memory tables, statistics are not automatically updated manually and hence will have to be updated  before one attempts to find the row count. So, please refer to the query below, to update the statistics for in memory table alone.

 

 

DECLARE @id int,@rowcnt int

DECLARE @in_mem_tbl_name varchar(500)

SET @id = 1

 

CREATE TABLE #in_memory_tbl(id int identity(1,1),in_mem_tbl_name varchar(500))

 

INSERT INTO #in_memory_tbl(in_mem_tbl_name)

SELECT object_name(object_id) FROM sys.dm_db_xtp_table_memory_stats

WHERE object_id > 0

SET @rowcnt = @@ROWCOUNT

WHILE @id <= @rowcnt

BEGIN

 

 

SELECT @in_mem_tbl_name = in_mem_tbl_name

FROM #in_memory_tbl

WHERE id = @id

 

EXEC( 'UPDATE STATISTICS ' + @in_mem_tbl_name + ' WITH FULLSCAN,NORECOMPUTE ' );

SET @id = @id + 1

 

END

 

DROP TABLE #in_memory_tbl

 

Please ensure to run the statistics section above, before attempting to find the row counts on "in memory tables"

 

 

 

 

 

 

 

      

  

Hekaton Part 10: Things Hash Indexes Can do

Hash Indexes on "In memory" tables are best suited for equality searches. Reason is obvious, as they are based on hash algorithm which doesn't sort data, hash indexes aren't useful for sorting or non equality searches ( Refer here). But, hashing being a fast and a effective technique, hash indexes are perhaps the best bet for handling equality searches ( where conditions using "=" ). Note that, equality searches are by far the most common filter condition you would see in queries. So, it is one of those things Microsoft has done to address the obvious with best possible solution ignoring the not so common problems :) Besides equality filters other areas where hash indexes can be useful are

Group by clause:

Consider the following query

SELECT count(*),data
FROM [HK_tbl]
GROUP BY Data

Query plan indicates a index scan but no additional sorting or grouping required, as even though data is not sorted by hash index, it is grouped / categorized and hence it is very effective for processing "group by" queries.

Inner Join:

HK_tbl2 is another "in memory" table with hash index on data column.

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
INNER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Outer table is scanned as there is no filtering condition while the inner table makes use of the "Hash Index" . Query plan shown below

 
Left Outer Join:

SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
LEFT OUTER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data

Semi equijoin condition like left outer join also benefits from hash index