Scripts are Like a Loaded Gun
Everything Looks Like a Target When You Have a Gun
Congratulations! Through ingenuity or skill with using your programming language of choice or your favorite search engine you are now in possession of a script that you can use to make all your performance tuning problems vanish in an instant. Be sure to kick it off whenever you need to; and use the results to instantly make important tuning decisions in your production database environment. <–Sarcasm, folks!
Having code to use and understanding it’s usage are two different things entirely. Unless you’re lucky in troubleshooting and Russian Roulette, not understanding there is a difference leads to making bad decisions with bad – or even correct – information.
A Little Something for the SQL Geeks
While this applies to all programming disciplines I am going to use Microsoft SQL Server’s programming language and engine as an example due to my familiarity. Let’s talk about the fine art of waiting for a second…
When I present for user groups, at technical summits, or on SQL Cruise I frequently do so on the subject of SQL Server’s Dynamic Management Objects. These are internal code constructs that allow for advanced troubleshooting and analysis of what is going on under the covers in your SQL Servers. One of the more useful DMOs (as they’re referred to) is sys.dm_os_wait_stats. Without getting into the nitties and gritties of what this DMO is and how to call it (for those of you not versed in SQL Server) let’s talk instead of the data it offers up to those who use it.
It’s Like Marriage
Sure marriage is full of unicorns dancing in fields of marshmallow-coated bacon where it rains chocolate-covered cherries and money grows on trees but it is also about waiting. Waiting for him to find his car keys for the 5th time this month. Waiting for her to choose the right black dress from the 19 she owns. Waiting for him to finish that last match of Halo multiplayer. Waiting for her to finish that last section of code and log off the computer for the night.
The good spouse or partner accepts these things as the overhead to a successful, lasting marriage. The alternative is that you keep a running tally sheet in your head
That was the 8th time this month she had to try on little black dresses while I’m waiting in the car… a total of 40 minutes! That’s an average of 5 minutes!
If he starts up another game of Halo he’s going to have to learn how to toss a grenade by pressing on his left testicle where I’m planning on shoving that controller. That is the 7th time this week!
You get the idea. Microsoft SQL Server does the exact same thing. Not performing outpatient surgery to insert XBox controllers into the lower GI tract of gamers, but rather keeping track of when it needs to wait for something. This is the crux of what wait stats are. By querying the sys.dm_os_wait_stats DMO though a great script that I’ve discussed here and others have created, refined, altered, and tuned elsewhere as well you can get a great idea of where to start tuning things when you get that dreaded (at non-informative) call from a user stating “the database is slow.” Cue the screeching music and tighten the tension, that phone call will come eventually…
The Call Is Coming from Inside the Office!
Typically the call is short on information; no idea what the database name or server is perhaps, or if the performance issues are related to a given process. Once all that is ironed out where do you begin? The issue could be a poorly performing query or a constraint on resources or, well, ANYTHING!
Scripts Don’t Replace Years of Expertise and Experience
The first place I start once I am at this stage is with the wait stats query that shows up on multiple blogger sites. Here is the flavor of it I pulled from Glenn Berry with SQLskills. I had the pleasure of working with him on my book on the subject we’re discussing now and he really understands performance diagnostics.
What Glenn’s script does is identify the top waits that SQL has accumulated since the last time the wait stats were cleared via issuing the DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR); command or by cycling the SQL Services. This is a key thing to understand – these waits are not permanently accumulating – they are transitory and it’s important to understand how much metadata you’re basing your decisions on rather than blindly placing your trust in the data displayed on the screen in front of you. The waits also don’t take into consideration routine maintenance and backups as well as perhaps single – expensive tasks that may not represent the true current workload causing issues on the SQL Server. The wait stats cover all the activity that has accumulated over time.
Performance Tuning Scripts Can Be Powerful, but Dangerous
It’s critical that when you download a script to resolve a particular issue, you have faith in the source of that script (expertise) and you also understand it’s use and what it does (and does not) tell you. After all a loaded gun is a dangerous thing in and of itself, but is quite a powerful tool in the hands of a trained professional.
Do you want to get more SQL Server training with a side of fun, networking, and rejuvenation? Then join me in 2013 for SQL Cruise 2013!