Anti-padrão de Base de Dados OcupadaBusy Database antipattern

O descarregamento de processamento para um servidor de base de dados pode fazer com que passe bastante tempo a executar o código, em vez de responder a pedidos para armazenar e obter dados.Offloading processing to a database server can cause it to spend a significant proportion of time running code, rather than responding to requests to store and retrieve data.

Descrição do problemaProblem description

Muitos sistemas de base de dados podem executar código.Many database systems can run code. Os exemplos incluem acionadores e procedimentos armazenados.Examples include stored procedures and triggers. Muitas vezes, é mais eficiente realizar este processamento próximo aos dados, em vez de os transmitir os dados para uma aplicação de cliente para processamento.Often, it's more efficient to perform this processing close to the data, rather than transmitting the data to a client application for processing. No entanto, utilizar estas funcionalidades em demasia pode prejudicar o desempenho, por vários motivos:However, overusing these features can hurt performance, for several reasons:

  • O servidor de base de dados pode passar demasiado tempo a processar, em vez de aceitar novos pedidos de cliente e obter dados.The database server may spend too much time processing, rather than accepting new client requests and fetching data.
  • Uma base de dados é normalmente um recurso partilhado, pelo que esta pode tornar-se um estrangulamento durante períodos de utilização elevada.A database is usually a shared resource, so it can become a bottleneck during periods of high use.
  • Os custos de runtime podem ser excessivos se o arquivo de dados for limitado.Runtime costs may be excessive if the data store is metered. É particularmente verdadeiro nos serviços de base de dados geridos.That's particularly true of managed database services. Por exemplo, as cobranças da Base de Dados SQL do Azure para Unidades de Transação de Base de Dados (DTUs).For example, Azure SQL Database charges for Database Transaction Units (DTUs).
  • As bases de dados têm capacidade finita para aumentar verticalmente e não é trivial para aumentar horizontalmente uma base de dados.Databases have finite capacity to scale up, and it's not trivial to scale a database horizontally. Por conseguinte, poderá ser melhor mover o processamento para um recurso de computação, como uma aplicação de VM ou do Serviço de Aplicações, que pode facilmente aumentar horizontalmente.Therefore, it may be better to move processing into a compute resource, such as a VM or App Service app, that can easily scale out.

Este anti-padrão ocorre normalmente porque:This antipattern typically occurs because:

  • A base de dados é apresentada como um serviço, em vez de um repositório.The database is viewed as a service rather than a repository. Uma aplicação poderá utilizar o servidor de base de dados para formatar os dados (por exemplo, converter em XML), manipular dados de cadeia ou realizar cálculos complexos.An application might use the database server to format data (for example, converting to XML), manipulate string data, or perform complex calculations.
  • Os programadores tentam escrever consultas cujos resultados podem ser apresentados diretamente aos utilizadores.Developers try to write queries whose results can be displayed directly to users. Por exemplo, uma consulta pode combinar campos ou formatar datas, horas e a moeda, de acordo com a região.For example, a query might combine fields or format dates, times, and currency according to locale.
  • Os programadores estão a tentar corrigir o anti-padrão Obtenção Externa ao enviar cálculos para a base de dados.Developers are trying to correct the Extraneous Fetching antipattern by pushing computations to the database.
  • Os procedimentos armazenados servem para encapsular a lógica de negócio, talvez porque sejam considerados mais fáceis de manter e atualizar.Stored procedures are used to encapsulate business logic, perhaps because they are considered easier to maintain and update.

O exemplo seguinte obtém as 20 ordens mais valiosas para um território de venda especificado e formata os resultados como XML.The following example retrieves the 20 most valuable orders for a specified sales territory and formats the results as XML. Utiliza as funções do Transact-SQL para analisar os dados e converter os resultados em XML.It uses Transact-SQL functions to parse the data and convert the results to XML. Pode encontrar o exemplo completo aqui.You can find the complete sample here.

SELECT TOP 20
  soh.[SalesOrderNumber]  AS '@OrderNumber',
  soh.[Status]            AS '@Status',
  soh.[ShipDate]          AS '@ShipDate',
  YEAR(soh.[OrderDate])   AS '@OrderDateYear',
  MONTH(soh.[OrderDate])  AS '@OrderDateMonth',
  soh.[DueDate]           AS '@DueDate',
  FORMAT(ROUND(soh.[SubTotal],2),'C')
                          AS '@SubTotal',
  FORMAT(ROUND(soh.[TaxAmt],2),'C')
                          AS '@TaxAmt',
  FORMAT(ROUND(soh.[TotalDue],2),'C')
                          AS '@TotalDue',
  CASE WHEN soh.[TotalDue] > 5000 THEN 'Y' ELSE 'N' END
                          AS '@ReviewRequired',
  (
  SELECT
    c.[AccountNumber]     AS '@AccountNumber',
    UPPER(LTRIM(RTRIM(REPLACE(
    CONCAT( p.[Title], ' ', p.[FirstName], ' ', p.[MiddleName], ' ', p.[LastName], ' ', p.[Suffix]),
    '  ', ' '))))         AS '@FullName'
  FROM [Sales].[Customer] c
    INNER JOIN [Person].[Person] p
  ON c.[PersonID] = p.[BusinessEntityID]
  WHERE c.[CustomerID] = soh.[CustomerID]
  FOR XML PATH ('Customer'), TYPE
  ),

  (
  SELECT
    sod.[OrderQty]      AS '@Quantity',
    FORMAT(sod.[UnitPrice],'C')
                        AS '@UnitPrice',
    FORMAT(ROUND(sod.[LineTotal],2),'C')
                        AS '@LineTotal',
    sod.[ProductID]     AS '@ProductId',
    CASE WHEN (sod.[ProductID] >= 710) AND (sod.[ProductID] <= 720) AND (sod.[OrderQty] >= 5) THEN 'Y' ELSE 'N' END
                        AS '@InventoryCheckRequired'

  FROM [Sales].[SalesOrderDetail] sod
  WHERE sod.[SalesOrderID] = soh.[SalesOrderID]
  ORDER BY sod.[SalesOrderDetailID]
  FOR XML PATH ('LineItem'), TYPE, ROOT('OrderLineItems')
  )

FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[TerritoryId] = @TerritoryId
ORDER BY soh.[TotalDue] DESC
FOR XML PATH ('Order'), ROOT('Orders')

Claramente, esta consulta é complexa.Clearly, this is complex query. Como iremos ver mais tarde, acaba por utilizar recursos de processamento significativos no servidor da base de dados.As we'll see later, it turns out to use significant processing resources on the database server.

Como resolver o problemaHow to fix the problem

Mova o processamento do servidor de base de dados para outros escalões de aplicação.Move processing from the database server into other application tiers. Idealmente, deve limitar a base de dados para realizar operações de acesso de dados, ao utilizar apenas as capacidades que a base de dados está otimizada, como a agregação num RDBMS.Ideally, you should limit the database to performing data access operations, using only the capabilities that the database is optimized for, such as aggregation in an RDBMS.

Por exemplo, o código anterior do Transact-SQL pode ser substituído por uma instrução que obtém apenas os dados a serem processados.For example, the previous Transact-SQL code can be replaced with a statement that simply retrieves the data to be processed.

SELECT
soh.[SalesOrderNumber]  AS [OrderNumber],
soh.[Status]            AS [Status],
soh.[OrderDate]         AS [OrderDate],
soh.[DueDate]           AS [DueDate],
soh.[ShipDate]          AS [ShipDate],
soh.[SubTotal]          AS [SubTotal],
soh.[TaxAmt]            AS [TaxAmt],
soh.[TotalDue]          AS [TotalDue],
c.[AccountNumber]       AS [AccountNumber],
p.[Title]               AS [CustomerTitle],
p.[FirstName]           AS [CustomerFirstName],
p.[MiddleName]          AS [CustomerMiddleName],
p.[LastName]            AS [CustomerLastName],
p.[Suffix]              AS [CustomerSuffix],
sod.[OrderQty]          AS [Quantity],
sod.[UnitPrice]         AS [UnitPrice],
sod.[LineTotal]         AS [LineTotal],
sod.[ProductID]         AS [ProductId]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[Customer] c ON soh.[CustomerID] = c.[CustomerID]
INNER JOIN [Person].[Person] p ON c.[PersonID] = p.[BusinessEntityID]
INNER JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[TerritoryId] = @TerritoryId
AND soh.[SalesOrderId] IN (
    SELECT TOP 20 SalesOrderId
    FROM [Sales].[SalesOrderHeader] soh
    WHERE soh.[TerritoryId] = @TerritoryId
    ORDER BY soh.[TotalDue] DESC)
ORDER BY soh.[TotalDue] DESC, sod.[SalesOrderDetailID]

A aplicação utiliza então APIs System.Xml.Linq do .NET Framework para formatar os resultados como XML.The application then uses the .NET Framework System.Xml.Linq APIs to format the results as XML.

// Create a new SqlCommand to run the Transact-SQL query
using (var command = new SqlCommand(...))
{
    command.Parameters.AddWithValue("@TerritoryId", id);

    // Run the query and create the initial XML document
    using (var reader = await command.ExecuteReaderAsync())
    {
        var lastOrderNumber = string.Empty;
        var doc = new XDocument();
        var orders = new XElement("Orders");
        doc.Add(orders);

        XElement lineItems = null;
        // Fetch each row in turn, format the results as XML, and add them to the XML document
        while (await reader.ReadAsync())
        {
            var orderNumber = reader["OrderNumber"].ToString();
            if (orderNumber != lastOrderNumber)
            {
                lastOrderNumber = orderNumber;

                var order = new XElement("Order");
                orders.Add(order);
                var customer = new XElement("Customer");
                lineItems = new XElement("OrderLineItems");
                order.Add(customer, lineItems);

                var orderDate = (DateTime)reader["OrderDate"];
                var totalDue = (Decimal)reader["TotalDue"];
                var reviewRequired = totalDue > 5000 ? 'Y' : 'N';

                order.Add(
                    new XAttribute("OrderNumber", orderNumber),
                    new XAttribute("Status", reader["Status"]),
                    new XAttribute("ShipDate", reader["ShipDate"]),
                    ... // More attributes, not shown.

                    var fullName = string.Join(" ",
                        reader["CustomerTitle"],
                        reader["CustomerFirstName"],
                        reader["CustomerMiddleName"],
                        reader["CustomerLastName"],
                        reader["CustomerSuffix"]
                    )
                   .Replace("  ", " ") //remove double spaces
                   .Trim()
                   .ToUpper();

               customer.Add(
                    new XAttribute("AccountNumber", reader["AccountNumber"]),
                    new XAttribute("FullName", fullName));
            }

            var productId = (int)reader["ProductID"];
            var quantity = (short)reader["Quantity"];
            var inventoryCheckRequired = (productId >= 710 && productId <= 720 && quantity >= 5) ? 'Y' : 'N';

            lineItems.Add(
                new XElement("LineItem",
                    new XAttribute("Quantity", quantity),
                    new XAttribute("UnitPrice", ((Decimal)reader["UnitPrice"]).ToString("C")),
                    new XAttribute("LineTotal", RoundAndFormat(reader["LineTotal"])),
                    new XAttribute("ProductId", productId),
                    new XAttribute("InventoryCheckRequired", inventoryCheckRequired)
                ));
        }
        // Match the exact formatting of the XML returned from SQL
        var xml = doc
            .ToString(SaveOptions.DisableFormatting)
            .Replace(" />", "/>");
    }
}

Nota

Este código é um pouco complexo.This code is somewhat complex. Para uma nova aplicação, poderá preferir utilizar uma biblioteca de serialização.For a new application, you might prefer to use a serialization library. No entanto, o pressuposto é de que a equipa de desenvolvimento está a refatorizar uma aplicação existente, pelo que o método tem de devolver o mesmo formato exato do código original.However, the assumption here is that the development team is refactoring an existing application, so the method needs to return the exact same format as the original code.

ConsideraçõesConsiderations

  • Muitos sistemas de base de dados são altamente otimizados para realizar determinados tipos de processamento de dados, como calcular valores agregados ao longo de grandes conjuntos de dados.Many database systems are highly optimized to perform certain types of data processing, such as calculating aggregate values over large datasets. Não mova esses tipos de processamento para fora da base de dados.Don't move those types of processing out of the database.

  • Não altere a localização do processamento, uma vez que se o fizer a base de dados irá transferir muitos mais dados através da rede.Do not relocate processing if doing so causes the database to transfer far more data over the network. Veja o Extraneous Fetching antipattern (Anti-padrão de Obtenção Externa).See the Extraneous Fetching antipattern.

  • Se mover o processamento para uma camada de aplicação, essa camada poderá ter de aumentar horizontalmente para lidar com o trabalho adicional.If you move processing to an application tier, that tier may need to scale out to handle the additional work.

Como detetar o problemaHow to detect the problem

Os sintomas de uma base de dados ocupada incluem um declínio desproporcional nos tempos de resposta e no débito em operações que acedem à base de dados.Symptoms of a busy database include a disproportionate decline in throughput and response times in operations that access the database.

Pode realizar os passos seguintes para ajudar a identificar este problema:You can perform the following steps to help identify this problem:

  1. Utilize a monitorização de desempenho para identificar quanto tempo o sistema de produção passa a realizar a atividade de base de dados.Use performance monitoring to identify how much time the production system spends performing database activity.

  2. Examine o trabalho realizado pela base de dados durante estes períodos.Examine the work performed by the database during these periods.

  3. Se suspeitar que operações específicas possam causar demasiada atividade da base de dados, execute um teste de carga num ambiente controlado.If you suspect that particular operations might cause too much database activity, perform load testing in a controlled environment. Cada teste deve executar uma combinação das operações suspeitas com uma carga de variável de utilizador.Each test should run a mixture of the suspect operations with a variable user load. Examine a telemetria dos testes de carga para observar a forma como a base de dados é utilizada.Examine the telemetry from the load tests to observe how the database is used.

  4. Se a atividade da base de dados revelar processamento significativo, mas pouco tráfego de dados, reveja o código de origem para determinar se o processamento pode ser melhor realizado noutro local.If the database activity reveals significant processing but little data traffic, review the source code to determine whether the processing can better be performed elsewhere.

Se o volume de atividades de base de dados for baixo ou os tempos de resposta forem relativamente rápidos, então é pouco provável que uma base de dados ocupada tenha um problema de desempenho.If the volume of database activity is low or response times are relatively fast, then a busy database is unlikely to be a performance problem.

Diagnóstico de exemploExample diagnosis

As secções seguintes aplicam estes passos para o exemplo de aplicação descrito anteriormente.The following sections apply these steps to the sample application described earlier.

Monitorizar o volume de atividades de base de dadosMonitor the volume of database activity

O gráfico seguinte mostra os resultados da execução de um teste de carga de um exemplo de aplicação, através de uma carga de passos de até 50 utilizadores em simultâneo.The following graph shows the results of running a load test against the sample application, using a step load of up to 50 concurrent users. O volume de pedidos atinge rapidamente um limite e permanece nesse nível, enquanto o tempo de resposta médio aumenta constantemente.The volume of requests quickly reaches a limit and stays at that level, while the average response time steadily increases. Uma escala logarítmica é utilizada para essas duas métricas.A logarithmic scale is used for those two metrics.

Resultados de teste de carga para realizar o processamento na base de dados

O gráfico seguinte mostra a utilização da CPU e de DTUs como uma percentagem da quota de serviço.The next graph shows CPU utilization and DTUs as a percentage of service quota. As DTUs indicam uma medida da quantidade processamento realizada pela base de dados.DTUs provide a measure of how much processing the database performs. O gráfico mostra que tanto a utilização da CPU, como da DTU, atingiram rapidamente 100%.The graph shows that CPU and DTU utilization both quickly reached 100%.

O monitor da Base de Dados SQL do Azure mostra o desempenho da base de dados ao realizar o processamento

Examinar o trabalho realizado pela base de dadosExamine the work performed by the database

É possível que as tarefas executadas pela base de dados sejam operações de acesso de dados genuínas, em vez de processamento, pelo que é importante compreender as instruções SQL que estão a ser executadas enquanto a base de dados está ocupada.It could be that the tasks performed by the database are genuine data access operations, rather than processing, so it is important to understand the SQL statements being run while the database is busy. Monitorize o sistema para capturar o tráfego SQL e correlacione as operações SQL com pedidos de aplicações.Monitor the system to capture the SQL traffic and correlate the SQL operations with application requests.

Se as operações da base de dados são puramente operações de acesso de dados, sem uma grande quantidade de processamento, então o problema poderá ser Obtenção Externa.If the database operations are purely data access operations, without a lot of processing, then the problem might be Extraneous Fetching.

Implementar a solução e verificar o resultadoImplement the solution and verify the result

O gráfico seguinte mostra um teste de carga com o código atualizado.The following graph shows a load test using the updated code. O débito é significativamente superior, mais de 400 pedidos por segundo em relação aos 12 anteriores.Throughput is significantly higher, over 400 requests per second versus 12 earlier. O tempo médio de resposta também é muito inferior, imediatamente acima de 0,1 segundos, em comparação com mais de 4 segundos.The average response time is also much lower, just above 0.1 seconds compared to over 4 seconds.

Resultados de teste de carga para realizar o processamento na base de dados

A utilização da CPU e da DTU mostra que o sistema demorou mais tempo a alcançar a saturação, apesar do débito aumentado.CPU and DTU utilization shows that the system took longer to reach saturation, despite the increased throughput.

Monitor da Base de Dados SQL do Azure mostra o desempenho da base de dados ao realizar o processamento na aplicação do cliente