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.
Check our SQL Health Check case study!
Here is the complete list of items verified for an on-premise Microsoft SQL Server environment:
MS SQL Server Health Check Checklist
Server level configurations
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.
Services configurations
Anti-Virus settings
Multiple SQL Server instances
Unnecessary SQL services
SSMS missing updates
System BIOS updates
SQL services using non SA account
Instant file initialization access right
Dangerous SQL Server builds
SQL engine startup settings
CPU schedulers offline
Windows operating system settings
Windows page file configuration
Windows visual effects settings
Inspect task scheduler
Windows power plan optimal
Lock pages in memory setting
Page verification not optimal
Windows updates settings
Multiple RDP sessions actives
Instance parameters and options
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.
Security points
Workload using SA account
Accounts with elevated permissions
The CLR integration
Remote DAC disable
Instance options and features
Trace flags usage
SQL max memory settings
Priority boost enabled
Missing alerts
Deprecated features in use
Query store not in use
SQL server blocks, locks and deadlocks
Orphaned data files
DBCC shrink ran recently
Change tracking enabled
Default cost threshold for parallelism
Default max degree of parallelism
Wait statistics
SQL Server tempDB configuration
Default tempDB size settings
TempDB with a single data file
Database properties and level checks
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 Agent Jobs
Delayed durability not in use
Autoshrink ON
SQL agent jobs starting simultaneously
SQL agent jobs owned by non SA account
SQL agent jobs history settings
SQL agent jobs without notifications
Maintenance plans missing
Missing failsafe operator
Backup Health Checks
Backups failing
Backups on live DB drivers (bkp files on C drive)
MSDB BKPs history not being purged
Snapshot BKPs occurring
Unnecessary backups
Missing corruption checks
DBS missing BKPs
Backups compression settings
Full recovery mode without backup logs
Backups consuming high resources
BKPs over UNC
Simple recovery model usage
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 configurations
Storage formatting optimal
Drive performance
Slow storage latency by file
Storage perf or I/O warnings and problems
DB data file low on available space
Low disk space
All DB files on single disk
Filegroups and files layout
DB files layout optimal
system or users DBs on OS drive
High Virtual log files (VLF) counts
File growth options
Optimize for ad-hoc workloads
Large LDF file
Log files larger than MDF files
User tables in sys DBs
DB compatibility setting
Objects created with SET Options
Max DB file size set
DBS owner by non SA account
DB state offline
Data compression usage
Database with non-aligned indexes
Data and IXs within single filegroup/file
Filestream for large databases
Top SQL objects and performance checks
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.
Review top worst performers
Top TSQL by reads
Top TSQL by CPU
Top TSQL by execution count
Top SPs by CPU usage
Top SPs By execution count
Top SPs by I/O
Query plan analysis
Single-use plans for one query
Many plans for one query
Implicit data conversion
Database objects and tables designs
Active tables without clustering keys
Foreign keys or constraints that are not trusted
Tables over-indexed
Triggers usage
UDFs and Cursors usage
Queries forcing join and order hints
Indexes and statistics
Missing indexes
Useless and disabled indexes
Fill factor option
Auto-update statistics
Auto-update statistics asynchronous
User-created statistics
Want a FREE SQL Server health check sample report? Send an email using our contact us form, and say that you saw this post, and I will email it to you.
For fellow DBAs – what did I miss? What can be done better?
107 Responses
Hello,
Can you please send me a sample report?
Thank you.
Dheeraj Rao
emailed
Very well explained.
Can you please provide sample report?
emailed.
Hi I want sample script and health check report
Sorry for the long delay. Sample report emailed.
could you please provide sample report?
Many Thanks
Ed
I saw your post, could you share a sample report?
Sent. Thanks for reaching out!
please send sample report
Lirus, just sent you your SQL Health Check Report. Any questions – let me know! Thanks
I saw your post, could you send me a sample report?
Many thanks.
HS
Hi, HSolak. Report emailed. Enjoy!
emailed
Excellent piece of writing. Please allow me to have a copy of the sample report.
Regards And thank you.
Frank
Sent. Thanks for reaching out!
Hi Frank, report emailed.
Please sent me a sample report.
Sent. Thanks for reaching out!
emailed
Could you please provide a sample report?
Many Thanks
Eric
Sent. Thanks for reaching out!
Hi Eric, Sorry for the long delay. Sample report emailed.
Hi Can you please sent sample report.
Hi
Excellent and highly apprecieted the way explained and very documented.
could you please share the sample report
Could you please provide a sample report?
Hi,
Could you please provide a sample report?
Many Thanks!
Hi, Your inputs regarding MS SQL Server are excellent. Can I receive the sample report? Thanks, Regards
Emailed.
Thanks for sharing all the Details with us. Could you please share a sample report ?
Hi Bikram, I just emailed you the sql report. Sorry for the delay.
Great list. Could you please send a sample report?
Thanks and Regards,
Ash
Hi Ash, I just emailed you the sql report. Sorry for the delay.
Could you please send me a sample report? Thanks.
Hi Hugh, I just emailed you the sql report. Sorry for delay.
Could you please provide sample report
You probably already forgot you requested the SQL report, but I will email it to you in a few anyway 🙂 Sorry for delay.
Please sent me a sample report.
Thanks for the checklist.
Could you please provide the sample report?
Thanks for the checklist.
Could you please provide the sample report?
Hi Rob, Sorry for the super long delay. I just emailed you the sample report.
Could you please provide me a sample report?
Hi Mahesh, Sorry for the long delay. Just emailed you your sample report. Enjoy!
can you send me the sample report please?
Thanks in advance
Hi Vikas, Sorry for a long delay. Just emailed you your SQL Health Check Report. Enjoy!
emailed
I read your article at: https://red9.com/blog/sql-server-database-health-check-checklist/ and found it very helpful.
Could you please send me the sample health check report?
Have you checked out the PowerShell dbaChecks module: https://dbachecks.io ?
Many thanks!
Hi, Bill. Just checked out link you sent. Pretty cool. I will digging into it deeper. It looks like our SQL Health Check at least checks 95% of these already 🙂
Your SQL Health Check Sample email is on the way.
Any questions – let me know! Enjoy!
Good one.. Please share a sample report!
Hi, Uday! Report emailed. Any questions – let me know! Enjoy!
Hi, could you please share a sample report.
Thanks
Sorry for the long delay. Sample report emailed.
Thanks so much! Could you please provide a sample report?
Hi, Mike. Report emailed. Any questions – let me know! Enjoy!
Hi,
Please send a SQL sample report. Nice write up. Thank you. really appreciate
Sorry for the long delay. Sample report emailed.
Hi,
Please send a SQL sample report. Nice write up. Thank you. really appreciate
Kind regards,
Sorry for the long delay. Sample report emailed.
Hi please share the health check report
Sorry for the long delay. Sample report emailed.
Could you please send me a sample report?
Sorry for the long delay. Sample report emailed.
Can you pls provide sample report?
Sorry for the long delay. Sample report emailed.
Could you please send me a sample report?
Sorry for the long delay. Sample report emailed.
Hi There, would love to get the sample report.
Sorry for the long delay. Sample report emailed.
Hi, can you please send me a sample report?
Sorry for the long delay. Sample report emailed.
Excellent stuff. Kindly email a sample report for output evaluation
can you please share the sample data collection or email?
Hi, I saw your post, could you please share a sample report?
Thanks,
Hi Please send me sample health check report and its sql code
Hi Please can you send me sample report
Hi, all! This is a great, helpful article. I’d love to see a sample report if you are offering to still send one out!
Hi, Could you please send me the sample report?
Can you share me the sample report?
thanks for great explanation. would you please mail a single script which will help to get the complete report of server and db level.
Sorry, but we dont have TSQL scripts in a sharable ready format.
Hi ,
Can you please send me a sample report??
would you please share server and db health checks scripts
would you please share me sample report pls……
&
happy to inform you that you people have done wonderful work & it’s really helpful.
Hi, can you please send me a sample report?
Hi Could you send me a sample reports ?
I would like to see one of your reports. Thank you.
Could you please provide the sample report?
Could you please send the sample report?
Could you please send me a free sample report? Thanks!
send me a free sample report
Could you please send the sample report and the queries you are using if you have it handy if not thats okay .Thank you!
Could you please send me a free sample report? Thanks!
Can you please send a sample report. thanks for your help to SQL community
Hello, I have created a simple queries based on your explanations, thank you. Also could you please send the sample report?
Thanks!
Sample report plz
Could you Please provide sample report.
Can you please share the sample report
Could you please send me the sample report?
Hi Mark,
Could you kindly share Sample Report Plz?
Good afternoon, i saw this on red9 and I would like to request the sample report
Very useful !
Could you please send me the sample report please ?
thank you.
could you please email the script
could you please email me the script
This is interesting article. I’d like to see a sample report if you can send one!
Could you please send me a free sample report? Thanks!
Could you please provide me a sample report?
Can you email me sample report?