Advanced Index Tuning for SQL Server
Download as PPTX, PDF
5 likes2,254 views
How to make Microsoft SQL Server 2005 and 2008 go faster by querying the Dynamic Management Views (DMVs) to build new indexes and drop ones that aren't being used.
Convert to study guideBETA
Transform any presentation into a summarized study guide, highlighting the most important points and key insights.
![The End ResultCREATE 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)](https://image.slidesharecdn.com/advancedindextuning-091009080940-phpapp02/85/Advanced-Index-Tuning-for-SQL-Server-27-320.jpg)
![Rename Each IndexCREATE NONCLUSTERED INDEX [IX_ServerName_ActivityTypeID_StatusTypeID_StartTime_DatabaseID] ON [dbo].[Activity] ( [ServerName] ASC, [ActivityTypeID] ASC, [StatusTypeID] ASC, [StartTime] ASC, [DatabaseID] ASC) ON [PRIMARY]](https://image.slidesharecdn.com/advancedindextuning-091009080940-phpapp02/85/Advanced-Index-Tuning-for-SQL-Server-28-320.jpg)
Advanced Index Tuning for SQL Server
- 2. Brent OzarSQL Server MVPfor Quest SoftwareSQLServerPedia Ed.SQL, SAN, VM adminDot-com-crash experiencePerformance freakTwitter @BrentOBlog: www.BrentOzar.comPhoto Source http://www.flickr.com/photos/vasta/415625308/
- 3. Todays AgendaPassive TuningActive TuningIndex CompressionThe Silent KillerResources and Q&A
- 4. Passive Tuning with DMVs
- 7. Results
- 8. Why Are Writes Different?
- 10. Indexes On An Item TableIndex #1:UPC_CodeIndex #2:SupplierIDIndex #3:CategoryQtyInStockPhoto licensed with Creative Commons from:http://www.flickr.com/photos/revolute/1944742197/
- 11. Data In The Index
- 12. But When It 遺鞄温稼乙艶壊
- 13. Thats Why
- 14. Our Query
- 15. A Better Way: IncludesCREATE INDEX IX_Category_Includes ON dbo.Items (Category) INCLUDE (QtyInStock)
- 16. And When It 遺鞄温稼乙艶壊
- 17. Query Plan
- 18. Remember, Never SELECT *
- 19. Leave a Bread Crumb Trail
- 22. Okay Now What?
- 23. When Do We Add Indexes?
- 25. Active Tuning: The Wizards
- 26. Danger! Danger!
- 27. The End ResultCREATE 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)
- 28. Rename Each IndexCREATE NONCLUSTERED INDEX [IX_ServerName_ActivityTypeID_StatusTypeID_StartTime_DatabaseID] ON [dbo].[Activity] ( [ServerName] ASC, [ActivityTypeID] ASC, [StatusTypeID] ASC, [StartTime] ASC, [DatabaseID] ASC) ON [PRIMARY]
- 29. Makes Ongoing Tuning Easier
- 30. Active Tuning SummaryDont just click applyUse smart namesLook for overlapsGo passive first
- 31. SQL 2008 Data Compression
- 33. Index Compression DrawbacksEnterprise Edition onlyNo inheritanceNo automation
- 34. The Silent Performance Killer
- 35. Pretend Youre SQL ServerPhoto Licensed with Creative Commons From http://www.flickr.com/photos/quacktaculous/3143079032/
- 37. Absolutely No:App changesVendor issuesTesting requiredGuessing on indexesBackfires
- 38. Not All Unicorns and RosesStandard Edition: Offline OnlyEnterprise Edition: Most OnlineGenerates a lot of transaction logs
- 40. The Better Way: T-SQL Code
- 41. Dont Reinvent The Wheel