Most of your questions can be answered from this TechNet best practices article.

http://technet.microsoft.com/en-us/library/cc966412.aspx

You have an indication that at some points in time, your disk sub-system is unable to keep up with I/O requests from SQL Server. Whether this is a severe...

jason (6/19/2009)

---

ALTER DATABASE @dbname SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE @dbname SET READ_COMMITTED_SNAPSHOT ON;

After i set these 2 option, do i still need to use SET Transaction Isolation Level Snapshot on each...

It depends. The row-versioning isolation levels do come with a cost, the question is whether the improvement in concurrency outweighs...

John Rowan (6/19/2009)

---

At some point, you still need to query the DB to determine which ranges belong to which threads and which values within those ranges correspond to table data...

Just taken a quick look at the estimated plan - would it be possible to try running:

select * from dbo.iv_SystemTransactionCardFund (option hash join)

to see if that reduces the CXPACKET waits?

edit:...

Heh. Well unless you are $deity posting under a psedonym, I think we're going to need more than just...

It's a shame that DBCC CLEANTABLE only operates on variable-length columns, but I guess it makes sense, given the row layout.

Books Online is very brief concerning all this. After...

Win.

Phil's posts were just placeholders. ...

Jamie,

Good post.

However, the first post did say that 'kladibeeto' knew about full scan and percentages - the question was specifically about the default sampling rate...

I say 'good post'...

John Rowan (6/18/2009)

---

Ah, the Sequence Table. If not implemented correctly, you're going to be chasing deadlock problems until you're blue in the face. I can't say it any...

Hey Jeff,

For added safety on 2K5 and 2K8 (pre-SP1) you need a MAXDOP (1) before the SCOPE_IDENTITY to avoid the bug described here: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

In the interests of fair play and...

[font="Tahoma"]Excellent post Bruce.

You are right that the parent column(s) in a foreign-key relationship must have a unique index defined (primary key constraint, unique index or constraint).

The child column(s) do not...

This is the Books Online link: http://msdn.microsoft.com/en-us/library/ms175156(SQL.90).aspx

This is an article on this site: http://www.sqlservercentral.com/articles/T-SQL/63115/

The APPLY solution should generally slightly out-perform the CTE (although the plans are pretty similar).

The main difference...