CODEWITHSUNDEEP
azure-sql-database
daxu
daxu

Reputation: 4084

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE not removing cache?

I have an azure PaaS database and would like to clear cache to test some SP. So I found some scripts from the Internet:

-- run this script against a user database, not master

-- count number of plans currently in cache

select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache

select count(*) from sys.dm_exec_cached_plans;

-- list available plans

select * from sys.dm_exec_cached_plans;

select * from sys.dm_exec_procedure_stats

However, the amount of cache is always around 600-800, so somehow it is not dropping.

I didn't get any error (no permission denied etc), so how this command is not cleaning cache?

Upvotes: 3

Views: 1517

Answers (2)

daxu
daxu

Reputation: 4084

I contacted Microsoft support and understood it now.

Try to run the following T-SQL on AdventureWorks database.

-- create test procedure
create or alter procedure pTest1
as begin 
  select * from salesLT.product where ProductCategoryID =27
end
go


-- exec the procedure once.
exec pTest1


-- check for cached plan for this specific procedure - cached plan exists
select * from sys.dm_exec_cached_plans p 
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc' and st.objectid = OBJECT_ID('pTest1')


-- clear plan cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- check for cached plan for this specific procedure - not exists anymore
select * from sys.dm_exec_cached_plans p 
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc' and st.objectid = OBJECT_ID('pTest1')


-- cleanup
drop procedure pTest1
select 'cleanup complete'

with this sample, we can confirm that plan cache is cleared for the database, however, sys.dm_exec_cached_plans is server wide and give you results from other databases as well (internal system databases) that for them the cache was not cleared with the CLEAR PROCEDURE_CACHE command.

Upvotes: 0

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4501

I haven't had time to debug through the code to be 100% sure, but based on my understanding of the system it is likely that merely bumping the database schema version (which happens on any alter database command) will invalidate the entries in the cache on next use. Since the procedure cache is instance wide, any attempt to clear the entries associated with the database would need to walk all entries one-by-one instead of merely freeing the whole cache.

So, you can think of this as invalidating the whole cache but lazily removing entries from the cache as they are recompiled or if the memory is reclaimed by other parts of the system through later actions.

Conor Cunningham Architect, SQL

Upvotes: 1

Related Questions