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.
Now 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.