Welcome back to another edition of T-SQL Tuesday. This month’s edition is hosted by my good friend Cathrine Wilhelmsen (b|t). Her topic of choice is “Monitoring.”
When the topic of monitoring arises, the focus almost universally shifts to Production environments. But as a developer, I would like to give Development environments some monitoring love as well.
Dev: “Monitor Dev? HAHAHA! Why do we care what’s happening in Dev?”
How many times have you been working on something and its performance is less than stellar? Perhaps you’re refactoring a legacy chunk of code and contrary to your expectations, your re-write is running dog slow. Or you’re working away in a shared Dev environment and suddenly queries that had taken mere seconds are now taking their own lunch break before coming back with resultsets?
Dev: “Okay, what should we watch for?”
Well, some of the metrics that are mission-critical to a Production environment may not have such a high priority in an Development environment. Perhaps you don’t care as much about about CPU spiking to 100%, because your Dev environment has fewer & less powerful CPUs than your Production hardware? Of course that hardware will be stressed more easily.
But here’s some things that you may still want to keep an eye on. How about blocking, especially in a shared environment? I’d really like to know that Fred’s new routine was written in a way that it unexpectedly takes exclusive table locks all over my database, and interfering with the rest of the development team. Better to find it causing trouble in Dev than Prod, right?
How about wait stats? If you’re working on a poorly performing chunk of code, and your Dev SQL Server is waiting on something, chances are good that your code will cause your Prod SQL Server to suffer some similar waits.
Dev: “Yeah, I can see how that kind of information can be useful. But how should we monitor?”
There are a wide variety of tools out there, both DIY and 3rd party vendor. Do you already have a product that you use to watch Production, that you could also use to watch Development? Talk to your DBAs about making use of it.
Want to roll your own solution? That’s easy too! Read up on how to baseline your SQL Server – Erin Stellato’s Baselines Series is a fantastic place to start! Leverage SQL Agent Jobs in conjuction with different diagnostic scripts like Glenn Berry’s Diagnostic Scripts, and save that data off to a table periodically for analysis.
Dev: “Huh, there’s some cool stuff in there!”
Absolutely! Be creative and do what makes sense for your enterprise and application. Remember, the sooner you learn about a problem, the sooner you can deal with it before it becomes a fire – even in Development!