SQL Server articles
In-depth guides on maintenance, performance, and operational excellence — written for people who run production databases.
Parameter sniffing explained with a real-world pattern
How a stored procedure can be fast for one parameter and terrible for another — and practical ways to fix it without cargo-cult OPTION RECOMPILE everywhere.
Index maintenance without the nightly rebuild ritual
Why rebuilding every index every night is expensive — and a safer, smarter approach to fragmentation, statistics, and write-heavy workloads.
Performance tuning: start with wait stats
Why wait statistics are the fastest way to stop guessing about SQL Server slowness — with a practical workflow you can use in production.
A practical SQL Server maintenance checklist
The core jobs every production SQL Server should run — backups, integrity, indexes, stats — and how to prioritise them without over-maintaining.
What a SQL Server health check prioritises (and why)
A practical look at how Allay Data Solutions ranks findings: integrity and recovery first, then stability, then performance polish.
Query Store is off when you need it most
Without a flight recorder for plans, every regression becomes folklore. Why enabling Query Store is a standard health-check recommendation.
Database owners, orphaned users, and the TRUSTWORTHY bit
Ownership chains, orphaned users after restore, and TRUSTWORTHY settings create security and operational surprises after migrations.
Implicit conversions that blind your indexes
Datatype mismatches force scans and CPU waste. How health and performance reviews spot conversion anti-patterns in real workloads.
Recovery model mismatched to the real RPO
Simple recovery on critical databases, or Full recovery without log backups — both break the recovery story the business thinks it bought.
Suspect pages and I/O errors are not background noise
Entries in suspect pages and recurring I/O errors deserve incident treatment. How reviews escalate storage risk before CHECKDB becomes a crisis.
Windows power plans and SQL Server CPU behaviour
Balanced power plans can park cores and surprise latency SLAs. Why host configuration still belongs in a database health review.
Duplicate and unused indexes tax every write
Indexes that never seek still slow inserts and updates. How reviews identify write tax without recklessly dropping production indexes.
Plan cache bloat from one-off queries
Ad-hoc SQL can flood the plan cache with single-use plans. When optimize for ad hoc workloads helps — and when the real issue is the app.
Blocking chains and long-running transactions
How open transactions, chatty apps, and missing indexes combine into blocking storms — and what a health review prioritises first.
Autogrowth events and instant file initialization
Tiny growth settings and missing instant file initialization turn routine file growth into multi-second stalls users feel as freezes.
When msdb history bloat slows the platform
Backup and job history tables grow without cleanup and eventually tax disk, maintenance, and even SSMS responsiveness.
Compatibility level lag after SQL Server upgrades
Upgrading the engine without raising compatibility level leaves performance features and cardinality estimator behaviour on the table.
Heaps, forwarded records, and hidden I/O
Update-heavy heaps create forwarded record chains that quietly multiply reads. What reviewers look for and when a clustered index is the real fix.
Stale statistics and plans that used to be fine
How out-of-date statistics create sudden regressions after data growth, ETL loads, and “nothing changed” releases.
Parallelism defaults that surprise production
Cost threshold for parallelism and MAXDOP left at defaults can cause CX waits, worker starvation, or underused CPUs.
Too many VLFs: when the transaction log fights you
Virtual log file fragmentation slows recovery and log operations. How growth habits create VLF mess and how reviews catch it.
PAGE_VERIFY and corruption risk you can reduce today
CHECKSUM page verification is a basic defence. Why older NONE/TORN_PAGE settings still show up and what to change carefully.
Dangerous permissions and shared logins on SQL Server
sysadmin sprawl, shared application logins, and guest access still appear in security hygiene reviews of production estates.
Failed SQL Agent jobs nobody notices
Silent job failure is operational debt. How to turn Agent history into an alerting practice that actually protects production.
TempDB configuration red flags we see every month
Uneven files, tiny autogrowth, and contention patterns that make TempDB the hidden bottleneck on busy systems.
Max server memory left at the default
Why unlimited SQL Server memory settings fight the OS, cause paging pressure, and show up constantly in configuration reviews.
Auto-shrink is still enabled — and still a bad idea
Why AUTO_SHRINK creates fragmentation and I/O thrash, and what to do when you find it on a production database.
CHECKDB skipped until corruption shows up
Why integrity checks get postponed, what that costs, and how production teams make CHECKDB practical again.
When backups exist but restores would fail
A green backup job is not a recovery plan. How health checks separate “we take backups” from “we can restore on time”.