search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

running rebuild on individual index takes a long time compared to native SQL commands #808

Open stevetrinhwork opened 5 months ago

stevetrinhwork commented 5 months ago

Description of the issue I'm trying to run an index rebuild and comparing Ola commands vs native SQL commands and the time it takes to complete is significantly longer for Ola version.

SQL Server version and edition Execute SELECT @@VERSION Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) Mar 19 2024 00:23:01 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

Version of the script Check the header of the stored procedure Version: 2022-01-02 13:58:13

What command are you executing? EXECUTE dbo.IndexOptimize @Databases = 'TE_3E_PROD', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @Indexes = 'TE_3E_PROD.dbo.CostBill.PKCostBill737B04B8', @SortInTempdb = 'Y', @LogToTable = 'Y'

ALTER INDEX [PKCostBill737B04B8] ON [dbo].[CostBill] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, RESUMABLE = OFF)

What output are you getting? This took a little over 9 minutes to run. See the time stamps.

Date and time: 2024-06-05 13:45:44 Version: 2022-01-02 13:58:13 Date and time: 2024-06-05 13:45:44 Date and time: 2024-06-05 13:54:52 Completion time: 2024-06-05T13:54:52.1654988-07:00

vs

Native SQL This took about 45 seconds.

griffitmatt commented 5 months ago

Hi Steve, the OLA (Online Archival) script execution time will vary based on the table size and system resources. This is because it assesses the fragmentation level to determine the appropriate maintenance action, such as an index reorganization or an index rebuild.

stevetrinhwork commented 5 months ago

Hmmm, so I should pass complete set of tables/indexes to process so it only has to do it once?