© 2008 Quest Software, Inc. ALL RIGHTS RESERVED.

Advanced Index Tuning

About Me: Brent Ozar

• SQL Server Expert for

Quest Software

• Former SQL DBA

• Managed >80tb SAN,

VMware

• Dot-com-crash

experience

• Specializes in

performance tuning

Today’s Agenda

• Passive Tuning: Using DMVs

• Active Tuning: Outsmarting the Wizards

• Compression for Indexes

• The Silent Performance Killer

• Resources and Q&A

Passive Tuning with DMVs

DMV Tuning Lifecycle

Remove Indexes

Add Indexes

Observe

Removing Indexes

Results

Why Are These Different?

Our Table: Items

Indexes On An “Item” Table

• Index #1:– UPC_Code

• Index #2:– SupplierID

• Index #3:– Category

– QtyInStock

Photo licensed with Creative Commons from:

http://www.flickr.com/photos/revolute/1944742197/

Data In The Index

Category Qty in Stock Item ID

Bacon 45 1

Bacon 47 2

Bacon 98 3

Bacon 110 5

But When It Changes…

Category Qty in Stock Item ID

Bacon 45 1

Bacon 42 2

Bacon 200 3

Bacon 110 5

That’s Why…

Adding a Formula Column

Our Query

A Better Way: Includes

CREATE INDEX

IX_Category_Includes

ON dbo.Items (Category)

INCLUDE (QtyInStock)

And When It Changes…

Category Qty in Stock Item ID

Bacon 45 1

Bacon 42 2

Bacon 200 3

Bacon 110 5

Query Plan

Remember, Never SELECT *

Leave a Bread Crumb Trail

DMV Tuning Lifecycle

Remove Indexes

Add Indexes

Observe

Missing Index Query

Okay – Now What?

When Do We Add More Indexes?

DMV Tuning Lifecycle

Remove Indexes

Add Indexes

Observe

Active Tuning: The Wizards

Danger! Danger!

The End Result

CREATE NONCLUSTERED INDEX

[_dta_index_Activity_11_1977058079__K1_K4_K7_K5_K3]

ON [dbo].[Activity]

(

[ServerName] ASC,

[ActivityTypeID] ASC,

[StatusTypeID] ASC,

[StartTime] ASC,

[DatabaseID] ASC

) ON [PRIMARY]

GO

Rename Each Index

CREATE NONCLUSTERED INDEX

[IX_ServerName_ActivityTypeID_StatusTypeID_StartTime_

DatabaseID] ON [dbo].[Activity]

(

[ServerName] ASC,

[ActivityTypeID] ASC,

[StatusTypeID] ASC,

[StartTime] ASC,

[DatabaseID] ASC

) ON [PRIMARY]

GO

Makes Ongoing Tuning Easier

Active Tuning Summary

• Don’t just apply the recommendations

• Use smart names

• Look for overlaps

• Take the passive approach first

SQL 2008 Data Compression

Estimating Compression

• sp_estimate_data_compression_savings– @schema_name

– @object_name

– @index_id

– @partition_number

– @data_compression

Index Compression Drawbacks

• Enterprise, Developer Editions only

• No inheritance, no automation

• Get scripts from www.SQLServerBible.com

The Silent Performance Killer

Pretend You’re SQL Server

Photo Licensed with Creative Commons From

http://www.flickr.com/photos/quacktaculous/3143079032/

Does It Really Matter?

The workload performance increase realized in the

small-scale environment ranged from 60 percent at

the low level of fragmentation to more than 460

percent at the highest level of fragmentation.

The workload performance increased realized for the

large-scale environment ranged from 13 percent at

the low fragmentation level to 40 percent at the

medium fragmentation level.

http://technet.microsoft.com/en-

us/library/cc966523.aspx

13-460% Faster, And…

• No app changes

• No vendor issues

• No testing required

• No guessing on indexes

• No backfires

• Fully supported

• Even recommended!

Not All Unicorns and Roses

• Standard Edition:

Offline Only

• Enterprise Edition:

Most Online

• Generates a lot of

transaction logs

The Native Solution

The Better Way: T-SQL Code

Don’t Reinvent The Wheel

Resources On The Web

• SQL Server Wiki and Script Library:

SQLServerPedia.com

• Scripts to Enable Compression Fast:

www.SQLServerBible.com

• Index Tuning & Maintenance:

SQLfool.com