SQL Server Storage and Index Structures

1. SQL Server Storage andIndex Structures Physical Data Organization Indexes B-Trees SQL Server Data Access Clustered and Non-Clustered Creating, Altering, Dropping Indexes Choosing your Indexes Maintaining your Indexes

2. SQL Server Storage Hierarchy • Database • Extent • 8 contiguous 64K data pages • Once extent full, next record will take up a whole additional extent. • Pre-allocating space saves time.

3. SQL Server Storage Hierarchy • Page • 64K bytes • # of records/page varies with bytes/record • Types of Pages: Data and Index pages • Page Split • When page becomes full, it splits • New page allocated: ½ data from old page moved to new • Rows • 8060 bytes and 1024 columns

4. B-tree Key Points to Remember • Tree portion includes key attributes only • ordered as in create index statement • Keys packed in index pages • Fewer bytes per key -> more keys per page/extent -> fewer page faults per access. • Clustered indexes have records at leafs • Records are in data pages • Data pages sequentially linked • Non-Clustered indexes point into heap or tree portion of clustered index

5. Create Index Statement • Create [unique] [clustered | nonclustered] index <indexName> on <table or view> (col. Name> [asc|desc] [,…]) include (<col name> [,…]) (with … pad_index, fillfactor, ignore_dup_key drop_existing, statistics_norecompute sort_in_tempDB, online, allow_row_locks, allow_page_locks, maxdop

6. Create Index Details • Asc/Desc • Ascending & descending sort order for index • Include (cool!) • Includes col in leaf nodes of clustered index • Allows very fast access to non-key attribute • Useful with very large record – fewer page faults

7. Create Index “with” Details • Pad_Index= (on|off) • Initial fill-factor for index’s non-leaf pages • Fill Factor = <1% – 100%> • Default is index pages are as full as possible minus two records • Fill factor is how full after index is created • Once split goes to 50% • Ignore_dup_key • Circumvent unique key constraint somewhat • Still get error message, but no rollback • useful for storing unique values but trashing transactions

8. Create Index “with” Details • Drop_Existing • Any existing index with same name is dropped with this create statement • More efficient than drop index followed by create for clustered index as no need to touch non-clustered indexes or data pages • Statistics_nonrecompute • Default: sql server automates the process of updating the statistics on tables/ indexes • This option says you will maintain stats • DON’T USE THIS!

9. Create Index “with” Details • Sort_In_tempdb • Only useful when tempdb on physically separate drive • Reads/write for sort compete with read/writes to write data and index pages • This make sense if and only if you understand disk writes - discussion • Online • Keeps table available to users while creating index – sounds good, but ….!!

10. Create Index “with” Details • Allow row/page locks • Don’t use unless really good • MAXDOP • Overrides system setting for max degree of parallelism while building index • How many processes are used to construct an index. MAXDOP sets limit on how many processors per operation. • Compare and contrast these terms

11. Create Index “with” Details • ON • Can store index separately from data • Space for index spread across drives • I/O for indexes not compete with physical data retrieval

12. XML Indexes • Indexes into XML data • Xml VERY unstructured • Column can be of type xml in sql server • Create index on xml column • Page 276 for more details

13. Implied indexes • created by some constraints • Primary Key • Unique • Can easily end up with duplicate constraints and not realize it

14. Deciding what indexes go where? • Indexes speed access, but costly to maintain • Almost every update to table requires altering both data pages and every index. • All inserts and deletions affect all indexes • Many updates will affect non-clustered indexes • Sometimes less is more • Not creating an index sometimes may be best • Code for tranasaction have where clause? What columns used? Sort requried?

15. Deciding what indexes go where? • Selectivity • Indexes, particularly non-clustered indexes, are primarily beneficial in situations where there is a reasonably HIGH LEVEL of Selectivity within the index. • % of values in column that are unique • Higher percentage of unique values, the higher the selectivity • If 80% of parts are either ‘red’ or ‘green’ not very selective

16. Choosing Clustered Index • Only one per table! - Choose wisely • Default, primary key creates clustered index • Do you really want your prime key to be clustered index? • Option: create table foo myfooExample (column1 int identify primary key nonclustered column2 …. ) • Changing clustered index can be costly • How long? Do I have enough space?

17. Clustered Indexes Pros & Cons • Pros • Clustered indexes best for queries where columns in question will frequently be the subject of • RANGE query (e.g., between) • Group by with max, min, count • Search can go straight to particular point in data and just keep reading sequentially from there. • Clustered indexes helpful with order by based on clustered key

18. Clustered Indexes Pros & Cons • The Cons – two situations • Don’t use clustered index on column just because seems thing to do (e.g., primary key default) • Lots of inserts in non-sequential order • Constant page splits, include data page as well as index pages • Choose clustered key that is going to be sequential inserting • Don’t use a clustered index at all perhaps?

19. Column Order Matters • (P#, S#, Qty) • P# S# together are primary key • One index that includes all columns is not useful in all situations! • Only end up storing data a second time. • Clustered index of P#S# not same as S#P# • P#S# can lookup P# fairly easily, but looking up S# requires a linear search. • S#P# can lookup S# fairly easily, but not P#. • Note that even though key of S#P# means can’t lookup P# quickly, are some advantages in include P# in key.

20. Dropping Indexes • Sometimes makes sense to constantly re-analyze situation and add indexes • DON’T FORGET TO DROP INDEXES!! • Big overhead for inserts and deletes • Always ask yourself: “Can I get rid of any of these?” • Drop INDEX <TABLE NAME> <Index name>

21. Index Tuning Wizard • Hopefully you will evolve to the point you don’t need to use this gadget • But still can be quite handy • Uses workload file generated using sql server profiler (ch 19) • Not ideal to depend on this tool, but it may make some suggestions that you have not thought of.

22. Maintaining Indexes • Page Splits • Insert/delete order and rate critical • Fragmentation • Not OS fragementation – e.g. defrag tool • Happens when database grows, pages split, and then data eventually deleted. • Btrees great on maintaining balance on insertions, but with deletes, can end up with many pages containing small # of records.

23. Fragmentation Problems • Wasted space • Sql server allocates an extend at a time • Could end up with an extent, containing single page, with single record. • Thrashing (way too many disk hits) • Could end up with page 1 of data on one extend, page 2 on another, page 3 on the first, page 4 on another, …. • Records all over the place • Bit better for inserts but really bad for reads!

24. Identifying Fragmentation vs. page splits • DBCC SHOWCONTIG • Page 283 • Demo with northwind

25. DBREINDEX & Fillfactor • DBCC DBREINDEX • Can drop index and rebuild • Usually best to use drop-existing • Completely rebuilds the index • If supply table name, rebuilds all indexes on table. • Re-establishes base fillfactors etc. • Strongly recommend disallow transactions while doing this. • Rebuilding is probably better.

26. Summary • Clustered indexes usually faster than non-clustered • Only place non-clustered indexes on columns with high selectivity (>95% of rows are unique on that column) • All data manipulation language statements can benefit, from indexes, but inserts, deletes, and updates are slowed. • Indexes take up space and require page hits.

27. Summary • Index used only if first column in index is relevant to query • Indexes can hurt as much as they help • Make sure don’t add one by accident. • Indexes can provided structured data performance to unstructured XML, but overhead involved.

28. Summary • Is there a high level of selectivity on the data? • if yes and is frequently target of where clause, then add index • Have I dropped indexes I no longer need? • Why not? • Do I have a maintenance strategy established? • Why not?

29. Critical Questions • Are there lots of inserts of modifications to this table? • If yes, keep indexes to minimum • Is this a reporting table? • E.g. not many inserts but lots of reports run many different ways • If yes, more indexes are fine. • Is there a high level of selectivity on the data? • If yes and is frequently target of where clause, then add index