Microsoft Dynamics NAV - C/SIDE Solution Development -

Microsoft Dynamics NAV – C/SIDE Solution Development

Locus IT ServicesMicrosoft Dynamics NAV – C/SIDE Solution Development

Lesson 5: SQL Server Query Optimizer

SQL Server Query Optimizer

Query Optimizer is the brain of SQL Server when it decides how to execute a query. SQL Server collects statistics about individual columns (single-column statistics) or sets of columns (multicolumn statistics). Query Optimizer uses statistics to estimate the selectivity of expressions, and therefore, the size of intermediate and final query results. Good statistics let the optimizer accurately assess the cost of different query plans and select a high-quality plan. All information about a single statistics object is stored in several columns of a single row in the sysindexes table, and in a statistics binary large object (statblob) that is kept in an internal-only table.

SQL Server Statistics

SQL Server maintains some information at the table level. Tables are not part of a statistics object, but SQL Server uses them occasionally during query cost estimation. The following data is stored at the table level:

  • Number of rows in the table or index (rows column in sys.sysindexes)
  • Number of pages that are occupied by the table or index (dpages column in sys.sysindexes)

SQL Server collects the following statistics about table columns and stores them in a statistics object (statblob):

  • Time that the statistics are collected
  • Number of rows that are used to produce the histogram and density information (described later)
  • Average key length
  • Single-column histogram that includes the number of steps

A histogram is a set of up to 200 values of a given column. All or a sample of the values in a given column are sorted. The ordered sequence is divided into up to 199 intervals so that the most statistically significant information is captured. Generally, these intervals are not of equal size.

Suppose that a user filters on the Document Type column in the Cust. Ledger Entry table, for example, to look for all Credit Memo type entries. The Credit Memo type entries have a value of Document Type that is equal to three.

Locus IT Project Management Office
What’s it?