It could be resource contention. I'd check the wait states to see what's causing things to slow down. Also you can monitor calls as they're running slow using sys.dm_exec_requests and...

SKYBVI (2/8/2011)

---

I couldn't get wat you meant by command inside a sql agent ???

I am the only guy who does the backup so im pretty sure that the backups are...

Tom Brown (2/8/2011)

---

Seems like VirtualBox is the preferred choice.

Just wondered why, no one mentioned VmWare or Hyper-V?

As far as I know Hyper-V is only available on Windows 2008 Server...

No, I think you might be in a minority there. Where I was working was using PBM to monitor backup age, agent status, and a bunch of other server settings...

I think that you installed SQL Server Express. That doesn't support SQL Agent. You can't install it because it just won't work. You have to look for some other method...

It can be, but I thought you were more interested in what was running the backup process so you can get some control & understanding.

parissa_bakhshi (2/8/2011)

---

But I/O can be anywhere. the simple I/O issue is disk speed,while we have many I/O issues.

another question is that I didn't see any wait resource showing CPU...

If you're looking to focus on system metrics, then why would you set up a trace? That's for tactical measures of the performance of individual queries. If you want to...

Yes, use the script supplied by avinashily above.

avinashily (2/8/2011)

---

one option is to take the profiler output to tables directly while creating a trace file

and if suppose you have taken the trace output to a .trc file you...

A backup can be run from anywhere. You can run one from the command line in sqlcmd, you can run them from Query Analyzer, and most often, it's stored as...

smushti (2/7/2011)

---

Thanks Gianluca and Craig for the suggestions.

The explain plans are different in prod and test although test is a clone of prod.

The wait time and type are both...

Also, also, be cognizant of the fact that just becuase you have a high cost in an execution plan, doesn't mean that the process itself is very high cost. Execution...

Those waits are I/O related. Spend your time working on that. Don't worry about the memory for now. Keep an eye on it, of course, but don't focus there.

Thank you Jeff!