Reorganize Index Task [closed]

Question

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance?

Is this an adequate step of triage for degraded application/query performance?

Answer 1

The Maintenance Plan task can reorganize indexes and that should have some benefit for you. Likely, however, you will reorganize more than necessary using that approach.

You should look at Ola Hallengren's solution for Maintenance at: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 2

Can the Reorganize Index task (maint' plan or ssis package) be used to 'performance tune?

Performance tuning is an art. You should avoid knee jerk performance tuning methods - instead you should try to narrow down your investigation.

Is this an adequate step of triage for degraded application/query performance?

It may or may not be, depending on how many pages the index has and how bad the fragmentation. Brent Ozar has a nice post on - Stop Worrying About SQL Server Fragmentation and Kendra Little explains - Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) ?

What I would suggest you is have a baseline - e.g you can baseline your waitstats and check for any anomalies as a starting point. Then you can narrow down your investigation and find the root cause and address it.

Depending on sql server version you are using, use SQL Server Diagnostic Information Queries or SQL Server Health Check Script - sp_Blitz. These are free and very efficient tools written by experts and given to the community as free tools.

As a side note, as RLF suggested - use SQL Server Index and Statistics Maintenance for doing index maintenance. Its very widely used in the community and its free as well. Dont reinvent the wheel by writing your own solution.