Each of those INSERT statements is treated as a miniature transaction and the INSERT ... SELECT ... UNION is a giant transaction. That's probably where the differences lie.

Congratulations, well done.

jason.spangler (3/17/2011)

---

Great to know! I do see the parameterized plan in the plan cache.

I'm checking out the ad hoc workload option. Looks like it will help. We...

Brandie Tarvin (3/17/2011)

---

Every so often, I stop and remember that I learn (or remember) things much better if I have to explain it or teach it to someone else.

I...

It's an artifact I wouldn't sweat it. If you're getting the cache hit, you're good to go. You can also look at the execution plans to see if the plan...

GilaMonster (3/17/2011)

---

Order by is only permitted in the outer-most select statement unless there is a TOP. Order by on a subquery is only honoured if there is a row-limiting TOP...

Restoring the database will recreate it with the same size and allocation you have now.

108gb is not that big a database. In fact, it's almost a small database. If it's...

No reason why you can't move that into the derived table. I've done it before.

Shrinking the log file won't affect the log chain. You can do it when you think best.

Shrinking files is a problematic thing to do since it leads to fragmentation. But...

I don't like correcting people online, but don't follow Rohit's advice. Constantly shrinking your files leads to severe file fragmentation. You don't want to do this to a production server.

I...

So you're just trying to sync some common code sets? Oh, that's a different problem. First, I'd get your database under source code control. Second, I'd design a deployment process...

Craig Farrell (3/16/2011)

---

Grant Fritchey (3/16/2011)

---

It looks like the Stream Aggregate is getting called in a loop. If you look at the actual output from it, it has 83 rows. The...

Joe Korn (3/16/2011)

---

depending on how fragmented your tables the pointers on the non clustered index pages that point to where the data is on the clustered index page will be...

You simply reorganize the clustered index, nothing happens to the nonclustered index. If you rebuild the clustered index, the nonclustered index is automatically rebuilt as well.

It looks like the Stream Aggregate is getting called in a loop. If you look at the actual output from it, it has 83 rows. The table spool is executed...