BlogmyQuery – BMQ

(2006-09-01 added a paragraph on parallel query plans)

In SQL Server, “Statistics Profile” is a mode in which a query is run where you can see the number of invocations of each physical plan operator in the tree. Instead of running a query and just printing the output rows, this mode also collects and returns per-operator row counts. Statistics Profile is used by the SQL Query Optimizer Team to identify issues with a plan which can cause the plan to perform poorly. For example, it can help identify a poor index choice or poor join order in a plan. Oftentimes, it can help identify the needed solution, such as updating statistics (as in the histograms and other statistical information used during plan generation) or perhaps adding a plan hint. This document describes how to read the statistics profile information from a query plan so that you can also debug plan issues.

A simple example query demonstrates how to retrieve the statistics profile output from a query:

use nwind

set statistics profile on

select * from customers c inner join orders o on c.customerid = o.customerid;

(continue reading...)

If you read the Books Online page describing the UPDATE STATISTICS command, you will see that there are some undocumented options.

UPDATE STATISTICS table | view [ { { index | statistics_name } | ( { index |statistics_name } [ ,...n ] ) } ] [    WITH [ [ FULLSCAN ] | SAMPLE number { PERCENT | ROWS } ] | RESAMPLE | <update_stats_stream_option> [ ,...n ] ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] ] ; <update_stats_stream_option> ::= [ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric contant ]

<update_stats_stream_option>

This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time.

There is a very good reason why these options are undocumented. They are meant for testing and debugging purposes, and should never ever be used on production systems.

Not everyone knows that query level hints (like loop join) will impact the entirety of a DML query plan. This includes foreign key validation and indexed view maintenance.

Let us look at an example with two tables involved in a foreign key constraint.

use tempdb

go

create table department(deptid int primary key clustered, deptname varchar(10))

go

create table employee(empid int primary key clustered, empname varchar(10), deptid int references department(deptid))

go

insert department values(1, 'Optimizer')

go

(continue reading...)

A question we are frequently asked is what happens when an update statement assigns a column to its same current value. For example,

use tempdb

go

create table t(i int, cc as i + 1)

create index t_cc on t(cc)

go

insert into t values(1)

go

update t set i = 1

go

update t set i = i

go

SQL is a declarative language that returns multi-sets (sets allowing duplicates) of rows. The operations exposed in SQL, such as join, filter, group by, and project, do not inherently have any ordering guarantees. ANSI SQL does expose an ORDER BY clause for the top-most SELECT scope in a query, and this can be used to return rows in a presentation order to the user (or to a cursor so that you can iterate over the results of query). This is the only operation in ANSI SQL that actually guarantees row order.

Microsoft SQL Server provides additional ordering guarantees beyond ANSI, mostly for backwards-compatibility with previous releases of the product. For example, variable assignment in the top-most SELECT list when an ORDER BY is specified is done in the presentation order of the query.

Example:

SELECT @a = @a + col FROM Table ORDER BY col2;