By Patrick Hynds

1
By Patrick Hynds
Index Optimization and Performance Tuning
2
Why is this important?

• Successful applications can ultimately fail due
  to bad performance as they grow
• Perception is making standards for wait times
  shrink
• How can you optimize what you do not understand
• If it is worth doing it is worth doing right
• Math always wins

3
Performance and Perception

• Perception equals reality
• Unofficial standards
• Less then ½ sec instant to most users
• ½ to 1 sec sluggish
• 1 to 3 sec slow
• 4 to 9 sec broken
• 10 sec or more unusable

4
How do I maximize perf

• Application Design
• Database Design
• Indexes
• Query Tuning

5
Application Design

• Determine Application Critical Success Factors
• Which operations
• What performance
• What concurrency
• What load profile

6
Database Design

• Normalize then Denormalize as needed
• Strategically add columns to reduce joins
• Think about row size in terms of rows per data
  page
• Split tables to keep rows short
• Index appropriately
• Not as easy as it sounds
• The main reason for this session

7
Understanding Indexes

• How they work
• Clustered
• Non-Clustered
• When to use them
• Read vs. Write centric applications
• What to avoid
• Best Practices

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.

9
(No Transcript)
10
Index Root Node Example(grossly oversimplified)

• Values Idx Pg Values Idx Pg
• AA DE 101 DF GE 102
• GF HA 103 HB LU 104
• LV NO 105 NP SA 106
• SB TI 107 TJ ZZ 108

11
Intermediate Level Example (grossly
oversimplified)
Clustered

• Index Page 101 (AA DE)
• Values Data Pg Values Data Pg
• Anderson 201 Aper 201
• Ballmer 201 Bronson 201
• Calvin 202 Chuna 202
• Davidson 202 Deleon 203

12
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 doesnt have a clustered index.

13
(No Transcript)
14
Index Root Node Example(grossly oversimplified)

• Values Idx Pg Values Idx Pg
• AA DE 101 DF GE 102
• GF HA 103 HB LU 104
• LV NO 105 NP SA 106
• SB TI 107 TJ ZZ 108
• (Basically the same as Clustered Index)

15
Intermediate Level Example (grossly
oversimplified)
Non-Clustered

• Index Page 101 (AA DE)
• Values Data Pg Values Data Pg
• Anderson 398 Aper 891
• Ballmer 221 Bronson 293
• Calvin 782 Chuna 222
• Davidson 202 Deleon 232

16
Add an index when

• 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, 2007 AND
  Dec 31, 2007

17
Add an index when

• Data within the column is used to build joins.
  Foreign keys are almost always good candidates
  for indexes.
• For example
• 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

18
Add an index when

• The data in the column is needed in the same
  order every time it is retrieved. (clustered)
• The values in the intermediate node can answer
  the query without going to the leaf node (a.k.a.
  covering index).

19
Add an index when

• The values in the column must avoid duplicates.
• The distinction of values in the column is very
  great (i.e. Age index, Gender dont index).
• The values of the column help group result sets
  into frequently used categories, especially in
  the GROUP BY clause.

20
Fill Factor

• When SQL Server creates indexes, every page is
  nearly 100 full.
• No room on the leaf or intermediate pages for
  INSERTs and UPDATEs
• Default can cause costly page splits on certain
  tables
• Promotes table fragmentation
• SQL Server allows you to specify amount of free
  space in leaf pages with FILL FACTOR, an option
  in the CREATE INDEX statement.

21
How Many 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
• Decision Support System (DSS) vs. Online
  Transaction Processing (OLTP)

22
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
  things you can do to help it find the best plan.

23
Querying against Composite Keys

• Composite keys are only 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 _at_a
• WHERE a _at_a AND b _at_b
• The following WHERE clauses will access only part
  of NDX_FOO
• WHERE a _at_a AND d _at_d
• WHERE a _at_a AND c _at_c AND b _at_b
• The following WHERE clauses invalidate NDX_FOO
• WHERE b _at_b AND c _at_c
• WHERE b _at_b AND a _at_a

24
Number of Joins

• Joins are executed very quickly in the newest
  version of SQL Server.
• However, each successive join 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 sixteen table join is the max allowable, but
  bring a magazine!

25
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

26
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 gt 10000
• WHERE ISNULL(ord_date, Jan 01,2001) gt Jan 01,
  2002 120000 AM
• Instead, move the function or calculation to the
  SARG
• WHERE qty gt 10000/12
• WHERE ord_date IS NOT NULL
• AND ord_date gt Jan 01, 2002 120000 AM

27
Conclusion

• SQL Server is a powerful platform, but if you
  dont know how to use it correctly it wont
  perform
• Indexes are key to getting the most out of your
  SQL Server
• If it were easy enough for a program to always
  pick correctly, they wouldnt need us