• An unusual situation occurred for me. The first time I ran the SELECT statement the plan was a Clustered Index Scan. Subsequent runs were the expected NC Index Seek & Key Lookup. It took me a moment to realize the cause - it was due to having the following database settings set:

    Auto Create Statistics - On

    Auto Update Statistics - On

    Auto Update Statistics Asynchronously - On

    Testing with the various settings yields these results:

    If you do not Auto Create stats you only get a clustered index scan.

    If you Auto Create but do not Auto Update you also only get a clustered index scan.

    If you Auto Update stats (synchronously) you get the results in the answer (NC Index Seek & Key Lookup).

    And finally if you Auto Update Statistics Asynchronously the first run is clustered index scan with subsequent runs NC Index Seek & Key Lookup.