The Era of The Shared SQL Server Database is Finally Ending

For more than 20 years, many small and medium SaaS companies have built products with complicated business logic and a flexible user experience on the .NET stack using an architecture with a shared SQL Server database. This approach has kept the production environment relatively simple.

These companies have new incentives to move away from this architecture because agentic development lands new business rules on the shared database fast enough to painfully cut velocity and add significant risk to deployments.

Continue reading

Query Store Size Based Cleanup Causes Performance Problems - How to Avoid It

I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.

When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.

Continue reading

Should You Use SQL Server Readable Secondaries If Queries Can Fail Repeatedly at Any Time?

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully ‘refreshed’ on the secondary replica. Those queries may keep failing until you manually intervene.

It’s unclear if Microsoft will ever fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Continue reading

The SQL Server Data Row Size Question: Why Is It Bigger?

This morning, I received the following question from a user:

Could you please clarify SQLServer “Data Row” size: If I run the script below on SQL Server 2012, then Slot(row) Size is 710 bytes if I run the same script against SQL Server 2016 and above, then Slot(row) Size is 724 bytes.

They then provided a script which creates and inserts a few rows into a sample table, runs the DBCC IND command to find a list of pages for the sample table, then uses the DBCC PAGE command to examine the page.

Continue reading

Why is My Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)

A database transaction log is expanding, even though the DBA is running log backups and doesn’t see an open transaction? What’s going on with this Availability Group?

Continue reading

3 Ways Availability Groups Beat Database Mirroring

SQL Server Availability Groups are growing up. SQL Server 2016 adds more features and improvements, and these include options to run SQL Server in different domains, or without a domain.

Continue reading