5 ways to improve performance through indexing

5 Ways to Improve Performance through Indexing

Jason Strate

e: jstrate@pragmaticworks.com

e: jasonstrate@gmail.com

b: www.jasonstrate.com

t: StrateSQL

Resources jasonstrate.com/go/indexing

Introduction

MAKING BUSINESS INTELLIGENT www.pragmaticworks.com

• Founded 2008 by MSFT MVP Brian Knight• Focused on the MSFT SQL Server Platform• Provides services, training and software• MSFT/HP “go to” partner: • Gold Certified:

o BIo Data Managemento SQL Performance

• Team led by multiple MVP’s• Offices throughout the US with Corporate

HQ in Jacksonville, FL

Pragmatic Works Company History

Indexes

Disappointment

5 Ways to Improve Performance through Indexing

Lookup Imbalance

• Excessive key lookups

– Exceed number of seeks

• Problems

– Increased CPU

– Increased IO

– Increased duration

Possible Solutions

• Ignore the problem

– Choose high key lookup rate

• Move clustered index

– Maybe the key is wrong

• Add included columns

– Improve NC indexes

Case Study 1

• What’s wrong?

• Do you see the pattern?

• What do you do?

– Move clustered index

Case Study 2

• What’s wrong?

• What do you do?

– Move clustered index

– Add included columns

Case Study 3

• What’s wrong?

• What do you do?

– Add included columns

November 6-9, Seattle, WA

Lookup Imbalance• Demo

Lookup Imbalance Summary

• Key lookups should not exceed seeks

• Investigate “WHY” when they do

• Consider whether to:

– Move the clustered index

– Add included columns to non-clustered indexes

• Usage patterns will shift over time

Scan Heavy Indexes

• High scans on large indexes

– Exceed seeks

• Problems

– Increased IO

– Increased locking

• Potential deadlocks

– Affect page life expectancy

– Impact memory distribution

Case Study 1

• Metric is high, so what?

• How do you tie into performance counter?

– Sys.dm_db_index_usage_stats

– Plan cache

Access Methods:Full Scans/sec

Case Study 2

• What’s wrong?

• Why aren’t the other indexes used?

• Are the right indexes in place?

• What do you do?– Examine execution plans

– Add in correct indexes

Scan Heavy Indexes• Demo

Scan Heavy Indexes Summary

• High scans on indexes can impact performance– Higher IO

– More locking• More Blocking

• Possible Deadlocking

– Memory impacted

• Find the occurrences– Identify indexes and analyze plans

– Add alternative indexes

Filtered Indexes

• Index with a WHERE clause – Only includes filtered rows

– Decreases rows in index

• Effect– Improved plan quality

– Improved storage

– Lower build time

– Less maintenance

• Benefit– Scan of small set of data

Case Study

• New data coming this week– Represents < 1% of rows

• Can’t rebuild indexes– Statistics on new rows inaccurate

• Need high quality plans– Indexes in place aren’t used

• What do you do?

Filtered Index Issue

• Filter Criteria

– Must be included

– Can not be parameterized

• Plan warning

– Unmatched indexes

Filtered Indexes• Demo

Filtered Index Summary

• Consider filtered indexes for:

– Large indexes with limited range of use

– Tables with large number of new rows

• Improves index performance

– Smaller, lightweight, agile

– Improved plan quality

• Seeks over scans (small data over large data)

• Scans over seeks (tiny data over small data)

Missing Indexes

• Optimize through materialization

– Index versus statistics

• Three options

– Dynamic management views

– Database Engine TuningAdvisor

– Plan Cache

Missing Indexes

MISSING INDEXES!!!

Missing Indexes• Demo

Missing Index Summary

• These are FREE recommendations

– Results of actual performance opportunities

– Provide opportunity to focus on other needs

• These are recommendations

– Index schema to ensure value

– Consolidate when possible

– Review associated queries

Foreign Key Indexes

Indexing Foreign Keys

• Foreign keys enforcement

– INSERT

– UPDATE

– DELETE

• Performed in background

• Unseen until a problem

Indexing Foreign Key Issues

• Unindexed foreign key

– Constraint columns

• Performance issues

– Scan of column values

– Increased locking

– Blocking likely

– Potential deadlocking

Case Study

Delete from SalesOrderHeader?

Case Study

Delete from SalesOrderDetail?

Case Study

Delete from SalesOrderHeaderSaleReason?

Indexing Foreign Keys• Demo

Indexing Foreign Key Summary

• Foreign keys are important

– Enforce integrity of data

– Constraints on values

• Operations occur in background

– DELETEs often source of issues

– Can lead to deadlocks

Learn More About Indexes

ServicesSpeed development through training, and rapid development services from Pragmatic Works.

ProductsBI products to covert to a Microsoft BI platform and simplify development onthe platform.

FoundationHelping those who do not have themeans to get into information technologyachieve their dreams.

For more information…

Name: Jason Strate

Email: jstrate@pragmaticworks.com

Blog: www.jasonstrate.com

Resource: jasonstrate.com/go/indexing

5 ways to improve performance through indexing

Technology

ways to improve your sales

15 ways to improve your advertising

101 ways to improve business performance

5 ways to improve final

ways to improve

10 ways to improve collections

25 ways to improve yourself

10 ways to improve your investment...

25 ways to improve website ranking

different ways to improve presentation skills

nine ways to improve an ad

6 ways to improve your telecommute

21 ways to improve your website

ways to improve surabaya cuisine

ways to improve storage i/o

5 ways to improve dba efficiency

5 ways improve email campaign

5 ways to improve council-manager relationships ·...

ivf: ways to improve pregnancy rates

indexing with semantic components improve information...