There are, as Steve says, almost limitless issues. One of them is most likely that you have the default value set for "Cost Threshold for Parallelism." That value is 5....

It kind of depends on what the job is doing how you can monitor it. If it's running DOS or PowerShell commands, the primary metrics are going to be the...

I'm with Kevin. It's very likely to be contention.

But, from the differences you're seeing, I'd also suggest it's possibly statistics being different between the two. Those differences in estimations the...

There are only a few that do both.

Dell (formerly Quest) Foglight

Solarwinds

Microsoft Systems Center Operation Manager

Personally, I'm not crazy about the job that Foglight does on SQL Server, but it works...

If you don't set the max memory on each instance, they will fight for the memory resources and you could see a "winner" in that fight.

In general, if a...

The other recommendations for MAXDOP are good. You can also leave it at zero if you're not experiencing any particular CPU pressure.

But, the one thing I would suggest changing on...

In what might be an epic role reversal, don't you mean RESTORE LOG etc.?

For the two queries listed, one shows an actual row count of 10,000+ rows. The other shows an actual count of 1 row. You can also see a difference in...

The reason snapshots are so fast is that they're only tracking changes pages. It's not an actual backup. It completely dependent on your original database being in place. As Gail...

If it was just the query you show and no other statements, no other processing updating tables or anything else, yeah, I'm a little surprised that query all by itself...

zi (11/5/2013)

---

I didn't investigate the main reason for slowing down, but I have noticed the difference in performance after adding a file to tempdb, I expect that something happened made...

I'd suggest not relying too much on those screens. Instead, if you're interested in monitoring the system, use a combination of Performance Monitor, Dynamic Management Objects and Extended Events. You'll...

It doesn't sound like tempdb was the issue. I'd suggest monitoring your wait statistics (sys.dm_os_wait_stats) and running an extended event session to capture query metrics.

Don't run the Profiler gui against...

Use extended events to capture the data to a file.

Then, you can use fn_xe_file_target_read_file to read the file. It'll output as XML. Here's an example XML query that might be...

I'm not sure who wrote that tip, but in case it was me...

I must have meant to just clean up the code. It's actually not a major performance enhancement...