August 1, 2008 at 5:48 am
We have the following situation and I would like to know if SSIS or T-SQL is the best solution for speeding up the (backup)process ?
* On Server A we backup a SQLdb of 230GB between a time period of 1 or 2 hours.
Here we use a "Db Maintenance" (plan) (=> SSIS)
* On Server B we backup a SQLdb of 78GB and this backup lasts for 4 hours !
On Server B we use a T-SQL command.
Can someone explain to me why the backupprocess via T-SQL is that much slower than when it goes via SSIS ?
Or in which situation we should prefer SSIS before T-SQL.
Regards,
J.
August 1, 2008 at 12:30 pm
this will also depend on the network connections of each server, the server hardware itself, the destination of the backups and the network traffic at the time
August 1, 2008 at 12:44 pm
As SQLNoob has said there are many other factors in place as well. IN reality the maintenance plan which uses SSIS, is really just running the BACKUP DATABASE command like you are.
Is the hardware exactly the same on both servers? Are both backups going to the same type of drives? Are the loads the same on the 2 servers?
When the backups are running you can check IO by using the sys_dm_os_performance_counters view like this:
[font="Courier New"]DECLARE @value1 bigint,
@value2 bigint,
@time1 DATETIME,
@time2 DATETIME
SELECT
@value1 = cntr_value,
@time1 = GETDATE()
FROM
sys.dm_os_perfomance_counters
WHERE
counter_name = 'Backup/Restore Throughput/sec' AND
instance_name = [database name]
-- wait for 10 seconds as the counters are cumulative
WAITFOR DElay '00:00:10'
SELECT
@value2 = cntr_value,
@time2 = GETDATE()
FROM
sys.dm_os_perfomance_counters
WHERE
counter_name = 'Backup/Restore Throughput/sec' AND
instance_name = [database name]
SELECT
(@value2 - @value1)/DATEDIFF(second, @time1, @time2) AS throughput_per_sec
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2008 at 12:12 pm
It's not a difference between maintenance plans and T-SQL. The maintenance plans use T-SQL commands, just like typing them in yourself.
The difference could be the options selected for the backups (locations, multiple files, etc.), it could be the hardware they are running on, it could be disk fragmentation. Lots of things it could be, but it's not a difference between maintenance plans and T-SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply