2779 Module 4 – Creating and Tuning Indexes

Planning

SQL Server Data Access

-SQL Server accesses data in two ways:

-Table scan (reads every single row/page in the table to see if there is a value it needs to return)

-yucky unless your going to return every row in the table

-depends on how your users are using the data.

-Index

-on what data page does a particular value reside.

-if you have a where clause this speeds up data access.

-indexes require maintenance

-Clustered Indexes

-Only one clustered index per table.

-Data pages are stored in index key order

select * from sys.partitions (index_id=1 if there is a clustered index)

Select * from sys.system_internals_allocation_units (look at root_page)

In a clustered index you have

-a root_page

-non leaf level pages that point to leaf level pages. (leaf pages hold the actual data)

The leaf level pages contain the data, in a clustered index the ACTUAL leaf pages(data) are sorted in the order specified by the index. DING DING DING!! I didn’t know that! J

Heaps

-a table with out a clustered index (just a pile of data)

select * from sys.partitions (index_id=0 if there is NOT a clustered index)

Select * from sys.system_internals_allocation_units (look at first_iam_page)

Non Clustered index

-an index built on top of a heap

-index pages point to index pages that point to leafs

i.e. Like a phone bookà actually going to the persons house.

-B-tree refers to underlying heap or clustered index

-Up to 249 non clustered indexes allowed on a table

-the non leaf level index pages point to the data pages which in turn point to the actual data rows of interest.

Built on a heap: contains pointers to the data pages in the table.

Built on a clustered index: contains the index values for the clustered index.

Creating Indexes

Options:
ALLOW_ROW_LOCKS – Row level locks on index

ALLOW_PAGE_LOCKS – Page level locks on index

ONLINE – Access to index during creation

FILLFACTOR – Create free space on leaf-level pages

PAD_INDEX – Created free space on no-leaf-level pages

Unique Indexes

-Prevents inserting duplicate values in index key

-A unique constraint automagically adds a unique index on the column(non clustered).

-If you create a primary key on a column that will create a unique CLUSTERED index on that column.

Creating Indexes with Multiple Columns

Composite Indexes- index of all the columns in the index

-can include up to 16 columns and 900 bytes in key.

-best to define most unique column first

-all columns in the index are indexed

Included columns- columns are included in index which are not indexed.

- Covering Index -example: a query on last name never has to hit the actual datapages of the table because there is an index on lastname.

If all you want to pull back is last name then SQL Server can return your info without hitting the table.

-in sql server 2005 we can include columns that are not indexed.

USE AdventureWorks

CREATE UNIQUE NONCLUSTERED INDEX IX_PRODUCT_SUPPLY_CHAIN

ON Production.Product (ProductNumber, Color, ReorderPoint, SafetyStockLevel)

INCLUDE (DaysToManufacture)

WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, PAD_INDEX = ON)

Indexes on Computed Columns

Requirements

-Expression is deterministic (returns the same value every time it’s run) and precise (no approximate data types real,float)

-ANSI_NULLS has to be on

-Column does not evaluate the text,ntext, or imagedata types.

-Required options are set to ON when when index is created and when changes cause index to update.

-NUMERIC_ROUNDABORT option is set to OFF.

Partitioned Indexes

-Just like indexing a table, preferable that you use the same partition function/scheme you used on a table.

-Align index partitions with underlying partitioned table and include partitioning key in index key.

Incorporating Free Space in Indexes

-Free space affects performance

New entries are put into the leaf level page of the index. Say that the leaf level page is full. That page is going to split into two leaf level pages, each containing half of the entries of the old one.

Non leaf level pages now have to have an entry for the new leaf level page… what if THAT page is full?

That page will split which will …. Etc. etc.

All the way up to a root page, there can only be one root_page of an index.

We LIKE fragmentation in online transaction processing

Fragmentation speeds up inserts and slows down select. Keep that in mind.

The “FILLFACTOR =” determines the amount of free space in the LEAF pages of an index. It’s a percentage between 0 and 100

Only applied when you create or rebuild an index.

Low fillfactor for analysis processing

High fillfactor for transaction processing so that there are not to my page splits duringthe business day.

Non leaf level pages need the “PAD_Index” applies the fillfactor we had specified on the leaf level pages to the nonleaf level pages.

Apply a fill factor after the business day is over or during a lull.

SO you can rebuild your inexes at night and reapply the fill factor.

Obtaining Index Information

-SSMS

-System Sprocs

-sp_help

-sp_helpindex

-Catalog views

System functions

(demonstration)

Optimizing Indexes

-Database Tuning Advisor

Looks at the schema of your database

Looks at statistics on columns

Looks at the workload that is being applied to the database

-sql script

-set of sql scripts

-trace created by profiler

If you want to tune your DB so it performs optimally you would create a tract wit the profiler and you would use that tract in your tuning thingy

If you want to optimize your DB for specific queries or sets of queries then you would use the scripting thingy in the tuning thingy.

(demonstration)

Index fragmentation

Types-

Internal – Pages not full

External – Pages out of logical sequence

Detecting-

Index Properties window in SSMS

System func sys.dm_db_index_physical_stats

Defragmenting Indexes

-ALTER INDEX

-Reorganize defrags the leaf level index pages clust/ or nonclus (<30% do this)

-Rebuild drops the index and recreates it (>30% do this)

(demo)

Run this to find out your fragmentation percentages:

select a.index_id as [index ID], name as [Index Name],

avg_fragmentation_in_percent as fragmentation

From sys.dm_db_index_physical_stats (DB_ID(N'Adventureworks'),

OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL) as a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =

b.index_id

ORDER BY Fragmentation DESC

PK_Employee_EmployeeID is way fragmented, lets fix it:

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee

Rebuild

XML Indexes

(four types)

-must be a clustered not null index on a column in a table before you can create a primary index.

-must have a primary key or a column that functions as a primary key.

Yay module over

Review…