SLOWDOWN IN APPLICATION DURING SQL SERVER BACKUP.

Question

Hi,

We are noticing a major slow down in one of our applications when daily full backup runs; application comes to normal speed once it is over. There hasn't been major increase in data; happening from last couple of weeks. We tried to figure out any thing if not normal; but everything seems fine. That particular db size is 30 gb. Ram utilization grows to 90% of 64 gb during backup and that is a dedicated server for that app. Application also slows down during 2-3 jobs from last week.

Please suggest what else to check or to speed up backup process/speed up job.

Answer 1

It would have helped to see the numbers. But PAGEIOLATCH_SH, CXPACKET, WRITELOG and LATCH_EX sounds like regular application activity to me. The PREEMPTIVE waits on the other hand, could from from the backup. Then again, I don't know how you collected this information. Did you follow my instructions, or did you just take a snapshot of the DMV? Overall, I have made several comments and question, which you have left without notice. I maintain that 15 minutes to backup a 30 GB database an external SSD is really poor - or is that USB 2.0.

As I said before, I think it is time for a major revision of both hardware and SQL Server version.

Answer 2

Hi @DM

Backup and restore operations are I/O intensive. Backup/Restore throughput depends on how well the underlying I/O subsystem is optimized to handle the I/O volume.

Please troubleshoot follow by the methods motioned in this doc: Troubleshoot SQL Server backup and restore operations

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hello

when you mentioned "It's a dedicated external ssd connected to the server" you mean it is an USB connected drive ?

Not sure if that is best option. You also mentioned a mayor slowdown to your app while running the backup. For this I will suggest you 3 points.

1- check CPU usage while running the backup

2- review the Max server Memory setting on your instance. maybe you need to reduce it to leave more memory for the OS

3- Consider splitting the backup into multiple files

Ex.

BACKUP DATABASE [MyDatabase] TO DISK='D:\Backups\MyDatabase_01.BAK' ,DISK='D:\Backups\MyDatabase_02.BAK'

,DISK='D:\Backups\MyDatabase_03.BAK'

,DISK='D:\Backups\MyDatabase_04.BAK'

WITH COMPRESSION,BLOCKSIZE = 65536, BUFFERCOUNT = 64, MAXTRANSFERSIZE = 4194304, STATS = 10 ;

Regards

Javier

Answer 4

Probably a good idea to patch to the terminal release (6560 I think), for security, but it's unlikely to help you with the backup speed.

I have found the following to be useful. SOmeone else posted something similar.

WITH [Waits] AS

(SELECT

[wait_type],

[wait_time_ms] / 1000.0 AS [WaitS],

([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

[signal_wait_time_ms] / 1000.0 AS [SignalS],

[waiting_tasks_count] AS [WaitCount],

100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],

ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

FROM sys.dm_os_wait_stats

WHERE [wait_type] NOT IN (

N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',

N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',

N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',

N'CHKPT', N'CLR_AUTO_EVENT',

N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',

N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',

N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',

N'EXECSYNC', N'FSAGENT',

N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',

N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',

N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',

N'PWAIT_ALL_COMPONENTS_INITIALIZED',

N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',

N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',

N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',

N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',

N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',

N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',

N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',

N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',

N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',

N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',

N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',

N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',

N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',

N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',

N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')

AND [waiting_tasks_count] > 0

)

SELECT

MAX ([W1].[wait_type]) AS [WaitType],

CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],

CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],

CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],

MAX ([W1].[WaitCount]) AS [WaitCount],

CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],

CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],

CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],

CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]

FROM [Waits] AS [W1]

INNER JOIN [Waits] AS [W2]

ON [W2].[RowNum] <= [W1].[RowNum]

GROUP BY [W1].[RowNum]

HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold

GO