SQL Server Monitoring – Installation

The blog is part of the blog series named SQL Server Monitoring and here it will be shown how to install the tool.

Download from GitHub

The tool can be downloaded from my GitHub repository, where you in the build folder find the SQL script, FHSQLMonitor.sql, and the Power BI report FHSQLMonitor.pbix.

Install and upgrade FHSQLmonitoring sql service

To install a new installation, or upgrade an already existing installation, the procedure are the same.

  • Open the FHSQLMonitor.sql in SQL Server Management Studio
  • Set the parameters in the installation file

    The following 5 parameters specifies how and where the tool will be installed or is already installed in case of an update.

    Also where the Ola Hallengren installation is installed in case it is in a different database.
ParameterDescription
@createSQLAgentJobIf set to 1 the corresponding agent job are created if it not already exists.

If set to 0 an already existing agent job will not be deleted.

The job name is constructed like this “FHSQLMonitor in {@fhSQLMonitorDatabase }”
@fhSQLMonitorDatabaseThe default name of the database to be used is “FHSQLMonitor”
@pbiSchemaThe default name of the schema used for the views that the Power BI Desktop loads data from
@blocksAndDeadlocksFilePathPath to directory where the service BlocksAndDeadlocks saves the extended events files.

If not set the directory used are the SQL Server LOG directory.
@olaDatabaseIn case that Ola Hallengren is installed in another database, specify the name of the database.

When upgrading and the parameter is at default NULL the current configuration will be used.

The following parameters 28 are only used during a fresh installation.
The parameters are not used during an update except if the service has not previously been installed.
When updating, the already configured values in the tables dbo.fhsmSchedules and dbo.fhsmRetensions remains unchanged.

ParameterDefault
value
Reports affected
@enableAgentJobs1Agent jobs configuration
@enableAgentJobsPerformance1Agent jobs performance
@enableAgeOfStatistics1Statistics
@enableBackupStatus1Backup
@enableBlocksAndDeadlocks1Blocks and deadlocks
@enableConnections1Connections
@enableCPUUtilization1CPU
@enableDatabaseIO1Database IO
@enableDatabaseSize1Database size
@enableDatabaseState1Database configuration
@enableIndexOperational1Index leaf statistics
Lock statistics
@enableIndexPhysical1Index physical
@enableIndexUsage1Index configuration
Index usage
@enableInstanceState1Instance configuration
Instance status
Resource Governor configuration
@enableMissingIndexes1Missing indexes
@enablePartitionedIndexes1Partitioned indexes
@enablePerformanceStatistics1Always On traffic
Performance statistics
Resource Governor
@enablePlanCacheUsage1Plan cache usage
@enablePlanGuides1Plan guides
@enableQueryStatistics1Query statistics
@enableTableSize1Table size
@enableTriggers1Triggers
@enableWaitStatistics1Wait statistics
@enableWhoIsActive1Who is active
@enableIndexRebuild0Index optimize
Ola Hallengren errors
@enableIndexReorganize0Index optimize
Ola Hallengren errors
@enableUpdateAllStatistics0Index optimize
Ola Hallengren errors
@enableUpdateModifiedStatistics0Index optimize
Ola Hallengren errors
  • Execute the installation script

A fresh installation takes a few seconds depending on your hardware of course.

An update can take some time depending on the amount of data.

New indexes will be created with page compression if the SQL server instance supports it.
Existing indexes will not be updated or in anyway changed to e.g. use page compression.

Configure Power BI report

The Power BI report requires that the program version of Power BI Desktop is the May 2025 release version number 2.143.878.0 or higher.

The Power BI Desktop report is parametrized and allows you to specify the following parameters:

ParameterDefault
value
Description
Server namelocalhostThe server where FHSQLMonitor is installed
Database nameFHSQLMonitorThe database where FHSQLMonitor is installed
Schema nameFHSMThe schema for the views used by the Power BI report.

It must match the installation parameter @pbiSchema
Data load listA comma separated list of services for which data should be loaded into the Power BI report.

The default value with an empty configuration string is that data for all services are loaded.

When opening the Power BI Desktop file, FHSQLMonitor.pbit, you can change the above parameters if needed to match your installation.

1) In the Edit Parameters form you must fill in the values matching your installation.

2) Specify the credentials to be used when connecting to the database.

3) Click OK if the Encryption Support dialog is shown.

4) After data load the Front page in the report will show the parameters used in the report.

Close and save the report as a PBIX file and the specified parameters are now saved as part of the report.