Minor ailments and Healthy SQL

Comments: No Comments
Published on: January 13, 2015

TSQL2sDay150x150One of the things that DBAs love to do is keep their servers running and healthy.  A healthy server, after all, is your ticket to a stress free day and a full night’s sleep.  Granted this not a guarantee but it sure helps make life easier.

We are always looking for the big ticket items to keep the servers tuned and purring.  But from time to time, like with humans, it’s not Ebola that takes us down but the little sniffles and minor aches and pains that keep us from doing our best.

This month as a part of the TSQL Tuesday party, Robert Pearl (blog | twitter) has asked us to write about Healthy SQL and the things that make SQL go yum.

Nagging Cough

Like that tickle in the back of the throat, that makes you cough now and again, there is an occasional tickle that can bug SQL Server and cause some pain here and there.  I have written about this nagging cough in the past.

The problem with this ailment is that since it is not always a cold, or always present, it is often times missed and frequently hard to diagnose.  I talked about this previously as one of those things that should be checked from time to time.  This is that nagging synonym* cough.  You can read about it here.

That’s great, we can take a little cough syrup, fix some synonyms and feel a lot better about SQL Server in the morning.

Aching Joints

Ever have one of those trick knees that decides to give out on you out of the blue?  You might be walking up the stairs (or down the stairs) and suddenly the knee is gone and you end up flat on your face.  And it could be great most of the time, it’s just that once in a while the knee decides to give a little twinge of pain, fold up and drop you to the floor.

SQL Server has a similar problem with this next one.  I come across on a frequent basis, within SQL Server, an bad knee in the form of a linked server.  Sure, I can hear you saying that linked servers are always bad.  Fair enough!  I have seen linked servers work wonderfully and most of the time they cause pain.

The type of pain with a linked server I would like to share today is around more of an edge case (like standing on the edge of the stairs with your knee about to give way).

As a good DBA would want to do, you may want to restore your databases to a test environment on a routine basis to ensure the backups worked and that you have a functioning recovery point for your databases in the event of a disaster.  I was working on just that sort of thing for a client recently when I ran into this beautifully pain filled knee.

This customer had not one, not two, not even three instances of this problem.  They had a glorious 492 instances of this problem.  The vendor for this client decided the best thing to do would be to replicate the production database to a separate database to help offload performance.  This other database happens to be on the same server (so no performance offload).  While that is not the most intelligent thing to do, it is not the end of this ailment.

In addition to the replication, there were 492 views that utilized linked servers to UNION ALL data between the two databases.  The data in each table between the two databases (in this case) is the same.  So we have a linked server to UNION ALL this data between two databases on the same server that is replicated.  Wowza!

Now, due to this linked server proliferation in the views to get to data the long way around, when restoring this database to a test environment there is a lot of cleanup work left to be done.  After all, the restore of the database is only a piece of the healthy backup puzzle.  You would want to test the data and the application against it.

Gratefully, this kind of cleanup can be made easy by doing a simple search and replace when querying sql_modules to find any views or stored procedures need to be updated to work in the test environment.  Here is an example of such a script to help fix that problem.

Take the results from a query such as that and now, I can either change the views en masse or I could copy all of the results from the ModCode column and paste those to a new window.  Using a regex (to replace all GO statements with a GO \n as shown in the pic) or something like SQL Prompt to prettify the code would be pretty easy from there to make it more useful.

Capture

Of course, this only helps address the issue with the linked servers in the views.  The same problem would exist with stored procedures.  It is up to the DBA to know which ones need to be changed and which should not be changed.  Just understand that linked servers are there to present yet another nagging symptom to keep your server from being healthy and worse is they help keep you from being healthy (remember the lack of sleep they cause).

If you are interested, I also have this article to help you find those pesky linked servers before you start diving down the restore rabbit hole.  The article will help evaluate the scope of linked server use and has a query to help identify linked servers.

*Funny afterthought is that both of these niggles that can help decrease health of your SQL Server have ties back to Linked Servers.  If you read the links, you will see what I mean.

Las Vegas UG This week

Comments: No Comments
Published on: January 5, 2015

What a fun week we have tuned up for the folks in Las Vegas.  It is the first full week of January and there is this huge convention going on near the strip.  And as timing would have it, this week is also the perfect time to have our User Group meeting.

What major conference is going on this week you ask?  Why it is time for 200,000 people to invade the strip, all of the major convention centers (well nearly all of them), and every last hotel room (as seen by the tripling in price for hotel rooms starting Tuesday Night – unless you go to the Cosmo where the room rate increased 20x this week).  That conference happens to be CES (Consumer Electronics Show).

If you happen to be in town for CES, live here in Vegas or are just in town this week, we invite you to come join us at the Las Vegas UG meeting on Thursday.

January SQL Invite

We have the full details for the meeting on our meetup site.  This month we have an interactive session where Wayne Sheffield will walk you through some nifty tips and tricks for Management Studio.  Read the full details on the meetup site and come visit us in person or via our livemeeting.  We’d love to see you.

Update:

We are swapping presenters this month at the chance to have a presenter in person.  Randy Knight will be in the area and has asked to be able to present.  Thanks to Wayne for being understanding.  Please check the meetup site for more details.

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.

«page 1 of 84






Calendar
January 2015
M T W T F S S
« Dec    
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, January 25, 2015