Query Plan In Ms Sql


Hello everybody,

some MS SQL internals.

Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans. 

For example like this:

select * from sys.dm_exec_cached_plans

this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?

Take look at the screenshot :

the area of interest for us is column plan_handle. 

You can use it for another sql request. Like this:

select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);

if you click at column with xml you'll see the following picture:

How to interpret those results? I hope to describe in one of other notes on my blog.

For now another question, how to see the text of cached value? 

You can do it with another sql 

for example like this:

select * from sys.dm_exec_sql_text(0x06000C002D51D1304021F8AE000000000000000000000000)

in the column text you'll see column text which will represent what MS SQL cached. In my case it is following text:

select [first name], [last name], dense_rank() over (order by [last name]) as rn from employees

No Comments

Add a Comment


Comments are closed