6

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?

Improve this question
1
  • 1
    Do you have index and stats maintenance too?
    – gbn
    Commented May 31, 2011 at 19:30

5 Answers 5

Reset to default
4

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

Improve this answer
2
  • I will take a look. I didn't set this up so I will do some investigating.
    – Tony
    Commented Jun 1, 2011 at 15:13
  • @SqlACID I appended my results to my original question.
    – Tony
    Commented Jun 1, 2011 at 15:39
2

Is the backup job sending data down the wire, hogging all the network bandwidth from the users? A full database backup shouldn't freeze a server, unless like SQLRockstar said you're using compression of some sort and running cpu up, or some other resource bottleneck.

Improve this answer
1
  • Looks like the actual backup isn't causing the issue. Seems to be something else when SQL Agent is running that seems to be the issue. Still trying to figure this out.
    – Tony
    Commented Jun 1, 2011 at 15:44
2

Based on your edits on today, I would strongly suggest to look at the stored procedures that are expensive in terms of CPU, IO, Execution count. Also, you should look at using the WAIT STATISTICS that SQL Server collects whenever its waiting for resources and that is a valuable tool in identifying the bottlenecks on the server.

Glenn Alan Berry wrote some excellent queries for all of these and here is the link.

http://dl.dropbox.com/u/13748067/SQL%20Server%202005%20Diagnostic%20Information%20Queries%28March%202011%29.sql

First step is identifying the expensive procedures and the next step is to either modify them for performance or add indexes to help them run better.

This is a good starting point for you and if you need further help and then please add more details based on your findings.

Improve this answer
1
  • Thanks. I will take a look at what you suggested. I will post any new findings.
    – Tony
    Commented Jun 1, 2011 at 17:21
2

The page life expectancy (PLE) counter metrics you posted are an indicator of memory pressure for SQL Server - it's a metric of how long SQL Server can keep data in memory, rather than having to pull it off disk. Disks are much much much slower than memory, so when SQL Server has to go to disk, queries will appear to run slower.

Every application is different, but the recommended minimum for PLE is 300 (seconds). Now, just because it may be below 300 doesn't necessarily mean there's a problem, but if performance tanks at the same time - it's something to investigate further.

If it's a 64bit OS and SQL Server combination, consider adding more memory - and allowing SQL Server to use it. If it's 32bit, it's a tad more involved.

Improve this answer
0

I'll answer to the last edit: "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 you read the duration data in the trace file, then the data is saved in milliseconds, so divide by 1,000 and you'll get time in seconds. If you have the same information saved by Profiler in a table, than it's saved in microseconds, so you'll have to divide by 1,000,000 to get the right duration time.

You'd better set also a trace for blocked processes, maybe your stored procedure is blocked by some other batch process that blocks an important table. See examples in these following articles:

Enjoy your investigation :-).

Improve this answer
1
  • Thanks. I will be looking into the blocking problem next.
    – Tony
    Commented Jun 2, 2011 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.