Advanced Index Tuning
Download as PPTX, PDF
1 like691 views
This document discusses advanced index tuning techniques in SQL Server, including: - Using DMVs (dynamic management views) to passively tune indexes by observing performance and removing or adding indexes. - Active tuning techniques such as avoiding over-application of tuning wizard recommendations and giving indexes smart names for ongoing maintenance. - Using data compression for indexes in SQL Server 2008 to reduce storage requirements. - Addressing database fragmentation as a "silent performance killer" and using online reindexing techniques to defragment indexes without taking tables offline.
![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](https://image.slidesharecdn.com/advancedindextuning-090317083441-phpapp02/85/Advanced-Index-Tuning-28-320.jpg)
![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](https://image.slidesharecdn.com/advancedindextuning-090317083441-phpapp02/85/Advanced-Index-Tuning-29-320.jpg)
Advanced Index Tuning
- 1. Advanced Index Tuning 息 2008 Quest Software, Inc. ALL RIGHTS RESERVED.
- 2. 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
- 3. Todays Agenda Passive Tuning: Using DMVs Active Tuning: Outsmarting the Wizards Compression for Indexes The Silent Performance Killer Resources and Q&A
- 4. Passive Tuning with DMVs
- 5. DMV Tuning Lifecycle Remove Indexes Add Observe Indexes
- 7. Results
- 8. Why Are These Different?
- 10. 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/
- 11. Data In The Index Category Qty in Stock Item ID Bacon 45 1 Bacon 47 2 Bacon 98 3 Bacon 110 5
- 12. But When It Changes Category Qty in Stock Item ID Bacon 45 1 Bacon 42 2 Bacon 200 3 Bacon 110 5
- 13. Thats Why
- 14. Adding a Formula Column
- 15. Our Query
- 16. A Better Way: Includes CREATE INDEX IX_Category_Includes ON dbo.Items (Category) INCLUDE (QtyInStock)
- 17. And When It Changes Category Qty in Stock Item ID Bacon 45 1 Bacon 42 2 Bacon 200 3 Bacon 110 5
- 18. Query Plan
- 19. Remember, Never SELECT *
- 20. Leave a Bread Crumb Trail
- 21. DMV Tuning Lifecycle Remove Indexes Add Observe Indexes
- 23. Okay Now What?
- 24. When Do We Add More Indexes?
- 25. DMV Tuning Lifecycle Remove Indexes Add Observe Indexes
- 26. Active Tuning: The Wizards
- 27. Danger! Danger!
- 28. 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
- 29. 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
- 30. Makes Ongoing Tuning Easier
- 31. Active Tuning Summary Dont just apply the recommendations Use smart names Look for overlaps Take the passive approach first
- 32. SQL 2008 Data Compression
- 33. Estimating Compression sp_estimate_data_compression_savings @schema_name @object_name @index_id @partition_number @data_compression
- 34. Index Compression Drawbacks Enterprise, Developer Editions only No inheritance, no automation Get scripts from www.SQLServerBible.com
- 35. The Silent Performance Killer
- 36. Pretend Youre SQL Server Photo Licensed with Creative Commons From http://www.flickr.com/photos/quacktaculous/3143079032/
- 37. 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
- 38. 13-460% Faster, And No app changes No vendor issues No testing required No guessing on indexes No backfires Fully supported Even recommended!
- 39. Not All Unicorns and Roses Standard Edition: Offline Only Enterprise Edition: Most Online Generates a lot of transaction logs
- 41. The Better Way: T-SQL Code
- 42. Dont Reinvent The Wheel
- 43. 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