Problem

You suspect that you have indexes that are heavily fragmented. You need to run a query to confirm the fragmentation levels of the indexes in your database.

Solution

Query the sys.dm_db_index_physical_stats dynamic management function.

Fragmentation is the natural by-product of data modifications to a table. When data is updated in the database, the logical order of indexes (based on the index key) gets out of sync with the actual physical order of the data pages. As data pages become further and further out of order, more I/O operations are required in order to return results requested by a query. Rebuilding or reorganizing an index allows you to defragment the index by synchronizing the logical index order, reordering the physical data pages to match the logical index order.

The sys.dm_db_index_physical_stats dynamic management function returns information that allows you to determine the fragmentation level of an index. The syntax for sys.dm_db_index_physical_stats is as follows:

sys.dm_db_index_physical_stats (
{ database_id | NULL }
, { object_id | NULL }
, { index_id | NULL | 0 }
, { partition_number | NULL }
, { mode | NULL | DEFAULT }
)

Table 23-1 describes the arguments of this command.

In this example, the sys.dm_db_index_physical_stats dynamic management function is queried for all objects in the AdventureWorks2012 database with an average fragmentation percent greater than 30.

USE AdventureWorks2012;
GO
SELECT OBJECT_NAME(object_id) ObjectName,
index_id,
index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2012'),NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30
ORDER BY OBJECT_NAME(object_id);

This returns the following (abridged) results:

This second example returns fragmentation for a specific database, table, and index.

USE AdventureWorks2012;
GO
SELECT OBJECT_NAME(f.object_id) ObjectName,
        i.name IndexName,
        f.index_type_desc,
        f.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
        (DB_ID('AdventureWorks2012'), OBJECT_ID('Production.ProductDescription'), 2, NULL, 'LIMITED') f
INNER JOIN sys.indexes i
        ON i.object_id = f.object_id
        AND i.index_id = f.index_id;

This query returns the following:

How It Works

The first example started by changing the database context to the AdventureWorks2012 database.

USE AdventureWorks2012;
GO

Since the OBJECT_NAME function is database-context sensitive, changing the database context ensures that you are viewing the proper object name.

Next, the SELECT clause displayed the object name, index ID, description, and average fragmentation percent.

SELECT OBJECT_NAME(object_id) ObjectName, index_id, index_type_desc, avg_fragmentation_in_percent

The index_type_desc column tells you if the index is a heap, clustered index, nonclustered index, primary XML index, spatial index, or secondary XML index.

Next, the FROM clause referenced the sys.dm_db_index_physical_stats dynamic management function. The parameters, which were put in parentheses, included the database name and NULL for all other parameters except the scan mode.

FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2012'),NULL, NULL, NULL, 'LIMITED')

Since sys.dm_db_index_physical_stats is table-valued function, the WHERE clause was used to qualify that only rows with a fragmentation percentage greater than 30 percent be returned in the results.

WHERE avg_fragmentation_in_percent > 30

The query returned several rows for objects in the AdventureWorks2012 database with a fragmentation level greater than 30 percent. The avg_fragmentation_in_percent column shows logical fragmentation of nonclustered or clustered indexes, returning the percentage of disordered pages at the leaf level of the index. For heaps, avg_fragmentation_in_percent shows extent-level fragmentation. Regarding extents, recall that SQL Server reads and writes data at the page level. Pages are stored in blocks called extents, which consist of eight contiguous 8KB pages. Using the avg_fragmentation_in_percent, you can determine whether the specific indexes need to be rebuilt or reorganized using ALTER INDEX.

In the second example, fragmentation was displayed for a specific database, table, and index. The SELECT clause included a reference to the index name (instead of index number).

SELECT OBJECT_NAME(f.object_id) ObjectName, i.name IndexName, f.index_type_desc, f.avg_fragmentation_in_percent

The FROM clause included the specific table name, which was converted to an ID using the OBJECT_ID function. The third parameter included the index number of the index to be evaluated for fragmentation.

FROM sys.dm_db_index_physical_stats
(DB_ID('AdeventureWorks2012'),
OBJECT_ID('Production.ProductDescription'),
2,
NULL,
'LIMITED') f

The sys.indexes system catalog view was joined to the sys.dm_db_index_physical_stats function based on the object_id and index_id.

INNER JOIN sys.indexes i ON i.object_id = f.object_id AND i.index_id = f.index_id;

The query returned the fragmentation results just for that specific index.

Problem

After analyzing fragmentation levels of your indexes, you have determined that many indexes need to be rebuilt.

Solution

Rebuild the indexes using ALTER INDEX.

Rebuilding an index serves many purposes, the most popular being the removal of fragmentation that occurs as data modifications are made to a table over time. As fragmentation increases, query performance can slow. Rebuilding an index removes fragmentation of the index rows and frees up physical disk space.

Large indexes that are quite fragmented can reduce query speed. The frequency of how often you rebuild your indexes depends on your database size, how much data modification occurs, how much activity occurs against your tables, and whether your queries typically perform ordered scans or singleton lookups.

The syntax for ALTER INDEX to rebuild an index is as follows:

ALTER INDEX { index_name | ALL }
        ON <object>
        { REBUILD
        [ [ WITH ( <rebuild_index_option> [ ,. . .n ] ) ]
        | [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_index_option>
        [ ,. . . n ] )
        ]
        ]
        ]
        }

Table 23-2 describes the arguments of this command.

This recipe demonstrates ALTER INDEX REBUILD, which drops and re-creates an existing index. It demonstrates a few variations for rebuilding an index in the AdventureWorks2012 database.

-- Rebuild a specific index
USE AdventureWorks2012;
GO
ALTER INDEX PK_ShipMethod_ShipMethodID ON Purchasing.ShipMethod REBUILD;
-- Rebuild all indexes on a specific table
USE AdventureWorks2012;
GO
ALTER INDEX ALL
ON Purchasing.PurchaseOrderHeader REBUILD;
-- Rebuild an index, while keeping it available -- for queries (requires Enterprise Edition)
USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductReview_ProductReviewID
ON Production.ProductReview REBUILD WITH (ONLINE = ON);
-- Rebuild an index, using a new fill factor and -- sorting in tempdb
USE AdventureWorks2012;
GO
ALTER INDEX PK_TransactionHistory_TransactionID
ON Production.TransactionHistory REBUILD WITH (FILLFACTOR = 75, SORT_IN_TEMPDB = ON);
-- Rebuild an index with page-level data compression enabled
USE AdventureWorks2012;
GO
ALTER INDEX PK_ShipMethod_ShipMethodID
ON Purchasing.ShipMethod REBUILD WITH (DATA_COMPRESSION = PAGE);

How It Works

In this recipe, the first ALTER INDEX was used to rebuild the primary key index on the Purchasing.ShipMethod table (rebuilding a clustered index does not cause the rebuild of any nonclustered indexes for the table).

ALTER INDEX PK_ShipMethod_ShipMethodID ON Purchasing.ShipMethod REBUILD

In the second example, the ALL keyword was used, which means that any indexes, whether nonclustered or clustered (remember, only one clustered index exists on a table), will be rebuilt.

ALTER INDEX ALL
ON Purchasing.PurchaseOrderHeader REBUILD

The third example in the recipe rebuilt an index online, which means that user queries can continue to access the data of the PK_ProductReview_ProductReviewID index while it’s being rebuilt.

WITH (ONLINE = ON)

The ONLINE option requires SQL Server Enterprise Edition, and it can’t be used with XML indexes, disabled indexes, or partitioned indexes. Also, indexes using large object data types or indexes made on temporary tables can’t take advantage of this option.

In the fourth example, two index options were modified for an index—the fill factor and a directive to sort the temporary index results in tempdb.

WITH (FILLFACTOR = 75, SORT_IN_TEMPDB = ON)

In the previous example, an uncompressed index was rebuilt using page-level data compression.

WITH (DATA_COMPRESSION = PAGE)

Problem

In addition to the many indexes that need to be rebuilt, you have determined that several need to be defragmented.

Solution

Use ALTER INDEX REORGANIZE to reduce fragmentation in the leaf level of an index (clustered and nonclustered), causing the physical order of the database pages to match the logical order. During this reorganization process, the indexes are also compacted based on the fill factor, resulting in freed space and a smaller index. ALTER TABLE REORGANIZE is automatically an online operation, meaning that you can continue to query the target data during the reorganization process. The syntax is as follows:

ALTER INDEX { indexname | ALL } ON < object > { REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ] }

Table 23-3 describes the arguments of this command.

This recipe demonstrates how to defragment a single index, as well as all indexes on a single table.

-- Reorganize a specific index
USE AdventureWorks2012;
GO
ALTER INDEX PK_TransactionHistory_TransactionID
ON Production.TransactionHistory
REORGANIZE;
-- Reorganize all indexes for a table
-- Compact large object data types
USE AdventureWorks2012;
GO
ALTER INDEX ALL
ON HumanResources.JobCandidate
REORGANIZE
WITH (LOB_COMPACTION = ON);

How It Works

In the first example of this recipe, the primary key index of the Production.TransactionHistory table was reorganized (defragmented). The syntax was very similar to rebuilding an index, only instead of REBUILD, the REORGANIZE keyword was used.

In the second example, all indexes (using the ALL keyword) were defragmented for the HumanResources.Jobcandidate table. Using the WITH clause, large object data type columns were also compacted.

Use ALTER INDEX REORGANIZE if you cannot afford to take the index offline during an index rebuild (and if you cannot use the ONLINE option in ALTER INDEX REBUILD because you aren’t running SQL Server Enterprise Edition). Reorganization is always an online operation, meaning that an ALTER INDEX REORGANIZE operation doesn’t block database traffic for significant periods of time, although it may be a slower process than a REBUILD.

Problem

You have a table in the database that does not have a clustered index and is a heap. You have noticed that this table is nearly 90 percent fragmented, and you want to defragment that table.

Solution

Since SQL Server 2008, you can rebuild a heap (a table without a clustered index) using the ALTER TABLE command. In previous versions, rebuilding a heap required adding and removing a temporary clustered index or performing a data migration or table re-creation.

In this example, I will create a heap table (using SELECT INTO) and then rebuild it.

USE AdventureWorks2012;
GO
-- Create an unindexed table based on another table
SELECT ShiftID, Name, StartTime, EndTime, ModifiedDate
INTO dbo.Heap_Shift FROM HumanResources.Shift;

I can validate whether the new table is a heap by querying the sys.indexes system catalog view.

USE AdventureWorks2012;
GO
SELECT type_desc FROM sys.indexes
WHERE object_id = OBJECT_ID('Heap_Shift’);

This query returns the following:

type_desc
HEAP

If I want to rebuild the heap, I can issue the following ALTER TABLE command.

USE AdventureWorks2012;
GO
ALTER TABLE dbo.Heap_Shift REBUILD;

How It Works

In this recipe, I created a heap table and then rebuilt it using ALTER TABLE. . .REBUILD. Using ALTER TABLE. . .REBUILD, you can rebuild a table, even if it does not have a clustered index (heap). If the table is partitioned, this command also rebuilds all partitions on that table and rebuilds the clustered index if one exists.

Problem

You are concerned you may have some indexes in the database that are more costly than the benefit they provide or that are no longer being used. You want to find which indexes fit these criteria.

Solution

You can query the sys.dm_db_index_usage_stats dynamic management view (DMV).

Creating useful indexes in your database is a balancing act between read and write performance. Indexes can slow down data modifications while at the same time speed up SELECT queries. You must balance the cost/benefit of index overhead with read activity versus data modification activity. Every additional index added to a table may improve query performance at the expense of data modification speed. On top of this, index effectiveness changes as the data changes, so an index that was useful a few weeks ago may not be useful today. If you are going to have indexes on a table, they should be put to good use on high-priority queries.

To identify disused indexes, you can query the sys.dm_db_index_usage_stats DMV. This view returns statistics on the number of index seeks, scans, updates, or lookups since the SQL Server instance was last restarted. It also returns the last dates the index was referenced.

In this example, the sys.dm_db_index_usage_stats DMV is queried to see whether the indexes on the Sales.Customer table are being used. Prior to referencing sys.dm_db_index_usage_stats, two queries will be executed against the Sales.Customer table: one query returning all rows and columns and the second returning the AccountNumber column for a specific TerritorylD.

USE AdventureWorks2012;
GO
SELECT *
FROM Sales.Customer;
USE AdventureWorks2012;
GO
SELECT AccountNumber
FROM Sales.Customer
WHERE TerritoryID = 4;

After executing the queries, the sys.dm_db_index_usage_stats DMV is queried.

USE AdventureWorks2012;
GO
SELECT i.name IndexName, user_seeks, user_scans, last_user_seek, last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE database_id = DB_ID('AdventureWorks2012')
AND s.object_id = OBJECT_ID('Sales.Customer'),

This query returns the following:

How It Works

The sys.dm_db_index_usage_stats DMV allows you to see what indexes are being used in your SQL Server instance. The statistics are valid since the last SQL Server restart.

In this recipe, two queries were executed against the Sales.Customer table. After executing the queries, the sys.dm_db_index_usage_stats DMV was queried.

The SELECT clause displayed the name of the index, the number of user seeks and user scans, and the dates of the last user seeks and user scans.

SELECT i.name IndexName, user_seeks, user_scans, last_user_seek, last_user_scan

The FROM clause joined the sys.dm_db_index_usage_stats DMV to the sys.indexes system catalog view (so the index name could be displayed in the results) on object_id and index_id.

FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON
s.object_id = i.object_id AND
s.index_id = i.index_id

The WHERE clause qualified that only indexes for the AdventureWorks2012 database be displayed and, of those indexes, only those for the Sales.Customer table. The DB_ID function was used to get the database system ID, and the OBJECT_ID function was used to get the table’s object ID.

WHERE database_id = DB_ID('AdventureWorks2012')
AND s.object_id = OBJECT_ID('Sales.Customer'),

The query returned two rows, showing that the PK_Customer_CustomerID clustered index of the Sales.Customer table had indeed been scanned recently (most likely by the first SELECT * query) and the IX_Customer_TerritoryID nonclustered index had been used in the second query (which qualified TerritoryID = 4).

Indexes assist with query performance but also add disk space and data modification overhead. Using the sys.dm_db_index_usage_stats DMV, you can monitor whether indexes are actually being used and, if not, replace them with more effective indexes.

Problem

You have noticed that a high-use query is performing poorly. After some investigation, you have noted that AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS are enabled. You are certain that new statistics are needed.

Solution

Use the CREATE STATISTICS command and create new statistics.

SQL Server will usually generate the statistics it needs based on query activity. However, if you still want to explicitly create statistics on a column or columns, you can use the CREATE STATISTICS command.

The syntax is as follows:

CREATE STATISTICS statistics_name ON { table | view } ( column [ ,. . .n ] )
[ WHERE < filter_predicate> ]
[ WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT | ROWS } STATS_STREAM = stats_stream ] [ , ] ]
[ NORECOMPUTE ] ]

Table 23-4 describes the arguments of this command.

In this example, new statistics are created on the Sales.Customer AccountNumber column.

USE AdventureWorks2012;
GO
CREATE STATISTICS Stats_Customer_AccountNumber
ON Sales.Customer (AccountNumber) WITH FULLSCAN;

How It Works

This recipe demonstrated manually creating statistics on the Sales.Customer table. The first line of code designated the statistics name.

CREATE STATISTICS Stats_Customer_AccountNumber

The second line of code designated the table to create statistics on, followed by the column name used to generate the statistics.

ON Sales.Customer (AccountNumber)

The last line of code designated that all rows in the table would be read in order to generate the statistics.

WITH FULLSCAN

Using the FULLSCAN option will typically take longer to generate but provide a higher-quality sampling. The default behavior in SQL Server is to use SAMPLE with an automatically determined sample size.

Problem

You have a very large table that is frequently queried. Most of the queries performed are against a range of data that comprises less than 20 percent of the records in the table. You have determined that the indexes are appropriate, but you may be missing a statistic. You want to improve the performance of these queries.

Solution

Create filtered statistics.

In Chapter 16, in Recipe 16-17, I demonstrated the ability to create filtered, nonclustered indexes that cover a small percentage of rows. Doing this reduced the index size and improved the performance of queries that needed to read only a fraction of the index entries that they would otherwise have to process. Creating the filtered index also creates associated statistics.

These statistics use the same filter predicate and can result in more accurate results because the sampling is against a smaller row set.

You can also explicitly create filtered statistics using the CREATE STATISTICS command. Similar to creating a filtered index, filtered statistics also support filter predicates for several comparison operators to be used, including IS, IS NOT, =, <>, >, <, and more.

The following query demonstrates creating filtered statistics on a range of values for the UnitPrice column in the Sales.SalesOrderDetail table:

USE AdventureWorks2012;
GO
CREATE STATISTICS Stats_SalesOrderDetail_UnitPrice_Filtered ON Sales.SalesOrderDetail (UnitPrice)
WHERE UnitPrice > = 1000.00 AND UnitPrice < = 1500.00
WITH FULLSCAN;

How It Works

This recipe demonstrated creating filtered statistics. Similar to filtered indexes, I just added a WHERE clause within the definition of the CREATE STATISTICS call and defined a range of allowed values for the UnitPrice column. Creating statistics on a column creates a histogram with up to 200 interval values designating how many rows are at each interval value, as well as how many rows are smaller than the current key but less than the previous key. The query optimization process depends on highly accurate statistics. Filtered statistics allow you to specify the key range of values your application focuses on, resulting in even more accurate statistics for that subset of data.

Problem

You have created some statistics on a table in your database and now want to update them immediately.

Solution

You can use the UPDATE STATISTICS command.

The syntax is as follows:

UPDATE STATISTICS table | view
        [
        {
        { index | statistics_name }
        | ( { index |statistics_name } [ ,. . .n ] )
        }
        ]
        [ WITH
        [
        [ FULLSCAN ]
        | SAMPLE number { PERCENT | ROWS }
        | RESAMPLE
        ]
        [ , ] [ ALL | COLUMNS | INDEX ]
        [ [ , ] NORECOMPUTE ]
        ]

Table 23-5 shows the arguments of this command.

This example updates all the statistics for the Sales.Customer table, populating statistics based on the latest data.

USE AdventureWorks2012;
GO
UPDATE STATISTICS Sales.Customer
WITH FULLSCAN;

How It Works

This example updated all the statistics for the Sales.Customer table, refreshing them with the latest data. The first line of code designated the table name containing the statistics to be updated.

UPDATE STATISTICS Sales.Customer

The last line of code designated that all rows in the table would be read in order to update the statistics.

WITH FULLSCAN

Problem

You are benchmarking new queries and do not want to wait for the query optimizer to create new single-column statistics.

Solution

Execute sp_createstats to create single-column statistics.

You can automatically generate statistics across all tables in a database for those columns that don’t already have statistics associated with them, by using the system stored procedure sp_createstats. The syntax is as follows:

sp_createstats [ [ @indexonly = ] 'indexonly' ] [ , [ @fullscan = ] 'fullscan' ] [ , [ @norecompute = ] 'norecompute' ]

Table 23-6 describes the arguments of this command.

This example demonstrates creating new statistics on columns in the database that don’t already have statistics created for them.

USE AdventureWorks2012;
GO
EXECUTE sp_createstats;
GO

This returns the following (abridged) result set:

Table 'AdventureWorks2012.Production.ProductProductPhoto':
Creating statistics for the following columns:
       Primary
       ModifiedDate
Table 'AdventureWorks2012.Sales.StoreContact':
Creating statistics for the following columns:
       ModifiedDate
Table 'AdventureWorks2012.Person.Address':
Creating statistics for the following columns:
       AddressLine2
       City
       PostalCode
       ModifiedDate
…

How It Works

This example created single-column statistics for the tables within the AdventureWorks2012 database, refreshing them with the latest data.

Problem

You want to update statistics across all tables in the current database.

Solution

You can execute the stored procedure sp_updatestats.

If you want to update all statistics in the current database, you can use the system stored procedure sp_updatestats. This stored procedure updates statistics only when necessary (when data changes have occurred). Statistics on unchanged data will not be updated.

The next example automatically updates all statistics in the current database.

USE AdventureWorks2012;
GO
EXECUTE sp_updatestats;
GO

This returns the following (abridged) results. Notice the informational message of “update is not necessary.” The results you see may differ based on the state of your table statistics.

Updating [Production].[ProductProductPhoto]
[PK_ProductProductPhoto_ProductID_ProductPhotoID], update is not necessary. . .
[AK_ProductProductPhoto_ProductID_ProductPhotoID], update is not necessary. . .
[_WA_Sys_00000002_01142BAl], update is not necessary. . .
[Primary], update is not necessary. . .
[ModifiedDate], update is not necessary. . .
0 index(es)/statistic(s) have been updated, 5 did not require update.
. . .

How It Works

This example updated statistics for the tables within the AdventureWorks2012 database, updating only the statistics where data modifications had impacted the reliability of the statistics.

Problem

You want to see detailed information about column statistics.

Solution

To view detailed information about column statistics, you can use the DBCC SHOW STATISTICS command.

The syntax is as follows:

DBCC SHOW_STATISTICS ( 'tablename' | 'viewname' , target )
[ WITH [ NO_INFOMSGS ]
< STAT_HEADER | DENSITY_VECTOR | HISTOGRAM > [ , n ] ]

Table 23-7 shows the arguments of this command.

This example demonstrates how to view the statistics information on the Sales.Customer Stats_Customer_CustomerType statistics.

USE AdventureWorks2012;
GO
DBCC SHOW_STATISTICS ('Sales.Customer' , Stats_Customer_AccountNumber);

This returns the following result sets:

How It Works

This recipe demonstrated how to get more specific information about column statistics. In the results of this recipe’s example, the All density column points to the selectivity of a column. Selectivity refers to the percentage of rows that will be returned given a specific column’s value. Columns with a low density and high selectivity often make for useful indexes (useful to the query optimization process).

In this recipe’s example, the All density value was 5.045409E-05 (float), which equates to a decimal value of 0.00005045409. This is the result of dividing 1 by the number of rows, in this case 19,820.

If you had a column with a high density of similar values and low selectivity (one value is likely to return many rows), you can make an educated assumption that an index on this particular column is unlikely to be very useful to SQL Server in generating a query execution plan.

Problem

You have finished a cycle in your benchmarking and want to remove statistics that were created during that cycle.

Solution

To remove statistics, use the DROP STATISTICS command. The syntax is as follows:

DROP STATISTICS table.statistics_name | view.statistics_name [ ,. . . n ]

This command allows you to drop one or more statistics, prefixed with the table or indexed view name.

In this example, the Sales.Customer_Stats_Customer_AccountNumber statistics are dropped from the database.

USE AdventureWorks2012;
GO
DROP STATISTICS Sales.Customer.Stats_Customer_AccountNumber;

How It Works

This recipe dropped user-created statistics using DROP STATISTICS. The statistics were dropped using the three-part name of schema.table.statistics_name.