Quais são as principais causas de deadlocks e podem ser evitadas?

Question

Josh Waclawski

Asked: 2013-06-19 05:54:20 +0800 CST2013-06-19 05:54:20 +0800 CST 2013-06-19 05:54:20 +0800 CST

Procedimento armazenado central para executar na chamada do contexto do banco de dados

772

Estou trabalhando em uma solução de manutenção personalizada usando o modo de sys.dm_db_index_physical_statsexibição. Atualmente, ele está sendo referenciado a partir de um procedimento armazenado. Agora, quando esse procedimento armazenado é executado em um dos meus bancos de dados, ele faz o que eu quero e obtém uma lista de todos os registros referentes a qualquer banco de dados. Quando eu o coloco em um banco de dados diferente, ele puxa uma lista de todos os registros relacionados apenas a esse banco de dados.

Por exemplo (código na parte inferior):

A consulta executada no banco de dados 6 mostra informações [solicitadas] para os bancos de dados 1-10.
A consulta executada no banco de dados 3 mostra informações [solicitadas] apenas para o banco de dados 3.

A razão pela qual quero este procedimento especificamente no banco de dados três é porque prefiro manter todos os objetos de manutenção no mesmo banco de dados. Eu gostaria que esse trabalho ficasse no banco de dados de manutenção e funcionasse como se estivesse no banco de dados do aplicativo.

Código:

ALTER PROCEDURE [dbo].[GetFragStats] 
    @databaseName   NVARCHAR(64) = NULL
    ,@tableName     NVARCHAR(64) = NULL
    ,@indexID       INT          = NULL
    ,@partNumber    INT          = NULL
    ,@Mode          NVARCHAR(64) = 'DETAILED'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @databaseID INT, @tableID INT

    IF @databaseName IS NOT NULL
        AND @databaseName NOT IN ('tempdb','ReportServerTempDB')
    BEGIN
        SET @databaseID = DB_ID(@databaseName)
    END

    IF @tableName IS NOT NULL
    BEGIN
        SET @tableID = OBJECT_ID(@tableName)
    END

    SELECT D.name AS DatabaseName,
      T.name AS TableName,
      I.name AS IndexName,
      S.index_id AS IndexID,
      S.avg_fragmentation_in_percent AS PercentFragment,
      S.fragment_count AS TotalFrags,
      S.avg_fragment_size_in_pages AS PagesPerFrag,
      S.page_count AS NumPages,
      S.index_type_desc AS IndexType
    FROM sys.dm_db_index_physical_stats(@databaseID, @tableID, 
           @indexID, @partNumber, @Mode) AS S
    JOIN 
       sys.databases AS D ON S.database_id = D.database_id
    JOIN 
       sys.tables AS T ON S.object_id = T.object_id
    JOIN 
       sys.indexes AS I ON S.object_id = I.object_id
                        AND S.index_id = I.index_id
    WHERE 
        S.avg_fragmentation_in_percent > 10
    ORDER BY 
        DatabaseName, TableName, IndexName, PercentFragment DESC    
END
GO

2 respostas

Voted

Aaron Bertrand · Answer 1 · 2013-06-19T07:02:18+08:00

Uma maneira seria criar um procedimento do sistema mastere, em seguida, criar um wrapper em seu banco de dados de manutenção. Observe que isso funcionará apenas para um banco de dados por vez.

Primeiro, no mestre:

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(),
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
    -- shouldn't s.partition_number be part of the output as well?
  FROM sys.tables AS t
  INNER JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(), t.[object_id], 
      i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  -- probably also want to filter on minimum page count too
  -- do you really care about a table that has 100 pages?
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

Agora, em seu banco de dados de manutenção, crie um wrapper que use SQL dinâmico para definir o contexto corretamente:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,      -- can't really be NULL, right?
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
    EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';

  EXEC sp_executesql 
    @sql,
    N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
    @tableName, @indexID, @partNumber, @Mode;
END
GO

(A razão pela qual o nome do banco de dados não pode realmente ser NULLé porque você não pode se juntar a coisas como sys.objectse sys.indexesdesde que existam independentemente em cada banco de dados. Portanto, talvez tenha um procedimento diferente se desejar informações em toda a instância.)

Agora você pode chamar isso para qualquer outro banco de dados, por exemplo

EXEC YourMaintenanceDatabase.dbo.GetFragStats 
  @DatabaseName = N'AdventureWorks2012',
  @TableName    = N'SalesOrderHeader';

E você sempre pode criar um synonymem cada banco de dados para nem precisar referenciar o nome do banco de dados de manutenção:

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

Outra maneira seria usar SQL dinâmico, porém isso também funcionará apenas para um banco de dados por vez:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT
    DatabaseName    = @DatabaseName,
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
        DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;';

  EXEC sp_executesql @sql, 
    N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
      @partNumber INT, @Mode NVARCHAR(20)',
    @DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO

Outra maneira seria criar uma exibição (ou função com valor de tabela) para unir os nomes de tabela e índice de todos os seus bancos de dados; no entanto, você teria que codificar os nomes dos bancos de dados na exibição e mantê-los à medida que adiciona /remove bancos de dados que você deseja permitir que sejam incluídos nesta consulta. Isso permitiria, ao contrário dos outros, recuperar estatísticas para vários bancos de dados de uma só vez.

Primeiro, a visão:

CREATE VIEW dbo.CertainTablesAndIndexes
AS
  SELECT 
    db = N'AdventureWorks2012',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM AdventureWorks2012.sys.tables AS t
  INNER JOIN AdventureWorks2012.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  UNION ALL

  SELECT 
    db = N'database2',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM database2.sys.tables AS t
  INNER JOIN database2.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  -- ... UNION ALL ...
  ;
GO

Então o procedimento:

CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName NVARCHAR(128) = NULL,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(s.database_id),
    TableName       = v.[table],
    IndexName       = v.[index],
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM dbo.CertainTablesAndIndexes AS v
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
    AND v.index_id = COALESCE(@indexID, v.index_id)
    AND v.[table] = COALESCE(@tableName, v.[table])
    AND v.db = COALESCE(@DatabaseName, v.db)
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO

Solomon Rutzky · Answer 2 · 2015-08-27T22:16:13+08:00

Bem, há más notícias, boas notícias com um problema e algumas notícias realmente boas.

As más notícias

Os objetos T-SQL são executados no banco de dados onde residem. Existem duas exceções (não muito úteis):

procedimentos armazenados com nomes prefixados com sp_e que existem no banco de [master]dados (não é uma ótima opção: um banco de dados por vez, adicionando algo a [master], possivelmente adicionando sinônimos a cada banco de dados, o que deve ser feito para cada novo banco de dados)
procedimentos armazenados temporários - local e global (não é uma opção prática, pois eles devem ser criados a cada vez e deixam você com os mesmos problemas que você tem com o sp_procedimento armazenado em [master].

A boa notícia (com uma pegadinha)

Muitos (talvez a maioria?) As pessoas estão cientes das funções internas para obter alguns metadados realmente comuns:

O uso dessas funções pode eliminar a necessidade de JOINs sys.databases(embora este não seja realmente um problema), sys.objects(preferido sobre o sys.tablesque exclui exibições indexadas) e sys.schemas(você estava perdendo esse e nem tudo está no dboesquema ;-). Mas mesmo removendo três dos quatro JOINs, ainda estamos funcionalmente no mesmo lugar, certo? Errado!

Um dos recursos interessantes das funções OBJECT_NAME()e OBJECT_SCHEMA_NAME()é que elas têm um segundo parâmetro opcional para @database_id. Ou seja, enquanto o JOINing para essas tabelas (exceto para sys.databases) é específico do banco de dados, o uso dessas funções fornece informações de todo o servidor. Mesmo OBJECT_ID() permite informações de todo o servidor, fornecendo a ele um nome de objeto totalmente qualificado.

Ao incorporar essas funções de metadados na consulta principal, podemos simplificar e, ao mesmo tempo, expandir além do banco de dados atual. A primeira passagem da refatoração da consulta nos dá:

SELECT  DB_NAME(stat.database_id) AS [DatabaseName],
        OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
        OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
        ind.name AS [IndexName],
        stat.index_id AS [IndexID],
        stat.avg_fragmentation_in_percent AS [PercentFragment],
        stat.fragment_count AS [TotalFrags],
        stat.avg_fragment_size_in_pages AS [PagesPerFrag],
        stat.page_count AS [NumPages],
        stat.index_type_desc AS [IndexType]
FROM sys.dm_db_index_physical_stats(@DatabaseID, @TableID, 
        @IndexID, @PartitionNumber, @Mode) stat
INNER JOIN sys.indexes ind
        ON ind.[object_id] = stat.[object_id]
       AND ind.[index_id] = stat.[index_id]
WHERE stat.avg_fragmentation_in_percent > 10
ORDER BY DatabaseName, TableName, IndexName, PercentFragment DESC;

E agora, para o "pedaço": não há função de metadados para obter nomes de índice, muito menos uma para todo o servidor. Então é isso? Estamos em 90% completos e ainda precisando estar em um banco de dados específico para obter sys.indexesdados? Nós realmente precisamos criar um procedimento armazenado para usar o SQL dinâmico para preencher, cada vez que nosso proc principal é executado, uma tabela temporária de todas as sys.indexesentradas em todos os bancos de dados para que possamos ingressar nela? NÃO!

A notícia realmente boa

Então vem um pequeno recurso que algumas pessoas adoram odiar, mas quando usado corretamente, pode fazer coisas incríveis. Sim: SQLCLR. Por quê? Como as funções SQLCLR podem obviamente enviar instruções SQL, mas pela própria natureza do envio do código do aplicativo, é um SQL dinâmico. Portanto, ao contrário das funções T-SQL, as funções SQLCLR podem injetar um nome de banco de dados na consulta antes de executá-la. Ou seja, podemos criar nossa própria função para espelhar a capacidade de pegar OBJECT_NAME()e obter as informações desse banco de dados.OBJECT_SCHEMA_NAME()database_id

O código a seguir é essa função. Mas é preciso um nome de banco de dados em vez de ID para que não precise fazer a etapa extra de procurá-lo (o que o torna um pouco menos complicado e um pouco mais rápido).

public class MetaDataFunctions
{
    [return: SqlFacet(MaxSize = 128)]
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true,
        SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString IndexName([SqlFacet(MaxSize = 128)] SqlString DatabaseName,
        SqlInt32 ObjectID, SqlInt32 IndexID)
    {
        string _IndexName = @"<unknown>";

        using (SqlConnection _Connection =
                                    new SqlConnection("Context Connection = true;"))
        {
            using (SqlCommand _Command = _Connection.CreateCommand())
            {
                _Command.CommandText = @"
SELECT @IndexName = si.[name]
FROM   [" + DatabaseName.Value + @"].[sys].[indexes] si
WHERE  si.[object_id] = @ObjectID
AND    si.[index_id] = @IndexID;
";

                SqlParameter _ParamObjectID = new SqlParameter("@ObjectID",
                                               SqlDbType.Int);
                _ParamObjectID.Value = ObjectID.Value;
                _Command.Parameters.Add(_ParamObjectID);

               SqlParameter _ParamIndexID = new SqlParameter("@IndexID", SqlDbType.Int);
                _ParamIndexID.Value = IndexID.Value;
                _Command.Parameters.Add(_ParamIndexID);

                SqlParameter _ParamIndexName = new SqlParameter("@IndexName",
                                                  SqlDbType.NVarChar, 128);
                _ParamIndexName.Direction = ParameterDirection.Output;
                _Command.Parameters.Add(_ParamIndexName);

                _Connection.Open();
                _Command.ExecuteNonQuery();

                if (_ParamIndexName.Value != DBNull.Value)
                {
                    _IndexName = (string)_ParamIndexName.Value;
                }
            }
        }

        return _IndexName;
    }
}

If you will notice, we are using the Context Connection, which is not only fast, but also works in SAFE Assemblies. Yep, this works in an Assembly marked as SAFE~~, so it (or variations of it) should even work on Azure SQL Database V12~~ ^{(support for SQLCLR was removed, rather abruptly, from Azure SQL Database in April, 2016)}.

So our second-pass refactoring of the main query gives us the following:

SELECT  DB_NAME(stat.database_id) AS [DatabaseName],
        OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
        OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
        dbo.IndexName(DB_NAME(stat.database_id), stat.[object_id], stat.[index_id])
                     AS [IndexName],
        stat.index_id AS [IndexID],
        stat.avg_fragmentation_in_percent AS [PercentFragment],
        stat.fragment_count AS [TotalFrags],
        stat.avg_fragment_size_in_pages AS [PagesPerFrag],
        stat.page_count AS [NumPages],
        stat.index_type_desc AS [IndexType]
FROM sys.dm_db_index_physical_stats(@DatabaseID, @TableID, 
        @IndexID, @PartitionNumber, @Mode) stat
WHERE stat.avg_fragmentation_in_percent > 10
ORDER BY DatabaseName, TableName, IndexName, PercentFragment DESC;

That's it! Both this SQLCLR Scalar UDF and your maintenance T-SQL Stored Procedure can live in the same centralized [maintenance] database. AND, you don't have to process one database at a time; now you have meta-data functions for all dependent info that is server-wide.

P.S. There is no .IsNull checking of input parameters in the C# code since the T-SQL wrapper object should be created with the WITH RETURNS NULL ON NULL INPUT option:

CREATE FUNCTION [dbo].[IndexName]
                   (@DatabaseName [nvarchar](128), @ObjectID [int], @IndexID [int])
RETURNS [nvarchar](128) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [{AssemblyName}].[MetaDataFunctions].[IndexName];

Additional notes:

The method described here can also be used to solve other, very similar problems of missing cross-database meta-data functions. The following Microsoft Connect suggestion is an example of one such case. And, seeing that Microsoft has closed it as "Won't Fix", it is clear that they are not interested providing built-in functions like OBJECT_NAME() to meet this need (hence the Workaround that is posted on that Suggestion :-).

Add metadata function to get object name from hobt_id
To learn more about using SQLCLR, please take a look at the Stairway to SQLCLR series I am writing on SQL Server Central (free registration is required; sorry, I don't control the policies of that site).
The IndexName() SQLCLR function shown above is available, pre-compiled, in an easy-to-install script on Pastebin. The script enables the "CLR Integration" feature if it is not already enabled, and the Assembly is marked as SAFE. It is compiled against .NET Framework version 2.0 so that it will work in SQL Server 2005 and newer (i.e. all versions that support SQLCLR).

SQLCLR Meta-data function for cross-database IndexName()
If anyone is interested in the IndexName() SQLCLR function and over 320 other functions and stored procedures, it is available in the SQL# library (which I am the author of). Please note that while there is a Free version, the Sys_IndexName function is only available in the Full version (along with a similar Sys_AssemblyName function).

Procedimento armazenado central para executar na chamada do contexto do banco de dados

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Conceder acesso a todas as tabelas para um usuário

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Procedimento armazenado central para executar na chamada do contexto do banco de dados

2 respostas

relate perguntas