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 4 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.
| 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. The job name is constructed like this “FHSQLMonitor in {@fhSQLMonitorDatabase }” |
| @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 following parameters 26 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.
| Parameter | Default value | Reports affected |
|---|---|---|
| @enableAgentJobs | 1 | Agent jobs configuration |
| @enableAgeOfStatistics | 1 | Statistics |
| @enableBackupStatus | 1 | Backup |
| @enableConnections | 1 | Connections |
| @enableCPUUtilization | 1 | CPU |
| @enableDatabaseIO | 1 | Database IO |
| @enableDatabaseSize | 1 | Database size |
| @enableDatabaseState | 1 | Database configuration |
| @enableIndexOperational | 1 | Index leaf statistics Lock statistics |
| @enableIndexPhysical | 1 | Index physical |
| @enableIndexUsage | 1 | Index usage |
| @enableInstanceState | 1 | Instance configuration Instance status Resource Governor configuration |
| @enableMissingIndexes | 1 | Missing indexes |
| @enablePartitionedIndexes | 1 | Partitioned indexes |
| @enablePerformanceStatistics | 1 | Always On traffic Performance statistics Resource Governor |
| @enablePlanCacheUsage | 1 | Plan cache usage |
| @enablePlanGuides | 1 | Plan guides |
| @enableQueryStatistics | 1 | Query statistics |
| @enableTableSize | 1 | Table size |
| @enableTriggers | 1 | Triggers |
| @enableWaitStatistics | 1 | Wait statistics |
| @enableWhoIsActive | 1 | Who is active |
| @enableIndexRebuild | 0 | Index optimize Ola Hallengren errors |
| @enableIndexReorganize | 0 | Index optimize Ola Hallengren errors |
| @enableUpdateAllStatistics | 0 | Index optimize Ola Hallengren errors |
| @enableUpdateModifiedStatistics | 0 | Index 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, and if the installation to be upgraded are missing some of the indexes in the ver. 2.1 package.
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 February 2025 release version number 2.140.1205.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.pbix, for the first time you must change the above parameters to match your installation.
1) Go to Transform data and Edit parameters
2) In the Edit Parameters form you must fill in the values matching your installation.
In the example here only the parameter for the server name has been changed from localhost to SQL2022.
The rest of the parameters are the default ones.
3) Click Apply changes in warning dialog.
4) Specify the credentials to be used when connecting to the database.
5) Click OK if the Encryption Support dialog is shown.
6) Click Refresh to load data from the tool into the report.
7) After refresh the Front page in the report will show the parameters used in the report.
Close and save the report and the specified parameters are now saved as part of the report.
