SQL Server Health Check Articles
Whether you're brand new to SQL Server Health Checks, or want to learn advanced strategies, this is your hub for SQL knowledge.
Most Popular Posts
SQL Server Health Check SQL Server Health Check How High Virtual Log File (VLF) Count Kills SQL Performance
SQL Server Health Check Ola Hallengren Maintenance for SQL Server [Full Guide]
SQL Server Health Check Optimal Window’s OS Page File Settings For SQL Server
SQL Server Health Check SQL Server Agent Job History Retention
SQL Server Health Check TempDB Best Practices for Optimal Performance
Server-level configuration
Services configurations
Ensure all your SQL Server services are functioning as required.
Anti-virus settings
Multiple SQL Server instances
Unnecessary SQL services
SSMS missing updates
System BIOS updates
Windows OS updates settings
SQL services using non-SA account
Instant file initialization (IFI) access right
Dangerous SQL Server builds
SQL engine startup settings
CPU schedulers offline
SQL Server memory dumps
SQL Server error logs are not optimal
Windows operating system settings
Optimized OS settings ensure stable and efficient system performance.
SQL Server instance configuration
Security
Make sure your SQL Server security settings are up-to-date and robust.
Workload using SA account
Accounts with elevated permissions
CLR integration
Remote DAC
SQL instance options and features
Check SQL instance settings and features for optimal configuration and reliability.
Trace flag usage
SQL max RAM memory settings
Priority boost enabled
Missing alerts
Deprecated features in use
Query store not in use
SQL deadlocks
Orphaned data files
DBCC shrink run recently
Change tracking (CDC) enabled
Default cost threshold for parallelism
Default max degree of parallelism
Wait statistics
Auto update statistics ASYNC not optimal
tempdb settings
Check if tempdb is configured for the best performance.
Database properties
Database options and SQL Server Agent Jobs
Review SQL Agent job setups, maintenance plans, and database configurations for optimal performance.
Delayed durability
Auto shrink ON
SQL Server Agent jobs starting simultaneously
SQL database owned by non-SA account
SQL objects owned by non-SA account
SQL Server Agent jobs owned by non-SA account
SQL Server Agent jobs history settings
SQL Server Agent jobs without notifications
Missing failsafe operator
Log files larger than MDF files
Backup health checks
Verify backup processes, corruption checks, and storage configurations to ensure data integrity and availability.
Architectural design overview
Disks and storage configuration
Optimize storage by analyzing drive performance and file distribution.
Filegroups and file layout
Streamline performance by optimizing filegroups, managing VLF counts, and ensuring precise DB file configurations.
DB files layout optimal
System or user databases placed on C:\
High Virtual Log Files (VLF) counts
DB file growth options
Optimize for ad-hoc workloads
LDF file too large
LDF file larger than MDF
User tables in system databases
DB compatibility setting
Objects created with SET Options
Maximum DB file size is set
The database owner is a non-SA account
DB state offline
Data compression is not in use
Non-aligned indexes
Data and indexes within a single filegroup or file
FILESTREAM usage for large databases
System DB on OS drive
Performance checks of top SQL Server objects
Identify top resource-heavy queries and processes
Find high-impact T-SQL and SPs, assess backups, and review memory usage.
Query plan analysis
Detect single-use/multiple query plans, implicit conversions, and RECOMPILE usage.
Database objects and tables design
Review tables without clustering keys, untrusted constraints, triggers, cursors, and forced hints.
Indexes and statistics
Check for missing, useless indexes, over-indexing, and outdated statistics.
Discover More
Discover what clients are saying about Red9
Red9 has incredible expertise both in SQL migration and performance tuning.
The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.
Rich StaatsCloud EngineerMetalToad