Original posted September 26, 2020 but has now, May 1, 2022, been updated.
The blog is part of the blog series named SQL Server Monitoring and here it will be shown how to install the tool FHSQLMonitor.
Download from GitHub
The tool can be downloaded from my GitHub repository, where you find the SQL scripts files as well as the Power BI Desktop report.
Installing SQL parts
The installation order of the SQL script files is alphabetic and should be taken in that order or else you will see warnings or errors.
| Scripts | Action performed |
|---|---|
| _Install-FHSQLMonitor.sql | The script installs the framework including creation of database, functions, schema, stored procedures, tables and agent job. Parameters: In the top of the SQL script there are 3 parameters you can change if you wish. The defaults parameter values will create a database named 'FHSQLMonitor', a schema named FHSM and a SQL agent job. - SET @createSQLAgentJob = 1; - SET @fhSQLMonitorDatabase = 'FHSQLMonitor'; - SET @pbiSchema = 'FHSM'; Tables created: - dbo.fhsmConfigurations - dbo.fhsmDimensions - dbo.fhsmLog - dbo.fhsmRetensions - dbo.fhsmSchedules Views created: - <pbiSchema>.[Junk dimensions] (Dimension) - <pbiSchema>.[Log] (Fact) - <pbiSchema>.[Retentions] (Fact) - <pbiSchema>.[Schedules] (Fact) Functions created: - dbo.fhsmFNGenerateKey (table-valued) - dbo.fhsmFNGetConfiguration (scalar) - dbo.fhsmFNGetExecutionDelaySec (scalar) - dbo.fhsmFNGetTaskParameter (scalar) - dbo.fhsmFNParseDatabaseStr (table-valued) - dbo.fhsmFNSplitString (table-valued) - dbo.fhsmFNIsValidInstallation (scalar) - dbo.fhsmFNTryParseAsInt (scalar) Stored procedures created: - dbo.fhsmSPCleanup - dbo.fhsmSPExtendedProperties - dbo.fhsmSPLog - dbo.fhsmSPSchedules - dbo.fhsmSPUpdateDimensions |
| AgentJobs.sql | The script installs the feature Agent Jobs. Tables created: - dbo.fhsmAgentJobs Views created: - <pbiSchema>.[Agent jobs - grid] (Fact) - <pbiSchema>.[Agent jobs - list] (Fact) Stored procedures created: - dbo.fhsmSPAgentJobs |
| BackupStatus.sql | The script installs the feature Backup Status. Tables created: - dbo.fhsmBackupStatus Views created: - <pbiSchema>.[Backup Age] (Fact) - <pbiSchema>.[Backup Status] (Fact) - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPBackupStatus |
| ConnectionStatistics.sql | The script installs the feature Connection Statistics. Tables created: - dbo.fhsmConnections Views created: - <pbiSchema>.[Connections] (Fact) - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPConnections |
| CPUStatistics.sql | The script installs the feature CPU Statistics. Tables created: - dbo.fhsmCPUPerDatabase - dbo.fhsmCPUUtilization Views created: - <pbiSchema>.[CPU per database] (Fact) - <pbiSchema>.[CPU utilization] (Fact) - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPCPUUtilization |
| DatabaseIO.sql | The script installs the feature Database IO. Tables created: - dbo.fhsmDatabaseIO Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Database file] (Dimension) - <pbiSchema>.[Database IO] (Fact) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPDatabaseIO |
| DatabaseSize.sql | The script installs the feature Database Size. Tables created: - dbo.fhsmDatabaseSize Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Database file] (Dimension) - <pbiSchema>.[Database size] (Fact) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPDatabaseSize |
| DatabaseState.sql | The script installs the feature Database State. The feature uses code and ideas from the SQL Server Diagnostic Queries made by Glenn Berry. Tables created: - dbo.fhsmDatabaseState Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Database state] (Fact) - <pbiSchema>.[Database state history] (Fact) Stored procedures created: - dbo.fhsmSPDatabaseState |
| IndexOperational.sql | The script installs the feature Index Operational. Tables created: - dbo.fhsmIndexOperational - dbo.fhsmIndexOperationalReport Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index operational] (Fact) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPIndexOperational |
| IndexOptimize-001-OlaHallengren-CommandLog.sql IndexOptimize-002-OlaHallengren-CommandExecute.sql IndexOptimize-003-OlaHallengren-IndexOptimize.sql IndexOptimize-004.sql | The script installs the feature Index Optimize. The feature is a wrapper around the SQL Server Index and Statistics Management solution made by Ola Hallengren. The Ola Hallengren objects used are the table dbo.CommandLog and the stored procedures dbo.CommandExecute and dbo.IndexOptimize, and they are used as is without any modifications. Tables created: - dbo.CommandLog Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index optimize] (Fact) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.CommandExecute - dbo.IndexOptimize - dbo.fhsmSPIndexOptimize |
| IndexPhysical.sql | The script installs the feature Index Physical. Tables created: - dbo.fhsmIndexPhysical Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index type] (Dimension) - <pbiSchema>.[Index alloc type] (Dimension) - <pbiSchema>.[Index physical] (Fact) - <pbiSchema>.[Index physical detailed] (Fact) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPIndexPhysical |
| IndexUsage.sql | The script installs the feature Index Usage. Tables created: - dbo.fhsmIndexUsage Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index usage] (Fact) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPIndexUsage |
| InstanceState.sql | The script installs the feature Instance State. The feature uses code and ideas from the SQL Server Diagnostic Queries made by Glenn Berry. Tables created: - dbo.fhsmAgentAlerts - dbo.fhsmInstanceConfiguration - dbo.fhsmInstanceState - dbo.fhsmTraceFlags Views created: - <pbiSchema>.[Agent alerts] (Fact) - <pbiSchema>.[Agent jobs] (Fact) - <pbiSchema>.[Instance configurations] (Fact) - <pbiSchema>.[Instance configurations history] (Fact) - <pbiSchema>.[Instance dump files] (Fact) - <pbiSchema>.[Instance hardware] (Fact) - <pbiSchema>.[Instance hardware history] (Fact) - <pbiSchema>.[Instance services] (Fact) - <pbiSchema>.[Instance suspect pages] (Fact) - <pbiSchema>.[Resource governor configuration] (Fact) - <pbiSchema>.[Resource governor external resource pool affinity] (Fact) - <pbiSchema>.[Resource governor external resource pools] (Fact) - <pbiSchema>.[Resource governor resource pool affinity] (Fact) - <pbiSchema>.[Resource governor resource pools] (Fact) - <pbiSchema>.[Resource governor workload groups] (Fact) - <pbiSchema>.[Trace flags] (Fact) - <pbiSchema>.[Trace flags history] (Fact) Stored procedures created: - dbo.fhsmSPInstanceState |
| MissingIndexes.sql | The script installs the feature Missing Indexes. Tables created: - dbo.fhsmMissingIndexes Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Missing indexes] (Fact) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPMissingIndexes |
| PerformanceStatistics.sql | The script installs the feature Performance Statistics. The feature uses code and ideas from the stored procedure sp_BlitzFirst which is part of the First Responder Kit made by Brent Ozar. Tables created: - dbo.fhsmPerfmonCounters - dbo.fhsmPerfmonStatistics Views created: - dbo.fhsmPerformStatisticsActual (Helper view) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Performance counter] (Dimension) - <pbiSchema>.[Performance statistics] (Fact) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPPerfmonStatistics |
| PlanCacheUsage.sql | The script installs the feature Plan Cache Usage. Tables created: - dbo.fhsmPlanCacheUsage Views created: - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Plan cache usage] (Fact) Stored procedures created: - dbo.fhsmSPPlanCacheUsage |
| PlanGuides.sql | The script installs the feature Plan Guides. Tables created: - dbo.fhsmPlanGuides Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Plan guides] (Fact) Stored procedures created: - dbo.fhsmSPPlanGuides |
| QueryStatistics.sql | The script installs the feature Query Statistics. Tables created: - dbo.fhsmQueryStatement - dbo.fhsmQueryStatistics - dbo.fhsmQueryStatisticsReport - dbo.fhsmQueryStatisticsReportTemp - dbo.fhsmQueryStatisticsTemp Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Query statements] (Fact) - <pbiSchema>.[Query statistic] (Dimension) - <pbiSchema>.[Query statistics] (Fact) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPQueryStatistics |
| StatisticsAge.sql | The script installs the feature Statistics Age. Tables created: - dbo.fhsmStatisticsAge - dbo.fhsmStatisticsAgeIncremental Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index partition] (Dimension) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Object partition] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Statistics age] (Fact) - <pbiSchema>.[Statistics age detailed] (Fact) - <pbiSchema>.[Statistics age incremental] (Fact) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPStatisticsAge |
| TableSize.sql | The script installs the feature Table size. Tables created: - dbo.fhsmTableSize Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Index] (Dimension) - <pbiSchema>.[Index partition] (Dimension) - <pbiSchema>.[Object] (Dimension) - <pbiSchema>.[Object partition] (Dimension) - <pbiSchema>.[Schema] (Dimension) - <pbiSchema>.[Table size] (Fact) - <pbiSchema>.[Time] (Dimension) Stored procedures created: - dbo.fhsmSPSpaceUsed (Helper stored procedure) - dbo.fhsmSPTableSize |
| Triggers.sql | The script installs the feature Triggers. Tables created: - dbo.fhsmTriggers Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Triggers] (Fact) Stored procedures created: - dbo.fhsmSPTriggers |
| WaitStatistics.sql | The script installs the feature Wait Statistics. The feature uses code and ideas from the stored procedure sp_BlitzFirst which is part of the First Responder Kit made by Brent Ozar. Tables created: - dbo.fhsmWaitCategories - dbo.fhsmWaitStatistics Views created: - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) - <pbiSchema>.[Wait statistics] (Fact) - <pbiSchema>.[Wait type] (Dimension) Stored procedures created: - dbo.fhsmSPWaitStatistics |
| WhoIsActive-001-AdamMachanic-12.00-sp_WhoIsActive.sql WhoIsActive-002.sql | The script installs the feature Who Is Active. The feature is a wrapper around the Who Is Active solution made by Adam Machanic. The Adam Machanic stored procedure dbo.sp_WhoIsActive is used as is without any modifications. Tables created: - dbo.fhsmWhoIsActive Views created: - <pbiSchema>.[Database] (Dimension) - <pbiSchema>.[Date] (Dimension) - <pbiSchema>.[Time] (Dimension) - <pbiSchema>.[Who is active] (Fact) Stored procedures created: - dbo.sp_WhoIsActive - dbo.fhsmSPWhoIsActive |
Configuration and scheduling of services
To get information about how the features are configured and scheduled use the links above in the table pointing to individual features.
Connecting Power BI Desktop report
The Power BI Desktop report is parametrized and allows you to specify the following parameters:
- Server name
- Database name
- Schema
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. Here I had only to change the server name from localhost to SQL2012 which is the SQL server where I installed the tool.
3) Click Apply changes in warning dialog.
4) Specify the credentials you are going to use when connecting to the database. Here the default is used which is integrated authentication.
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 Start 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.
