Effects of Max Mem on Plan Cache

Reading a Microsoft Article (which can be found here) while making sure I understood what can cause a query plan to be recompiled or removed from the plan cache, I got to thinking a bit.

In the article, it lists the following as things that can cause a plan to be removed from cache or to be recompiled.

  • Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan.
  • Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Changes to any indexes used by the execution plan.
  • Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executing a stored procedure using the WITH RECOMPILE option.

The first item removes a plan from cache while the rest mark the plan is invalid and a recompile will be forced on the next execution of the SQL Statement.  But then the question comes, why make the distinction between the two?  Doesn’t removing a plan from cache in essence equate to a recompile further down the road when the query is executed again?  While semantically it is not a recompile, it sure sounds a lot like one.  The query was compiled, invalidated and removed from cache and then compiled again when executed the next time around.

When thinking about all of this, how can we see that memory pressure can “invalidate” query plans?  That is actually quite simple.  We can see the same net effects with the following experiment.

WARNING: DO NOT DO THIS ON ANY ENVIRONMENT THAT IS NOT A SANDBOX ENVIRONMENT.

In this setup, I have a Sandbox server with a simulated load that keeps plenty of plans in cache and SQL Server is happy with about eight gigabytes of memory allocated to it.  To show that memory can cause a bunch of plans to be invalidated (and removed from the plan cache), I am going to take memory away from SQL Server and in essence simulate a memory leak or memory pressure.

This shows that the settings have taken effect on SQL Server and all of this without a Service restart (I keep hearing that myth).

Max Memory in SQL Server

Seeing that the memory settings are taking immediate effect, we can turn our attention to that query that was run.  In the query to experiment with this, I took a count of the number of plans in the plan cache first, then changed the max memory, and then took a count of the number of plans in cache again.  This next image shows the results of those two plan count queries.  It is obvious here that the number of plans in cache was severely impacted by this change in Max memory when taking TOO much memory away from SQL Server (this is why you should only do this on a sandbox server).

Max Mem effect on Plan Cache

 

If you happen to take too much memory away from SQL Server, you could end up in a severe memory pressure situation where connections are limited.  Even trying to increase max memory could be severely hampered due to insufficient resources in the default resource pool.  Here is a blog post that demonstrates the effects on connections due to this error (even as bad as SQL Server not starting up).

Since the problem is in the default resource pool, you could try a connection to the DAC and modify the max memory there.  If the pressure is severe enough, you may be limited in your ability to connect to the DAC as well.  In that case, you may be in need of starting up in single user mode as referenced in that prior blog post.  Sometimes, it is enough to reduce the number of connections or resource requirements to regain access to the server to change the max memory appropriately.  Suffice it to say, do not run this on a production type of box, lest you are ready to cause a bit of an outage.  The intent here is solely to share that max memory can have an immediate impact on compiles and plans in cache.

Happy Holidays

Comments: No Comments
Published on: December 26, 2014

Quick and simple for today.  Happy Holidays to you!

Happy Holidays

 

T-SQL Tuesday 61: A Season of Giving

Comments: No Comments
Published on: December 9, 2014

TSQL2sDay150x150Tis the season for TSQL Tuesday.  Not only is it that season again, but it is also the Holiday season.

During this season, many people start to think about all of the things for which they are thankful.  Many may start to think about their families and friends.  And many others will focus more of their attention to neighbors and other people in the community.  This is often done regardless of how well you may know the people or in spite of ill feelings that may exist for the people at other times of the year.

Yes, it is a good time of the year.  And to top it off, we may even get to enjoy snow during this time of year while we sip hot cocoa, learn SQL and eat pies of many different sorts.  Yes! It is a glorious time of the year.

I already have a couple of SQL books to read as I cozy up close to the fire with my children near.  (Oh yes, it is never too early to learn SQL.)  A little SQL roasting on the open fire so to speak.  Awesome time of year.

With all that is going well and all the SQL I can be learning, it is also a Season of Giving.  It is because of the time of year that Wayne Sheffield was probably prompted to invite all of us to write about that topic for this months TSQL Tuesday.  You can read the invite here.

But thinking about that topic and the time of year, I wanted to talk briefly about some ways I know the SQL Community gives back.

Doctors without Borders

A really well known opportunity this past year that helped people to give back to the community was hosted by Argenis Fernandez (twitter) and Kirsten Benzel (twitterhere.  The two of them had this fantastic idea to involve the SQL Family in driving a fundraiser for Doctors without Borders.  They had publicized various goals to make it fun and achieved a lot of those goals.  This was an event I would like to see again and it was one that accomplished a lot of good.

Christmas Jars

Christmas JarEach Christmas season there is a phenomenon associated to a book called Christmas Jars.  People from all across the country anonymously donate a jar to somebody in the community that may have hit a stretch of hard luck.  In the jar is a variable amount of money for the family to use to help with whatever they need during that time.  You can read more about that here and here.

The Christmas Jars is something that my children do each year.  They find a family somewhere in town and find a way to get the jar to the family anonymously.  The amount of money is never the same, but the intent and love is always the same.  They are doing it to help their neighbors without any publicity.  They know the good that is brought from the love they show to their neighbors.

Watching my children participate in a worthwhile way to give makes me happy.  I hope it is something that will stick with them throughout their lives.

Community

All of that said, the TSQL Tuesday invite asked for what we plan to do in the upcoming year for the SQL community.  This is a really hard topic to answer.  It kind of depends on what opportunities become available in the upcoming year.  I can say this though, I do plan to continue to help and give where I can.

SQLSat in DC – it’s a wrap

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: December 8, 2014

sqlsatdc14
This past weekend I had the opportunity to go visit Washington DC.  It was just the second time I got to stay in the Nation’s capitol for more than just a few hours.  The previous opportunity came with last years event which I talked about here.  Sadly, my time was far too limited this trip and seeing the sites was far more limited.  Thus, I only saw them from the car or plane window in passing.  But that is far better than seeing them in photos or not at all.

The reason for the visit?  It was SQL Saturday 347.  Now, it is my chance to recap the event and what I learned.

sqlsat347_web

This year I really wanted to attend to see some of my friends and to help some friends as well.  An example is that one friend was looking for some good constructive feedback to his presentation.  That friend happens to be a person I met at the event from last year: Ayman El-Ghazali (blog).  I had a good conversation with Ayman in the speaker room following his session and between some of the sessions.  Ayman is a DBA that is working hard to learn and improve any way he can.  Those are traits that are important to have as a DBA these days.

Much like last year (and I even made the comment), it was warm again (definitely shorts weather) and it also rained again.  I really think there must be a trend with SQL Saturday in DC in that it brings rain.  There may be something there.

The event was well organized.  I think that is mostly due to Gigi Bell (twitter).  She is the wife of Chris Bell (twitter) and she whipped those boys into shape. ;)

There were some things that couldn’t be controlled necessarily.  But everybody came together and helped to make it work.

I had the great opportunity this year to present the very very very short session on Murder that I give as a precon along with Wayne Sheffield.  People walked away from the session expressing gratitude for the content and how much they learned.  Many were even curious to learn more of what we could present in the all day version.  Very COOL!

I also had a great time seeing SQLFamily.  Talking with friends and enjoying everybody’s company.  I did make it to a few sessions outside of mine.  And I got to chat with attendees while trying to answer their questions in the halls.

I am looking forward to this event again next year.  And I hope everybody that attended my sessions learned at least one thing.

One last thing.  Thanks to all of the attendees.  To say “the attendees were great,” at this event, would be a gross mis-understatement in my opinion.  The attendees were awake and engaged (even if they had never heard of “Clue” before the session).  They invested their time and effort and I think they helped to make the event top notch.

Murder in Charleston

I am about to set sail on a new venture with my next official whistle stop.  This year has been plenty full of whistle stops and I plan on continuing.  You can read (in full) about previous whistle stops and why they are called whistle stops here.

Suffice it to say at this point that it all started with a comment about a sailing train a few months back.

Charleston Friend

 

Time to sink or sail, so to speak.  SQL Saturday 354 in South Carolina will mark the next attempt at what I hope to be a repeat performance – many times.  I will be tag-teaming with Wayne Sheffield in this all day workshop event.  The session is one of two all day sessions for the event in Charleston, SC.

If you are a DBA or a database developer, this session is for you.  If you are managing a database and are experiencing performance issues, this session is a must.  We will chat with attendees about a horde of performance killers and other critical issues we have seen in our years working with SQL Server.  In short, some of these issues are pure murder on your database, DBA, developer and team in general.  We will work through many of these things and show some methods to achieve a higher state of database Zen.

Description

Join Microsoft Certified Masters, Wayne Sheffield and Jason Brimhall, as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server.  No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Presented by:

wayneWayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80’s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90’s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne

 

 

 

JasonBrimhall

Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments.  Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant, SQL Server MVP and a Microsoft Certified Master(MCM). Jason is the VP of the Las Vegas User Group (SSSOLV).

 

 

 

 

Course Objectives

  1. Recognize practices that are performance pitfalls
  2. Learn how to Remedy the performance pitfalls
  3. Recognize practices that are security pitfalls
  4. Learn how to Remedy the security pitfalls
  5. Demos Demos Demos – scripts to demonstrate pitfalls and their remedies will be provided
  6. Have fun and discuss
  7. We might blow up a database

kaboom

 

There will be a nice mix of real world examples and some painfully contrived examples. All will have a good and useful point.

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment, come and join us.  You can find registration information and event details at the Charleston SQL Saturday site – here.  There are only 75 seats available for this murder mystery theater.  Reserve yours now.

The cost for the class is $110 (plus fees) up through the day of the event.  When you register, be sure to tell your coworkers and friends.

Wait, there’s more…

Not only will I be in Charleston for this workshop, we will also be presenting as a part of the SQLSaturday event on December 13, 2014 (the day after the workshop which is December 12, 2014).  You can view the available sessions here.

Shameless plug time

I present regularly at SQL Saturdays.  Wayne also presents regularly at SQL Saturdays.  If you are organizing an event and would like to fill some workshop sessions, please contact either Wayne, myself or both of us for this session.

«page 2 of 85»






Calendar
March 2015
M T W T F S S
« Feb    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, March 1, 2015