UNION ALL, not UNION. Otherwise you're paying for an unnecessary distinct sort.

Have a look at the execution plans if you want to see how the two queries are run differently. Ignore the scan count, it does not give you particularly useful...

Ignore the scan count. It's not the number of times the table has been scanned. It's not consistent.

As I said above, if it's CheckDB you're running, you'll need to use the TABLERESULTS option, checkDB does not by default return a resultset. Other DBCC statements do, so it...

If this is a DBCC statement that only returns messages and you want to insert into a table, you have to use the TABLERESULTS option, otherwise it will only return...

Execute it once and see?

If you're trying to avoid access to a table, maybe stored procedures?

Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Is that plan with them being run together with other stuff, or them running separately?

Honestly, if something's messed up master, I would recommend restoring it from the latest backup before whatever happened.

First thing. Why are you partitioning? What's the goal here?

No, it'll consume memory until Windows...

Brandie Tarvin (9/19/2013)

---

Jack Corbett (9/19/2013)

---

Brandie Tarvin (9/19/2013)

---

Thanks, Jack.

Huh. Apparently the Key Lookup shook itself out of the execution plan a bit later on. Now I've got Clustered Index...

Brandie Tarvin (9/19/2013)

---

Okay, this is interesting. When I run the first two queries in conjuntion with everything else, the first execution plan changes back to a Key Lookup.

When I run...

You could filter for Writes = 0, but that will exclude anything that touches TempDB, so maybe not what you want. Why do you want to exclude anything that writes?