Comments / Profile of generalx / Habr

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 12:32:42 PM Progress: 2024-12-07 12:32:43.29 Source: {3F75105D-FFED-468F-8903-4844FFB5DE7E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2024-12-07 12:32:50.71 Source: Execute T-SQL Statement Task Executing query "SET QUOTED_IDENTIFIER ON; if DB_ID('profiler') ...".: 100% complete End Progress Error: 2024-12-07 12:32:50.76 Code: 0xC002F210 Source: Execute T-SQL Statement Task 2 Execute SQL Task Description: Executing the query "DECLARE @FramentationReportTable_2 VARCHAR(255) ..." failed with the following error: "Incorrect syntax near 'bad_indexes_2'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2024-12-07 12:32:50.76 Code: 0xC002F210 Source: Execute T-SQL Statement Task 1 Execute SQL Task Description: Executing the query "DECLARE @FramentationReportTable_1 VARCHAR(255) ..." failed with the following error: "Incorrect syntax near 'bad_indexes_1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2024-12-07 12:32:50.76 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:32:42 PM Finished: 12:32:50 PM Elapsed: 8.078 seconds. The package execution failed. The step failed.

Look

MSSQL Переиндексация в несколько потоков

generalx Dec 7 2024 at 09:27

ща попробуем запрос проверить
и за коменнтить

Look

MSSQL Переиндексация в несколько потоков

generalx Dec 7 2024 at 08:41

DECLARE @FramentationReportTable_1 VARCHAR(255)
SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES WHERE table_Name LIKE 'Fragmentation_GoldenHome_Treasure_NEW_2012_'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_1

exec ('
DECLARE bad_indexes_1 CURSOR FOR

select
frag.db_name,
frag.schema_name,
frag.table_name,
frag.index_name,
frag.partition_num,
case
when frag.record_size16 <= 403 then 95 when frag.record_size16 <= 806 then 90
when frag.record_size*16 <= 1209 then 85
else 80
end as suggested_fillfactor

from Profiler.dbo.'+ @FramentationReportTable_1 +' frag
where frag.page_count > 24 and frag.fragmentation >= 5 and frag.num = 1 and frag.index_type <>''HEAP''
order by ROUND ([fragmentation],0 ) desc, [rows_count] desc')

-- Open the cursor.
OPEN bad_indexes_1

DECLARE @db_name nvarchar(130);
DECLARE @schema_name nvarchar(130);
DECLARE @table_name nvarchar(130);
DECLARE @index_name nvarchar(130);
DECLARE @fragmentation bigint;
DECLARE @suggested_fillfactor int;
DECLARE @partition_num bigint;
DECLARE @partitionOption nvarchar(130);
DECLARE @fillfactorOption nvarchar(130);
DECLARE @object_name nvarchar(1000);
DECLARE @command nvarchar(1000);
DECLARE @time nvarchar(130)

WHILE (1=1)
BEGIN
FETCH NEXT
FROM bad_indexes_1
INTO @db_name, @schema_name, @table_name, @index_name, @partition_num, @suggested_fillfactor;

IF @FETCH_STATUSS < 0 BREAK

IF @partition_num > 1
begin
SET @partitionOption = N' PARTITION=' + CAST@partition_numm AS nvarchar(10));
set @fillfactorOption = N''
end
else
begin
SET @partitionOption = N''
set @fillfactorOption = N' FILLFACTOR=' + CAST@suggested_fillfactorr as nvarchar(10)) + N', '
end

SET @object_name = QUOTENAME@db_namee) + N'.' + QUOTENAME@schema_namee) + N'.' + QUOTENAME@table_namee)
BEGIN TRY

SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption +  N'maxdop = 0, ONLINE = ON, SORT_IN_TEMPDB = ON, MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = BLOCKERS)'

set @time = CURRENT_TIMESTAMP;
print @time
RAISERROR@commandd, 0, 1)
EXEC@commandd)
set @time = CURRENT_TIMESTAMP;
RAISERROR(N'DONE', 0, 1)
END TRY
BEGIN CATCH
SET @command = N'ALTER INDEX ' + QUOTENAME@index_namee) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption + N'maxdop = 0, SORT_IN_TEMPDB = ON)'
set @time = CURRENT_TIMESTAMP;
print @time
RAISERROR@commandd, 0, 1)
EXEC@commandd)
set @time = CURRENT_TIMESTAMP;
RAISERROR(N'DONE', 0, 1)
print @time
END CATCH
END

close bad_indexes_1
deal

Look

MSSQL Переиндексация в несколько потоков

generalx Dec 7 2024 at 07:40

успешно выполнено есть 31 строка
куда мне дальше

Look

2 3 4