Finding Misconfigurations With One Script

A server can run for years with a setting nobody remembers choosing. Finding misconfigurations with one script is a way to surface those choices before they become an outage explanation.

Define a Finding Before Running a Checker

Best-practice analyzers are useful because they make forgotten settings visible. They are less useful when a warning is treated as a universal command. Max server memory, MAXDOP and cost threshold for parallelism depend on hardware and workload. PAGE_VERIFY is a database setting with a clearer desired state for most user databases. A checker should report facts, context and a reason to review, not silently rewrite configuration.

I start with a script that can run on an unfamiliar SQL Server instance without changing it. Which warnings are actionable on this system, and which are intentional exceptions? That question needs an owner and evidence. A red label alone does not know whether the server is dedicated, virtualized, consolidated or running a special application. The first pass at finding misconfigurations is inventory, not a tuning contest.

Finding Misconfigurations in Instance-Level Configuration

The first result set reports configured and running values for a small group of instance settings. value is what has been configured; value_in_use is what the engine is currently using. A difference can indicate a restart or reconfiguration is still needed. The selected names are documented sys.configurations entries and keep the script portable across ordinary SQL Server installations.

I do not hard-code a preferred MAXDOP or memory number in the query. A good threshold needs the machine’s available memory, CPU topology, other services and actual workload. The output gives a review starting point. When I inherit a server, the most valuable surprise is sometimes that a value was never intentionally chosen. Defaults have excellent public relations and limited knowledge of your host.

SELECT name,
       value AS configured_value,
       value_in_use AS running_value,
       is_dynamic,
       description
FROM sys.configurations
WHERE name IN
(
    N'max server memory (MB)',
    N'min server memory (MB)',
    N'max degree of parallelism',
    N'cost threshold for parallelism',
    N'backup compression default',
    N'optimize for ad hoc workloads'
)
ORDER BY name;

Inspect Database-Level Protection When Finding Misconfigurations

The second result set checks database state, recovery model and page verification. CHECKSUM is the expected page verification setting for a modern SQL Server database unless a documented exception exists. OFFLINE or RESTORING databases deserve different interpretation from an online production database. Recovery model also has to match the backup and restore objective; FULL without a log-backup chain is not a successful recovery plan.

I include system databases in the display for visibility, then filter to user databases when making application decisions. The script flags settings for review but does not call every SIMPLE recovery model a mistake. A development database with no point-in-time recovery requirement can be perfectly deliberate. The moment a checker forgets purpose, it becomes a very confident source of noise.

SELECT name,
       state_desc,
       recovery_model_desc,
       page_verify_option_desc,
       is_auto_close_on,
       is_auto_shrink_on
FROM sys.databases
ORDER BY database_id;

Make Finding Misconfigurations One Review Script

Run the two SELECT statements together as one read-only script. The first result set is the instance checklist; the second is the database checklist. Save the output with the server name, collection time and ticket or review record. Add a third local check only when it answers a real operational question, such as whether a required backup schedule exists. A script that claims to know the backup policy from a setting alone is overselling itself.

I compare results with an approved baseline, then investigate differences. Repeated runs matter more than a one-time clean bill of health because server settings drift through upgrades, troubleshooting and copied build scripts. A baseline should include approved exceptions and the reason for each one. Otherwise the same warning gets rediscovered every quarter, which is an odd tradition to maintain.

Interpret Memory and Parallelism Carefully

Max server memory is a common review item because SQL Server shares the host with the operating system and other processes. The configured value should leave room for those needs, but the right amount is not a fixed fraction that fits every machine. MAXDOP and cost threshold should be assessed with query plans, CPU layout and wait patterns. Changing them in response to a single slow query can simply move the bottleneck.

I ask for workload evidence before proposing a new value. The script tells me where to look, not where to stop. A setting that differs from a popular blog recommendation is not automatically wrong. It becomes a finding when its purpose, current effect and a better alternative can be stated clearly. That is the difference between diagnosis and collecting configuration trivia.

Treat Database Warnings as Recovery Questions

A database set to AUTO_SHRINK or AUTO_CLOSE deserves scrutiny because those behaviors can create avoidable work or unpredictable pauses. PAGE_VERIFY set to NONE or an older option deserves a plan to move to CHECKSUM, plus the understanding that existing pages gain a checksum when next written. Recovery model should be read alongside actual backup history and a tested restore. The name FULL does not perform a log backup for you.

I prioritize the items that affect recovery and data integrity. Then I look at performance-sensitive settings with workload evidence. A small number of well-explained findings is more useful than fifty unexplained flags. If a reviewer cannot say what failure a recommendation prevents, the recommendation is not ready for a change window.

Document the Exception and the Change

For each confirmed misconfiguration, capture the current value, desired value, reason, expected effect and rollback plan. Apply a change through the normal process and compare results afterward. Some options take effect immediately; others require additional action. The configured-versus-running pair in sys.configurations helps avoid declaring victory after only changing metadata.

I keep the script read-only so it remains safe for repeated collection. Auto-fix behavior belongs in a separately reviewed deployment step, with environment-specific values and a test path. A good configuration review makes decisions easier to defend. It should leave the DBA with a short list of known facts and next actions, not a mysterious grade from a tool that never met the application.

Related reading on this blog: A Monthly Health Check You Can Run in Ten Minutes and Audit Script to Get CPU and Memory Information with MAXDOP Guidelines.

A configuration warning is not a verdict, it is a prompt to compare the setting with the server’s job.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.