This DBCC statement is used to obtain information about an index or table that can be used to investigate performance degradation. It is a very useful tool for performance analysis. Some of the output is a little arcane and is not very useful, but that is more than made up for by the fact that DBCC SHOWCONTIG outputs useful information concerning the level of fragmentation that has occurred in a table—in other words, the level of page splitting. The following DBCC SHOWCONTIG output was from the Accounts table after it had been loaded with 12,500 rows with even val- ues in the account_no column and a clustered index created on the account_no column.
DBCC SHOWCONTIG scanning 'accounts' table...
Table: 'accounts' (709577566); index ID: 1, database ID: 7
IsFulltextKey Index is the full-text key for a table.
1 = True, 0 = False, NULL = Invalid input.
IsHypothetical Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.
1 = True, 0 = False, NULL = Invalid input.
IsPadIndex Index specifies space to leave open on each interior node.
1 = True, 0 = False, NULL = Invalid input.
IsPageLockDisal- lowed
Page locking is disallowed through sp_indexoption.
1 = disallowed, 0 = allowed, NULL = Invalid input. IsRowLockDisal-
lowed
Row locking is disallowed through sp_indexoption.
1 = disallowed, 0 = allowed, NULL = Invalid input. IsStatistics Index was created by the CRE-
ATE STATISTICS statement or by the auto create statistics option sp_dboption. Statistics indexes are used as a placeholder for column-level statistics.
1 = True, 0 = False, NULL = Invalid input.
IsUnique Index is unique. 1 = True, 0 = False, NULL = Invalid input.
Table 5.3 Property values for the INDEXPROPERTY function (continued)
TABLE level scan performed.
- Pages Scanned : 695 - Extents Scanned : 88 - Extent Switches : 87 - Avg. Pages per Extent : 7.9
- Scan Density [Best Count:Actual Count] : 98.86% [87:88] - Logical Scan Fragmentation : 12.52%
- Extent Scan Fragmentation : 0.00% - Avg. Bytes Free per Page : 380.2 - Avg. Page Density (full) : 95.30%
The above was taken from a filled database available from the previous edition of this book.
The first line of output, Pages Scanned, is the number of pages in the page chain; in our example, it is the number of pages in the table (dpages in sysindexes). Another way of looking at this item is that it has taken 695 pages to hold the 12,500 rows. Since a page will hold about 18 rows by the time you have taken away the 96-byte page header and other overhead from the 8 Kb page size, this is in the right ballpark.
Extents Scanned is the number of extents read, which means that this is the number of extents used to hold the data rows. Since we have 695 pages, the best we can hope for is (number of pages/8 pages per extent) extents to hold the data. In our case 695/8 is 86.9, and, therefore, the best we can hope for is to hold the data in 87 extents. The data is actually held in 88 extents, slightly over our theoretical calculation but because of the initial allocation in mixed extents, this is reasonable.
Extent Switches is the number of times the DBCC statement moved off an extent while it was scanning the pages in the extent. We would expect an extent switch to happen after the whole extent had been scanned and a new extent needed to be scanned next. Our extent switches value is 87, which is expected, since the jump onto the first extent is not counted.
The Average Pages per Extent is merely the number of pages per extent, which is the (number of pages/number of extents). In our example this is 695/88, which gives us 7.9.
Perhaps the most useful line of output is the Scan Density [Best Count:Actual Count]. This is our measure of fragmentation. The Best Count is the ideal number of extents used to hold our data pages if every- thing is contiguously linked, whereas the Actual Count is the actual num-
ber of extents used to hold our data pages. The Scan Density is the ratio of these two values expressed as a percentage. In other words ([Best Count/ Actual Count] * 100). In our example Scan Density is 87/88 * 100, giving us 98.86 percent, which is close enough to perfect—we are pretty much utilizing our data pages and extents in the most effective way.
The Logical Scan Fragmentation and Extent Scan Fragmentation are not particularly useful, but they do represent the non-contiguity of pages and extents in the index leaf level. The Average Bytes Free per Page and Avg. Page Density (full) are a measure of the average free bytes on the pages in the chain and the percentage of fullness, respectively. These are values that are affected by the FILLFACTOR used.
Next, 12,500 rows with odd values in the account_no column were loaded. This results in page splitting, since the even-numbered rows now have odd-numbered rows inserted between them.
Output after loading 12,500 rows with odd values in the account_no column:
DBCC SHOWCONTIG (accounts)
DBCC SHOWCONTIG scanning 'accounts' table...
Table: 'accounts' (709577566); index ID: 1, database ID: 7 TABLE level scan performed.
- Pages Scanned : 1389 - Extents Scanned : 176 - Extent Switches : 1388 - Avg. Pages per Extent : 7.9
- Scan Density [Best Count:Actual Count] : 12.53% [174:1389] - Logical Scan Fragmentation : 50.04%
- Extent Scan Fragmentation : 1.14% - Avg. Bytes Free per Page : 374.6 - Avg. Page Density (full) : 95.37%
The above was taken from a filled database available from the previous edition of this book.
After loading our second batch of 12,500 rows, we can see that the situ- ation has deteriorated. We have doubled the number of rows in the table and the Pages Scanned value is now 1,389, which is double the number of pages scanned previously, 695. The number of extents used to hold the data
is now 176, which, again, is not far off from double the number we have just seen, which was 88. The most dramatic increase is in the number of extent switches performed, which is now 1,388—about 16 times greater than the previous value. This gives us a Scan Density of only 12.53 percent. The bottom line is that there is much page fragmentation. Many pages have been inserted into the original page chain and SQL Server would have to jump around a lot to scan this table. Note also that the page fullness has not changed much. This is often not the case with real-world applications. After page splitting, pages are often found to be between two-thirds and three-quarters full. This is common when page splitting is occurring and is due to the fact that 50:50 splitting is taking place, as mentioned earlier in this chapter. An index rebuild, preferably with an appropriate FILLFAC- TOR value, would be advisable here.
The full syntax of the DBCC SHOWCONTIG statement is as follows:
DBCC SHOWCONTIG [
( { table_name | table_id | view_name | view_id } [ , index_name | index_id ] )
] [ WITH
{ ALL_INDEXES
| FAST [, ALL_INDEXES]
| TABLERESULTS [, {ALL_INDEXES}] [, {FAST | ALL_LEVELS}] }
]
IDs may be used instead of names, if preferred. The index name is optional and if omitted DBCC SHOWCONTIG reports information for the table—unless there is a clustered index on the table, in which case it reports information for that. So, if you want to report on a non-clustered index, it should be named. The option ALL_INDEXES outputs informa- tion on all the indexes on the table. The FAST option specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the data on each page. The TABLERESULTS option displays results as a rowset and also outputs extra information. Some of this extra information can be very useful. For example, the number of rows ref- erenced by forwarding pointers (as discussed in Chapter 4) is output. By default, information pertaining to a table’s data pages (also by convention
the clustered index leaf-level pages) or the non-clustered index leaf-level index pages is output. If the ALL_LEVELS option is specified, information pertaining to all index levels is output.
5.11
Creating indexes on views
Unlike previous versions of SQL Server, in SQL Server 2000 indexes can be created on a view, if its definition meets certain criteria. Unlike a non- indexed view, which does not physically hold data, an indexed view has its result physically stored in the database. Any modifications to the base data are reflected in the indexed view, so they are best created on tables that are changed infrequently.
The first index created on a view that is to be indexed must be a unique clustered index. Other indexes may then be created. For a view to be indexed it must satisfy a number of criteria.
One criterion is that it must be created with the SCHEMABINDING option. This option binds the view to the schema of the underlying base tables. This means that any views or tables participating in the view cannot be dropped, unless that view is dropped or changed so that it no longer has schema binding. Also, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition. Some, but not all, of the other criteria are as follows:
The view must only use base tables in its definition—no views.
Any user-defined functions in the view must use the SCHEMA- BINDING option.
The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON for the connection that defined the view.
The ANSI_NULLS option must have been set to ON for the con- nection that defined the tables referenced by the view.
The base tables referenced in the view must be in the same database and have the same database owner.
Base tables and user-defined functions referenced in the view must use a two-part name. No other combination of names is allowed.
All functions referenced by expressions in the view must be determin- istic. This means that for a given set of inputs, the same result is always returned.
The select_list of the SELECT statement in the view must not include the * notation—the columns must be listed explicitly.
Columns must not appear more than once, unless they appear the second time (or third time, etc.) in a complex expression. The select_list Col1, Col2 is valid and so is Col1, Col2, Col1+Col2 but not Col1, Col2, Col1.
Also not allowed are derived tables, rowset functions, the UNION operator, subqueries, outer or self joins, the TOP clause, the ORDER BY clause, the DISTINCT keyword, and COUNT(*); however, COUNT_BIG(*) is allowed.
If the AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggre- gate functions are specified in queries referencing the indexed view, the optimizer can often calculate the result if the view select_list contains SUM and COUNT_BIG. For example, AVG() can be cal- culated from SUM() / COUNT_BIG().
A SUM function that references an expression that can be nullable is not allowed.
The full-text search predicates CONTAINS or FREETEXT are not allowed.
The view select_list cannot contain aggregate expressions unless a GROUP BY is present.
If GROUP BY is present, the view select_list must contain a COUNT_BIG(*) expression, and the view definition cannot include HAVING, CUBE, or ROLLUP.
A column that results from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view.
We’ve not finished yet! Indexes created on the view have some restric- tions also, as shown in the following list. Most importantly, the first index that is created on the view must be clustered and unique.
The user executing the CREATE INDEX statement must be the owner of the view.
The following options must be set to ON for the connection creating the index: CONCAT_NULL_YIELDS_NULL, ANSI_NULLS,
ANSI_PADDING, ANSI_WARNINGS, and ARITHABORT. The QUOTED_IDENTIFIERS and NUMERIC_ROUNDABORT options must be set to OFF.
Even if the CREATE INDEX statement does not reference them, the view cannot include text, ntext, or image columns.
If the SELECT statement in the view definition specifies a GROUP BY clause, then the key of the unique clustered index can reference only columns specified in the GROUP BY clause.
An example view definition is as follows:
CREATE VIEW dbo.BranchTotalFunds WITH SCHEMABINDING AS SELECT branch_no, COUNT_BIG(*) AS AccountInstances, SUM(balance) AS TotalBalance FROM dbo.accounts GROUP BY branch_no
The following clustered index can now be created:
CREATE UNIQUE CLUSTERED INDEX CIV_BranchTotalFunds ON dbo.BranchTotalFunds (branch_no)
Although the clustered index key will only contain the branch_no col- umn, being a clustered index, the complete set of data rows with all the col- umns will be stored at the clustered index leaf level in the database. Non- clustered indexes may also now be created on the indexed view if desired.
The query optimizer automatically makes use of indexed views—they do not have to be named explicitly—however, this is only true of the Enter- prise Edition. We will discuss this behavior in Chapter 7.