SQL Server monitoring from the trenches: An interview with Anthony Nocentino

I recently sat down with Enterprise Architect, Anthony Nocentino, to talk about how effective monitoring can help resolve many of the issues companies face every day. I was fascinated because my career revolves around how we can improve SQL Server monitoring. Anthony’s involves actually doing it at the coalface.

He’s made a name for himself creating SQL Server-based systems that enable companies to collect meaningful data they can act upon. He works with clients to find the right technology for their business, design and deploy it, and provide ongoing expertise on system performance, maintenance, and availability.

I was interested in talking to him about SQL Server monitoring to find out what advantages it brings to different clients in different scenarios.

MC: I know you’re a consultant, Anthony, but what kind of clients do you work with?

AN: All kinds of companies from e-commerce to manufacturing. I do, though, tend to favor small to mid-sized companies. That way I talk directly to decision-makers with a specific pain point that needs to be fixed. As importantly – probably more importantly – any changes can be implemented relatively easily. In larger companies and enterprises, you have to go through layers of management and even a simple job can take a long time. This gives me the ability to affect positive change more quickly.

MC: What kind of pain points are we talking about?

AN: It could be a systems problem, a scalability issue inside a relational database, or the redesign of a whole data tier. It varies each day.

MC: You’ve been a full time consultant for five years now. How many clients have you worked for in that time?

AN: Dozens and dozens. Sometimes it’s a long project that takes a month or two, other times, it’s a problem I can resolve in a couple of days. Guess that’s why I like it. When I take my first sip of coffee in the morning, I often don’t know what the day’s going to launch at me.

MC: Have you noticed any common thread or theme over the five years?

AN: Monitoring, which I guess is the reason for our chat. To get the information I need to come up with a viable, long term solution to anything, I need data. I’m a trained engineer. It’s what I thrive on. There’s no point even trying to resolve a problem without appropriate monitoring to baseline the environment and provide the information required to make decisions.

MC: Why is it a common theme?

AN: You’d be surprised at the number of clients I go to who don’t have a monitoring system in place. I’m there because there’s a problem but, by the time I arrive, it’s often hidden away. The confusion is still there about what caused it, but revealing it takes a lot longer.

MC: Why is that?

AN: Think of a monitoring tool as a flight recorder. During normal service, it ticks away in the background and you might check it once or twice a day to reassure yourself everything is on track. When there’s a problem, it comes into its own. You can use it to zoom in on what happened, find out why it happened, and stop it happening again.

MC: Is there one particular problem or issue that crops up time and again where monitoring helps?

AN: Actually, there are three: database performance, maintenance, and availability. They’re so common I often do an audit for clients that covers these problem classes.

MC: Could you give us some examples?

AN: Sure. This past holiday season, I was working for a major Internet brand which sells a lot of things in November and December, but their database couldn’t insert rows fast enough to cope with the demand…also known as sales.

MC: How did monitoring help?

AN: A good monitoring solution constantly gathers metrics on CPU, memory, and I/O issues, so spikes and deviations from baselines are easy to spot. Comparing those to normal baseline values will often show what’s changed and the why can be figured out pretty quickly.

MC: What about maintenance?

AN: I’m often asked to identify the optimum window for clients that will allow them to perform maintenance or backups with the minimum disruption to their business. You could take a guess and simply say Sunday evening, or Wednesday after midnight. Where’s the harm, right? Probably none, but I still use a monitoring tool to be absolutely sure – and then I monitor the server following the maintenance or backup to see if any unusual activity follows.

MC: And availability?

AN: I’m a big fan of SQL Server AlwaysOn Availability Groups and the advantages they bring to high availability and disaster recovery. Managing them, though, is a bit like spinning plates – you have to keep an eye on lots of instances and spot any issues that come up straight away. You need to know, for example, when a failover occurs, when a listener is offline, or when replication is falling behind impacting availability or is causing transaction delays and impacting performance.

MC: You can do that without a monitoring tool, though.

AN: You can, but it would be difficult at best. With one – a good one – you can see at a glance the status of an availability group as a whole, and then go deeper into each replica and database. Add group-specific metrics and alerts and you don’t need to spin plates any more.

MC: You mention monitoring tools, but a lot of DBAs still roll their own scripts.

AN: They do, and custom scripts are perfectly adequate for providing alerts about issues like database space and database status. Beyond that it becomes increasingly complicated. My own view is that it’s better to take advantage of mature third party tools which, while paid for, are constantly updated, maintained, and supported, and tend to solve problems very quickly and easily. That said, I literally won’t let a customer go live without a monitoring tool.

MC: Is there any particular monitoring tool you recommend?

AN: SQL Monitor is my number one choice – and I’m not just saying that. I started with SQL Monitor and I’ve seen lots of other monitoring tools in my years as a consultant. I like it because the contextual switching from screen to screen allows me to zoom in on the problem and get right down to the information I need. When I have a particularly complex problem, it also lets me write my own custom metrics so I get even more detailed baselines, graphs, and historical data.

MC: Is there some final advice you’d give to readers about monitoring?

AN: Sure, don’t treat it as an option, treat it as part of the normal routine. That way, you’ll firefight less, resolve issues faster, and have more time to do the interesting stuff.

You can find out more about SQL Monitor on the Redgate website. And you can read more about the kind of work Anthony Nocentino does at his website.