Sql server query plan cache Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 143 of signals over
tutorial in this video well discuss what
happens when a query is issued a secret
server how to check what is in secrets
of a plan cash and finally things to
consider to promote query plan
reusability
so first let's understand what happens
when query is issued to secret server in
sequel server every query requires a
query plan before it is executed when
you run a query the first time the query
gets compiled and a query plan is
generated that query plan is then saved
in sequence of a plan cache next time
when we run that same query again that
cached plan is reused this means sequel
server does not have to create that plan
again for that same query so reusing a
query plan can increase the performance
how long that query plan stays in the
plan cache depends on how often the plan
is reused besides other factors the more
often the plan is reused the longer it
stays in the plan cache so now let's see
how to check what is in sequence or plan
cache to check what we have caught in
the plan cache we are going to make use
of these three dynamic management views
and functions provided by secret server
the first one that is DM exit cached
plans is a dynamic management view and
the other two DMX X equal to X and DMX a
query plan our dynamic management
functions we are going to use cross
apply operator between these three
objects to retrieve what we have got in
sequence or plan cache we discussed
cross apply operator in detail in part
91 of sequence of a tutorial so if
you're new to this operator please check
that video I've typed this exact same
query already in sequence of management
studio when we execute this look at the
result we get look at the first column
use counts this column basically tells
us how many times the cached plan in the
plan cache is reused and the second
column tells us the cached object type
that is a compiled plan and the third
column tells us the object type for
which this compiled plan is
created whether it's an odd health
statement that we have issued or is it a
prepared statement by sequel server or
is the plan for a stored procedure if
it's a plan for the stored procedure
then the object type will be Prague and
then we have the query text itself and
finally the query plan in XML format and
when I click this XML because that
sequence of a opens the query plan in
graphical format
well disk is analyzing this query plan
in a later video to remove all elements
from the plan cache use this command
DVCC free pro cache at the moment this
is what we have in the plan cache now I
want to remove all these elements from
the plan cache I can use this dbcc
command to do that let's execute this
and now when we execute this query
notice in the plan cache this is the
only query that we have now when we
execute again look at the use counts it
gets incremented every time we execute
this query because that same plan that
sequence over has generated is being
reused when we execute this query one
important thing to keep in mind is that
in all the versions of sequel server up
until sequence over 6.5 only stored
procedure plans are cached the query
plans for ad-hoc or dynamic signal
statements are not cached so the get
compiled every time we issue an ad-hoc
or a dynamic sequence statement but
sequence over seven and later versions
things have changed the query plans for
ad-hoc and dynamic sequence statements
are also cache along with the query
plans for stored procedures now let's
execute a stored procedure and see what
we get in the plan cache within our
employee DP database we've got a stored
procedure SP search employees let's
execute this procedure but before we do
that let's remove all entries from the
plan cache by executing this dbcc
command and now let's execute the stored
procedure once we get the result that we
expect let's execute this query to
retrieve what we have in the plan cache
notice within the plan cache we have got
two entries the first entry is a
compiled plan for a stored procedure
look at the
type it is Prague Prague stands for
procedure and look at the use counts it
is 1 because we have executed the stored
procedure only once and then we have a
compiled plan for an ad-hoc object so
what is the dead Hawk object it is
nothing but this ad-hoc query that we
have issued directly what we have in the
plan cache and since we have executed
this only once the use cons for that is
also one now let's execute the stored
procedure one more time and then let's
select what we have in the plan cache
notice the use counts for Prague has
gone up by one and similarly the use
counts for ad hoc has gone by one
because we have excluded both of them
two times so here the plan is being
reused and we also have compiled plans
for prepared objects so what are these
prepared objects these are the sequel
statements that secret server has issued
in the background now if we execute our
stored procedure one more time and then
we execute this query again to retrieve
the data from the plan cache look at the
compiled plan for proc and ad hoc it is
three for both of them
so up until sequel server 6.5 only
stored procedure plans are cached the
sequence of a7 and later versions the
credit plans for ad-hoc and dynamic
sequel statements are also cached the
credit plan cash lookup is by a hash
value computed from the query text if
the query text changes even slightly
secret server will not be able to reuse
the existing plan let's understand what
we mean by this with an example
let's flip over the sequel server
management studio so what we have here
is a very simple query select star from
employees where first-name equals smart
when we issued this query the first time
sequel server compiles this query
creates a new plan and stores the plan
in a plan cache when we issue the same
query again sequel server tries to find
the plan from the plan cache and we use
that plan instead of compiling this
query again and generating at another
plan but one important thing to keep in
mind here is that the
clan cache look up is by a hash value
computed from the square e-text so when
we issued this query again it computes a
hash value using this query text and
then compares that hash value with the
hash value it already has in a plan
cache if the match it will find the
query plan and reuse that credit plan so
what this means is if this query text
changes even slightly for example you
introduce a space somewhere in the query
or you change the case of any of these
letters then in that case query text has
changed so when it computes the hash
value it will also be different than
what it already has in the plan cache so
the hash values will not match and hence
sequel server will not be able to find
that existing plan so it ends up
compiling this query again and creating
another plan and storing that plan in
the plan cache so that's why it's very
important that the query text stays the
same so that's true even if we change
the value here from mark to Steve again
technically here though it is the same
query since the query text has changed
the hash value will not match and it
will not be able to find the existing
plan in the plan cache so it ends up
again compiling this and generating
another plan for that same query let's
actually prove that first let's clear
everything that we have in the plan
cache and then let's execute our query
and then let's see what we have in the
plan cache notice here we have our
ad-hoc statement and look at the use
counts at the moment it's one let's
issue the same query again and let's see
what we have notice the use cons for our
ad-hoc query has gone up by one since
the query text has not changed in any
way we are able to reuse the same plan
again now let's change this query
slightly let's change the letter F from
capital to small and let's execute this
one more time let's create a cache again
now look at this query right here
of a query with first name where letter
F is capital the use cones is still 2 so
we are not able to reuse that existing
plan instead it has created another plan
notice here the letter F is in small
letter so it has created another plan
and look at the use cones for that at
the moment it is 1 so since the credit
text has changed slightly it's not able
to reuse the existing plan and this will
be true even if we introduce an extra
space somewhere in the query or remove
an existing extra space the query text
has to be exactly the same and even when
we change first name value from Steve to
mark again here the query text has
change and it ends up creating another
plan let's actually look at that in
action so when we look at the plan cache
notice here we have our first initial
query the use counts is 2 and then we
have another query here the use counts
for that is 1 and we have first name
equals Steve again a different plan for
that so when we change you know first
name column values here we technically
want to find another employee you know
with the different first-name it's the
same query we just have changed the
value but still sequel server is not
able to find the existing plan instead
it's compiling the query again and
creating at another plan and storing
that in a plan cache so this is why it
is very important to use parameterize
queries so sequel server can reuse the
cached query plans with parameterize
queries sequel server will not read
parameter values as part of the query
text so when you change the parameter
values sequel server can still reuse the
cached query plan let's prove this here
I have a parameterised dynamic sequel
statement select star from employees
where first-name equals @ FN @ FN is a
parameter now first let's clear
everything that we have in the plan
cache and then let's execute our
parametrized then
seto statement let's see what we have in
the plan cache notice we have a compiled
plan for our dynamic sequel statement
and at the moment use count is fun let's
execute the same query once again
without modifying anything so we expect
that same plan to be reused look at the
use counts for that same query it has
gone up by one now let's change the
parameter value let's change it from
Steve to mark and let's execute it now
let's see what we have in the plan cache
notice the use count has gone up by one
we are still able to reuse the cached
query plan because in this case since
it's a parameterised query the parameter
values are not treated as part of the
query text so when we change the
parameter values the hash value has not
changed and we are able to reuse the
cache query plan let's change it one
more time from mark to John execute the
query we get John's record now let's see
what we have in the plan cache we expect
the use counts to be four notice it is
four as expected here is that same
example one more important thing to keep
in mind is that when you have dynamic
sequel in a stored procedure the query
plan for the stored procedure will not
include that dynamic sequel the block of
dynamic sequel will have its own query
plan let's prove this in one of our
previous videos we have implemented this
page dynamic sequel in store procedure
this page calls this procedure SP search
employees good dynamic sequel and we
have that procedure right here let's
script this procedure to a new query
editor vendor and notice with this
procedure we've got some dynamic sequel
and here is the page which calls the
procedure now before we search for an
employee
let's clear what we already have in the
plan cache and then let's search for
John
we get John's record now let's see what
we have in the plan cache now notice we
have a compiled plan for the stored
procedure SD surge employees good
dynamic sequel and we also have a
compiled plan for the dynamic sequel
block that we have in that stored
procedure now let's search for another
employee let's search for mark and let's
see what we have in the plan cache
again notice the compiled plan for our
stored procedure has gone up by one and
if you look at the dynamic sequel block
again the use counts has gone up by one
so the summary is you should never ever
concatenate user input values with
strings to build any Mexico statements
you should always use parameterize
queries which not only promotes cached
query plans reuse but also prevents
sequel injection attacks thank you for
listening and have a great day