March 14, 2013 at 10:40 am
Hi,
In one of my production envirnment Maintinace Backup job is taking longer than expected.
I have maintinace plan to take the full backup on weekly baisis.Datasize is around 3.5TB (174 DBs).
Previousaly This job used to take around 10-11 hours and now its taking more than 24 hrous.
Below is the configuration.
SQL Server 2005 EE + SP4,
Windows Server 2003
SAN disk on 3PAR
I checked the disk latency and that is also below 10ms.We have seprate disk for backup.
Could you Please guide me to find the root cause of this issue.Its very urgent..
Regards
Ajay Bhardwaj
March 14, 2013 at 6:58 pm
Does any one have any idea about this?
March 15, 2013 at 1:48 am
Do you know how long each database backup is taking? Has the time increased for a single backup or all of them?
Apart from the slowdown issue, with 3.5TB of data, it's time to stop using maintenance plans and move to a custom scripted backup solution.
There is a commonly used solution here (I have not used it personally but it's widely used elsewhere) http://ola.hallengren.com/
That will at least give you some better logging. I'm not sure of your level of experience, but it sounds like you'll need to research more about backup performance tuning. What is the best performance you can achieve by backing up to a NUL device? There are switches like BLOCKSIZE and BUFFERCOUNTS which can make a huge difference in backup times and throughput. Actually what throughput are you achieving to the SAN in MB/s or IOPS?
You'll need to investigate the wait activity occuring at the time of the backup too. You can use this code, but remember you will lose all the wait statistics when running the DBCC clear.
-- Clear Wait Stats
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold
March 23, 2013 at 9:58 pm
If you have a budget for it, you should consider using a product like Redgate SQL Backup. It compresses your backup file as it writes it to disk. This has helped us to greatly reduce the time it takes to create a backup, and to restore it. It also reduces the space needed to store it, which has a lot of other benefits.
April 3, 2013 at 2:37 pm
Thanks for your response and sorry for late response.
@foxxo
I investiagtae futher and there is no pattern for backup dealy.While investigating on this i found that backup set verification is taking longer than backup task but always not the same dbs.
I have also think about the solutions like BLOCKSIZE ,BUFFERCOUNTS and temporay change in MAXDOP during backup options.
But its a production box and before provideing any action plan i need to provide the root cause for any issue then pros and cons for solutions also.
As well as i also gone through the PSSDiag blocking log and no process is blocking the backup and verifiaction task.
So first I am more insterseted in Root cause
@djyoungberg :- As of now my company is not looking for any tool.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply