Index Tuning Strategies for SQL Server

1. Index Tuning Strategies for SQL Server Kevin Kline Technical Strategy Manager, SQL Server Solutions Twitter @kekline Blog at http://KevinEKline.com[

2. Agenda • Speaker Bio • Index Structures & Fundamentals • Strategies for choosing good indexes • Best practices for maximizing your indexes within queries • What is outside our scope? • The actual SQL statements applying to indexes (CREATE, DROP, and ALTER) • Special case indexes: full-text, image/text • Locking, transactions, and isolation levels • Summary • Resources • Q & A

3. Kevin Kline • SQL Server Expert for Quest Software • Former developer, DBA, and enterprise architect since ‘86 • Former president of PASS (www.sqlpass.org) • Microsoft MVP since ‘04 • Author of SQL in a Nutshell and 9 other books • Twitter @kekline • Blogs at http://sqlblog.com and http://KevinEKline.com

4. Index Structures & Fundamentals • Index Structure and the Importance of Fill Factor • Selecting Indexes: Rules of Thumb • Clustered vs. Non-Clustered Indexes • More vs. Fewer Indexes • Short vs. Long Index Keys • Covering, Included, and Filtered Indexes • Helping SQL Server to Choose Indexes • Index Selection • Querying against Composite Keys • Join Order • Transitive Properties • Queries that include OR statements • Queries that use LIKE and wildcards • Queries using Functions and Calculations in the WHERE clause

5. Clustered Index Structure • 8K pages, 8064 bytes available for data • Leaf pages ARE the data. • Non-leaf pages are pointers. Root Page Level 2 Intermediate Pointer Pages Level 1 Leaf Pages Level 0

6. Heaps • Table without a clustered index. • Records are unordered, without a doubly-linked list • Efficient for large serial data loads starting from empty • Inefficient for transaction processing • Can provoke forwarded records • Forces an 8 byte RID for every inserted row

7. Fill Factor Page Split • When SQL Server creates indexes, every page is 100% full. • No room on the leaf or intermediate pages for INSERTs, UPDATEs, or DELETEs. • Default can cause costly page splits on certain tables. • Promotes table fragmentation. • Specify amount of free space in leaf pages with FILL FACTOR • PAD_INDEX option pushes fill factor up into index pages

8. Clustered Indexes • Clustered indexes are the actual physically written records. • A SELECT statement with no ORDER BY clause will return data in the clustered index order. • 1 clustered index per table, 249 non-clustered indexes per table. • Highly recommended for every table! • Very useful for columns sorted on GROUP BY and ORDER BY clauses, as well as those filtered by WHERE clauses. • Place clustered indexes on the PK on high-intensity OLTP tables.

9. Non-Clustered Indexes • Useful for retrieving a single record or a range of records. • Maintained in a separate structure and maintained as changes are made to the base table. • Tend to be much narrower than the base table, so they can locate the exact record(s) with much less I/O. • Has at least one more intermediate level than the clustered index, but are much less valuable if table doesn’t have a clustered index.

10. Choosing Indexes #1 • Most common rules of thumb dictate an index should go on a column if: • #1. Data within the column is frequently accessed by transactions, particularly if the data is used to filter data either by specific value or by range of values • For example: • WHERE emp_SSN = ‘123-56-7890’ • WHERE emp_hire_date BETWEEN ‘Jan 01, 2000’ AND ‘Nov 01, 2002’

11. Choosing Indexes #2 • #2. When data within the column is frequently accessed to build joins. Foreign keys constraints are almost always good candidates for indexes. • For example, to find all the titles in the pubs database that have any sales: • SELECT DISTINCT t.title • FROM pubs..titles AS t • JOIN pubs..sales AS s ON t.title_id = s.title_id • Title_ID on the sales table should be indexed

12. Choosing Indexes #3 • #3. When the data in the column is needed in the same order every time it is retrieved. Clustered indexes arrange the rows of a table physically on the disk. • #4. When the values in the intermediate node can answer the query without going to the leaf node (a.k.a. covering index).

13. Choosing Indexes #4 • #5. When the values in the column must avoid duplicates. • #6. When the distinction of values in the column is very great. • #7. When the values of the column help group result sets into frequently used categories, especially in the GROUP BY clause. • #8. When the table has a very large number of records.

14. More vs Fewer Indexes • Adding non-clustered indexes to a table can greatly speed SELECT statements. • Every index has a certain amount of overhead. • The greater the number of indexes, the more overhead with every INSERT, UPDATE, and DELETE statement. • Must balance the needs of the application with the pros and cons of added indexes (but is usually worth it.)

15. Short vs Long Keys • A “concatenated key” is an index composed of more than one column • Up to 16 columns • Up to 900 bytes • Short keys are usually more performant, solely from fewer I/Os • Concatenated keys, also called composite keys, are evaluated from leftmost column to right (more on that later). So be sure the columns are indexed in order from most frequently used to least frequently used.

16. Covering Index • A covering index is one that answers a query entirely from its own intermediate pages, rather than going all the way down the tree to leaf pages. • All columns referenced in the query need to be in the index. For example: • SELECT lname, fname • FROM employee • WHERE lname LIKE '%ttlieb%' • All columns in the index must be referenced in the query: • CREATE INDEX employee_name_lvl ON employee (lname, fname) • Don’t confuse a covering index with an included columns index

17. Best Practices for Queries • The query optimizer will attempt to select the best indexes based on what it knows at the time the transaction is issued: • Evaluate filter conditions on each table • Satisfy joins • Find columns without going to the leaf pages (covering indexes) • Filter conditions are known as search arguments or SARGs. • Even though the query optimizer will find the best plan available very quickly, there are lots of things you can do to help it find the best plan.

18. Query Best Practice #1: Querying against Composite Keys • Composite keys are most useful from the leftmost column to the rightmost column, in the order they appeared in the CREATE INDEX statement. Example: • CREATE NONCLUSTERED INDEX ndx_foo ON foo(a, b, c, d) • The following WHERE clauses will access the NDX_FOO: • WHERE a = @a • WHERE a = @a AND b = @b • The following WHERE clauses may access only part of NDX_FOO: • WHERE a = @a AND d = @d • WHERE a = @a AND c = @c AND b = @b • The following WHERE clauses ignore NDX_FOO: • WHERE b = @b AND c = @c

19. Query Best Practice #2: Join Order • Joins are executed very quickly in the newest version of SQL Server. • However, each successive joined performed within a single query adds an order of magnitude in complexity for the query optimizer. • Avoid queries with more than a five table join. • A 32 table join is the max allowable, but bring a magazine! • Note that subqueries, not just explicit joins, count towards the max number of joins in a single query.

20. Query Best Practice #3: Transitive Properties • The only algebra rule for transitive properties is almost common-sense intuition these days: • If A = B and B = C, then A = C • However, the query engine doesn’t know this axiom. Help it to know by putting this into your WHERE clause: • SELECT c.customer_id • FROM customer_order c, • product p, • shipping_registry s • WHERE c.product_id = p.product_id • AND c.product_id = s.product_id • AND p.product_id = s.product_id

21. Query Best Practice #4: Queries with OR clauses • Queries containing an OR clause are effectively the same as two separate queries using a UNION statement. • Each and every OR condition requires an index to avoid a table scan. • Evaluate using a UNION statement so that at least some portion of your query can perform an index seek.

22. Query Best Practice #5: Queries with LIKE • Queries on production systems should NOT use SELECT * FROM… • Main reason is that any time the underlying table is changed, all query plans stored in the cache must be rebuilt • The SQL tools allow very quick scripting – so no excuses! • Queries that use the LIKE clause have two simple rules: • LIKE can use indexes if the pattern starts with a character string, such as WHERE lname LIKE ‘w%’ • LIKE cannot use an index if the pattern starts with a leading wildcard, such as WHERE lname LIKE ‘%alton’

23. Query Best Practice #6: Queries with Functions & Calculations in the WHERE clause • Avoid using functions or calculations on the column in a WHERE clause because it causes SQL Server to ignore any index on the column: • WHERE qty * 12 > 10000 • WHERE ISNULL(ord_date, ‘Jan 01,2001’) > ‘Jan 01, 2002 12:00:00 AM’ • Instead, move the function or calculation to the SARG: • WHERE qty > 10000/12 • WHERE ord_date IS NOT NULL • AND ord_date > ‘Jan 01, 2002 12:00:00 AM’

24. DATABASE TUNING WIZARD • It is very easy to use and might have a profound impact on the performance of your application. • Returns best results using a realistic workload captured using SQL Profiler. (The Sample1 – TSQL trace definition template is a good start.) • Recommendations returned by the wizard are SQL commands that can be run directly against the database in SSMS.

25. Index Strategy Review • Understand the Primary Usage of Table (read vs read/write). This determines lots of things such as fill factor, number of indexes, and which is column clustered index. • Always create primary key. • Manually add (nonclustered) indexes to foreign key constraints and other important columns such as join columns. • Capture a workload to analyze with the Index Tuning Wizard. • Most important of all is to test, analyze, and retest! Don’t be afraid to experiment!

26. Query Strategy Review • Limit the columns requested by restricting the asterisk wildcard. • Limit the search through search arguments. • Use covering indexes to answer queries with few rows in the result set. • Know the rules for OR and LIKE queries so you can optimize your queries. • Know the impact of join order, composite keys, and transitive-style WHERE clauses. • Keep tabs on functions and calculations in your WHERE clause.

27. Quest Software Swag for SQL Server Free posters, guides, and other goodies. HTTP://www.quest.com/backstage/promotions.aspx March 2010 July 2010 Free DVD Training: HTTP://db-management.com/live

28. Quest Software Resources for SQL Server SQLServerPedia – SQL Server knowledge base, straight from the experts: HTTP://www.SQLServerPedia.com SQL Server Community – Online discussion forums, customization library, and beta programs. HTTP://SQLServer.quest.com SQL Server Backstage – All things SQL Server at Quest including our Pain of the Week Webcasts. HTTP://www.quest.com/BackStage

29. Send questions to me at: kevin.kline@quest.com Twitter @kekline Blogs at SQLServerPedia.com, SQLblog.com, SQLMag.com Rate Me – http://SpeakerRate.com/kekline/ Content at http://KevinEKline.com/Slides/ Questions ?