Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 перестройка индексов и SET LOCK_TIMEOUT  [new]
londinium
Member

Откуда: Киев
Сообщений: 1193
Всем здравствуйте,
имеется
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
	Jun 28 2012 08:36:30 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)


и вот такой скрипт для перестройки индексов
DECLARE @InTableName NVARCHAR(128);
DECLARE @InIndexName NVARCHAR(128);
DECLARE @InIndexID INT;
DECLARE @InPagesCount BIGINT;
DECLARE @InIndexTypeDescription NVARCHAR(255);
DECLARE @InFragmentationPercent DECIMAL(20,2);
DECLARE @InSQLString NVARCHAR(1024);

SELECT  @InTableName='';
SELECT  @InIndexName='';
SELECT  @InIndexID=0;
SELECT  @InPagesCount=0;
SELECT  @InIndexTypeDescription='';
SELECT  @InFragmentationPercent=0.00;
SELECT  @InSQLString='';

DECLARE @InAddedRecord TABLE(IndexRebuildLog_ID INT);
DECLARE @InAddedRecordID INT;

DECLARE cIndexCursor CURSOR LOCAL FAST_FORWARD READ_ONLY
 FOR
 (
   SELECT 
    OBJECT_NAME(A.[object_id]) as TableName,
    B.[name] as IndexName,
    A.[index_id],
    A.[page_count],
    A.[index_type_desc],
    A.[avg_fragmentation_in_percent]/*,
    A.[fragment_count]*/
   FROM
    sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
    sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id 
    WHERE B.[name] IS NOT NULL
    AND OBJECT_NAME(A.[object_id])NOT LIKE 'MS%' 
    
 )ORDER BY TableName,IndexName;
OPEN cIndexCursor;
FETCH NEXT FROM cIndexCursor INTO @InTableName,@InIndexName,@InIndexID,
@InPagesCount,@InIndexTypeDescription,@InFragmentationPercent
WHILE @@FETCH_STATUS=0
 BEGIN--START CURSOR
 
 
 
 INSERT dbo.SERVICE_IndexRebuildLog(IndexName,StartDate,StopDate,TABLENAME)
  OUTPUT INSERTED.IndexRebuildLog_ID INTO @InAddedRecord
 VALUES(@InIndexName,GETDATE(),NULL,@InTableName)
 
 SELECT @InAddedRecordID=A.IndexRebuildLog_ID
  FROM @InAddedRecord A; 
 
 IF(@InFragmentationPercent BETWEEN 5.00 AND 30.00)
  BEGIN
   SELECT @InSQLString='ALTER INDEX '+@InIndexName+' '+
    'ON '+@InTableName+' '+' REORGANIZE';
    
   EXEC sp_executesql @InSQLString;
   
  END
 
 IF(@InFragmentationPercent>30.00)
  BEGIN
    SELECT @InSQLString='ALTER INDEX '+@InIndexName+' '+
    'ON '+@InTableName+' '+' REBUILD';
    
    EXEC sp_executesql @InSQLString;
    
  END 
  --SET @sql = 'ALTER INDEX ALL ON ' + @TableName + 
  --' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
 
  UPDATE dbo.SERVICE_IndexRebuildLog SET
   StopDate=GETDATE()
   WHERE IndexRebuildLog_ID=@InAddedRecordID; 
    
  SELECT  @InTableName='';
  SELECT  @InIndexName='';
  SELECT  @InIndexID=0;
  SELECT  @InPagesCount=0;
  SELECT  @InIndexTypeDescription='';
  SELECT  @InFragmentationPercent=0.00;
  SELECT  @InSQLString='';
  
  DELETE  @InAddedRecord;
  SELECT  @InAddedRecordID=0;
  
  FETCH NEXT FROM cIndexCursor INTO @InTableName,@InIndexName,@InIndexID,
  @InPagesCount,@InIndexTypeDescription,@InFragmentationPercent
  
 END--END CURSOR
CLOSE cIndexCursor;
DEALLOCATE cIndexCursor; 

Вопрос такой: как можно усовершенствовать скрипт так, чтобы если за какое-то время(например 40 секунд) он не может заблокировать индекс для перестройки, то работал бы дальше, а не пытался получить блокировку? Достаточно ли будет в начале скрипта прописать
SET LOCK_TIMEOUT 40000;

?

С уважением, Londinium
31 мар 15, 10:25    [17453069]     Ответить | Цитировать Сообщить модератору
 Re: перестройка индексов и SET LOCK_TIMEOUT  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
londinium,

а что мешает открыть две сессии ssms и попробовать?

+

А в 2014 есть low priority locks...

31 мар 15, 10:36    [17453142]     Ответить | Цитировать Сообщить модератору
 Re: перестройка индексов и SET LOCK_TIMEOUT  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ тест

-- сессия 1
create table dbo.t1(id int);
go

create index idx__t1__id on dbo.t1(id);
go

begin transaction;

insert into t1(id) values(1);
go


-- сессия 2
set lock_timeout 1000;
go

alter index idx__t1__id on t1 rebuild with(online = on);
go


где-то через секунду, во второй сессии
Msg 1222, Level 16, State 56, Line 4
Lock request time out period exceeded.

31 мар 15, 10:45    [17453190]     Ответить | Цитировать Сообщить модератору
 Re: перестройка индексов и SET LOCK_TIMEOUT  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
А еще проще взять готовое, бесплатное и проверенное решение.
SQL Server Index and Statistics Maintenance
LockTimeout

Set the time, in seconds, that a command waits for a lock to be released. By default, the time is not limited.

The LockTimeout option in IndexOptimize uses the SET LOCK_TIMEOUT set statement in SQL Server.
31 мар 15, 22:20    [17456562]     Ответить | Цитировать Сообщить модератору
 Re: перестройка индексов и SET LOCK_TIMEOUT  [new]
londinium
Member

Откуда: Киев
Сообщений: 1193
автор
А еще проще взять готовое, бесплатное и проверенное решение.

спасибо. надо попробовать
1 апр 15, 09:14    [17457338]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить