Estimated/Actual Execution plan not displayed!!!

I have a Stored Procedure which was showing Execution Plan when execute it until some days back!!!

Suddenly, Execution plan not generating for the Procedure when I execute it!! But, Its executing and showing the result as expected.

I could not also see the Procedure definition and bellow is the Err!!

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 116
There is no text for object 'proc_sample2'.

What would be the reason ?

It seems like the Procedure's definition got !!! Encrypted !!!

Let's try to find out...

USE  <DatabaseName>
GO
SELECT CASE OBJECTPROPERTY([Object_ID],'IsEncrypted') WHEN 1 THEN 'YES' ELSE 'NO' END [IsEncrypted]
FROM sys.Objects WHERE type_desc ='SQL_STORED_PROCEDURE'
AND is_ms_shipped =0
AND Name='proc_sample2'
GO

Yes. That is correct!! The Procedure's definition got Encrypted. So that the Execution Plan can not be generated as per the MSDN Definition...

"Execution plans are not displayed for encrypted stored procedures or for triggers."

optimize for ad hoc workloads - Configuration Option

Normally, SQL Server compile, generate and stores the Plan for reuse. The process continues for all the following object types  

• Proc
• Prepared
• Adhoc
• ReplProc
• Trigger
• View
• Default
• UsrTab
• SysTab
• Check
• Rule

For the every type of queries the process generates and stores the plan and every plan consume some Size.

Here, we going to see about the Transact-SQL statement also referred to as "Adhoc" query.

USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

The plan consumes 72 KB (73728 Bytes) and the query uses 1st time. But, not sure whether the same plan going to be re-used next time. So, do we need to store the entire plan with consumes 72 KB at first time itself ?

No.

So, We have an option "optimize for ad hoc workloads" is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc queries/batches.

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE WITH OVERRIDE

Done. 

As long as the above option is ON. All the Adhoc queries' entire plan are not going to be stored in Plan cache. But, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time.

To Remove the specific plan from the cache
DBCC FREEPROCCACHE(0x06000A004230791470B5CC340100000001000000000000000000000000000000000000000000000000000000)
GO

Run the query at first time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Yes. Now, we got only 352 Bytes (small compiled plan stub) for the plan at first compile/use.

When run the same query again second time

Run the query at second time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

Check whether the plan re-generated and stored entirely in Plan Cache
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Now, New plan generated (See the use count column as 1) and stored the entire plan in plan cache (See the Plan Size(Bytes)).

Locking Hierarchy

SQL Server uses multi level of locking to allow different locks on various level of objects.

1. Lower level of locking on RID or KEY
RID - Row at heap (Actually doesn't have clustered Index)
KEY - Row at Clustered Index

2. Higher level of locking on Database

Hierarchies:
- Database (Highest level of locking)
- Database File
- Object
- Extent
- Page
- RID Or KEY (Lowest level of locking)

Note: SQL Server automatically decides on what level of lock should be placed to minimize the locking overhead.

Active Transactions - How long, Current State, Bytes Reserved/Used, Which DB and What Query ?

SELECT 
CASE dbt.database_id WHEN 32767 THEN 'Resource' ELSE D.name END [DB],
a.session_id [Session],
a.open_transaction_count [Tran. Count],
b.transaction_begin_time [Tran. Started at],
DATEDIFF(SECOND,b.transaction_begin_time,GETDATE()) [Tran. Active(Second(s))],
CHOOSE(b.transaction_type,'Read/write transaction','Read-only transaction','System transaction','Distributed transaction') [Tran. Type],
CHOOSE(b.transaction_state,'The transaction has been initialized but has not started.','The transaction is active.','The transaction has ended. This is used for read-only transactions.','The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.','The transaction is in a prepared state and waiting resolution.','The transaction has been committed.','The transaction is being rolled back.','The transaction has been rolled back.') [Transaction Sate],
dbt.database_transaction_log_bytes_reserved [Log reserved (Bytes)],
dbt.database_transaction_log_bytes_used [Log used (Bytes)],
x.[text] [Actual Query]
FROM sys.dm_tran_session_transactions a JOIN sys.dm_tran_active_transactions b
ON (a.transaction_id = b.transaction_id)
JOIN sys.dm_exec_connections C ON (a.session_id = C.session_id)
JOIN sys.dm_tran_database_transactions DBT ON(dbt.transaction_id = b.transaction_id)
LEFT JOIN sys.Databases D ON (D.database_id = DBT.database_id) 
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) x