This subject needs a good book.

There are pieces in lots of places, but nothing that I've found that tells the complete indexing story in one place.

Paul White (5/27/2009)

---

However, if a non-clustered index exists which covers the query completely, the optimizer will choose that - since the entire query can be resolved simply from the NC...

As far as I'm aware, yes. If an index is unique, mark it as unique.

You may find this useful - http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

wellygee (5/27/2009)

---

This being the case i would expect the clustered index to satisfy the query more efficiently by itself instead of doing a further scan via the NC since the...

Exactly the same as if you use backup/restore.

Users and roles are...

I would guess that for the first query the index is covering and in the second (because it's a select *) it's not. With a covering index just the NC...

If you go with option 2, you must back the transaction log up after switching back to full. This is because you can't take tail log backups if the DB...

Did they say what RAID level? Or are we talking just 2 physical disks with no redundancy of any form?

Not from the default trace. The SQL Batch/Statement start/complete events aren't there. The SP completed/started events aren't there.

You...

Probably not, but make sure that TempDB's initial size is set to something...

Uncle Moki (5/26/2009)

---

However, I do know that the "J-drive" is dedicated to the SQL Data files and the "P-drive" is for the SQL Log files. Physically, I...

That's an understatement. With all the data files on one drive, all of the DBs are fighting over what's probably...

Biggest one I've worked directly with hit 1.2 TB just before I left.

I'm curious. How will knowing what size DBs we've worked with help you in an interview?

All of them? The...

Probably IO contention.

What's the layout of databases on LUNs?

What's the RAID level on those LUNs

Are the dedicated or shared LUNs?

What do the perfmon counters Physical Disk: Avg sec/read, Avg...