1 / 9

A Beginner's Guide to Defragging SQL Indexes: Boost Performance and Efficiency

Discover why defragging SQL indexes is crucial for maintaining database performance. Fragmented indexes can slow down queries significantly, leading to inefficiencies. This guide will walk you through creating a database, loading data, adding an index, and understanding when to reorganize or rebuild indexes based on fragmentation levels. Learn about effective maintenance strategies, including the use of automated scripts, and tools like Idera’s SQL Defrag Manager. Keep your indexes optimized to ensure fast and reliable database operations.

lucien
Télécharger la présentation

A Beginner's Guide to Defragging SQL Indexes: Boost Performance and Efficiency

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Defragging Indexes For Beginners

  2. Why we need to defrag indexes • Fragmented indexes are ineffective • Ineffective indexes are slooooooow

  3. Over to the code…. We are going to: • Create a database • Create a table • Load some data • Add an index • Fragment the index • Fix the index

  4. Demo...

  5. Which option is best? • > 5% and < = 30% fragmentation ALTER INDEX REORGANIZE • > 30% fragmentation ALTER INDEX REBUILD WITH (ONLINE = ON)

  6. Keep your indexes sweet • Perform regular maintenance on your database, include index checks/defrags as part of the maintenance task. • A tool such as Idera’s SQL Defrag Manager could be useful, but it is pricey. • Write a script and set up a scheduled task to automate your index maintenance.

  7. Conclusion • Reorganize Index • Performed online • Does not lock for long periods • Does not block updates or other queries • Best option for fragmentation between 5% and 30%. • Rebuild Index • Drops and recreates the index • Will give the best defragmentation results • Can be performed online or offline • Best option for fragmentation greater than 30% • Maintenance • Monitor your index fragmentation • Fix fragmented indexes before they become a problem

  8. Resources • Click on the Word icon to the right to access the SQL code and notes used in the practical element of this session. • Download AdventureWorks2008 DBs for free here: http://tinyurl.com/c6y6a3 • More info on fill factors over at the MSDN site: http://msdn.microsoft.com/en-us/library/aa933139(SQL.80).aspx • Detailed information on Reorganize and Rebuild: http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx • Great information on Indexes from Brad McGehee • http://tinyurl.com/mfx8h7 • Maintenance Options: • http://www.idera.com/Products/SQL-Server/SQL-defrag-manager/ • http://msdn.microsoft.com/en-us/library/aa258803(SQL.80).aspx

  9. Website: www.aucklandsql.com Mailing list: announce@aucklandsql.com Blog: http://nzgirlgeek.blogspot.com Email: amanda@aucklandsql.com

More Related