Identify the issues on Azure SQL Managed Instance using BPCHECK

Managed Instance is a managed SQL Server database engine hosted in Azure cloud that provides most important database management features such as ensuring high availability, continuous backups, etc. Managed Instance handles core/basic management tasks and you are still responsible for data management, optimization of your databases and quality of your data structures. BpCheck is one of the tools that can give you recommendations that can help you to optimize your databases.

Sometime, you might not easily find that there are some issues in your databases such as — there are unused indexes, for a long time nobody made consistency checks, log files are growing, etc. BpCheck is one of the free opensource tools built by SQL Sever Tiger team that do the most common checks for issues in the databases on Managed Instance and gives you warnings and recommendations that you might apply.

Install BpCheck

BpCheck is a T-SQL procedure that you can install on your Managed Instance. You can install BpCheck by going to tigertoolbox GitHub account, download Check_BP_Servers.sql file and install it in your Managed Instance. This script will create a procedure called bp_check in msdb.

Check your instance

Now you can run this procedure on your Managed Instance. Here is an example of the script that can execute the BpCheck:

DECLARE @custompath NVARCHAR(500), @allow_xpcmdshell bit, @ptochecks bit, @duration tinyint, @logdetail bit, @diskfrag bit, @ixfrag bit, @ixfragscanmode VARCHAR(8), @bpool_consumer bit, @gen_scripts bit, @dbScope VARCHAR(256), @spn_check bit
--> Prerequisite for Managed Instance
SET @allow_xpcmdshell = 0 --(1 = ON; 0 = OFF)
--> /end prerequisite for Managed Instance
SET @duration = 10
SET @ptochecks = 1 --(1 = ON; 0 = OFF)
SET @spn_check = 0 --(1 = ON; 0 = OFF)
SET @diskfrag = 1 --(1 = ON; 0 = OFF)
SET @ixfrag = 1 --(1 = ON; 0 = OFF)
SET @ixfragscanmode = 'LIMITED' --(Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED)
SET @logdetail = 0 --(1 = ON; 0 = OFF)
SET @bpool_consumer = 1 --(1 = ON; 0 = OFF)
SET @gen_scripts = 0 --(1 = ON; 0 = OFF)
SET @dbScope = NULL --(NULL = All DBs)
EXEC bp_check @custompath, @dbScope, @allow_xpcmdshell, 
@ptochecks, @duration, @logdetail, @diskfrag,
@ixfrag, @ixfragscanmode, @bpool_consumer, 
@spn_check, @gen_scripts

There are several parameters that you can use to customize the checks. As an example, you can set @gen_scripts = 1 and BPCHECK will generate T-SQL scripts that you can run to fix the issues that are identified.

The only prerequisite is to set @allow_xpcmdshell = 0 because xpcmdshell is not supported on Managed Instance

Results

BpCheck returns several reports with the results about the various check in your databases.

Storage usage will show you how much storage you have on local storage and on remote storage (in General Purpose tier):

FreeSpace_MB is showing how much space you are can use — if this number goes close to zero you should increase the storage.

Increase the storage before these numbers go to 0. Storage change in Managed Instance instant operation and you might have a downtime/unavailability if your instance reaches storage limit until the change of storage size finishes.

Note: TotalSpace_MB seems wrong in the current version so you can ignore this info.

This result is specific for General Purpose storage tier where tempdb is placed on local SSD and data files are placed on remote Azure premium storage. In Business Critical tier there would be only one row.

Another interesting result is detection of databases with large virtual log file count:

Databases with high VLF can cause slower database log backups, which can throttle your input workload if log backups cannot catch-up incoming data.

BpCheck will also find the databases without recent DBCC CHECKDB. Managed Instance do not automatically run DBCC CHECKDB on every database because this might be expensive operation. Managed Instance checks the backups, but if there is a data corruption on some data page that is not covered by log backup, it will not be detected until the next full/differential backup is executed.

It is important to run DBCC CHECKDB on your databases.

Tiger team has recommended strategy for running DBCC CHECKDB in their Maintenance solution

• Weekly DBCC CHECKDB on all online, read-write user databases below 1TB.
• Daily combination of DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKTABLE (depending on VLDB setting) on all online, read-write user databases over 1TB.
• Weekly execution of DBCC UPDATEUSAGE on all online, read-write databases up to 4GB.
• Weekly execution of sp_createstats with indexonly.
• Weekly purge of all MSDB job history over 30d.
• Weekly purge of all maintenance plan text reports over 30d (if you are still using package based maintenance plans).

These are generic recommendations and you should review them to find the best strategy for your database.

Error log info — currently error logs in Managed Instance are hard to read because they are generating a large number of verbose log records. If you are reading error log you should use Dimitri Furman sp_readmierrorlog procedure that filters-out unnecessary verbose log records. BpCheck is also effective solution because it finds the most important error log messages that you should look for.

In addition, BpCheck gives you a summary of important messages in system health check session.

There are a lot of other useful information that con find in the results like:

• Information about wait statistics
• Recommendation to enable Optimize for ad hoc workloads setting on heavy OLTP ad-hoc workloads to conserve resources
• Warning about explicit hints and conversions that can affect query optimizer plan generation, inefficient memory, CPU or reads,
• Recommendations to update statistics
• Information about fragmented and unused indexes, which is very important since AUTOMATIC_TUNING feature in managed instance don’t support drop/rebuild indexes.
• Clustered indexes with GUID or nonunique values.
• No indexes on foreign keys
• Tables without clustered indexes
• Logins with weak password

Summary

BpCheck is one of the free/opensource community tools that can help you to more easily manage your data and find the potential issues in you databases placed on Managed Instance. I can make your job much easier because it will give you many important information that can help you to manage your databases. Note that there are other community tools that can help you, and you should use multiple tools/scripts to get the recommendations because every script might give you different useful information.