Monday, April 6, 2015

SQL Server Query Optimizer Inputs


At a very high level, the Query Optimizer consider the following inputs During Compilation & Query Execution

• Column data types used in query
• Data types of indexed columns
• The SQL Query and/or Stored Procedure
• If the query or stored procedure is parameterized, then the value of the parameters
• Knowledge about the index structures of table(s) to be accessed
• Statistics of index(es) and columns of the table(s) to be accessed
• Index Fragmentation and Fill Factor percentage
• Hints assigned to the query/Join
• Cost threshold for parallelism
• Global max degree of parallelism setting
• Option (Maxdop ?) // where? is integer value
• Cardinality Estimation(specially SQL Server 2014)
         o Trace Flag 9481 for Old
         o Trace flag 2312 for New
         o Database Compatibility Level
         o Version of Database
• The way Query/Procedure written to get result
• Server/memory pressure
• Database status(locking/blocking/pressure/plan etc)

Note:
There might be some other inputs which query optimizer consider but above are very high level points which we need to take in consideration.


1 comment :