Audit Per-Database MAXDOP, CE and Sniffing Config Overrides
A database restored from another server, or built by a vendor's install script, can carry its own MAXDOP, cardinality estimation model or parameter sniffing setting, and that setting overrides the server-wide default. The query below lists every database scoped option in the current database, one row per configuration, …
Read MoreAudit Server Configuration Drift with sys.configurations
Aug 24, 2026 / · 8 min read · sql server sys.configurations sp_configure reconfigure configuration drift advanced options xp_cmdshell security monitoring scripts ·Running sp_configure and getting back a success message is not the same thing as a setting actually taking effect. Between the moment a value is changed and the moment SQL Server is actually running it sits a gap — one a busy DBA can close with a single RECONFIGURE, or forget entirely until a restart months later flips …
Read MoreConfigure Database Mail and Send a Test Message
Jul 1, 2026 / · 12 min read · sql server database mail sp_send_dbmail sysmail_add_account_sp sysmail_event_log msdb sql server agent sys.configurations sys.databases sysmail_allitems administration scripts ·Database Mail is switched off on a new instance, and sp_send_dbmail cannot work until an SMTP account, a profile that holds it, and a grant that lets users send through that profile all exist in msdb. The script below enables the feature, builds those three objects, sends a test message, and reads the queue and the …
Read MoreSQL Server Max Server Memory Calculator Script
Every SQL Server installation ships with max server memory set to its default value of 2,147,483,647 MB — effectively unlimited — which allows the Buffer Pool to consume all available RAM and starve the operating system of the memory it needs for network stack, disk I/O, and other critical processes. This script reads …
Read MoreSQL Server Backup Compression: Enable and Verify
Apr 17, 2026 / · 7 min read · sql server backup recovery backup compression sp_configure msdb sql scripts database administration maintenance scripts sys.configurations ·Backup compression reduces the on-disk size of SQL Server backup files and can significantly shorten backup duration by reducing the amount of data written to disk. This post shows how to enable compression at the server level, verify the setting is active, and confirm compression ratios from backup history using the …
Read MoreSQL Server MAXDOP Recommendation Script
Apr 17, 2026 / · 8 min read · sql server maxdop performance sp_configure numa sql scripts database administration cpu configuration sys.configurations sys.dm_os_schedulers sys.dm_os_sys_info ·MAXDOP (max degree of parallelism) caps how many processors SQL Server uses for a single parallel query plan. The default value of 0 lets SQL Server use all available processors, up to 64, which Microsoft says is not the recommended value for most cases. This script reads the CPU and NUMA layout from system DMVs, …
Read More