Facing Performance issue at application end, after migrating the backups to Azure recovery service vault

 ISSUE DESCRIPTION

Facing Performance issue at application end, after migrating the backups to Azure recovery service vault (RSV) from Azure Blob storage.

 SUMMARY

Once backup process started, we could see severe IO latency. The increased IO activity could be due to all backups starting at the same time at 12:30 and reading from the drive. 

Earlier, when SQL agent-based BACKUP TO URL was running, it would be working with one database backup at a time which might not put high stress on disk, but Azure RSV is designed to take backup of all databases at the same time (For quick backup, to save time) which can invite considerable IO load on the disks. We checked internally and there are no options to change this behavior for Azure RSV.

In Azure blob storage when the backups are running, backup operation is performed serial manner that means server will take the single database backup at a time.

But when it comes to Azure RSV, the database backup operation is performed parallel manner that means it will trigger all the database backups at a time.a

So, when the multiple backups are running parallelly on the server. There will be huge impact on the IO resources due to that application team will face the slowness issue while running the queries or application jobs.

ACTION Needs to be taken from DBA end:

Redirect your backups to secondary replica (even if DB is not set for READ load on secondary, backup will work) which would reduce the IO latency on PRIMARY. If AG is in SYNC COMMIT mode, these intensive backups happening on secondary can add a little overhead to the DML operations on PRIMARY.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver16

You can check whether your VM is configured as per best practices (especially the Storage and VM sizing) as per the document below

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql

You can switch back to BACKUP TO URL method as you were doing earlier to mitigate the situation.

Check the AG configuration best practices.

As you suggested we are looking to check whether the PRIMARY and SECONDARY machines of the AG follow the best practices shown in the document below.


https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql#vm-size                                                                                                        


You can also to enable resource governor on the database server so that we could assign limited resources throughout to the backup process.


You can refer to the RESOURCE GOVERNOR configuration document in MS site to configure the resource governor related components (Resource pool, Workload group and classifier function)

Link - https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver16

Link - https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool?view=sql-server-ver16#min_iops_per_volume-and-max_iops_per_volume

 In this RESOURCE POOL configuration, you can set the MIN_IOPS_PER_VOLUME & MAX_IOPS_PER_VOLUME setting to restrict the IO. 

**************

CREATE RESOURCE POOL BACKUPPOOL1

WITH (MIN_IOPS_PER_VOLUME = 100, MAX_IOPS_PER_VOLUME = 250);  

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

************** 

To know the right value for it, during off business hours:

Set it to a value say Min100-Max250, start the backup and check if it shows high amount of PAGEIOLATCH in the waits. 

If yes, reconfigure RESOURCE POOL to set only the MAX value… Mention the MIN value as 80 and MAX value as 100 and retry. Repeat the same operation until you do not see much PAGEIOLATCH waits.

If No, then you can use this setting for backups and increase the MAX little by little to reduce the time of backups until you start witnessing PAGEIOLATCH waits.

 


Comments

Post a Comment

Popular posts from this blog

SQL Server DBA scripts for day-to-day tasks

   1. Check Database Size: SELECT name AS 'Database Name', size * 8 / 1024 AS 'Size (MB)' FROM sys.master_files WHERE DB_NAME(database_id) NOT IN ('master', 'tempdb', 'model', 'msdb'); 2. Check Fragmentation: SELECT OBJECT_NAME(ind.OBJECT_ID) AS 'Table', ind.name AS 'Index', indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC; 3. Backup Information: SELECT database_name, backup_start_date, backup_finish_date, compressed_backup_size/1024/1024 AS 'Backup Size (MB)' FROM msdb.dbo.backupset ORDER BY backup_start_date DESC; 4. Check Active Sessions: SELECT session_id, login_time, host_name, program_name FROM sys.dm_exec_session...
Read more

SQL Server Dynamic management views (DMV's)

  dm_audit_actions dm_audit_class_type_map dm_broker_activated_tasks dm_broker_connections dm_broker_forwarded_messages dm_broker_queue_monitors dm_cdc_errors dm_cdc_log_scan_sessions dm_clr_appdomains dm_clr_loaded_assemblies dm_clr_properties dm_clr_tasks dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys dm_cryptographic_provider_properties dm_cryptographic_provider_sessions dm_database_encryption_keys dm_db_file_space_usage dm_db_index_operational_stats dm_db_index_physical_stats dm_db_index_usage_stats dm_db_mirroring_auto_page_repair dm_db_mirroring_connections dm_db_mirroring_past_actions dm_db_missing_index_columns dm_db_missing_index_details dm_db_missing_index_group_stats dm_db_missing_index_groups dm_db_partition_stats dm_db_persisted_sku_features dm_db_script_level dm_db_session_space_usage dm_db_task_space_usage dm_exec_background_job_queue dm_exec_background_job_queue_stats dm_exec_cached_plan_dependent_objects dm_exec_cached_plans dm_exec_connections...
Read more

A brief comparison of Microsoft SQL Server versions from SQL Server 2012 to SQL Server 2022

 SQL Server 2012: • Introduced AlwaysOn Availability Groups for high availability. • Enhanced columnstore indexes for better data warehousing performance. • Contained Databases for easier database portability. • Improved security features, including user-defined server roles. SQL Server 2014: • In-Memory OLTP (Hekaton) introduced for improved transaction processing. • Buffer Pool Extensions for SSD integration. • Enhanced backup to Azure for improved disaster recovery. • JSON support introduced. SQL Server 2016: • PolyBase introduced for querying across relational and non-relational databases. • Stretch Database feature for hybrid cloud scenarios. • Always Encrypted for enhanced security. • Dynamic Data Masking for controlling access to sensitive data. SQL Server 2017: • Introduced support for SQL Server on Linux. • Adaptive Query Processing for improved query performance. • Graph Database capabilities. • Automatic dat...
Read more