Test #1 I have the SQL below that iterates through a cursor which has 374 records. I've tested the query a dozen times and it runs in 3 mins 40 secs (give or take a few seconds).
Test #2
I modify the query so that all declare statements appear before the while loop, the query then consistently completes 15 - 20 seconds faster.
I would have thought there would be almost no noticeable difference. Is a declare statement within a loop that much overhead, or is something else at play?
The cursor deals with the exact same records in both tests. I run test 1, then 2, then 1 again, then 2 again, etc. Test 2 has not run any worse than 8 seconds faster than test 1.
I'm pretty sure the declare statements are not causing the issue, but I can't explain why test 2 is consistently faster.
I should also mention that there are no other connections to the database while the tests are running.
declare @fillfactor int = 100
declare tableCursor CURSOR for
select table_catalog, table_schema, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
order by table_schema, table_name
open tableCursor
declare @db varchar(128), @schema varchar(128), @table varchar(128)
fetch next from tableCursor into @db, @schema, @table
declare @cmd nvarchar(2000)
declare @lastLogged datetime = getdate()
declare @msg as varchar(500)
declare @startDate datetime = getdate()
declare @processed int = 0
while @@FETCH_STATUS = 0
begin
declare @tableQualified varchar(200) = concat('[', @db, '].[', @schema, '].[', @table, ']')
set @cmd = 'ALTER INDEX ALL ON ' + @tableQualified + ' REBUILD WITH (FILLFACTOR = 100)'
exec (@cmd)
fetch next from tableCursor into @db, @schema, @table
set @processed += 1
declare @now datetime = getdate()
if datediff(second, @lastLogged, @now) > 30
begin
declare @elapsedSec int = datediff(second, @startDate, @now)
declare @mins int = @elapsedSec / 60
declare @seconds int = @elapsedSec % 60
set @msg = concat(@processed, ' tables processed in ', @mins, 'm ', @seconds, 's')
raiserror (@msg, 10, 1) with nowait
set @lastLogged = getdate()
end
END
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y'will rebuild or reorg all tables based on the fragmentation level and log resultsQUOTENAMEit should bedeclare @tableQualified nvarchar(1000) = CONCAT_WS(',', QUOTENAME(@db), QUOTENAME(@schema), QUOTENAME(@table))