Last week, we announced our support for Azure SQL Database monitoring for stand alone databases. We have a lot of great things planned to expand on the features we have released, but I thought I’d point out something specific you’ll see when setting up an Azure SQL Database target.
If you are evaluating or have already upgraded to the new version, when you go to create a new Azure SQL Database target, you’ll see this Add Target wizard:
Notice in the red rectangle, just under the credentials, is a checkbox option named Allow SQL Sentry monitoring objects in target. So, what exactly does that mean? If you click on the help icon next to it, you’ll see some popup text that gives you a little more detail:
What this means is that, in order for us to not pull a lot of data across the wire when we are monitoring query and procedure stats, we need a few objects in the target database to be created. This allows us to more efficiently pull only the monitoring data we need, and it also means less cost for scenarios where the monitoring service resides outside of the Azure Region where the target database lives.
If you leave the checkbox blank, and click on the Connect button, you notice that the Azure SQL DB Top SQL feature will come up with a yellow status. This is because, just as the help indicated, without these objects turned on, this feature is disabled. This is the only feature right now that relies on these objects, but there could be more identified in the future that benefit from having local tables or objects in the target.
When these are identified, you’ll see them treated the same as you see here, with a feature status indicator in the target wizard, and the help updated to clearly indicate which features are affected. If you check the option to allow the objects, then the connection verifications will ensure that the correct permissions are in place for the service to be able to create the objects.
The option to allow these objects is off by default on new installs of the product. You can override the value at the connection level, as you create each connection, just by checking or unchecking the option on the target wizard. If you wish to have this option to be defaulted to Allow, then simply update the settings at the global level (or really any level in the settings hierarchy). You can find the option under the Azure SQL Database Connection. Changing it to True will default the check box on the wizard to checked:
If you set up an Azure SQL Database target without these objects, but later want to turn them on, all you need to do is go to the Dashboard for the target Azure SQL Database. Click on the Top SQL tab, and you’ll see a button in the middle of tab that allows you to enable the objects for the target:
You can also set the value in the settings for the connection for the specific database.
At the moment, to support the Top SQL features of monitoring query and procedure stats, they are two stored procedures and a handful of tables. These are all created within a schema called SQLSentry to keep them isolated and identifiable from your own objects. There’s really not that much to them. Some of the tables are transient, only storing what we need between monitoring intervals from the service. You may notice that some of the tables have what looks like random values at the end of the table names, for example:
These random strings are actually identifiers for specific monitoring services. If you see more than one of these random strings on the table names, it means that this database is being monitored from multiple Monitoring Services.
The monitoring service maintains the objects as necessary and handles any updates that need to happen to them. If you accidentally delete the tables or procedures, they will automatically be recreated on the next monitoring service interval; however, you may see a gap in some of the related monitoring data as a result.
As previously mentioned, if you don’t have these objects in your database, the monitoring service won’t collect data on the procedure and query stats (or what we call our “Top SQL”). This feature is very helpful in identifying your worst performing queries, or perhaps the ones that are just using the most resources.
Once the data is collected, you can see which queries take up the most CPU, perform the most reads, most writes, etc. You can even get down to the query plan using the built-in Plan Explorer!
If for some reason you decide to turn this off, you can do so by updating the value for the database on the Azure SQL Database Connection setting. Just set the Allow SQL Sentry monitoring objects in target to False (example). Again, you can set this globally, or at any level in the hierarchy to serve as the default.
The underlying objects are not removed automatically; however, any feature that relies on them will no longer be available. For the current release that means the collection of procedure and query stats will be stopped, but we leave the tables behind so we don’t lose any metrics you’ve captured to date. To completely remove the objects, we include a script in the User Quick Start Guide. If, while you are running the script, you see an error that the objects are locked, or in use, it’s most likely because there is a monitoring service still actively using them.
To wrap up, the SQL Sentry monitoring objects help reduce a lot of back and forth between the target database and the monitoring service. We provide the option to turn them on at a per connection level, and you have full control over whether these objects are used in your target databases or not.
