none
Gerenciamento de indices X Consultas RRS feed

  • Pergunta

  • durante a alteração de ativação de Indices de tabelas no SQL Server

    inviabiliza inclusive consultas utilizando WITH (NOLOCK) ??

    quinta-feira, 21 de março de 2013 19:56

Respostas

  • Renato,

    Sim, a tabela entra em schema lock, ou seja, a tabela inteira fica lockada, fiz um teste aqui com 3 sessões e a opção de rebuild de todos os indices.

    Sessao 1:

    IF(OBJECT_ID('TabelaTesteNoLock') IS NOT NULL)
    	DROP TABLE TabelaTesteNoLock
    
    CREATE TABLE TabelaTesteNoLock
    (
    	Id	INT	IDENTITY(1,1)	PRIMARY KEY,
    	Nome	VARCHAR(10)
    )
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('F', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('G', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('H', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('I', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('J', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('K', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('L', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('M', 10)
    GO 50000
    
    --Criação de indice
    CREATE INDEX IX_01 ON TabelaTesteNoLock(Nome)
    CREATE INDEX IX_02 ON TabelaTesteNoLock(Id, Nome)
    CREATE INDEX IX_03 ON TabelaTesteNoLock(Id)
    CREATE INDEX IX_04 ON TabelaTesteNoLock(Nome, Id)
    
    ALTER INDEX ALL ON TabelaTesteNoLock REBUILD

    Sessão 2:

    SELECT *
    FROM TabelaTesteNoLock(NOLOCK)

    Sessão 3

    SELECT *
    FROM sys.sysprocesses
    WHERE blocked <> 0

    Lock encontrado: LCK_M_SCH_S

    Maiores detalhes, aqui: http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 22 de março de 2013 11:49
    Moderador
  • Deleted
    terça-feira, 26 de março de 2013 20:07

Todas as Respostas

  • Renato,

    Sim, a tabela entra em schema lock, ou seja, a tabela inteira fica lockada, fiz um teste aqui com 3 sessões e a opção de rebuild de todos os indices.

    Sessao 1:

    IF(OBJECT_ID('TabelaTesteNoLock') IS NOT NULL)
    	DROP TABLE TabelaTesteNoLock
    
    CREATE TABLE TabelaTesteNoLock
    (
    	Id	INT	IDENTITY(1,1)	PRIMARY KEY,
    	Nome	VARCHAR(10)
    )
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('F', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('G', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('H', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('I', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('J', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('K', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('L', 10)
    GO 50000
    
    INSERT INTO TabelaTesteNoLock(Nome)
    SELECT REPLICATE('M', 10)
    GO 50000
    
    --Criação de indice
    CREATE INDEX IX_01 ON TabelaTesteNoLock(Nome)
    CREATE INDEX IX_02 ON TabelaTesteNoLock(Id, Nome)
    CREATE INDEX IX_03 ON TabelaTesteNoLock(Id)
    CREATE INDEX IX_04 ON TabelaTesteNoLock(Nome, Id)
    
    ALTER INDEX ALL ON TabelaTesteNoLock REBUILD

    Sessão 2:

    SELECT *
    FROM TabelaTesteNoLock(NOLOCK)

    Sessão 3

    SELECT *
    FROM sys.sysprocesses
    WHERE blocked <> 0

    Lock encontrado: LCK_M_SCH_S

    Maiores detalhes, aqui: http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    sexta-feira, 22 de março de 2013 11:49
    Moderador
  • Deleted
    terça-feira, 26 de março de 2013 20:07