SQL Server DBAs know that indexes in databases are very similar to
indexes in libraries. An index in a database is a structure associated with a
table or view that speeds retrieval of rows from the table or view.
This article lists the top index-related T-SQL statements that are
useful for SQL Server database administrators. The T-SQL statements covered in
this article are classified into three categories: Index Definition or Create,
Query — Query index related information and Maintenance.
Definition - Create Index
1. Clustered Index
Clustered indexes store the data rows in sorted order in the table
based on their key values. Only one clustered index can be created per table,
because the data rows themselves can only be sorted in one order.
A clustered index can be created while creating constraints like
primary key on an existing table. Example:
ALTER TABLE [MyAddress]
ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
) ON [PRIMARY]
GO
A clustered index can also be created on a column with no
constraints related clause. Example:
CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1]
(
[ID] ASC
)ON [PRIMARY]
GO
2. Non Clustered Index
Generally, nonclustered indexes are created to improve the
performance of frequently used queries not covered by the clustered index. In a
nonclustered index, the logical order of the index does not match the physical
stored order of the rows on disk.
A nonclustered Index can be created on an existing table covering
columns not covered by clustered index. Example:
CREATE UNIQUE NONCLUSTERED INDEX
[NIX_col5_col2_col3_col4_col6]
ON [MyAddress]
(
[AddressLine1] ASC,
[AddressLine2] ASC,
[City] ASC,
[StateProvinceID] ASC,
[PostalCode] ASC
)ON [PRIMARY]
GO
A nonclustered index can also be created while creating
constraints on the existing table. Example:
ALTER TABLE [MyAddressType]
ADD CONSTRAINT [DEFF_MyAddressType_ModifiedDate]
DEFAULT (getdate()) FOR [ModifiedDate]
GO
3. XML Index
An XML index can be created on an XML column and the table must
have a clustered index on the primary key. The XML index can be primary or
secondary.
A primary XML index can be created as shown below:
CREATE PRIMARY XML INDEX idx_xCol_MyTable on MyTable (xCol)
A secondary XML index can be created as shown below:
CREATE TABLE MyTable (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_MyTable_XmlCol
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_MyTable_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR VALUE
GO
4. Spatial Index
SQL Server 2008 provided a special type of column called a spatial
column, which is a table column that contains data of a spatial data type, such
as geometry or geography.
A spatial index can be created using the following syntax:
CREATE TABLE MySpatialTable(id int primary key, geometry_col geometry);
CREATE SPATIAL INDEX SIndx_MySpatialTable_geometry_col1
ON MySpatialTable(geometry_col)
WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
Query Index related metadata
5. Find all Indexes
The following query can be used to query all the tables, columns
and indexes on the current database:
SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema],
BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc]
FROM sys.[tables] AS BaseT
INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]
6. Fragmentation
The following query can be used to find the index fragmentation on
all the tables in the current database:
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc
7. Missing index
SQL Server keeps track of the indexes that it thinks you should
create that will help in improving the performance of queries. The following
query list all missing indexes.
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC
8. Unused index
The following statement lists all the indexes that have not been
used. This also generates the DROP index statement which can come handy when
deleting the indexes.
SELECT o.name, indexname=i.name, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads
Index Maintainenance
9. Rebuild index
When an index gets fragmented, it requires defragmentation.
Defragmentation can be done using the rebuild clause when altering a table.
This command is equivalent to DBCC DBREINDEX in SQL Server versions prior to
2005. The command that can be used to rebuild the index is as follows:
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
If ALL is not specified in rebuild, it will not rebuild a
nonclustered index.
10. REORGANIZE index
Specifies that the index leaf level will be reorganized. The
REORGANIZE statement is always performed online. This command is equivalent to
DBCC INDEXDEFRAG in SQL Server versions prior to 2005.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
Conclusion
I hope you've found useful this list of the top Index-related
T-SQL statements that are useful for SQL Server database administrators.
No comments:
Post a Comment