Big script like Brent Ozar's blitz?

If you want all the details about what's going through my mind with those simple phrases on the include topic, check this out.

http://www.youtube.com/watch?v=XmE_Utp8CY0

14 minutes 45 secs into it.

I'm retracting my comment on the less reads comment about include.

I remember doing testing in the past with conclusive results but further tests show very little improvement on another test...

rgtft (7/13/2011)

---

Good questions; both SCHOOL and SCHOOL_YEAR tables (and quite a number of other tables) have PK that...

No you had read what I meant correctly... but that was wrong too :w00t:.

Wow that's so cool!

Any other gems like this you care to share?

Any reasons you don't have a clustered index on Schools and SCHOOL_YEARS?

quan23 (7/13/2011)

---

I think The Ninja meant for you to add those columns to the included part of the index, rather than to the index key itself like this?

IF EXISTS...

Still 40% on bookmark lookup...

This should whack it completely (keep in mind that I'm guessing on the columns order, you need to put them in the best order possible (the...

90% + of the plan is caused by the seek + bookmark lookup on that index IX_UIC.

Are you able to add those 3 columns in the included part of the...

Well ya the process will lock the table so the schema doesn't change or the table isn't dropped. I'm not locks expert but that shouldn't prevent much to run!

From...

Deadlock and update stats???

The only caveat is disk and cpu. Stats don't take any locks (unlike indexes rework).

What gave the error? Bids or SQL SERVER?

Please post the actual execution plan of the query with the clustered index. I'll have a look if I can improve...

Then your problem is not solved. Hash join is likely not always the most efficient option.

If the table truely gets updated that often you can set update stats in...

Agreed :-D!