1 / 33

SQL Server Index Internals

SQL Server Index Internals. Tim Chapman Premier Field Engineer. About Me. Tim Chapman Dedicated Premier Field Engineer at Microsoft Contributing author SQL Server 2012 Bible SQL Server MVP Deep Dives 2. @ chapmandew. Session Goals. Index structure familiarity

kaya
Download Presentation

SQL Server Index Internals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Index Internals Tim Chapman Premier Field Engineer

  2. About Me Tim ChapmanDedicated Premier Field Engineer at Microsoft Contributing author SQL Server 2012 Bible SQL Server MVP Deep Dives 2 @chapmandew

  3. Session Goals Index structure familiarity I won’t break out a debugger  But, I will show you some neat tools for viewing indexes Have some fun talking about indexes!

  4. Heaps • Tables without a clustered index • Unordered masses of data • Data rows fit where they can – PFS pages used • Great for quickly importing large sets of data • Not great for most production environments • Use ALTER TABLE…REBUILD to “rebuild”

  5. Index Structures Clustered Indexes Non-Clustered Indexes

  6. Clustered Indexes Implemented as a B-Tree data structure • Logical order must always be maintained • The leaf level of the index contains all table columns • Why there is only one per table – the index IS the table • We always implement these as unique

  7. Clustered Index Structure

  8. Non-clustered Indexes • Also a B-Tree structure data structure • Is NOT part of the table it is defined on • It MUST point to the base table somehow • Only a subset of the table columns • A skinny table for fast searching and sorting

  9. Non-clustered Index Structure

  10. Included Columns • Added columns of data in the leaf level of an NC index • Used for covering queries • Not restricted to the NC index 900 byte size restriction • You can use (n)varchar(max), but not (n)text or image data types

  11. Included Columns

  12. Demo Investigate Index/Data Pages

  13. Statistics A sampling of the data in a given table/index column The optimizer relies on these for decision making Out of date or skewed statistics can lead to sub-optimal execution plans

  14. Showing Statistics…

  15. Database Statistics Options (1) • AUTO_CREATE_STATISTICS • AUTO_UPDATE_STATISTICS • AUTO_UPDATE_STATISTICS_ASYNC

  16. Database Statistics Options – Best Practices Use the defaults unless you NEED to do otherwise Often large DW workloads are the exception Note: Trace Flag 2371 can help with RT issues.

  17. Demo Out of date statistics

  18. Index Maintenance Reviewing fragmentation Affects of fragmentation Rebuild vs Reorganization

  19. Page Splits • A record must always be placed on a specific page • We must maintain the index logical order • If the record doesn’t fit, we must do some rearranging • This is resource intensive - causes logical fragmentation

  20. Logical Fragmentation Index/Data pages not physically and logically aligned Can hurt scan performance, but not seek operations

  21. Page Density How full a page is upon a (re)build/reorganization More full pages can cause page splits Less full pages can waste Buffer Pool space

  22. When does fragmentation matter? Negligible for singleton lookup seek operations Matters most for scanning purposes Note: If your index is highly fragmented, there is a good chance your statistics are skewed or out of date.

  23. Demo The effects of Index Fragmentation

  24. Rebuilding an index (1) • Very few uses for DROP INDEX…CREATE INDEX • All NC indexes rebuilt twice if you do this with the clustered index • You must know the exact index structure for recreation • ALTERINDEX…REBUILD • NC indexes not automatically rebuilt if done on a clustered index • ALTER TABLE…REBUILD • Use for Heap tables – will always rebuild all NC indexes too

  25. Rebuilding an Index (2) • Offline rebuilds locks the index during the operation • NOT the entire table – though that can certainly be the case • ALTER INDEX ALL • Rebuilds all indexes on the table in index_id order • DROP_EXISTING • Part of the CREATE INDEX syntax • Great for changing the index definition

  26. Index Reorganization Exclusively locks, compacts and reorders 8 pages at a time Removes leaf level fragmentation Tries to establish the original fill factor Always: single threaded, Online

  27. Rebuild vs. Reorganize Strategy (1) • When to do what? • Common wisdom is: • If <= 30% logical fragmentation THEN Reorganize • If > 30% logical fragmentation THEN Rebuild • …your mileage will vary. Choose what works for you.

  28. Rebuild vs. Reorganize Strategy (2) Reorganize… • Is always an online operation • Can be stopped and you won’t lose the work accomplished • Generates a LOT more t-log records than Rebuild • Typically will not remove as much fragmentation as Rebuild • Never updates Statistics

  29. Rebuild vs. Reorganize Strategy (3) • Rebuild… • Is an atomic operation – all happens or none happens • Will update statistics with FULLSCAN • One caveat to this in 2012 • Will involve some blocking – even Online operations • Can be minimally logged

  30. Demo Looking at transaction log usage from an index rebuild

  31. SQL Server 2012 Index Changes Rebuild indexes with (B)LOB columns online Online rebuilds of partitioned tables do not always result in a 100% sample rate

  32. SQL 2014 Index Changes Online operation lock priority Hash and Bw-tree Indexes in Hekaton Create an index in table definition

  33. Questions?

More Related