SQL Server Performance Tuning - Indexing

CLUSTERED INDEX:

Clustered index physically sorts the data based on a single or composite column in a table. A Table without clustered index (HEAP) is not stored in any particular physical order. SQL Server has the ability to sequentially read the data in a clustered index an extent (8 data pages, or 64K) at a time.

Note:  Create only one clustered index per table.            

           

HEAP: Add a non-clustered index on an appropriate column or columns, the data is not physically ordered. SQL Server has to read the data from disk randomly using 8K pages.

DISADVANTAGE OF HEAP:

            It creates a lot of extra work for the disk subsystem to retrieve the same data, impact the performance.

            Rebuild indexes to reduce fragmentation, heaps are not defragmented, because are not indexes. A help will become more and more fragmented, further impact the performance, after some time.

            Adding a clustered index will insure that the table can be defragmented when indexes are rebuilt.

Note: Clustered index should be added to virtually all tables.

Missing Indexes:

A lack of useful indexes can result in a high-CPU condition. SQL Server is dependent on meaningful indexes to retrieve data efficiently, and missing indexes can often cause execution CPU utilization.

A lack of useful indexes can result in expensive operations such as hash joins and sorts that could otherwise be avoided with improved indexes.

Index Usage Statistics Report

            Adding indexes can be the fastest way to solve a performance problem. Repeated applications of that solution can be a pretty quick way to cause a performance problem.

To balace faster reads against slower insert/ update/ deletes, the database administrator needs to know how each index is used and how much it is being updated.

If an index is heavily used for weeks and then suddenly is no longer needed due to a query change or the presence of a better index, the number of User Seeks and User Scans will still seem high.

To mitigate this issue, the DBA needs to check the Last User Seek, Last User Scan, and Last User Lookup Time columns.

If they are old, then the index has not been used recently even though the Seeks and Scans number will be high.

If a database is only used once per month or once per quarter, the Last User Seek, Scan, and Lookup times won’t be updated.

         

         

Index Physical Statistics Reports

As data is inserted, updated, and deleted in a table, the table and its indexes become fragmented.

Index fragmentation slows down sequential operation s.a. range scans and read-ahead because the storae needs to hunt around for all the relevant pages.

This report shows all clustered and nonclustered indexes in the database and their depth – the number of levels in the b-tree of the index. 

Index type	Description	SYNTAX
Hash	With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.	CREATE TABLE t1_1    (c1 INT NOT NULL,    INDEX IDX HASH (c1) WITH (BUCKET_COUNT = 100))   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) -- table level
memory-optimized nonclustered indexes	For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns	CREATE TABLE t1_1    (c1 INT NOT NULL,    INDEX IDX HASH (c1) WITH (BUCKET_COUNT = 100))     WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
Clustered	A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.	CREATE CLUSTERED INDEX IX_Name  ON dbo.tablename (columname);
Nonclustered	A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.	CREATE NONCLUSTERED INDEX IX_Name  ON tablename (columname);
Unique	A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.	CREATE UNIQUE INDEX ID_UNIQ ON DBO.TABLENAME(COLUMNAME)
	Uniqueness can be a property of both clustered and nonclustered indexes.
Columnstore	An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.	CREATE TABLE T1(ProductKey [int] NOT NULL, OrderDateKey [int] NOT NULL, DueDateKey [int] NOT NULL, ShipDateKey [int] NOT NULL); CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
	Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.	ALTER INDEX mycolumnstoreindex ON mytable DISABLE; -- update mytable -- ALTER INDEX mycolumnstoreindex on mytable REBUILD
Index with included columns	A nonclustered index that is extended to include nonkey columns in addition to the key columns.	CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
Index on computed columns	An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.	CREATE TABLE t2 (a int, b int, c int, x float,  y AS CASE x  WHEN 0 THEN a  WHEN 1 THEN b  ELSE c END);
Filtered	An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.	CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL ;       ---Filtered Index in SELECT QRY SELECT ID, StDt FROM DBO.BOM WITH ( INDEX ( FIBOMEdt ) ) WHERE EndDate IN ('20000825', '20000908', '20000918');
Spatial	A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.	SELECT TOP(K) [WITH TIES] * FROM AS T [WITH(INDEX())]WHERE .STDistance(@reference_object) IS NOT NULL ORDER BY .STDistance(@reference_object) [;]
XML	A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.	SELECT CDes.value('(/PD:PDes/@PMdlID)[1]', 'int') as MdlID, CDes.value ('(/PD:PtDes/@PtMdl)[1]', 'varchar(30)') as MdlNm PMdl   WHERE PMdlID = 19
Full-text	A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.	CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON HR.name(Resume)   KEY INDEX ui_ukJobCand WITH CHANGE_TRACKING=MANUAL;