A Guide To SentryOne SQL Server Monitoring

There is a lot to consider when weighing up SQL server monitoring solutions, with various competing providers delivering compelling packages that promise to boost performance, improve maintenance and enhance troubleshooting.

SentryOne is a leading light in this market when it comes to the best SQL server monitoring tools, so here is a look at the features and functions that are included in its SQL Sentry monitoring suite.

Scalable server monitoring potential

Monitoring a small cluster of server instances to track performance is straightforward, but some solutions can reach a certain point and become overwhelmed, leading to a lack of responsiveness and other issues. This is not the case with SQL Sentry, as it has been built to cope with enterprise-level configurations of 800 or more instances operating simultaneously with no slowdown.

This means you can confidently adopt it to meet your monitoring needs today, safe in the knowledge that it will be more than capable of working seamlessly if you expand your server setup tomorrow.

Forecast future storage needs

Speaking of planning ahead, another of the advantages which SentryOne brings to the table with its SQL monitoring service is the ability to make accurate predictions about just how much capacity your database might require further down the line.

It might seem like a fairly straightforward process to the uninitiated, but experienced managers will know that this kind of forecasting is far from simple. Thankfully the forecasting capabilities afforded by SentryOne are powered by machine learning algorithms and so are able to study usage trends for logical disks over time and make increasingly accurate projections about how this might change in the future.

Being able to see not just how much space is monopolized at the moment, but also how much is likely to be taken up weeks or months later will let you plot out procurement strategies and schedule downtime for upgrades appropriately, without this all creeping up on you.

Finding flawed queries

A lot of performance problems with SQL servers can be caused by suboptimal queries. Working out which queries are making mischief can be taxing, but with the help of the Top SQL tool found within SQL Sentry, it needn’t be.

In short this acts as a means of collating all of the queries which have the most impact on your server but which might not immediately be obvious as potential culprits of inefficiencies.

Queries can be flagged based on a number of parameters, ranging from their duration to the amount of processing power they made use of. Any suspicious queries will be presented in an intuitive interface that lets you get to the heart of exactly what is going wrong, without any of the guesswork or trial and error that might otherwise be involved.

Queries can be analyzed across every server instance in your control and you can look back over data at a limit of up to 3 years.

Query optimization

Of course even if you use SentryOne to seek out the queries that are wreaking havoc with your SQL server, you might not actually be able to unpick exactly what is wrong with them. This is where the Plan Explorer makes its entrance, and it is available both as part of the SQL Sentry bundle as well as in the form of a standalone app for those looking to take it for a test drive.

It can rank queries, recommend indexes, make modifications and provide you with an unambiguous scoring system to see the positive impact that any changes can have. It acts as a great way of visualizing the ebb and flow of how hardware resources are used depending on the way queries are structured, with even small alterations in syntax potentially leading to significant improvements.

Plan Explorer goes beyond merely providing estimates of operator costs, but lets you see the real figures up close and personal, making sure that there are not issues with inaccuracy to overcome. Furthermore the plan diagrams it presents are easy to control and can also be optimized using a unique layout algorithm.

Furthermore because the software tracks the history of your query as you make changes, it is child’s play to track the impact, roll back to earlier versions and also share what you have done with others to aid collaboration in team working environments.

Wait stat analysis

Maintaining an SQL server requires a degree of intuition, which comes with experience. Wait statistics can tell you a lot about where problems may lay, although once again SentryOne empowers administrators with the ability to analyze this information in less time and with more precision.

It is worth noting that this is far from a unique ability; it is something you will find in almost every other SQL server monitoring solution on the market. What SQL Sentry does that makes it stand out is give you the ability to customize the way that this information is presented within the dashboard, while also allowing you to conjure up reports which demystify wait stats and see why your CPU resources are not being utilized as efficiently as possible.

Combating deadlocks

Deadlocks can be a major bugbear for DBAs, but unless you actively look for them you may not even be aware that any are present in your database. SQL Sentry is able to both find deadlocks and let you use a playback tool, in combination with clear visualizations, to locate the source and solve the conflict.

The in-depth details of the deadlock are presented in a logical way, covering things like the session ID, the host, the app, the database and the start time. You can even create bespoke alerts which kick in if a particular deadlock event arises and you want to be able to respond to it quickly, rather than allowing it to linger.

Index analysis & defragmentation

If you are looking to improve your management of SQL server indexes, especially in the case that they have become fragmented, SentryOne has the right capabilities for the task at hand.

It can analyze indexes, rate query performance as mentioned earlier and let you get an overview of all sorts of statistics, including those relating to fragmentation. There is a separate fragmentation manager that allows you to plot out any defragmentation processing you wish to carry out.

Monitor hardware & network usage

The performance analysis dashboard is at the heart of the SQL Sentry experience, and it does a great job of tracking CPU, memory, I/O and network usage levels.

It also includes the ability to monitor Windows processes and determine whether or not any of these are acting as potential bottlenecks, or generally misbehaving in a way that needs to be remedied.

Varied platform compatibility

SentryOne recognizes that its users will not necessarily want to be limited to monitoring a single SQL database type from a sole vendor, but might have other iterations to oversee. This is why it has partnered with the likes of Amazon and Microsoft to offer monitoring solutions for the most popular options around today.

This kind of partnership is a reassuring sign, recognizing that SentryOne has made every effort to develop monitoring tools that are of a high standard and can perform effectively and consistently in many common scenarios.

About the Author

Michael Dunlop is a technology and business writer based in the United Kingdom. He enjoys to explore the latest developments in cybersecurity and follow emerging trends that will impact our lives for years to come.