1

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?

closed as unclear what you're asking by Shanky, Colin 't Hart, RolandoMySQLDBA, RLF, Marian Jun 27 '15 at 14:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

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

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.