Clearing Plan Cache in SQL Database (Azure)

Sometimes when in a testing mode I do like to clear cache within my SQL database (Azure). Nowadays a lot of my test databases are Azure based so how I clear cache is different to how I would do it with an older “earthed” SQL Server.

Looking at my locally installed SQL Server.


SELECT @@VERSION

cold

Getting some basic information about the cache for a specific database.

 SELECT st.dbid, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE dbid = 12
OPTION (RECOMPILE);

cold1.JPG

Normally I would run:

-- Database_ID
DBCC FLUSHPROCINDB (12);

I have cleared it out.

cold2

So, lets go to the Azure world.


SELECT @@VERSION

cold3.JPG

Again, get some basic information for my database ID.

SELECT st.dbid, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE dbid = 6
OPTION (RECOMPILE);

cold5.JPG

-- Database_ID
DBCC FLUSHPROCINDB (6);

Just does not happen: Msg 15247, Level 16, State 14, Line 9
User does not have permission to perform this action – I am not sure what permission I need? I am logging in as a server admin.

So I used the following instead.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;

cold4.JPG

One thought on “Clearing Plan Cache in SQL Database (Azure)

  1. Pingback: Clearing Plan Cache in SQL Database (Azure) - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s