Convergence: Indexing For SQL Performance

Luc and kriki asked me to post the convergence sessions about sql performance in this forum...

It is quite hard to summarize this kind of sessions ... But I'll give it a go.

The session starts about explaining what are clustered/nonclustered indexes with some nice graphs (may be somehow they'll be put on the download section?). Also the ground principles of how it's going to decide

Also explained: the 4 basic access mehods (Unordered scans, Ordered scans, Index seek, Row fetch) and how they form the basis of the execution plan. The primary goal of this execution plan is to retrieve data as fast as possible.

If you're creating indexes, keep this information in mind:
- Uniqueness: order: most unique columns to the least unique
- Number of columns: keep it limited if possible
- Number of indexes: don't worry about it
- Try not to index columns that change a lot (not too big of an issue, but anyway ... ) Columns like status: no problem, but others that can have an unlimited number of values ... And change a lot ... Try to avoid.
- Try to eliminate duplicate indexes: (A,B,C and A,B,C,D ... Eliminate A,B,C - don't need it)

too many indexes is a myth... . Now, this is a bit surprising for me, because disabling the "MaintainSQLIndex" really gained some performance in some fields... .
I talked with Hynek and Mark about this after the session ... and indeed: bull§èçt. It DOES matter how many indexes there are ... .

There was some attention for "OLTP vs OLAP". You can tune indexes for transation processing (OLTP) or for Analysis processing (OLAP).

We got a demo, let's try to go over it.
For a statement, we looked at the execution plan. Result: a clustered index scan. This is typically. Users can search/filter all kind of fields. You have to investigate how users are using the data in a certain table ... . Usually, the where-clause causes the horror.
Remarks:
SQL Server reacts differently for big databases in stead of small databases. e.g.: for a small table, a clustered index scan might be better then something else...
Having a clustered key is a must (some products doesn't provide it out-of-the box...). This key is always available, also when doing an index seek, so if you create a nonclustered index with two columns (date, name) the clustered index (ID) is also available, so when doing a query with the data in the where clause ... It will do an index seek on the new nonclustered index, because it has all the fields there ... Together with the clustered... (am I making sense?)

Hopeful interesting stuff for you ... I know that Daniel (Denster), Mark (Brummel) and Hynek were also attending this session ... so may be they can add some more info ... or correct me if I misunderstood something.