Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan

When you are running Adhoc SQL query with optimize for ad hoc workloads set to 1, the very first time you wont be able to see any compiled Query plan saved in cache instead you will see some Compiled Plan Stud when you query sys.dm_exec_cached_plans. Now we need to check what this Complied Plan stub is.

We will do a small practical to check this Complied Plan Stub, let’s now create our infrastructure to get Compiled Plan Stub in sys.dm_exec_cached_plans.

For this first we need to set “Show advanced Options”.

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install

Now Let’s set the 'optimize for ad hoc workloads' to 1

sp_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.

Now once this advanced setting is done, now we need to free the Procedure cache and Buffer, so that we will get only our plan cached in the memory when we execute our set of query.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now since our cache memory is free, let’s fire a SQL query and check the cache using DMV sys.dm_exec_cached_plans.

Now we will run the below query and will check how plan is getting cached up in memory.

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

usecounts cacheobjtype     objtype text

--------- ---------------- ------- -------------------- ----------------

1         CompiledPlanStub Adhoc   select * from emp where ename='Sneha'

So from the output we can see there is no compiled plan which gets stored in cache even after running the select query. What we can see from the output there is something Compiled Plan Stub gets into memory but not the actual plan

Now without clearing the cache and buffer lets run the same query and check the dm_exec_cached_plans output.

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

1         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

Now here we can see the Complied plan get strored in cache and  the CompiledPlanStub is out of the game it is now being replaced by Compiled Plan.  

Complied Plan Stub is not a part of any query execution plan, it is just a kind of place holder to keep track of whether a particular query has been executed proviously. Once a query similar to this gets compiled up, the stub is replaced with actual query plan.

When a query that generated a compiled plan stub is recomplied, th stub is replaced with full complied plan. As we can see when same query gets compiled the Stub is replaced and Complied plan get stored on cache.  

and the knife plunged deeper into his heart, when a programmer was told to document his logic then sit for code…L

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan

Read Previous Post: Plan Caching I - Adhoc Query Caching (SQL Server 2008)

Whenever you fire any adhoc query, SQL server cache the plan in memory for reuse in future reference but to use the cached plan of Adhoc Query, one need to take special consideration while writing the query, any change in the query statement will generated new plan instead of making use of cached plan, statement to statement, word to word, space to space and case to case has to match with the earlier fired query, if you want to reuse the cached plan of query which was executed earlier.

 

Lets do a small practical to know this better. For our practical purpose we have set the database advanced configuration property 'optimize for ad hoc workloads' to 0.

For this first we need to set “Show advanced Options”.

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install

Setting the 'optimize for ad hoc workloads' to 0

sp_CONFIGURE 'optimize for ad hoc workloads',0

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 0. Run the RECONFIGURE statement to install.

Here we are freeing the Procedure cache and Buffer, so that we will get only our plan cached in the memory.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now platform is ready to execute our explanation, lets fire the below statement and check sys.dm_exec_cached_plans for cached plan.

Test 1

Here we are executing the same select statement twice with the help of GO 2 and then the same query we are executing with a little modification where we changed Case of “select” to “SELECT”  

select * from emp where ename = 'Sneha'

go 2

SELECT * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

2         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

1         Compiled     PlanAdhoc SELECT * from emp where ename = 'Sneha'

From the output it is clear that we have two plan saved one which is used 2 times where the “select” keyword is in lower case and other where “SELECT” keyword is in Uppercase.

Test 2

Now we will fire the same select statement without any change and will see what dm_exec_cached_plans produce as a result. Just free the cache and buffer before executing our query.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

select * from emp where ename = 'Sneha'

go

select * from emp where ename = 'Sneha'

go

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

3         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

Here we saw, there is only one plan will gets generated and stored in cache and is reused by further execution of same query.

Test 3

Now again we are executing the same set of queries, just we add a small comment after 2^nd query, let’s see the result 

select * from emp where ename = 'Sneha'

go

select * from emp where ename = 'Sneha'   --2nd

go

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

2         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

1         Compiled     PlanAdhoc select * from emp where ename = 'Sneha' –-2nd

From the output it is evident that if you are putting a small comment to the query which is completely same in all respect to the previously executed query, you will get a new plan.

Conclusion : A single cosmotic modification in the query will produde new plan n wont use the existing plan stored in cache memory. So be aware while writing adhoc queries.

Standing at its brink, like a swimmer who geared up; code bonfire.

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching I - Adhoc Query Caching (SQL Server 2008)

SQL server frequently reuse plan that have already generated and saved in Plan cache rather than go for new plan, but sometime this behavior is not suitable, especially in case of Adhoc query. Every time you run an adhoc query, its plan gets generated and stored in Procedure Cache, whether it will used or not in future.

Just imagine how many Adhoc query you are firing in a day. And if for every query plans get cached then how much memory your Adhoc query consumes.

To overcome from this, SQL Server 2008 comes up with 'optimize for ad hoc workloads', here we will look this feature by doing a small practical.

When Optimize for ad hoc workloads is 0

For this first we need to set “Show advanced Options”.

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install

Lets first set the 'optimize for ad hoc workloads' to 0 and check the output of caching.

sp_CONFIGURE 'optimize for ad hoc workloads',0

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 0. Run the RECONFIGURE statement to install.

Here we are freeing the Procedure cache and Buffer, so that we will get only our plan cached in the memory.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now since our cache memory is free, let’s fire a SQL query and check the cache using DMV sys.dm_exec_cached_plans.

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

1         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

It is clear from the output that when we fire this query first, the plan gets generated and stored in Cache. But if we never use this query again, the plan which gets stored in cache will never be used, so its just a waste of cache memory. Even if the same query is written in lower case or with any cosmetic changed manner, the plan which gets saved will never be used. So it’s just wastage of cache memory.

When Optimize for ad hoc workloads is 1

With SQL Server 2008 we can set “Show advanced Options” to 1 for optimization purpose of Adhoc Queries.

sp_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.

Lets again free the Procedure cache and Buffer, so that we will get only our plan cached in the memory.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now we will run the below query and will check how plan is getting cached up in memory.

select * from emp where ename = 'Sneha'

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

usecounts cacheobjtype     objtype text

--------- ---------------- ------- -------------------- ----------------

1         CompiledPlanStub Adhoc   select * from emp where ename='Sneha'

So from the output we can see there is no compiled plan which gets stored in cache even after running the select query. What we can see from the output there is something Compiled Plan Stub gets into memory but not the actual plan unlike when we have optimize for ad hoc workloads set to 0.

Now without clearing the cache and buffer lets run the same query 3 times and check the dm_exec_cached_plans output.

select * from emp where ename = 'Sneha'

go 3

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0 AND

[text] LIKE '%select * from emp where ename = ''Sneha''%'

order by usecounts desc

go

Beginning execution loop

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

Batch execution completed 3 times.

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

3         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

Now here we can see the Complied plan get strored in cache and being reference/used 3 times and the CompiledPlanStub is out of the game.

Conclusion : with optimize for ad hoc workloads set to 1, we saw, first time when you run adhoc query, the plan didn’t get stored in cache, if there a call later to the same query the Complied Plan Stub is replaced by actual plan. Unlike optimize for ad hoc workloads set to 1, even if you run query first time, its plan gets stored in cache whether it will be referenced in future or not.

He fluffed up his belt, tossed up trouser on the floor, lay down on bunk bed, toggle up his laptop, keyed in SQL server… he is a programmer  

Post Reference: Vikram Aristocratic Elfin Share

SET XACT_ABORT, to rolls back current transaction on Run-Time Error

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

To understand more on it we will do small practical, here we are creating two tables – table1 and table2 with primary key in first table as Parent Table and Foreign Key in second table as Child table.

create table table1

    (col1 int not null primary key);

create table table2

    (col1 int not null references table1(col1));

Go

Command(s) completed successfully.

Our table is ready, now lets insert some records in table 1, which has primary key on col1.

insert into table1 values (1);

insert into table1 values (3);

insert into table1 values (4);

insert into table1 values (6);

go

Now we are setting xact_abort to  off and inserting three records in child table table2 which is referenced by table1 primary key column.

set xact_abort off;

go

begin transaction;

insert into table2 values (1);

insert into table2 values (2); -- foreign key violation.

insert into table2 values (3);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

The statement has been terminated.

(1 row(s) affected)

select * from table2

col1

-----------

1

3

(2 row(s) affected)

In the above insertion the seocnd records which we were inserting was violating the Integrity constrain, since we were trying to insert value 2 which is not present in col1 of table1.

Eventhough the second insertion fails, it didn’t stop execution there and proceeds with the third row insertion and finally commited the 1 and 2 rows insertion implicitly. This is what happends due to xact_abort to  off.

   

Conclusion: SELECT shows only keys 1 and 3 added.  Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded.

Now lets set xact_abort to  ON and tries to insert some good and bad records.

set xact_abort on;

go

Command(s) completed successfully.

begin transaction;

insert into table2 values (4);

insert into table2 values (5); -- foreign key violation.

insert into table2 values (6);

commit transaction;

go

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table2__col1__571DF1D5". The conflict occurred in database "Sparsh", table "dbo.table1", column 'col1'.

select * from table2

col1

-----------

1

3

(2 row(s) affected)

Here from the output we can see none of the records gets inserted to the table even though the first and third records comply with the Integrity constrain. This is because, on the failure of the insertion of 2 records which was violating the integrity, the complete transaction rollback.

Conclusion: Key 5 insert error with XACT_ABORT ON caused  all of the second transaction to roll back.

Note: The alternative to this is TRY CATCH block

Take up a piece of code, blend it with ur logic, think dream n live it, let ur every part full of that logic, leave everything aside.. This is how Programmer lives their life. WILL U MARRY ME J  

Post Reference: Vikram Aristocratic Elfin Share