Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the following fairly simple query:
SELECT * FROM Table1 WHERE IndexedIntField1 = 12345 AND NonIndexedVarcharField IN ('Value1', 'Value2', 'Value3')
The number of results from this query were roughly 500 rows (from a table that holds about 10 billion rows) and the execution plan was essentially a nonclustered index seek with a key look up.
Is this normal?
- Table1 is an actual materialized regular disk-based table (nothing special going on here).
- It's about 30 columns wide.
- There's the 1 clustered index and 4 nonclustered indexes on it.
- The "IndexedIntField1" in my example is part of the index key in 2 of the nonclustered indexes, and is an included column on a third nonclustered index.
- "NonIndexedVarcharField" is not a key nor included on any of the indexes.
- We update statistics on the table and indexes at least once a week (and sometimes as much as once a day or more)
- No fancy calculated columns are on this table
- The indexes on the table are pretty simple, only a couple of fields in the key columns / included columns EXCEPT one of the indexes that have "IndexedIntField1" as a key column does include about 15 columns on it (so it's a rather unusually big index).