Product articles SQL Monitor
SQL Server monitoring from the…

7 March 2016
7 March 2016

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.

 

Tools in this post

SQL Monitor

SQL Monitor is a SQL server monitoring tool that transforms the way you look at your database. It cuts your daily check to minutes, with a web-based overview of all your SQL Servers.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Monitoring and Troubleshooting Deadlocks with SQL Monitor

    Tony Davis demonstrates a deadlock incident, as detected in SQL Monitor, and shows how we can find the cause quickly, using the Extended Events deadlock graph provided in the alert details, plus details of the sessions and queries that were running at the time it occurred.

  • Event

    Microsoft Future Decoded

    Microsoft Future Decoded is your essential guide to disruptive technology and how it will impact people and businesses, today and tomorrow. Back for a fourth year, Microsoft Future Decoded will focus on current and emerging trends at the cross over of business and technology. With cloud, big data and artificial intelligence already rewriting the rules

  • Article

    What you need to know about the State of SQL Server Monitoring 2019

    In 2018 we launched the industry’s first ever report into the state of SQL Server monitoring. The responses from over 600 SQL Server professionals gave us a unique insight into how they monitor their estates, the technologies they work with, and what were the biggest challenges they faced. In 2019 we ran our State of

  • Article

    Custom Metrics for Detecting Problems with Ad-hoc Queries

    Overuse of ad-hoc queries by applications is a common source of SQL Server performance problems. The symptoms include high CPU and memory pressure. Phil Factor offers a simple custom metric to monitor the percentage of ad-hoc queries being executed on a database, and shows how SQL Monitor can detect when the problem is happening, and show you the queries that are affecting the server.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics