Updated May 1, 2022.
Purpose
To get a quick overview of the instance and it’s configuration and if there are settings that should be changed.
Sources
This feature queries a number of DMV’s and it is heavily inspired by the SQL Server Diagnostic Queries made by Glenn Berry.
I will encourage you to deep dive into Glenn’s diagnostic queries as well as see his video’s where he detailed explains what to look after when you troubleshoot a SQL server installation.
Reports
The feature has 2 report pages.
#1 General Instance State
#2 Resource Governor Configuration
#1 – General Instance State
The report page Instance State has 6 tables showing selected information about the instance and where the settings that should be considered changed are colored yellow.
Trace flags
Depending on the SQL server version there are different trace flags recommended.
The trace flag recommendations are stored in the table dbo.fhsmTraceFlags including links to pages made by either Glenn Berry or Microsoft explaining why these trace flags should be configured.
When hovering over the table a tooltip is showing the history regarding trace flag changes.
Services
The output here is based on the DMV sys.dm_server_services which returns information about the SQL Server, Full-Text, SQL Server Launchpad service (SQL Server 2017+), and SQL Server Agent services in the current instance of SQL Server.
Configurations
The output here is based on the DMV sys.configurations which returns all the server-wide configuration options in the instance.
The table dbo.fhsmInstanceState contains all the configuration options but the Configurations table only shows the following options and also only if the SQL server version supports it:
- 1517 – Priority boost
- 1538 – Cost threshold for parallelism
- 1539 – Maximum degree of parallelism
- 1544 – Maximum size of server memory (MB)
- 1546 – User mode scheduler uses lightweight pooling
- 1562 – CLR user code execution enabled in the server
- 1576 – Dedicated Admin Connections are allowed from remote clients
- 1579 – Enable compression of backups by default
- 1581 – When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.
- 1584 – Enable checksum of backups by default
- 1585 – Automatic soft-NUMA is enabled by default
- 1589 – Tempdb metadata memory-optimized is disabled by default.
When hovering over the table a tooltip is showing the history regarding configuration changes.
Agent jobs
This table lists all agent jobs and color them yellow if:
- They are not enabled
- There is not schedules enabled
- There is no e-mail operator configured
- The notification level is set to not send e-mail
Agent alerts
The table lists a number of agent alerts and if they are not configured they are colored yellow:
- Error 823 – The operating system returned an error
- Error 824 – Logical consistency-based I/O error
- Error 825 – Read-Retry Required
- Error 832 – Constant page has changed
- Error 855 – Uncorrectable hardware memory corruption detected
- Error 856 – SQL Server has detected hardware memory corruption, but has recovered the page
- Severity 19 – Fatal Error in Resource
- Severity 20 – Fatal Error in Current Process
- Severity 21 – Fatal Error in Database Processes
- Severity 22 – Fatal Error: Table Integrity Suspect
- Severity 23 – Fatal Error: Database Integrity Suspect
- Severity 24 – Fatal Error: Hardware Error
- Severity 25 – Fatal Error
Instance hardware
The output here is based on the DMV sys.dm_os_sys_info which returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.
The table dbo.fhsmInstanceState contains all the information from the DMV but the Hardware table only shows the following options:
- Cores per socket
- CPU count
- Max workers count
- NUMA node count
- Physical memory MB
- Processor
- Scheduler count
- Socket count
- SQL memory model
- SQL Server and OS Version Info
- SQL server start time
- Virtual machine type
When hovering over the table a tooltip is showing the history regarding instance hardware changes.
Dump files
The output here is based on the DMV sys.dm_server_memory_dumps which returns information about the dump files existing on the SQL Server.
Suspect pages
The output here is based on the DMV msdb.suspect_pages which returns information about suspect pages on databases reporting the file and page id that is suspect.
#2 – Resource Governor Configuration
The report page Resource Governor Configuration has 6 tables showing the different configuration parameters.
Model
The model for the report uses the facts [Agent alerts], [Agent jobs],[Instance configuration], [Instance configuration history], [Instance dump files], [Instance hardware], [Instance hardware history], [Instance services], [Instance suspect pages], [Trace flags] and [Trace flags history].
Configuration
There is no configuration for this feature.
Scheduling
The default schedule for the feature is once every hour.
Retention
The default retention for the table dbo.fhsmInstanceState is 1.825 days (5 years).
