Performance and Tuning Guide

Home

Performance and Tuning Guide

Chapter 18: Tuning with dbcc traceon

The Table Information Block

This sample output shows the table information block for a query on the titles table:

Beginning selection of qualifying indexes for table 'titles',
correlation name 't', varno = 0, objectid 208003772.
   The table (Datapages) has 5000 rows, 736 pages,
   Data Page Cluster Ratio 0.999990
   The table has 5 partitions.
   The largest partition has 211 pages.
   The partition skew is 1.406667.

Identifying the Table

The first two lines identify the table, giving the table name, the correlation name (if one was used in the query), a varno value that identifies the order of the table in the from clause, and the object ID for the table.

In the query, titles is specified using "t" as a correlation name, as in:

    from titles t

The correlation name is included in the output only if a correlation name was used in the query. The correlation name is especially useful when you are trying to analyze the output from subqueries or queries doing self-joins on a table, such as:

    from sysobjects o1, sysobjects o2

Basic Table Data

The next lines of output provide basic data about the table: the locking scheme, the number of rows, and the number of pages in the table. The locking scheme is one of: Allpages, Datapages, or Datarows.

Cluster Ratio

The next line prints the data page cluster ratio for the table.

Partition Information

The following lines are included only for partitioned tables. They give the number of partitions, plus the number of pages in the largest partition, and the skew:

The table has 5 partitions.
The largest partition has 211 pages.
The partition skew is 1.406667.

This information is useful if you are tuning parallel queries, because:

Costing for parallel queries is based on the cost of accessing the table's largest partition.
The optimizer does not choose a parallel plan if the partition skew is 2.0 or greater.

See Chapter 11, "Introduction to Parallel Query Processing," for more information on parallel query optimization.


	Expand Search