THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Good advice can come from anywhere, but so can bad advice!

In the past I have advocated that when it comes to advice you need to consider its source before you actually use it.  I still believe this today, but recently I got some advice that makes me add a caveat to this statement.  You also need to consider the potential impact of the advice as well.  Some advice can mean the difference between severe damage or just additional headache.  Case in point, my 3 month old recently developed a croupy sounding cough.  Now any parent out there knows what this kind of cough sounds like, and generally speaking you run a hot shower and sit in a closed bathroom with a baby so the moisture develops the cough and helps express the material out. 

However, recently someone recommended that we also put Baby Vicks on his feet and cover them with socks when we put him to bed.  Considering the information, there was little risk involved in trying this out.  It just so happens that this worked better than putting Vicks on Michaels chest.  Why?  I have no idea, but since the risk of implementation was minimal we gave it a shot, and the results were excellent.  Turns out that the person that made the recommendation used to be a full time in home nanny and learned about this in a class on infant care.

home inspection photo of stove pipe next to windowNow lets flip it over to SQL Server.  You have a server that has a problem, and you jump online looking for help.  When you find information about your problem, consider the impact of the recommendation.  Can the recommendation be undone?  How hard is it to undo the recommendation?  In the event that the recommendation is wrong what is the potential impact?  A good example is someone having performance problems with a server. 

A recommendation can only be as good as the information that is known about the problem to begin with.  For example, in the picture to the right, the problem obviously was that the room was to hot.  The simple solution in this case was to add a window A/C unit.  However, the exhaust stack from the downstairs that vented in front of the window generally speaking would have precluded the use of a window A/C unit in that case.  The same kind of thing holds true with SQL Server.  If someone is making recommendations about a fix without having anything more than the most basic of information, consider it to be suspect.

For performance problems, my first recommendation is generally to post the output of DBCC MEMORYSTATUS and to post the top 10 wait stats which I look at for problematic waits (CXPACKET, PAGEIOLATCH_*, ASYNC_IO_COMPLETION, IO_COMPLETION, WRITELOG, SOS_SCHEDULER_YIELD, CMEMTHREAD, LOGMGR, LATCH_*, etc).  This initial request is quite benign, I am just asking for more information, but based on the information I generally make my recommendations.  In this case I am digging, looking for the first floor smoke stack that vents out in front of the second story window.

If I see that the TokenAndPermUserStore is large, I’ll recommend applying patches, citing KB articles that support this, and possibly setting trace flags.  I provide the KB articles from Microsoft to lend credit to my recommendation.  Undoing a Cumulative Update or Service Pack can be an arduous task if not impossible.  If I see that the procedure cache is quite large, I recommend setting PARAMETERIZATION to FORCED for the database(s) using ALTER DATABASE and then reviewing application code to update the adhoc requests to use parameterized requests.  If I see specific wait types like CXPACKET, I recommend that the Max Degrees of Parallelism for the server be reduced to 1/2 of the physical processor cores or to one if it is a true OLTP system.  These kinds of changes can be major, however, the overall risk of implementation is low.  If it goes wrong, it is really easy to reverse these changes, just as with the Vicks if it was wrong, it could easily be washed off.

I know this blog post is short, but its point should be clear.  When you receive advice concerning something you need to evaluate the source as well as the potential risk.  I am more inclined to try something that has a minimal negative risk associated with it regardless of where it comes from than something that could be dangerous.  If the same person had told me to hold Michael upside down to help whatever was in his chest work its way out through gravity, I certainly wouldn’t have tried it.  However, applying Vicks to his feet has little to no risk associated with it.  At worst he could get mad because his feet are warm, and then I am up with him because he is mad.  I might be up anyway because he is coughing all night.

Published Monday, October 12, 2009 8:00 AM by Jonathan Kehayias
Filed under:

Comments

 

Andrew Kelly said:

In the case of the vent and the air conditioner I believe the vent does not meet code to begin with. I know back where I was from these had to be vented higher than any window in the first place. So maybe they received bad advice before :).

October 12, 2009 9:54 AM
 

Rajib Bahar said:

Thanks for the advice on advice. I hope you didn't have to stay up too long. :)

October 12, 2009 10:08 AM
 

Jonathan Kehayias said:

Andrew,

I am not a contractor, so I have no idea, but yeah what you say makes perfect sense to me.  Part of the catalyst to this and another post I have was watching a home improvement show with my wife where people just kind of worked off hearsay and ended up ruining their homes in the process.  I am good with SQL Server, I leave major work on my house to professionals that are licensed, bonded and insured for a reason.  I need a new roof in the next year or two and a buddy keeps telling me how we could do it over a weekend without pulling contracts, and I could save $3-4K, but it would have to be complete by Sunday night (LOL).  I'll pay a professional to do that kind of job, just like I'd hire a professional to help me configure a IA64 hard NUMA SQL Server if I had to do it.  Somethings are worth the cost to have done correctly the first time.  (Sounds like another blog post almost doesn't it?)

October 12, 2009 11:21 PM
 

Oliver Mezquita said:

This is sooo true. You really gotta be careful who you're taking advice from. Many times people try to be an expert in areas they have no idea. Nice tip!

October 18, 2009 12:07 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement