Craig gave a great walkthrough; thank you, Craig.

One great point he brought up was tempdb; in particular, monitor that, and I'd be very curious as to the "true" physical level...

On 2000, I found after-the-fact creation to be better.

On 2008 R2, I didn't see much difference; slightly different CPU/Read/Write tradeoff, but nothing to write home about.

+1 on an ORDER BY...

Turn Hyperthreading off, see what happens. You mentioned a quad socket octo-core system, but 64 threads.

Definitely look at SORT_IN_TEMPDB and ONLINE; try both ways.

Also, you mentioned MAXDOP 0 and...

My first, general advice would be:

Don't conflate requirements.

Backup is a subset of the Restore requirement: Usually, the requirement to be able to more or less recover from a more or...

Gary has an excellent point; does anyone have the time to run a stable, known workload under uncompressed, page compressed, and row compressed conditions with a varying number of cores...

I've done some compression experiments, and performance impact varies significantly.

As far as size, 8.1GB of data and 6.2GB of indexes (with plenty of VARCHAR fields, none of which had trailing...

Thank you for a well written series of articles, Gail, though I have an outstanding question:

"Column order

... and whether the queries will filter with equality or inequality matches."

I didn't see...

Essentially; know your system, know your data, know your bottlenecks, know the plateaus, know your growth patterns, and keep an eye on future technology.

You have limited CPU, limited disk space,...

tfifield (12/29/2010)

---

Nadrek,

That said, I must maintain that in certain actual business cases, absolute accuracy is not required by the business use; simplified dashboards for management is an example of this...

For many things, absolute, repeatable, verifiable accuracy is indeed a must, I agree.

The general requirement should default to accuracy first, yes. Finance, auditing, anything that gets reconciled to anything...

TravisDBA (12/28/2010)

---

Yes, but that is known as being "on call",...

+1 on monitoring throughput of the test set as well as counters.

If you can, run a sqlio test set against both your spindle drives and your SSD's; I like to...

Unfortunately, I've never used mirroring.

I will, however, very seriously advise you to schedule monthly, quarterly, or semiannual failover/failback tests, and actually _run the business_ on the failover/secondary system for at...

While not part of a formalized "special team", I've been called in, or volunteered, or simply acted, in many emergencies.

The absolute best system I've seen, however, is when a carefully...

+1 on never have a transaction that waits on user input; collect all user input first, _then_ start transactional DML.

In response to "(nolock) and READPAST is always bad", while on...