locked
Lock no SQL Server RRS feed

  • Pergunta

  • Olá pessoal,

    No ambiente da empresa tenho um SQL SERVER 2008 R2 instalado, porém de um tempo pra cá começou a apresentar lock com uma certa frequência.

    Então em dois momentos fui obrigado a reiniciar o serviço do SQL SERVER e na hora voltou a funcionar normalmente.

    Percebi que a base TEMPDB depois de reiniciado começou com 8MB que é o padrão dele depois cresceu e ficou com aproximadamente 1,2 GB até ai tudo bem, mas quando vou no SQL Server na parte gráfica para abrir a tema do SHRINK o espaço alocado informa que esta com 8MB.

    1. A pergunta é o TEMPDB nessa parte gráfica do SQL Server não deveria esta apresentando o tamanho 1.2GB?
    2. faço manutenção das estatistificas diariamente e semanalmente recompilo os índices, há alguma coisa que posso ajuda para reduzir bastante os locks da base? Não tenho acesso ao código fonte da aplicação para analisar se estão fechando as conexões corretamente.

    sexta-feira, 5 de janeiro de 2018 17:06

Respostas

  • Olá Pedro,

    Identifiquei através da própria aplicação que utilizo da TOTVS, pois nele tem um seção que mostra os locks em tempo real.

    Vou utilizar os scripts fornecidos e avaliar.

    Obrigado.

    terça-feira, 9 de janeiro de 2018 19:36
  • Gambit,

    Os valores correspondem a contadores internos utilizados pelo SQL Server, no caso:

    execution_count 

    Número de vezes que o plano foi executado desde sua última compilação.

    total_elapsed_time

    Tempo total decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para execuções concluídas desse plano.

    last_elapsed_time

    Tempo decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para a execução completa mais recente desse plano.

    total_logical_reads

    Número total de leituras lógicas efetuadas por execuções deste plano desde sua compilação.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    quinta-feira, 11 de janeiro de 2018 21:42
    Moderador

Todas as Respostas

  • Gambit,

    De que forma você identificou esta ocorrência de locks?

    Suas querys estão retornando algum código de erro ou você verificou no log do seu banco de dados.

    Em relação ao tamanho do TempDB, por padrão ele vai sempre mostrar o tamanho que esta sendo alocado naquele momento, o valor de 8MBs realmente é o valor padrão, este valor de espaço alocado representa o que esta sendo consumido em tempo de execução por transações ou objetos temporários.

    Uma sugestão inicial e verificar o que realmente esta sendo executado e consumindo de buffer cache, acredito que as querys abaixo podem lhe ajudar:

    -- Obtendo a quantidade de linhas de registro por páginas armazenadas em cache --

    - Exemplo 1 - Sumarizado --
    Select ST.Name As 'Tabela',
           SP.rows As 'Linhas',
    	   SA.data_pages As 'Páginas de Dados',
    	   SA.used_pages As 'Páginas de Dados Utilizadas',
    	   SA.total_pages As 'Total de Páginas de Dados'	   
    from sys.tables ST Inner Join sys.partitions SP
                        On ST.object_id = SP.object_id
    				   Inner Join sys.allocation_units SA
    				    On SA.container_id = SP.partition_id
    Go
    
    -- Exemplo 2 - Detalhada --
    Select ST.Name As 'Tabela',
           SP.rows As 'Linhas',
    	   SA.data_pages As 'Páginas de Dados',
    	   SA.used_pages As 'Páginas de Dados Utilizadas',
    	   SA.total_pages As 'Total de Páginas de Dados',	   
    	   SB.Row_Count
    from sys.tables ST Inner Join sys.partitions SP
                        On ST.object_id = SP.object_id
    				   Inner Join sys.allocation_units SA
    				    On SA.container_id = SP.partition_id
    				   Inner Join sys.dm_os_buffer_descriptors SB
    				    On SB.allocation_unit_id = SA.allocation_unit_id
    Go
    
    -- Exemplo 3 - Mais Detalhada --
    Select ST.Name As 'Tabela',
           SP.rows As 'Linhas',
    	   SB.free_space_in_bytes As 'Espaço Livre em Bytes',
    	   SB.page_id As 'Identificador da Página',
    	   SB.page_type As 'Tipo da Página',
    	   SB.Row_Count As 'Total de Linhas por Página'
    from sys.tables ST Inner Join sys.partitions SP
                        On ST.object_id = SP.object_id
    				   Inner Join sys.allocation_units SA
    				    On SA.container_id = SP.partition_id
    				   Inner Join sys.dm_os_buffer_descriptors SB
    				    On SB.allocation_unit_id = SA.allocation_unit_id
    Go

    -- Obtendo a quantidade de páginas em buffer cache --

    -- Returning cached page count for each database --
    SELECT COUNT(*)AS cached_pages_count
        ,CASE database_id 
            WHEN 32767 THEN 'ResourceDb' 
            ELSE db_name(database_id) 
            END AS database_name
    FROM sys.dm_os_buffer_descriptors
    GROUP BY DB_NAME(database_id) ,database_id
    ORDER BY cached_pages_count DESC
    Go
    
    -- Returning cached page count for each object in the current database --
    SELECT COUNT(*)AS cached_pages_count 
        ,name ,index_id 
    FROM sys.dm_os_buffer_descriptors AS bd 
        INNER JOIN 
        (
            SELECT object_name(object_id) AS name 
                ,index_id ,allocation_unit_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p 
                    ON au.container_id = p.hobt_id 
                        AND (au.type = 1 OR au.type = 3)
            UNION ALL
            SELECT object_name(object_id) AS name   
                ,index_id, allocation_unit_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p 
                    ON au.container_id = p.partition_id 
                        AND au.type = 2
        ) AS obj 
            ON bd.allocation_unit_id = obj.allocation_unit_id
    WHERE database_id = DB_ID()
    GROUP BY name, index_id 
    ORDER BY cached_pages_count DESC;


    -- Obtendo o Buffer Cache for Database --

    SELECT  DB_NAME(database_id) AS [Database Name] ,
            COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
    FROM    sys.dm_os_buffer_descriptors
    WHERE   database_id > 4 -- exclude system databases
            AND database_id <> 32767 -- exclude ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC ;

    -- Identificando transações em cache de execução --

    SELECT QT.text,
                 QP.query_plan,
                 QS.execution_count,
                 QS.total_elapsed_time,
                 QS.last_elapsed_time,
                 QS.total_logical_reads
    FROM sys.dm_exec_query_stats as QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as QT
     CROSS APPLY sys.dm_exec_query_plan (QS.plan_handle) as QP
    ORDER BY QS.execution_count DESC


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 6 de janeiro de 2018 21:16
    Moderador
  • Olá Pedro,

    Identifiquei através da própria aplicação que utilizo da TOTVS, pois nele tem um seção que mostra os locks em tempo real.

    Vou utilizar os scripts fornecidos e avaliar.

    Obrigado.

    terça-feira, 9 de janeiro de 2018 19:36
  • Boa tarde Pedro,

    Gostaria de uma orientação de como vou proceder para analisar o resultado dos select. 

    Quais valores devo ter mais atenção?

    No último script fornecido Identificando transações em cache de execução , sempre que executa a primeira linha retorna em branco no campo text e query_plan e nas demais colunas apresenta os seguintes valores:

    execution_count = 2567668

    total_elapsed_time = 42597665

    last_elapsed_time = 0

    total_logical_reads= 1682300

    Att.

    quinta-feira, 11 de janeiro de 2018 16:56
  • Gambit,

    Os valores correspondem a contadores internos utilizados pelo SQL Server, no caso:

    execution_count 

    Número de vezes que o plano foi executado desde sua última compilação.

    total_elapsed_time

    Tempo total decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para execuções concluídas desse plano.

    last_elapsed_time

    Tempo decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para a execução completa mais recente desse plano.

    total_logical_reads

    Número total de leituras lógicas efetuadas por execuções deste plano desde sua compilação.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    quinta-feira, 11 de janeiro de 2018 21:42
    Moderador