Tag: availability-groups

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

By Kendra Little on April 23, 2024

Category: query-performance , sql-server , azure-sql

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.

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

By Kendra Little on February 21, 2024

Category: backup-recovery , sql-server , azure-sql

Tags: availability-groups , readable-secondaries , read-scale-out , error-2767 , bugs , statistics , azure-sql-managed-instance , query-failures

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’.

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

By Kendra Little on March 19, 2019

Category: database-administration , sql-server

Tags: row-versioning , snapshot-isolation , readable-secondaries , availability-groups , row-size

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.

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

on March 30, 2017

Category: database-administration

Tags: alwayson , availability-groups , dear-sql-dba , shrinking , transaction-log-growth , vlfs

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?

3 Ways Availability Groups Beat Database Mirroring

By Kendra Little on December 17, 2015

Category: high-availability

Tags: availability-groups , 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.