SQL Backup dynamic? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Backup dynamic?

Author

Topic

loamguy
Starting Member

15 Posts

Posted - 2009-01-20 : 10:22:45

Hello, I know that backups of SQL databases are dynamic and do not take the DB offline, however, during our maintenance window, we are seeing a website slowdown.

Is it possible that an automated SQL DB backup (size about 100MB) would slow down the website that uses it for read/write?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 12:59:15

A backup of a 100MB database should take seconds to run and should not impact the server at all. If you are seeing performance degradation, then I would be suspicious about your hardware, specifically IO.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

loamguy
Starting Member

15 Posts

Posted - 2009-01-20 : 13:09:04

That's kind of what I figured, but given my amateur status I thought I'd check with the pros.

Thanks very much!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 14:08:50

You're welcome. To check for hardware bottlenecks, you can use Performance Monitor. To check for query performance, you can use SQL Profiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

loamguy
Starting Member

15 Posts

Posted - 2009-01-21 : 10:11:32

With regard to SQL Profiler, for queries that take a long time to execute, should I be looking at the Duration and CPU columns? At what threshold should I be concerned about performance? I ran a quick trace and found one query to have a duration of 140. Not sure what that is... is it seconds?

Any other columns in a trace I should look at?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 12:24:12

The threshold depends upon your requirements. We have some apps that would freak out if any query took more than 500 milliseconds. We have other apps that worry when it is over 5 seconds. So it just depends on your requirements.

I typically run this from the trace file/table

SELECT TOP 1000 Duration/1000000.0 AS DurationInSecs, TextData
FROM ...
WHERE TextData IS NOT NULL
ORDER BY Duration DESC

Duration of 140 is either microseconds or milliseconds, depending upon how you are viewing it. If it's from the GUI, then that's in milliseconds. If you are looking at it in a trace file/table, then it's in microseconds. I've got a blog on this inconsistency that I'll dig up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 12:25:17

Here it is: http://weblogs.sqlteam.com/tarad/archive/2006/10/04/13621.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 12:25:55

Oh wait, you are using SQL Server 2000 according to the forum you selected. The duration is in milliseconds then. We didn't get to see microseconds until 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources