Sql Server Health Checks

miniDBA provides the ability to run a large number of server and database specific health checks and return recommendations for actions based on the results.
  • The server health check screen runs performance and configuration diagnostic tests against the SQL Server and gives warnings & recommendations based on what it finds.
  • The diagnostics include checks of disk, memory, security, agent jobs, query optimisations and other common performance pitfalls.
  • Results are either "Warning" - action should be taken to remedy the problem; "Info Only" - a definite course of action cannot be given but user experimentation should be performed or "Ok" - the test passed.

Clicking on a warning in the grid will display and related details in the textbox at the bottom of the screen. These may include information like table and files effected by the health check and amounts such as disk sizes of over thresholds.

Also related links to helpful web pages are listed where appropriate to help you understand the health check. These links are continually updated.

SQL Server Health Check List

Server specific health checks:
  • Potential OS Memory Swapping
  • Resource Governor Enabled
  • xp_cmdshell enabled
  • Plan Guides In Use
  • Is a Cluster Node
  • Query hints - sort order
  • Query hints - join
  • Slow reads
  • Slow writes
  • Suspect Pages
  • Active Trace Flags
  • TempDb only has 1 data file
  • User tables in master database
  • Server level triggers
  • Startup procedures
  • System databases on C drive
  • No SQL Agent Operator Enabled
  • No SA owned jobs
  • Actionless sys alerts
  • Security Admins Enabled
  • User Endpoints Started

Database Health Check List

Database specific health checks:
  • MAXDOP used
  • Unpartitioned Large Tables
  • Scalar functions
  • Wildcard used to begin search
  • Computed column not persisted
  • ExecSql not used
  • Slow reads
  • Slow writes
  • Forced Parameterization Enabled
  • Disabled Indexes
  • Triggers On User Tables
  • No Clustered Index On Table
  • Collation different to server
  • Auto Create Stats
  • Involved In Replication
  • Untrusted Check Constraints
  • Untrusted FK constraints
  • Is Auto Close Disabled
  • Full Backups Within Last Month
  • File % Growth
  • Files On C Drive
  • Uneven file growth
  • Log Larger Than Data File
  • Multiple Log Files On A Drive
  • Auto Update Stats Enabled
  • Auto Shrink Disabled
  • Optimal page verification
  • Is data correlation on
  • Non Aligned Indexes
  • Snap shot source
  • Old Compatability Level
  • Procedures 'with recompile'
  • Missing Indexes
  • Unused Indexes

Custom Server Health Checks

  • As well as the included checks mentioned above you can create am unlimited number of custom T-SQL health checks.
  • You dictate what passes or fails the healcheck making this great for testing metrics not included in miniDBA by default such as metrics produced by your application
  • A timeout value in seconds is used to ensure the custom T-SQL does not block or become long running.