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

Without more details, I can't tell you what's wrong.

I'm with Gail though. Use backup and restore. Doing that will not only get you your copy of of the database, but...

Ed Wagner (10/31/2013)

---

GilaMonster (10/31/2013)

---

Jeff Moden (10/31/2013)

---

GilaMonster (10/31/2013)

---

Last day of freedom. Starting dev job tomorrow. Hopefully will go well...

I'm unlikely to be around much during the week from now on (at...

In general, more disks can improve performance, not generally hurt it. But, you also have to have more controllers to really achieve meaningful benefits. In general, based on what you're...

Your second query, the UNION, is getting a timeout from the optimizer. That means the plan is likely to be suboptimal. I'd suggest looking at ways to simplify the query....

I meant internal memory measurements within SQL Server such as ConnectionMemory, DatabaseCacheMemory or FreeMemory. In theory you should be able to look at all the these and see what the...