Webinar Follow-Up: 5 Essentials of Database Performance Monitoring Q&A
I recently led a SentryOne webinar about the 5 Essentials of Database Performance Monitoring. In the webinar, we walked through the core steps to quickly begin monitoring and optimizing SQL Server performance—as well as the newest edition of the SentryOne flagship monitoring product, SQL Sentry Essentials, which helps you:
- Identify your highest-impact queries and target them for tuning
- Use proactive alerts to prevent performance problems
- Get to the bottom of the toughest query problems
- Troubleshoot and resolve SQL Server blocking and deadlocks
- View core Windows OS resource utilization metrics
If you missed the webinar, you can view it on-demand here.
Throughout the webinar, I received a lot of great questions. The full Q&A is below.
Q: What is the overhead of having Index Analysis enabled?
A: Index Analysis is currently "on-demand," so the only overhead is from collecting an estimated plan or running the query to collect an actual plan.
Q: Do you have a free version of SQL Sentry?
Q: Is there a SQL Sentry vs. SQL Sentry Essentials comparison?
A: Yes, please refer to the chart at the bottom of this page on our website. This webinar covered the features of SQL Sentry EssentialsNote that if you download the free SentryOne trial, you will be testing the enterprise version of SQL Sentry.
Q: I use SentryOne 18.1 and I don't see the replay option on the Deadlock tab.
A: To view this option, download the latest SentryOne version—19.1.1—which was released the week of June 17, 2019. You can go to our customer portal at my.sentryone.com to download the latest version.
Q: Is it possible to generate Top SQL reports weekly?
A: It is possible. There are canned reports for Top SQL and they can be deployed to the SQL Server reporting server. Once they’re deployed, you can edit them. We are happy to help you with what to edit, since it’s not extremely obvious and there’s no “How To” document. If you contact our Support team, they can help walk you through it.
Q: Does SQL Sentry Essentials work on Azure SQL as a Service?
A: It does. Azure SQL Database and Azure SQL Database Managed Instance are both supported. Azure SQL Database has a little bit less of a support footprint, primarily because of what Microsoft exposes to us. For that reason, I’d recommend SentryOne DB Sentry. It’s priced a bit differently to give you better options in terms of database volume so that you don’t have to do them one at a time. Managed Instance is supported by a regular SQL Sentry or Essentials license. Essentials also works for Azure SQL Database, but my recommendation is to use DB Sentry because it’s priced better for that use.
Q: How can we find historical (over 30 days long) running queries on a database in the SentryOne Dashboard?
A: We keep that data automatically for you, but you can adjust your view. To do so, go to the SentryOne Dashboard (I showed the Dashboard during the webinar demo), and at the top there is a date range in which you can change your start and end days to include your desired 30-day range. In order to keep us from having petabytes worth of data collected for you, we roll that up into different intervals. In a wide range like that, your peaks and valleys will appear to smooth out, but you’ll get a general idea of performance for that month.
Q: I have an Azure server that is shut off during business hours. How do I configure the alerts to not send me emails while the server is down during off hours?
A: Good question. For the different alerts and conditions that you’ll need to set up, I would create a window for SQL Sentry and then I would go to my different condition and action pairs and assign that window as an exclusion window so that those actions are performed during that desired time. It will be a little tedious—you’ll have to do a little set up—but it should work perfectly fine. You can also contact SentryOne Support for assistance.
Q: Do you have any alerts that would let us know exactly when a plan for a given query changes? That is, when the optimizer changes which plan it uses?
A: We don’t have alerts built in to let you know when a plan for a given query changes, but we have the Advisory Conditions systems I demoed during the webinar. Several SentryOne customers have done that in various ways, and we are happy to share those examples with you. We don’t build it in because we want to show true plan regression—not only when it changes but also when it becomes worse. There’s a lot of opinions on what “worse” means, and we are working to use that data to answer that question. When we get there, we will have something built in. In the meantime, we are happy for you to contact us and we can share what we have with you.
Q: How does the event chain feature work and is it included in SQL Sentry Essentials?
A: The event chain is an automation option. The way it works is it watches things like SQL Server Agent jobs. It also watches Windows Task Scheduler events—basically, anything we support that can be scheduled. It can apply different and simple logic. For example, if you remember back to the days of DTS, you would see whether something completed, failed, or was successful before deciding to move on to the next item in the chain. It’s great for setting up different things like backup procedures or things that have business logic that you don’t necessarily want to spend a lot of time scripting. It’s very simple and easy to use. It’s not a part of Essentials, so you’d want to use the full SQL Sentry product to take advantage of this feature.
Q: How can we restrict a user to see only a particular server on SQL Sentry?
A: In its present form, SQL Sentry uses two forms of security. One is role-based security in which you would use database roles in the SentryOne repository database to lock down the features someone has access to. The other is called rights-based security and it integrates with Active Directory in Windows. You basically assign an Active Directory login to one of your contacts in SQL Sentry and then you have another option in the security area to add or remove the things that contact has visibility to. From that point on, that contact will only see what you allow him/her to see server-wise.
Q: Does your baseline feature allow an hour-by-hour analysis? For example, my expected CPU baseline at 9am will be different than the baseline at 1pm. My current monitoring tool breaks down the baseline data hour by hour, based on what has typically occurred during that hour over the last x number of days. I’m curious to know if Essentials has a similar feature.
A: I would say “yes” and “no” to that. No, it doesn’t have an hour by hour breakdown. Yes, it does have baseline analyses that constantly keep track of the relative baselines so that you can see what’s called the “last interval.” If you’re looking at a 30-minute interval, it will compare what you are looking at to a baseline of the previous 30 minutes. It has one for the previous day, and it also has one for the previous week. So, it constantly keeps track of those intervals and you can bring that up in the baseline drop-down menu without having to manually create a baseline.
Q: I just recently had query show up in the "Query Stats" tab but not in "Completed Queries." I’m curious as to why.
A: That is a good question. There are a few scenarios. Sometimes they run very fast and very often. Take for instance Completed Queries. Your default there is the statement or the batch had to run for five seconds or longer to be captured in Completed Queries. But let’s say this query runs in half a second but the aggregated impact of that query in the last minute is that it did run, say, 100 times, and together those 100 times equals more than five seconds. That will absolutely show up in Query Stats instead of Completed Queries. The idea there is that we want to show you when it’s not just one slow running query or a few slow running queries, but when your resources are being taken up by queries that run quickly and the aggregate of them take up a lot of resources on the server. We call it a “death by 1,000 cuts” scenario.
Q: How can we trigger low diskspace alerts to trigger multiple times (e.g., every hour) when the disk space goes (for example, 15%)?
A: One of the things we’ve done in the area of data science is develop predictive models around disk/storage space. The idea is that these models can tell us when you are going to run out of storage based on your daily consumption. In these models, we show you a resource exhaustion date and formulate charts that show our confidence in that prediction. Obviously, the further out you get, the less confident the prediction is. We make this information available to you in the Advisory Conditions Builder. You can build Advisory Conditions against your resource exhaustion date and it helps create a sliding window. As you do things to reclaim space, your resource exhaustion date will move. This feature really helps you keep ahead of capacity planning issues like disk space.
Q: Does the Enhanced Platform Installer (EPI) work with the Essentials version of SQL Sentry?
A: That is untested. I wouldn’t try it. The reason its untested is because Essentials only supports up to five targets. It’s designed for smaller use cases. Frankly, you won’t need to use EPI with Essentials.
Q: Does Essentials support monitoring Amazon RDS? Would it involve the DB Sentry product mentioned for Azure SQL Database?
A: RDS, similar to Azure SQL Database Managed Instance, just uses a SQL Sentry license and Essentials does support it.
Q: What's the latest version of the EPI, and how would I know when there's a new version?
A: We’re trying much harder to reach out and let people know when there’s a new version. We’re also aware that people don’t want to get spammed with emails. Therefore, we have the Version Checker. If you have the Version Checker enabled in your SentryOne client, we’ll notify you when there’s a new version. With EPI, the end goal is to be able to download an update package from our content delivery network. In the SentryOne 19.1.1 version that was released recently, you do need to go to the customer portal and download both a special EPI installer and the upgrade pack. In the upgrade pack, you’ll use the command-line interface to push it to your repository to run and update the command. From there, everything happens magically across your environment.
Q: Do you have any displays on procedure cache regarding when you have an extremely large query plan or plans resulting in prolonged waits on grants?
A: The procedure cache is part of a stacked area chart in the memory section of the SQL Server area on our Performance Analysis Dashboard. You can go there at any time to see what is in your buffer cache, procedure cache, or in your object cache. Incidentally, one of the features we are adding right now on the Dashboard is for in-memory, memory clerk, and clustered columnstore memory. We’ll have sections on there for that so you can see when they are chewing through your memory.
You can also find the relevant and helpful information about SQL Sentry Essentials here. Keep your eye out for future SentryOne webinars. And, please connect with me online!
Jason Hall - @SQLSaurus VP of Product - SentryOne I love products and making them better. I enjoy seeing technology improve the professional and personal lives of others. If you have a problem, I want to help you solve it through product innovation.