SQL Health Checks
Healthy SQL is an important topic… I’ve gotten involved in a number of SQL health checks and would like to share some of the key methodology, metrics investigated and tools/utilities used.
There are at least two main components to this topic:
- Review of existing SQL Server implementations
- Developing on-going monitoring to detect small issues before they become large ones (think alerting)
This article will focus on the first component; a review of a SQL Server instance or instances that may have not been touched since the initial install. It happens with some of the smaller clients that do not have a local DBA. Or, these SQL instances have been modified, post installation, by a party or parties that are now, no longer there!
What follows are the main categories and items checked, with a few links to relevant content. My personal preference for these SQL health check reviews; is to start at the 10,000 foot view, then work my way down to the items or items that deserve closer scrutiny. For example, some reviews I’ve seen ignore the server platform… if there are performance issues at that level, this can adversely affect SQL Server performance!
Following this checklist, there are some of the tools/utilities that I’ve found useful or have had to create to assist in the documentation effort.
Note: By no means is this meant to be inclusive list, primarily the important, high-level topics that should checked.
Windows Server Platform
- Operating System Version / Service Pack: running on latest, supported OS and SP for that platform?
- Physical / Virtual: investigate platform for optimal settings
- Server security: is the server physically secure, and are the accounts with local administrative rights appropriate?
- Security Updates: is the Windows Server up-to-date with the latest security updates?
- Logical disk configuration: drive space; total and available
- NFTS block sizes: drives dedicated for SQL Server set to 64KB?
- Performance: set up a performance (Perfmon) trace logging to a file for at least a 24 hour period, sampling every 5 minutes. Use appropriate performance counters for both Windows Server and SQL Server. Then review. Look for potential bottlenecks, and address. Save the performance log files; they become a good comparison point or baseline. If available, SolarWinds can be an excellent asset for examination of server and SQL performance.
- Page file: located on a drive other than logical C: ? Is the page file in use?
- SQL Server install location: installed on a separate drive, not on logical C: ?
- SQL Server install date
- Current configuration values – sp_configure – consider enabling advanced options here to view everything
- SQL Server memory allocation: max memory value set? (link for use of SQL server with ConfigMgr 2012)
- Database Names, paths, file size and location
- Database properties: owner, recovery model, log size and log percent in use
- I/O Rank: list database rank by IO (MB) – may help prioritize database investigation
- TempDB: sized appropriately, located on a dedicated drive?
- SQL Security: Mixed vs. Windows security mode, review sysadmin list for appropriate membership
- Monitoring: System Center Operations Manager 2012 or something else?
- Database / transaction log files: Auto-growth set appropriately?
- Statistics: last updated date fairly recent?
- Index fragmentation: Examine Average fragmentation, page counts, etc.
- Virtual Log Files (VLF) counts: while written with a bias toward ConfigMgr 2012, the concept applies to any database
- Last backup date: backups properly scheduled and restores tested?
Tools / Utilities used
Custom PowerShell SQL Audit script – Many, if not all of the above metrics can be captured by PowerShell and saved as an XLS workbook. This helps provide a baseline, the bonus is available documentation that can be shared and provides a guideline for any future changes. And, the PS script can be extended as needed.
Using Ola Hallengren’s Maintenance scripts – optimizing ConfigMgr 2012 databases applies to any database that needs to have the indexes and statistics kept up-to-date. There is a link from that blog to Ola’s open source scripts.
Diagnostic Queries – some great queries that I recommend, and have modified on occasion for specific purposes. A few of these have found their way into the Custom PowerShell script ;) (Thanks Glenn)
Windows Performance Monitor – I don’t need to explain this one, do I? ;)
Microsoft Assessment and Planning Toolkit – I’ve found this solution accelerator to be very useful for mapping SQL Server installations in new environments. Especially, where the client is not always sure what is installed.
SQLIO Disk Subsystem Benchmark Tool – On occasion, where SQL appears to have difficulties with the disk subsystem. SQLIO can help determine I/O capacity of a given system.
The above guidelines should provide a good start to determining SQL Health. As mentioned, these guidelines are in no means meant to be inclusive. Many times, this preliminary information will lead to deeper investigation. Additional work may be indicated (as needed) to investigate specific application interaction with SQL Server.
The challenge – for full details: http://www.sqlservercentral.com/blogs/pearlknows/2015/01/06/t-sql-tuesday-62-invitation-to-healthysql/