SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. When the back up service was stopped we see no issues. Is this something common?

We are using SQL Server 2005 and the backups run on Sunday's and Wednesdays. I am also going to look into other things within the database itself, but this is what stood out.

-Edit-
I ran a perfmon on the machine for 3 days 5/27 to 5/31:

SQLServer:SQL Statistics\Batch Requests/sec
Average 355.412275
Median 306.610812
Min 108.9369962
Max 916.6332837
Std Deviation 141.7791552

SQLServer:General Statistics\User Connections
Average 83.14025501
Median 77
Min 52
Max 147
Std Deviation 19.27016231

SQLServer:Buffer Manager\Page life expectancy
Average 33.72386157
Median 21
Min 0
Max 246
Std Deviation 36.53737617

Looks like the maintenance backup that runs is not causing a problem while its running. It runs at 3am and on Sundays and Wednesdays and it completes successfully in about 2 hours.
I ran the SQL Server Profile:

I found one of the sp's that runs alot periodically takes a long time to execute.

Most of the time the Duration is 976 (microseconds) but sometimes its a big difference:
13922851
13025390
13021484
13019531
13018554
13017578
13016601

I am reading this correctly, right? 13 seconds?

If I run the same query from my machine in executes in a fraction of a second.

-Edit-

I have been looking at blocking and I haven't come up with much yet. I was just looking for recompile blocking in the SQL Server Profiler, but I did not see any events of that type (sp:recompile). I am still investigating blocking issues.

I also have noticed when I ran my last Profiler trace that there were a high number of sp_reset_connection events. Some of these sp_reset_connection 's were over 9 seconds long. Is this a normal type of behavior?

CHeck to see if you are having the backups stored on the same physical location as your data and log files. If you are trying to write all activity to one disk, that is going to be a likely bottleneck.

Also, if you are using a 3rd party tool (such as Litespeed) for backup compression, there is a chance you are consuming more CPU than you expected, and that could also result in less than optimal performance.

HTH

StackExchange Database Administrators Q#3078, answer score: 4