Our FULL SQL Server health check will cover a complete SQL Server health checklist of items on-premises and cloud-based DBMS services.
Hardware, operating system, SQL server configuration, security (users), availability, load (amount and type of access), operation (maintenance and monitoring plans), disaster recovery, and much more will be analyzed.
Some clients want to check server health (FULL SQL Health Check), while some only want to go deep on analysis for a SINGLE database health check.
Even though it is a single DB health analysis, we still check for best practices and optimal settings in server and instance-level items, covering performance, reliability, and security points.
That is intentional.
After the SQL health check report is done and presented to you, we will go over everything on the report until you run out of questions.
All the SQL checks are non-intrusive, very lightweight, and will not affect the production SQL Server. Data collection can be done during regular business operating hours.
This is about 90% of what will happen. The remaining 10% will be spent on items discovered during analysis, requiring more investigation. So, we will drill into that more as we find stuff.
Here is the complete list of items verified for an on-premise Microsoft SQL Server environment:
Server level configuration
Ensure that all your SQL Server services are working correctly according to your needs.
A comprehensive methodology will be applied to look at various levels SQL depended on NUMA, Clustering and failover, BIOS, VMWare setup, networking, and few more areas.
It may seem like an overkill to look at all the layers, but Red9 practices prove that comprehensive analysis is useful. Especially when stability and speed matter.
Check out some of the items below.
Windows operating system settings
Windows OS page file configuration
Windows OS visual effects settings
Inspect Windows Task Scheduler
Windows OS Power Plan optimal
Lock pages in memory (LPM) setting
Page verification not optimal
Windows OS updates settings
Multiple RDP sessions
SQL Instance configuration
Is your SQL Server with the latest SQL Service Pack and Cumulative Updates? How about SQL Server Management Studio? Are the parallelism settings matching the workload? Is deadlocking happening? How heavy is tempDB usage?
Are the features being appropriately utilized? Make sure you are taking advantage of some of the less known features.
We will isolate top server waits since SQL instance starts and for 30-60 min (production workload) to determine the client’s main pains.
When your database is corrupt, hacked, or destroyed – slow queries will not matter. So, that is why we will address the Reliability and Security first.
SQL instance options and features
Trace flag usage
SQL max RAM memory settings
Priority boost enabled
Deprecated features in use
Query store not in use
Orphaned data files
DBCC shrink ran recently
Change tracking (CDC) enabled
Default cost threshold for parallelism
Default max degree of parallelism
SQL Server tempdb config
The purpose is to determine if your database’s properties for each of your SQL Server instances are appropriately configured for your particular environment.
How SQL Agent jobs are setup? Is there a Maintenance plan? How are DB backups set up? When was the last corruption check date? Is the transaction log larger than the DB data file?
Our goal is to ensure the highest SQL Server performance and availability of your databases.
Database options and SQL Agent Jobs
SQL database owned by non-SA account
Backup Health Checks
Backups on drives where DB files are and backups on C:\
MSDB history not being purged
Snapshot backups occurring
Missing corruption checks
Databases missing backups
Full recovery mode without log backup
Architectural design overview
How is the majority of TSQL coming? Is it ad-hoc SQL queries or stored procs? Are files placed on best matching drives? How often do DB files grow? What causes the delay? Are you using third-party monitoring tools?
What is the read vs. write ratio at the DB file level? This will show if the workload is read or write-intensive, which changes how tuning is done.
We will test drive latencies and review how well DB files are placed across storage drives.
Also, our team will find top used objects: look at the sample data, data types in use, indexes, foreign keys, etc.
Disks and storage configuration
Filegroups and files layout
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
Database owner is non-SA account
DB state offline
Data compression not in use
Data and indexes within single filegroup or file
Filestream usage for large databases
Performance checks of top SQL objects
What are the tables with the most activity? What are the largest tables?
Are there tables without clustering keys? Are there any foreign keys that are not trusted?
Red9 will review top objects (usually up to 5-10 is sufficient) to see how well data type choices were made, indexing, constraints, triggers, statistics on those tables.
Which TSQL runs most frequently? Takes longest to execute? Which Consumes the most CPU, RAM and I/O (disk)?
Anything that costs too much in resources? If TSQL duration is sometimes fast and sometimes very slow, this is an opportunity for performance tuning.
Is there implicit data conversion happening? Are forced join hints? Are triggers being used for auditing? Are there UDFs in use? If yes, we will review how bad.
Additionally, our team will check how up to date are statistics and fragmentation levels.
The database indexes will be carefully analyzed – Often the fastest way to add speed to DB performance.
Investigate top worst server resources consumers
Query plan analysis
Database objects and tables design