The blog is part of the blog series named SQL Server Monitoring and here it will be shown how:
Download from GitHub
The tool can be downloaded from my GitHub repository.
Go to the Build directory.
Download the files FHSQLMonitor.pbix and FHSQLMonitor.sql.
Install and upgrade FHSQLmonitoring sql service
To install a new installation, or upgrade an already existing installation, the procedure are the same.
Start SQL Server Management Studio (here SSMS 21), connect to the instance and remember to set the Command timeout to 0.
Open the FHSQLMonitor.sql in SQL Server Management Studio
Set the parameters in the installation file
The first 3 parameters specifies how and where the tool will be installed or is already installed in case of an update.
| Parameter | Description |
|---|---|
| @fhSQLMonitorDatabase | The default name of the database to be used is “FHSQLMonitor” |
| @pbiSchema | The default name of the schema used for the views that the Power BI Desktop loads data from |
| @olaDatabase | In 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 next 3 parameters are only used during a fresh installation, and controls creation of the SQL Server agent job and the file location for the files holding the Blocks and Deadlocks data.
The parameters are not used during an update except if the service has not previously been installed.
| Parameter | Description |
|---|---|
| @createSQLAgentJob | If 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. |
| @blocksAndDeadlocksFilePath | Path to directory where the service BlocksAndDeadlocks saves the extended events files. If not set the directory used are the SQL Server LOG directory. |
| @fhsqlAgentJobName | The name of the SQL Agent job name. The job name is constructed like this “FHSQLMonitor in {@fhSQLMonitorDatabase }” |
The following 27 parameters 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.
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.
Enable SQL agent job
After installing and you choosed to create the SQL agent job by setting the installation parameter @createSQLAgentJob to 1, the SQL agent job is created but disabled.
To enable the SQL agent job right-click on it and select Enable.
The services will now start executing by the configured schedule and collect data.
Connect report
The Power BI report requires that the program version of Power BI Desktop is the August 2025 release version number 2.146.1454.0 or higher.
The Power BI Desktop report is parametrized and allows you to specify the following parameters:
| Parameter | Default value | Description |
|---|---|---|
| Server name | localhost | The server where FHSQLMonitor is installed |
| Database name | FHSQLMonitor | The database where FHSQLMonitor is installed |
| Schema name | FHSM | The schema for the views used by the Power BI report. It must match the installation parameter @pbiSchema |
| Data load list | A 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.
