It repairs the database, nothing more. Go read up on it in Books Online (clearly documented)

Join hints are a bad idea in most cases. Rather see why SQL's picking a loop join (probably low row estimations on one or both tables) and fix the cause.

Correct

Then the article goes on...

Not SQL, but likely the OS. Target lower than total means SQL's been told...

Just increased TempDB usage. The snapshot isolation levels use row versioning instead of locks, and those row versions go into TempDB. Long-running transactions will result in the versions not getting...

Without knowing the underlying indexes, not really. It's not the syntax, the optimiser's smart enough to convert the forms, it's going to be the indexes that make a difference.

Without knowing...

Because the optimiser works on the entire query in one go, not the subquery first and the outer query second, the two very likely simplify to the same form.

No way to tell without seeing the indexes and exec plan. Probably the indexes don't support the additional predicates, possibly combined with row estimation errors (NOT is a little hard...

If you're already seeing lock escalation, disabling page locks will likely make that escalation worse.

With page locks allowed, SQL can take locks at row, page or table. If it find...

Is there a question here?

Either the stats got sufficiently out of date (see referenced blog post) or the data volume increase and passed a tipping point where the plan changed dramatically

There's definitely some room...

Tried breaking it up? Insert the tvf into a temp table, join the temp table.

Can't tell much about the row count, the plans are estimated, but SQL thinks it's dealing...

http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

btw, you do know what nolock does?

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Master, model, msdb and resource, leave them alone, unless you have a really good reason to change them (and even then, just model and maybe msdb).

TempDB:

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

http://support.microsoft.com/kb/917047

http://technet.microsoft.com/library/Cc966545

http://www.sqlskills.com/blogs/paul/category/tempdb/

asmita.patel (2/1/2013)

---

Use "With (nolock)" hint with table name in query,

for example

select a.*

from <<tbl_aa>> a with (nolock)

Join <<tbl_bb>> b with (nolock) on a.aa_id = b.bb_id

--Note: You need to...