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.
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?