洗澡的沉澱的後續 (included columns)

The bottleneck is not Apache Tomcat。((喵的)) The bottleneck is the non-clustered index of MSSQL database.

透過幾個實驗，我發現 web application 常常卡在 MSSQL database，特別是某個 select SQL statement，他的 where 條件由某四個條件組成：C1、C2、C3、C4。我們對 C1、C2 各別建立 non-clustered indexes，但沒用 included columns。這導致 MSSQL 效能大減，select 速度變慢，連帶拖累其他 insert statement。

Included columns can benefit your application because non-clustered indexes that have included columns can "cover" more queries. An index is called a "covering" index if it contains each column referenced by the search condition of a query. For example, let's suppose a query includes a WHERE clause examining marital status and occupation of a given customer. The non-clustered index which is defined on marital status and occupation columns is called a covering index for this query. If the index is covering then the query results can be obtained by seeking through an index as opposed to scanning the table, resulting in fewer input / output operations.

Included columns 就能解決某個很耗資源的 select SQL statement。量身訂做，但解決問題最重要，不必把雞毛蒜皮小事過分推廣化，就算要推廣，也要做有意義的推廣。這是多年數學經驗教我的，也是職場老師告訴我的：指出問題，解決問題，光打嘴泡，繞來繞去，沒有用的。

補充: http://msdn.microsoft.com/en-us/library/ms190806.aspx

Design Recommendations：Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

有點軟還是蠻佛心的，網路教學。