Thursday, July 7, 2011

Top 10 Index T-SQL Statements That Pacific SQL Server DBAs Must Know

Top 10 Index T-SQL Statements That Pacific SQL Server DBAs Must Know
Hola Mon friends! Lol. Well, in the spirit of job survival, I have put this together to (Hopefully) help all of us learn a few things. I’ll try to be sending something new every day from today. Hopefully I’ll have time to maintain this. Good luck peeps.
First of all, all 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 database object (table or view) that speeds up the retrieval of rows from that table or view.
This piece lists the top ten index-related T-SQL statements that are “A MUST” for all Pacific SQL Server DBA’s. The T-SQL statements covered in this article are classified into three categories: Index Definition or Create, Query — Query index related information and Maintenance.
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 ) );
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
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

No comments:

Post a Comment