Guia de arquitetura de processamento de consultas

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O Mecanismo de Banco de Dados do SQL Server processa consultas em várias arquiteturas de armazenamento de dados, como tabelas locais, tabelas particionadas e tabelas distribuídas em vários servidores. As secções seguintes abordam como o Database Engine processa consultas e otimiza a reutilização de consultas através da cache do plano de execução.

Os exemplos de código neste artigo utilizam a AdventureWorks2025, AdventureWorksDW2025, ou AdventureWorksLT2025 base de dados de exemplos, que pode descarregar do repositório GitHub do Azure Data SQL Samples Repository.

Modos de execução

O Mecanismo de Banco de Dados do SQL Server pode processar instruções Transact-SQL (T-SQL) usando dois modos de processamento distintos:

• Execução em modo de linha
• Execução em modo de lote

Execução em modo de linha

A execução em modo linha é um método de processamento de consultas utilizado com tabelas e índices de rowstore, onde os dados são armazenados em formato de linha. Quando uma consulta é executada e acede a dados em tabelas e índices de rowstore, os operadores da árvore de execução e os operadores filhos leem cada linha necessária, em todas as colunas especificadas no esquema do objeto. De cada linha lida, o Database Engine recupera as colunas necessárias para o conjunto de resultados, conforme referenciadas por uma SELECT instrução, JOIN predicado ou predicado de filtro.

Observação

A execução em modo linha é muito eficiente para cenários OLTP, mas pode ser menos eficiente ao analisar grandes quantidades de dados, por exemplo em cenários de Data Warehousing.

Execução em modo de lote

A execução em modo batch é um método de processamento de consultas que processa várias linhas em conjunto (daí o termo batch). Cada coluna dentro de um lote é armazenada como um vetor em uma área separada da memória, portanto, o processamento em modo de lote é baseado em vetor. O processamento em modo lote também utiliza algoritmos otimizados para conjuntos avançados de instruções e aumento do débito de memória nos processadores modernos.

A execução em modo batch foi originalmente integrada e otimizada em torno do formato de armazenamento columnstore. A partir do SQL Server 2019 (15.x) e no Base de Dados SQL do Azure, a execução em modo batch não requer índices de column store. Para obter mais informações, consulte Modo em lote no rowstore.

O processamento em modo batch opera com dados comprimidos sempre que possível, eliminando o exchange operator que a execução em modo linha utiliza. O resultado é um melhor paralelismo e um desempenho mais rápido.

Quando uma consulta é executada em modo batch e acede a dados em índices de column store, os operadores da árvore de execução e os operadores filhos leem várias linhas em conjunto em segmentos de coluna. O Database Engine lê apenas as colunas necessárias para o resultado, conforme referenciadas por uma SELECT instrução, JOIN predicado ou predicado de filtro. Para mais informações sobre índices de columnstore, consulte Columnstore Index Architecture.

Observação

A execução em modo batch é muito eficiente para cenários de data warehousing, onde pode ler e agregar grandes quantidades de dados.

Processamento de instruções SQL

Processar uma única instrução T-SQL é a forma mais básica pela qual o Database Engine executa as instruções T-SQL. Os passos seguintes ilustram o processo básico ao processar uma única SELECT instrução SQL que apenas faz referência a tabelas base locais (sem vistas ou tabelas remotas).

Precedência do operador lógico

Quando se usam mais do que um operador lógico numa instrução, NOT é avaliado primeiro, depois AND, e finalmente OR. Operadores aritméticos e bit a bit são tratados antes dos operadores lógicos. Para mais informações, veja Precedência de operadores.

No exemplo seguinte, a condição de cor aplica-se ao modelo de produto 21, e não ao modelo de produto 20, porque AND tem precedência sobre OR.

SELECT ProductID,
       ProductModelID
FROM Production.Product
WHERE ProductModelID = 20
      OR ProductModelID = 21
         AND Color = 'Red';
GO

Usar parênteses, mesmo quando não são obrigatórios, pode melhorar a legibilidade das consultas e reduzir a probabilidade de cometer um erro devido à precedência do operador. O exemplo seguinte é mais legível do que o anterior e tem o mesmo significado.

SELECT ProductID,
       ProductModelID
FROM Production.Product
WHERE ProductModelID = 20
      OR (ProductModelID = 21
          AND Color = 'Red');
GO

Você pode alterar o significado da consulta adicionando parênteses para forçar a avaliação de OR primeiro. A consulta a seguir localiza apenas produtos nos modelos 20 e 21 que são vermelhos.

SELECT ProductID,
       ProductModelID
FROM Production.Product
WHERE (ProductModelID = 20
       OR ProductModelID = 21)
      AND Color = 'Red';
GO

Otimizar instruções SELECT em SQL

Uma SELECT instrução não é processual, não indica as etapas exatas que o servidor de banco de dados deve usar para recuperar os dados solicitados. Este comportamento declarativo significa que o servidor de base de dados deve analisar a instrução e a base de dados para determinar a forma mais eficiente de extrair os dados solicitados. Este processo é referido como otimização do plano de consulta. O componente que realiza esta tarefa é o Otimizador de Consultas. A entrada para o Otimizador de Consultas consiste na consulta, o esquema da base de dados (definições de objetos) e as estatísticas dos objetos. A saída do Otimizador de Consultas é um plano de execução de consultas, por vezes referido como plano de consulta ou plano de execução. O conteúdo de um plano de execução é descrito com mais detalhe em armazenamento em cache e reutilização do plano de execução.

O diagrama seguinte ilustra as entradas e saídas do Otimizador de Consultas durante a otimização de uma única SELECT instrução:

Uma SELECT instrução define apenas:

• O formato do conjunto de resultados. Isso é especificado principalmente na lista de seleção. No entanto, outras cláusulas, como ORDER BY e GROUP BY também afetam a forma final do conjunto de resultados.
• As tabelas que contêm os dados de origem. Isto é especificado na FROM cláusula.
• Como as tabelas estão logicamente relacionadas para os fins da SELECT declaração. Isto é definido na expressão join, que pode aparecer na cláusula WHERE ou numa cláusula ON a seguir a FROM.
• Condições que as linhas nas tabelas de origem devem satisfazer para qualificar a instrução SELECT. Estes são especificados nas cláusulas WHERE e HAVING.

Um plano de execução de consulta define:

• A sequência na qual as tabelas de origem são acessadas.

  Normalmente, o servidor de base de dados pode aceder às tabelas base em muitas sequências para construir o conjunto de resultados. Por exemplo, se a SELECT instrução fizer referência a três tabelas, o servidor de banco de dados poderá primeiro acessar TableA, usar os dados de TableA para extrair linhas correspondentes de TableBe, em seguida, usar os dados de TableB para extrair dados de TableC. As outras sequências nas quais o servidor de banco de dados pode acessar as tabelas são:

  TableC, TableB, TableA, ou
  TableB, TableA, TableC, ou
  TableB, TableC, TableA, ou
  TableC, TableA, TableB

• Os métodos usados para extrair dados de cada tabela.

  Geralmente, existem diferentes métodos para acessar os dados em cada tabela. Se forem necessárias apenas algumas linhas com valores de chave específicos, o servidor de base de dados pode rapidamente consultar linhas ou analisar um pequeno intervalo de índice. Se todas as linhas da tabela forem necessárias, o servidor da base de dados pode, em vez disso, analisar toda a tabela ou todo o índice. Se todas as linhas de uma tabela forem necessárias mas houver um índice cujas colunas-chave estão num ORDER BY, realizar uma varredura de índice em vez de uma varredura de tabela pode evitar uma ordenação separada do conjunto de resultados. Se uma tabela for muito pequena, varreduras completas podem ser o método mais eficiente para quase todo o acesso à tabela.

• Os métodos usados para realizar cálculos e como filtrar, agregar e classificar dados de cada tabela.

  À medida que os dados são acedidos a partir de tabelas, diferentes métodos realizam cálculos sobre dados, como o cálculo de valores escalares, e agregam e ordenam os dados conforme definido no texto da consulta. Por exemplo, ao usar uma cláusula GROUP BY ou ORDER BY cláusula, e como filtrar dados, por exemplo, ao usar uma cláusula WHERE ou HAVING cláusula.

O processo de seleção de um plano de execução entre muitos planos possíveis é conhecido como otimização. O Otimizador de Consultas é um dos componentes mais importantes do Mecanismo de Banco de Dados. Embora o Otimizador de Consultas use alguma sobrecarga para analisar a consulta e selecionar um plano, esta sobrecarga é normalmente poupada várias vezes quando o Otimizador de Consultas escolhe um plano de execução eficiente. Por exemplo, duas empresas de construção podem receber plantas idênticas para uma casa. Se uma empresa dedica alguns dias no início a planear como construir a casa, e a outra começa a construir sem planeamento, provavelmente é a empresa que dedica tempo a planear o projeto que acaba primeiro.

O Otimizador de Consultas é um otimizador baseado em custos. Cada plano de execução possível tem um custo associado em termos da quantidade de recursos informáticos utilizados. O Otimizador de Consultas deve analisar os planos possíveis e escolher aquele com o menor custo estimado. Algumas declarações complexas têm milhares de possíveis planos de execução. Nesses casos, o Otimizador de Consultas não analisa todas as combinações possíveis. Em vez disso, ele usa algoritmos complexos para encontrar um plano de execução que tenha um custo razoavelmente próximo do custo mínimo possível.

O Query Optimizer não escolhe necessariamente o plano de execução com o custo estimado mais baixo. Encontra um plano com um custo estimado razoável dentro do tempo disponível para otimização. Um plano de execução paralelo pode usar mais recursos do que um plano serial, reduzindo o tempo estimado decorrido.

O SQL Server Query Optimizer baseia-se em estatísticas ao estimar os custos de recursos de diferentes métodos para extrair informação de uma tabela ou índice. As estatísticas são mantidas para colunas e índices, e contêm informação sobre a densidade¹ dos dados subjacentes. Esta informação indica a seletividade dos valores num índice ou coluna em particular. Por exemplo, numa tabela que representa automóveis, muitos automóveis têm o mesmo fabricante, mas cada automóvel tem um número de identificação de veículo único (VIN). Um índice no VIN é mais seletivo do que um índice no fabricante, porque o VIN tem densidade menor do que o fabricante. Se as estatísticas de índice não forem atuais, o Otimizador de Consultas pode não fazer a melhor escolha para o estado atual da tabela. Para obter mais informações sobre densidades, consulte Estatísticas.

¹ A densidade define a distribuição de valores únicos que existem nos dados ou o número médio de valores duplicados para uma determinada coluna. À medida que a densidade diminui, a seletividade de um valor aumenta.

O Query Optimizer é importante porque permite ao servidor de base de dados ajustar-se dinamicamente às condições mutáveis da base de dados, sem necessidade de intervenção de um programador ou administrador de base de dados. Esta funcionalidade permite aos programadores focar-se em descrever o resultado final da consulta. Podem confiar no Otimizador de Consultas para construir um plano de execução eficiente para o estado da base de dados sempre que o plano de consulta para uma instrução for otimizado.

Observação

O SQL Server Management Studio tem três opções para exibir planos de execução:

• O Plano de Execução Estimado, que é o plano compilado, produzido pelo Query Optimizer.
• O Plano de Execução Real, que é o mesmo que o plano compilado mais o seu contexto de execução. Este contexto inclui informação em tempo de execução disponível após a conclusão da execução, como avisos de execução, ou em versões mais recentes do Database Engine, o tempo decorrido e o tempo de CPU utilizado durante a execução.
• As Estatísticas de Consulta ao Vivo são iguais ao plano compilado mais o seu contexto de execução. Este contexto inclui informação de execução durante o progresso da execução e é atualizado a cada segundo. A informação em tempo de execução inclui, por exemplo, o número real de linhas que passam pelos operadores.

Processar uma instrução SELECT

O Database Engine processa uma única SELECT instrução seguindo estes passos:

1. O analisador verifica a SELECT instrução e a divide em unidades lógicas, como palavras-chave, expressões, operadores e identificadores.
2. Uma árvore de consultas, por vezes referida como árvore de sequência, é construída para descrever os passos lógicos necessários para transformar os dados de origem no formato exigido pelo conjunto de resultados.
3. O Otimizador de Consultas analisa diferentes formas de aceder às tabelas de origem. Seleciona a série de passos que devolve os resultados mais rapidamente, utilizando menos recursos. Atualiza a árvore de consultas para registar exatamente esta série de passos. A versão final otimizada da árvore de consulta é chamada de plano de execução.
4. O motor relacional começa a executar o plano. À medida que processa passos que necessitam de dados das tabelas base, solicita os conjuntos de linhas necessários ao motor de armazenamento.
5. O mecanismo relacional processa os dados retornados do mecanismo de armazenamento no formato definido para o conjunto de resultados e retorna o conjunto de resultados para o cliente.

Dobragem constante e avaliação da expressão

O Mecanismo de Banco de Dados avalia algumas expressões constantes antecipadamente para melhorar o desempenho da consulta. Esta técnica de otimização utilizada pelo Otimizador de Consultas visa simplificar expressões em tempo de compilação em vez de em tempo de execução. Envolve a avaliação de expressões constantes durante a compilação da consulta para que o plano de execução resultante seja mais eficiente. Este processo é referido como constant folding. Uma constante é um literal T-SQL, como 3, 'ABC', '2025-12-31', 1.0e3, ou 0x12345678. Por exemplo, faça esta consulta:

SELECT *
FROM Orders
WHERE OrderDate < DATEADD(day, 30 * 12, '2025-01-01');

Aqui, 30 * 12 é uma expressão constante. O Database Engine avalia esta expressão durante a compilação e pode reescrever internamente a consulta como:

SELECT *
FROM Orders
WHERE OrderDate < DATEADD(day, 360, '2025-01-01');

Expressões dobráveis

O Database Engine utiliza folding constante com os seguintes tipos de expressões:

• Expressões aritméticas, como 1 + 1 e 5 / 3 * 2, que contêm apenas constantes.
• Expressões lógicas, como 1 = 1 e 1 > 2 AND 3 > 4, que contêm apenas constantes.
• Funções incorporadas que o Database Engine considera passíveis de folding, incluindo CAST e CONVERT. Geralmente, uma função intrínseca é redutível se for apenas função das suas entradas e não de outra informação contextual, como SET opções, definições de linguagem, opções da base de dados e chaves de encriptação. As funções não determinísticas não são dobráveis. As funções incorporadas determinísticas são dobráveis, com algumas exceções.
• Métodos determinísticos dos tipos CLR definidos pelo utilizador e das funções determinísticas do CLR definidas pelo utilizador com valores escalares. Para obter mais informações, consulte Dobramento constante para funções e métodos de User-Defined CLR.

Observação

Uma exceção é feita para tipos de objetos grandes. Se o tipo de saída do processo de folding for um tipo de objeto grande (text, ntext, image, nvarchar(max), varchar(max), varbinary(max), json ou xml), o Database Engine não efetua o folding da expressão.

Expressões não dobráveis

Todos os outros tipos de expressão não são dobráveis. Em particular, os seguintes tipos de expressões não são dobráveis:

• Expressões não constantes, como uma expressão cujo resultado depende do valor de uma coluna.
• Expressões cujos resultados dependem de uma variável ou parâmetro local, como @x.
• Funções não determinísticas.
• Funções T-SQL definidas pelo utilizador.
• Expressões cujos resultados dependem das configurações de idioma.
• Expressões cujos resultados dependem de SET opções.
• Expressões cujos resultados dependem das opções de configuração do servidor.

Exemplos de expressões constantes dobráveis e não dobráveis

Considere a seguinte consulta:

USE AdventureWorks2025;
GO

SELECT *
FROM Sales.SalesOrderHeader AS s
     INNER JOIN Sales.SalesOrderDetail AS d
         ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00;

Se a PARAMETERIZATION opção da base de dados não estiver definida para FORCED para esta consulta, a expressão 117.00 + 1000.00 é avaliada e substituída pelo seu resultado, 1117.00, antes da consulta ser compilada. Os benefícios deste constant folding incluem:

• A expressão não precisa ser avaliada repetidamente em tempo de execução.
• O valor da expressão após a sua avaliação é usado pelo Otimizador de Consultas para estimar o tamanho do conjunto de resultados da porção da consulta TotalDue > 117.00 + 1000.00.

Por outro lado, se dbo.f for uma função escalar definida pelo utilizador, a expressão dbo.f(100) não é simplificada, porque o Database Engine não simplifica expressões que envolvam funções definidas pelo utilizador, mesmo que sejam determinísticas. Para mais informações sobre parametrização, veja Parametrização forçada.

Avaliação da expressão

Além disso, algumas expressões que não são dobradas constantes, mas cujos argumentos são conhecidos em tempo de compilação, sejam os argumentos parâmetros ou constantes, são avaliadas pelo estimador de tamanho do conjunto de resultados (cardinalidade) que faz parte do otimizador durante a otimização.

Especificamente, as seguintes funções internas e operadores especiais são avaliados em tempo de compilação se todas as suas entradas forem conhecidas: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST, e CONVERT. Os seguintes operadores também são avaliados em tempo de compilação se todas as suas entradas forem conhecidas:

• Operadores aritméticos: +, -, *, /, unário -
• Operadores lógicos: AND, OR, NOT
• Operadores de comparação: <, >, <=, >=, LIKEIS NULL<>IS NOT NULL

Nenhuma outra função ou operador é avaliado pelo Otimizador de Consulta durante a estimativa de cardinalidade.

Exemplos de avaliação de expressão em tempo de compilação

Considere este procedimento armazenado:

USE AdventureWorks2025;
GO

CREATE PROCEDURE MyProc
(
    @d DATETIME
)
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d + 1;

Durante a otimização da instrução SELECT no procedimento, o Otimizador de Consulta tenta avaliar a cardinalidade estimada do conjunto de resultados da condição OrderDate > @d + 1. A expressão @d + 1 não é dobrada constantemente, porque @d é um parâmetro. No entanto, no momento da otimização, o valor do parâmetro é conhecido. Este valor permite ao Otimizador de Consultas estimar com precisão o tamanho do conjunto de resultados, o que lhe ajuda a selecionar um bom plano de consulta.

Agora considere um exemplo semelhante ao anterior, exceto que uma variável local @d2 substitui @d + 1 na consulta e a expressão é avaliada numa instrução SET em vez de na consulta.

USE AdventureWorks2025;
GO

CREATE PROCEDURE MyProc2
(
    @d DATETIME
)
AS
BEGIN
    DECLARE @d2 AS DATETIME;
    SET @d2 = @d + 1;
    SELECT COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate > @d2;
END

Quando a instrução SQL SELECT em MyProc2 é otimizada no Database Engine, o valor de @d2 não é conhecido. Portanto, o Otimizador de Consultas usa uma estimativa padrão para a seletividade de OrderDate > @d2 (neste caso, 30 por cento).

Processar outras declarações

Os passos básicos para processar uma SELECT instrução aplicam-se a outras instruções T-SQL como INSERT, UPDATE, e DELETE. Ambas as instruções UPDATE e DELETE devem direcionar o conjunto de linhas a modificar ou eliminar. O processo de identificação dessas linhas é o mesmo processo usado para identificar as linhas de origem que contribuem para o conjunto de resultados de uma SELECT instrução. Tanto as instruções UPDATE como INSERT podem conter instruções incorporadas SELECT que fornecem os valores de dados a atualizar ou inserir.

Mesmo as instruções da Linguagem de Definição de Dados (DDL), como CREATE PROCEDURE ou ALTER TABLE, resolvem-se para uma série de operações relacionais nas tabelas de catálogo do sistema e, por vezes, (como ALTER TABLE ADD COLUMN) contra as tabelas de dados.

Mesas de trabalho

O motor relacional pode precisar de construir uma tabela de trabalho para realizar uma operação lógica especificada numa instrução T-SQL. As tabelas de trabalho são tabelas internas que contêm resultados intermédios. As tabelas de trabalho são geradas para determinadas consultas de GROUP BY, ORDER BY ou UNION. Por exemplo, se uma cláusula ORDER BY faz referência a colunas que não são cobertas por quaisquer índices, o motor relacional pode precisar de gerar uma tabela de trabalho para ordenar o conjunto de resultados na ordem solicitada. Às vezes, as tabelas de trabalho também são usadas como spools que armazenam temporariamente o resultado da execução de uma parte de um plano de consulta. As mesas de trabalho são criadas em tempdb e depois são retiradas automaticamente quando deixam de ser necessárias.

Ver resolução

O processador de consultas do Database Engine trata as vistas indexadas e não indexadas de forma diferente:

• A base de dados armazena as linhas de uma vista indexada no mesmo formato que uma tabela. Se o Otimizador de Consultas decidir usar uma vista indexada num plano de consulta, trata a vista indexada da mesma forma que uma tabela base.
• Somente a definição de um modo de exibição não indexado é armazenada, não as linhas do modo de exibição. O Query Optimizer incorpora a lógica da definição da vista no plano de execução que constrói para a instrução T-SQL que faz referência à vista não indexada.

A lógica que o Otimizador de Consultas usa para decidir quando usar uma vista indexada é semelhante à lógica que usa para decidir quando usar um índice numa tabela. Se os dados na vista indexada cobrirem total ou parte da instrução T-SQL, e o Otimizador de Consultas determinar que um índice na vista é o caminho de acesso de baixo custo, escolhe o índice independentemente de a consulta referenciar a vista pelo nome.

Quando uma instrução T-SQL faz referência a uma vista não indexada, o analisador e o Otimizador de Consultas analisam a origem tanto da instrução T-SQL como da vista e depois resolvem-nas num único plano de execução. A instrução T-SQL e a view não têm planos separados.

Por exemplo, considere a seguinte visualização:

USE AdventureWorks2025;
GO

CREATE VIEW EmployeeName AS
    SELECT h.BusinessEntityID,
           p.LastName,
           p.FirstName
    FROM HumanResources.Employee AS h
         INNER JOIN Person.Person AS p
             ON h.BusinessEntityID = p.BusinessEntityID;
GO

Com base nesta perspetiva, ambas estas instruções T-SQL realizam as mesmas operações nas tabelas base e produzem os mesmos resultados:

USE AdventureWorks2025;
GO

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName,
       SalesOrderID,
       OrderDate
FROM Sales.SalesOrderHeader AS soh
     INNER JOIN dbo.EmployeeName AS EmpN
         ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate > '20230531';

/* SELECT referencing the Person and Employee tables directly. */
SELECT LastName AS EmployeeLastName,
       SalesOrderID,
       OrderDate
FROM HumanResources.Employee AS e
     INNER JOIN Sales.SalesOrderHeader AS soh
         ON soh.SalesPersonID = e.BusinessEntityID
     INNER JOIN Person.Person AS p
         ON e.BusinessEntityID = p.BusinessEntityID
WHERE OrderDate > '20230531';

A funcionalidade Showplan do SQL Server Management Studio (SSMS) mostra que o motor relacional constrói o mesmo plano de execução para ambas estas SELECT sentenças.

Usar sugestões com modos de exibição

Se colocar sugestões nas vistas numa consulta, podem entrar em conflito com outras sugestões descobertas quando a vista expande para aceder às suas tabelas base. Quando este conflito ocorre, a consulta devolve um erro. Por exemplo, considere a seguinte vista que contém uma dica de tabela na sua definição:

USE AdventureWorks2025;
GO

CREATE VIEW Person.AddrState
WITH SCHEMABINDING AS
    SELECT a.AddressID,
           a.AddressLine1,
           s.StateProvinceCode,
           s.CountryRegionCode
    FROM Person.Address AS a WITH (NOLOCK), Person.StateProvince AS s
    WHERE a.StateProvinceID = s.StateProvinceID;

Agora suponha que você insira esta consulta:

SELECT AddressID,
       AddressLine1,
       StateProvinceCode,
       CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

A consulta falha, porque a SERIALIZABLE dica na Person.AddrState vista propaga-se tanto para as tabelas Person.Address e Person.StateProvince na vista quando é expandida. No entanto, expandir a perspetiva também revela a sugestão NOLOCK sobre Person.Address. Como as dicas SERIALIZABLE e NOLOCK entram em conflito, a consulta resultante está incorreta.

As dicas da tabela PAGLOCK, NOLOCK, ROWLOCK, TABLOCK e TABLOCKX entram em conflito entre si, assim como as dicas da tabela HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD e SERIALIZABLE.

As sugestões podem propagar-se por níveis de vistas aninhadas. Por exemplo, suponha que uma consulta aplique a dica HOLDLOCK numa vista v1. Quando v1 é expandido, descobre-se que essa vista v2 faz parte da sua definição. v2A definição inclui uma dica NOLOCK em uma de suas tabelas base. Mas esta tabela também herda a dica HOLDLOCK da consulta na exibição v1. Como as dicas NOLOCK e HOLDLOCK entram em conflito, a consulta falha.

Quando se usa a FORCE ORDER dica numa consulta que contém uma vista, a posição da vista no construto ordenado determina a ordem de junção das tabelas dentro da vista. Por exemplo, a consulta a seguir seleciona entre três tabelas e um modo de exibição:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER);

E View1 é definido conforme mostrado no seguinte exemplo:

CREATE VIEW View1 AS
    SELECT Colx,
           Coly
    FROM TableA, TableB
    WHERE TableA.ColZ = TableB.Colz;

A ordem de junção no plano de consulta é Table1, Table2, , TableATableB, Table3.

Resolver índices em visualizações

Como em qualquer índice, o Database Engine escolhe usar uma vista indexada no seu plano de consulta apenas se o Query Optimizer considerar que é benéfico fazê-lo.

Pode criar vistas indexadas em qualquer edição do Database Engine. Em algumas edições, o Query Optimizer considera automaticamente a vista indexada com índice, enquanto noutras edições é necessário usar a NOEXPAND dica da tabela. O uso automático de uma vista indexada pelo Query Optimizer é suportado apenas em edições específicas do SQL Server.

Observação

O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure oferecem suporte ao uso automático de modos de exibição indexados sem especificar a dica NOEXPAND.

O Otimizador de Consultas utiliza uma vista indexada quando as seguintes condições são cumpridas:

• Estas opções de sessão estão definidas como ON:
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER
• A NUMERIC_ROUNDABORT opção de sessão está definida para OFF.
• O Otimizador de Consultas encontra uma correspondência entre as colunas do índice da vista e os elementos da consulta, tais como:
  • Predicados de condição de pesquisa na WHERE cláusula
  • Operações de combinação
  • Funções agregadas
  • GROUP BY cláusulas
  • Referências de tabelas
• O custo estimado para usar o índice tem o menor custo de todos os mecanismos de acesso considerados pelo Otimizador de Consulta.
• Cada tabela referenciada na consulta (diretamente ou expandindo uma exibição para acessar suas tabelas subjacentes) que corresponde a uma referência de tabela na exibição indexada deve ter o mesmo conjunto de dicas aplicadas nela na consulta.

Observação

As READCOMMITTED sugestões e as READCOMMITTEDLOCK são sempre consideradas sugestões diferentes neste contexto, independentemente do nível de isolamento da transação atual.

Além dos requisitos para as SET opções e dicas de tabela, essas são as mesmas regras que o Otimizador de Consulta usa para determinar se um índice de tabela cobre uma consulta. Não precisas de especificar mais nada na consulta para que uma vista indexada seja utilizada.

Uma consulta não precisa fazer referência explícita a um modo de exibição indexado FROM na cláusula para que o Otimizador de Consulta use o modo de exibição indexado. Se a consulta contiver referências a colunas nas tabelas base que também estão presentes no modo de exibição indexado e o Otimizador de Consulta estimar que o uso do modo de exibição indexado fornece o mecanismo de acesso de menor custo, o Otimizador de Consulta escolhe o modo de exibição indexado, semelhante à maneira como escolhe índices de tabela base quando eles não são diretamente referenciados em uma consulta. O Otimizador de Consultas pode escolher o modo de exibição quando ele contém colunas que não são referenciadas pela consulta, desde que o modo de exibição ofereça a opção de menor custo para cobrir uma ou mais das colunas especificadas na consulta.

O Otimizador de Consultas trata uma vista indexada referenciada na cláusula FROM como uma vista padrão. O Otimizador de Consulta expande a definição da exibição para a consulta no início do processo de otimização. Em seguida, é feita a correspondência de vistas indexadas. A exibição indexada pode ser usada no plano de execução final selecionado pelo Otimizador de Consulta ou, em vez disso, o plano pode materializar os dados necessários da exibição acessando as tabelas base referenciadas pela exibição. O Otimizador de Consultas escolhe a alternativa de menor custo.

Usar sugestões com vistas indexadas

Você pode impedir que índices de exibição sejam usados para uma consulta usando a dica EXPAND VIEWS de consulta ou pode usar a dica NOEXPAND de tabela para forçar o uso de um índice para uma exibição indexada especificada na FROM cláusula de uma consulta. No entanto, você deve permitir que o Otimizador de Consultas determine dinamicamente os melhores métodos de acesso a serem usados para cada consulta. Limite o uso de EXPAND e NOEXPAND para casos específicos em que os testes mostraram que eles melhoram significativamente o desempenho.

• A EXPAND VIEWS opção especifica que o Otimizador de Consulta não use nenhum índice de exibição para toda a consulta.

• Quando especifica NOEXPAND para uma vista, o Otimizador de Consultas considera usar quaisquer índices definidos na vista. NOEXPAND especificada com a cláusula opcional INDEX() força o Otimizador de Consulta a usar os índices especificados. Podes especificar NOEXPAND apenas para uma vista indexada e não podes especificá-la para uma vista que não está indexada. O uso automático de uma vista indexada pelo Query Optimizer é suportado apenas em edições específicas do SQL Server.

  Observação

  O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure oferecem suporte ao uso automático de modos de exibição indexados sem especificar a dica NOEXPAND.

Quando nem NOEXPAND nem EXPAND VIEWS é especificado em uma consulta que contém um modo de exibição, o modo de exibição é expandido para acessar tabelas subjacentes. Se a consulta que compõe a vista contiver indicações de tabela, estas indicações serão propagadas para as tabelas subjacentes. (Este processo é explicado com mais detalhe em Resolução de vistas.) Desde que as dicas existentes nas tabelas subjacentes da vista sejam idênticas entre si, a consulta pode ser emparelhada com uma vista indexada. Na maioria das vezes, estas dicas coincidem entre si, porque são herdadas diretamente da view. No entanto, se a consulta fizer referência a tabelas em vez de modos de exibição e as dicas aplicadas diretamente nessas tabelas não forem idênticas, essa consulta não será qualificada para correspondência com um modo de exibição indexado. Se as dicas INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, ou XLOCK se aplicarem às tabelas referenciadas na consulta após a expansão da vista, a consulta não será elegível para correspondência de vista indexada.

Se um indício de tabela na forma de INDEX ( index_val [ ,...n] ) referenciar uma vista em uma consulta e você também não especificar o indício de NOEXPAND, o indício de índice será ignorado. Para especificar o uso de um determinado índice, use NOEXPAND.

Geralmente, quando o Otimizador de Consultas associa uma vista indexada a uma consulta, aplica quaisquer sugestões especificadas nas tabelas ou vistas da consulta diretamente à vista indexada. Se o Otimizador de Consultas optar por não usar uma vista indexada, propaga quaisquer dicas diretamente para as tabelas referenciadas na vista. Para mais informações, consulte Ver resolução. Essa propagação não se aplica a dicas de junção. Eles são aplicados apenas em sua posição original na consulta. O Otimizador de Consultas não considera dicas de junção ao associar consultas a vistas indexadas. Se um plano de consulta usar uma vista indexada que corresponda a parte de uma consulta que contenha uma dica de junção, a dica de junção não será usada no plano.

Dicas não são permitidas nas definições de exibições indexadas. O Motor de Base de Dados ignora sugestões dentro das definições de vistas indexadas ao mantê-las, ou ao executar consultas que utilizam vistas indexadas.

Para obter mais informações, consulte Table Hints.

Resolver vistas particionadas distribuídas

O processador de consultas do Database Engine otimiza o desempenho das vistas particionadas distribuídas. O aspeto mais importante do desempenho da exibição particionada distribuída é minimizar a quantidade de dados transferidos entre servidores membros.

O Database Engine constrói planos inteligentes e dinâmicos que fazem uso eficiente de consultas distribuídas para aceder a dados de tabelas remotas dos membros:

• O Processador de Consultas primeiro usa o OLE DB para recuperar as definições de restrição de verificação de cada tabela membro. Este método permite ao processador de consultas mapear a distribuição dos valores-chave entre as tabelas membros.
• O Processador de Consultas compara os intervalos de chaves especificados numa cláusula de uma instrução T-SQL WHERE com o mapa que mostra como as linhas estão distribuídas nas tabelas membros. O processador de consultas constrói então um plano de execução de consultas que utiliza consultas distribuídas para recuperar apenas as linhas remotas necessárias para completar a instrução T-SQL. O plano de execução é também construído de forma a que qualquer acesso a tabelas de membros remotos, seja para dados ou metadados, seja adiado até que a informação seja necessária.

Por exemplo, considere um sistema em que uma Customers tabela é particionada entre Server1 (CustomerID de 1 a 3299999), Server2 (CustomerID de 3300000 a 6599999) e Server3 (CustomerID de 6600000 a 9999999).

Considere o plano de execução criado para esta consulta executada no Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

O plano de execução para esta consulta extrai as linhas com CustomerID valores de chave de 3200000 até 3299999 da tabela de membros local e emite uma consulta distribuída para recuperar as linhas com valores de chave de 3300000 a 3400000 do Server2.

O Database Engine Query Processor pode também incorporar lógica dinâmica nos planos de execução de consultas para instruções T-SQL em que os valores-chave não são conhecidos quando o plano deve ser construído. Por exemplo, considere este procedimento armazenado:

CREATE PROCEDURE GetCustomer
(
    @CustomerIDParameter INT
)
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

O Database Engine não consegue prever que valor-chave é fornecido pelo @CustomerIDParameter parâmetro sempre que o procedimento é executado. Como o valor da chave não pode ser previsto, o processador de consultas também não consegue prever a que tabela membro tem de aceder. Para lidar com este caso, o Database Engine constrói um plano de execução que tem lógica condicional, referida como filtros dinâmicos, para controlar a que tabela membro se acede, com base no valor do parâmetro de entrada. Assumindo que o GetCustomer procedimento armazenado foi executado no Server1, a lógica do plano de execução pode ser representada como mostrado na seguinte saída:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

O Database Engine por vezes constrói este tipo de planos de execução dinâmicos mesmo para consultas que não são parametrizadas. O Otimizador de Consulta pode parametrizar uma consulta para que o plano de execução possa ser reutilizado. Se o Otimizador de Consultas parametrizar uma consulta que faz referência a uma vista particionada, o Otimizador de Consultas já não pode assumir que as linhas necessárias provêm de uma tabela base especificada. Depois, tem de usar filtros dinâmicos no plano de execução.

Procedimento armazenado e execução de gatilho

O Database Engine armazena apenas a fonte para procedimentos armazenados e triggers. Quando um procedimento armazenado ou gatilho é executado pela primeira vez, a fonte é compilada em um plano de execução. Se o procedimento armazenado ou gatilho for executado novamente, antes que o plano de execução seja retirado da memória, o mecanismo relacional detetará o plano existente e o reutilizará. Se o plano for removido da memória, é construído um novo plano. Este processo é semelhante ao que o Database Engine segue para todas as instruções T-SQL. A principal vantagem de desempenho que os procedimentos armazenados e triggers têm no Database Engine em comparação com lotes de T-SQL dinâmico é que as suas instruções T-SQL são sempre as mesmas. Portanto, o mecanismo relacional facilmente os combina com quaisquer planos de execução existentes. Os planos de procedimentos armazenados e triggers são facilmente reutilizados.

O plano de execução para procedimentos armazenados e gatilhos é executado separadamente do plano de execução para o conjunto de instruções que invoca o procedimento armazenado ou aciona o gatilho. Esta separação permite uma maior reutilização do procedimento armazenado e dos planos de execução do trigger.

Plano de execução, armazenamento em cache e reutilização

O Database Engine utiliza memória para armazenar tanto planos de execução como buffers de dados. A percentagem de memória alocada a planos de execução ou buffers de dados é ajustada dinamicamente, dependendo do estado do sistema. A parte da memória que armazena planos de execução chama-se cache de planos.

Repositórios da cache de planos

O cache de planos tem dois armazenamentos para todos os planos compilados:

• o arquivo de cache de Planos de Objetos (OBJCP), para planos relacionados com objetos persistentes, como procedimentos armazenados, funções e triggers.
• O armazenamento de cache SQL Plans (SQLCP) armazena planos relacionados com consultas autoparametrizadas, dinâmicas ou preparadas.

A consulta seguinte fornece informações sobre o uso de memória destes dois armazenamentos de cache:

SELECT *
FROM sys.dm_os_memory_clerks
WHERE name LIKE '%plans%';

O cache de planos tem dois repositórios adicionais que não armazenam planos:

• O armazenamento em cache Bound Trees (PHDR), para estruturas de dados usadas durante a compilação de planos para vistas, restrições e valores predefinidos. Estas estruturas são conhecidas como bound trees ou algebrizer trees.
• O armazenamento de cache de Procedimentos Armazenados Estendidos (XPROC), para procedimentos de sistema pré-definidos, como sp_executesql ou xp_cmdshell. Estes procedimentos armazenados usam uma DLL em vez de instruções T-SQL. A estrutura em cache contém apenas o nome da função e o nome da DLL onde o procedimento é implementado.

Componentes do plano de execução

Os planos de execução do Database Engine têm os seguintes componentes principais:

Plano compilado (ou plano de consulta)

O plano de consulta produzido pelo processo de compilação é principalmente uma estrutura de dados reentrante, somente leitura, usada por qualquer número de usuários. Ele armazena informações sobre:

• Operadores físicos que implementam a operação descrita pelos operadores lógicos.

• A ordem desses operadores, que determina a ordem na qual os dados são acessados, filtrados e agregados.

• O número estimado de linhas que passam através dos operadores.

  Observação

  O plano de consulta também armazena informações sobre os objetos estatísticos usados para a Estimativa de Cardinalidade.

• Quais objetos de suporte devem ser criados, como tabelas de trabalho ou arquivos de trabalho no tempdb.

O plano de consulta não armazena qualquer informação do contexto do utilizador ou de execução. O Database Engine determina o número de threads de trabalho cada vez que recupera um plano da cache do plano. Um plano paralelo em cache também pode correr em série.

Contexto de execução

Cada utilizador que atualmente executa a consulta tem uma estrutura de dados que contém os dados específicos para a sua execução, como os valores dos parâmetros. Esta estrutura de dados é chamada de contexto de execução. As estruturas de dados do contexto de execução são reutilizadas, mas seu conteúdo não. Se outro usuário executar a mesma consulta, as estruturas de dados serão reinicializadas com o contexto para o novo usuário.

Reutilizar planos para instruções idênticas

Quando o Database Engine executa qualquer instrução T-SQL, primeiro analisa a cache do plano para verificar se existe um plano de execução existente para a mesma instrução T-SQL. A instrução T-SQL qualifica-se como existente se corresponder literalmente a uma instrução T-SQL previamente executada com um plano em cache, carácter por carácter. O Database Engine reutiliza qualquer plano existente que encontre, poupando a sobrecarga de recompilar a instrução T-SQL. Se não existir plano de execução, o Database Engine gera um novo plano de execução para a consulta.

Observação

A cache do plano não mantém planos de execução para algumas instruções T-SQL, como instruções de operação em massa a correr em rowstore ou instruções que contêm literais de strings grandes. Estes planos existem apenas enquanto a consulta está a decorrer.

O Database Engine utiliza um algoritmo eficiente para encontrar quaisquer planos de execução existentes para qualquer instrução T-SQL específica. Na maioria dos sistemas, os recursos mínimos que esta varredura consome são inferiores aos recursos poupados ao reutilizar planos existentes em vez de compilar todas as instruções T-SQL.

Os algoritmos para associar novas instruções T-SQL a planos de execução existentes e não utilizados na cache do plano exigem que todas as referências a objetos sejam totalmente qualificadas. Por exemplo, assuma que Person é o esquema padrão para o utilizador que executa as seguintes SELECT instruções. Embora este exemplo não exija que qualifiques totalmente a tabela Person para ser executada, significa que a segunda instrução não corresponde a um plano existente, mas a terceira afirmação corresponde:

USE AdventureWorks2025;
GO

SELECT *
FROM Person;
GO

SELECT *
FROM Person.Person;
GO

SELECT *
FROM Person.Person;
GO

Alterar qualquer uma das seguintes SET opções para uma dada execução afeta a capacidade de reutilizar planos, porque o Database Engine realiza constant folding e estas opções afetam os resultados dessas expressões:

• ANSI_DEFAULTS
• ANSI_NULL_DFLT_OFF
• ANSI_NULL_DFLT_ON
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• DATEFIRST
• DATEFORMAT
• FORCEPLAN
• LANGUAGE
• NO_BROWSETABLE
• NUMERIC_ROUNDABORT
• QUOTED_IDENTIFIER

Armazenar em cache vários planos para a mesma consulta

As consultas e os planos de execução são identificáveis exclusivamente no Mecanismo de Banco de Dados, como uma impressão digital:

• O hash do plano de consulta é um valor de hash binário calculado no plano de execução de uma determinada consulta e usado para identificar exclusivamente planos de execução semelhantes.
• O hash da consulta é um valor de hash binário calculado no texto T-SQL de uma consulta, e é usado para identificar consultas de forma única.

Pode obter um plano compilado da cache do plano usando um identificador do plano. Este identificador transitório mantém-se constante apenas enquanto o plano permanece na cache. O identificador de plano é um valor de hash derivado do plano compilado de todo o lote. O handle do plano para um plano compilado mantém-se igual mesmo que uma ou mais instruções do lote sejam recompiladas.

Observação

Se compilar um plano para um lote em vez de uma única instrução, pode usar o handle do plano e os deslocamentos das instruções para obter o plano para as instruções individuais do lote. O sys.dm_exec_requests DMV contém as colunas statement_start_offset e statement_end_offset para cada registo. Estas colunas referem-se à instrução atualmente em execução de um lote ou objeto persistente atualmente em execução. Para mais informações, consulte sys.dm_exec_requests. O sys.dm_exec_query_stats DMV também contém estas colunas para cada registo. Estas colunas referem-se à posição de uma instrução dentro de um objeto em lote ou persistente. Para mais informações, consulte sys.dm_exec_query_stats.

O texto T-SQL real de um lote é armazenado num espaço de memória separado do cache do plano, chamado cache SQL Manager (SQLMGR). Pode recuperar o texto T-SQL de um plano compilado a partir da cache do SQL Manager usando um handle SQL. Este identificador transitório mantém-se constante apenas enquanto pelo menos um plano que o referencia permanece na cache do plano. O handle SQL é um valor hash derivado de todo o texto do lote, e é garantido que é único para cada batch.

Observação

Tal como um plano compilado, o texto T-SQL é armazenado por lote, incluindo os comentários. O handle SQL contém o hash MD5 de todo o texto do lote e é garantido que é único para cada lote.

A consulta seguinte fornece informações sobre o uso de memória para a cache SQL Manager:

SELECT *
FROM sys.dm_os_memory_objects
WHERE type = 'MEMOBJ_SQLMGR';

Existe uma relação de um-para-muitos entre um handle SQL e os handle de plano. Esta condição ocorre quando a chave de cache para os planos compilados é diferente. Esta diferença pode ocorrer devido à alteração das SET opções entre duas execuções do mesmo lote.

Considere o seguinte procedimento armazenado:

USE WideWorldImporters;
GO

CREATE PROCEDURE usp_SalesByCustomer
(
    @CID INT
)
AS
SELECT *
FROM Sales.Customers
WHERE CustomerID = @CID;
GO

SET ANSI_DEFAULTS ON;
GO

EXECUTE usp_SalesByCustomer 10;
GO

Verifique o que pode encontrar na cache do plano usando a seguinte consulta:

SELECT cp.memory_object_address,
       cp.objtype,
       refcounts,
       usecounts,
       qs.query_plan_hash,
       qs.query_hash,
       qs.plan_handle,
       qs.sql_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
     INNER JOIN sys.dm_exec_query_stats AS qs
         ON qs.plan_handle = cp.plan_handle
WHERE text LIKE '%usp_SalesByCustomer%';
GO

Aqui está o conjunto de resultados.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CC6C534060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Agora executa o procedimento armazenado com um parâmetro diferente, sem outras alterações ao contexto de execução:

EXECUTE usp_SalesByCustomer 8;
GO

Verifica novamente o que conseguires encontrar no cache do plano. Aqui está o conjunto de resultados.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Note que o usecounts valor aumenta para 2, o que significa que o mesmo plano em cache é reutilizado tal como está, porque as estruturas de dados do contexto de execução são reutilizadas. Agora altera a SET ANSI_DEFAULTS opção e executa o procedimento armazenado usando o mesmo parâmetro.

SET ANSI_DEFAULTS OFF;
GO

EXECUTE usp_SalesByCustomer 8;
GO

Verifica novamente o que conseguires encontrar no cache de planos. Aqui está o conjunto de resultados.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CD01DEC060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02B031F111CD01000001000000000000000000000000000000000000000000000000000000
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Repare que agora há duas entradas na saída do sys.dm_exec_cached_plans Detran:

• A usecounts coluna mostra o valor 1 no primeiro registo. Este valor representa o plano executado uma vez com SET ANSI_DEFAULTS OFF.
• A coluna usecounts mostra o valor 2 no segundo registo. Este valor representa o plano executado com SET ANSI_DEFAULTS ON, porque foi executado duas vezes.
• O diferente memory_object_address refere-se a uma entrada de plano de execução diferente no cache do plano. No entanto, o sql_handle valor é o mesmo para ambas as entradas porque se referem ao mesmo lote.
  • A execução com ANSI_DEFAULTS definido para OFF tem um novo plan_handle, e está disponível para reutilização para chamadas que tenham o mesmo conjunto de SET opções. O identificador do novo plano é necessário porque o contexto de execução foi reinicializado devido a opções SET alteradas. Mas essa alteração não desencadeia uma recompilação: ambas as entradas referem-se ao mesmo plano e consulta, como evidenciado pelos mesmos valores de query_plan_hash e query_hash.

Esta situação significa que tens duas entradas de plano na cache correspondentes ao mesmo lote. Isto sublinha a importância de garantir que as opções que afetam a cache do plano SET são as mesmas quando as mesmas consultas são executadas repetidamente. Esta abordagem otimiza a reutilização do plano e mantém o tamanho do cache do plano no mínimo exigido.

Sugestão

Uma armadilha comum é que diferentes clientes podem ter valores padrão diferentes para as SET opções. Por exemplo, uma ligação feita através do SQL Server Management Studio define QUOTED_IDENTIFIER automaticamente para ON, enquanto SQLCMD define QUOTED_IDENTIFIER para OFF. Executar as mesmas consultas a partir destes dois clientes resulta em múltiplos planos (conforme descrito no exemplo anterior).

Remover planos de execução do cache de planos

Os planos de execução permanecem no cache do plano enquanto houver memória suficiente para os armazenar. Quando existe pressão de memória, o Mecanismo de Banco de Dados do SQL Server utiliza uma abordagem baseada em custos para decidir quais os planos de execução a remover da cache do plano. Para tomar uma decisão baseada em custos, o Mecanismo de Banco de Dados do SQL Server aumenta e diminui uma variável de custo atual para cada plano de execução com base nos seguintes fatores.

Quando um processo de utilizador insere um plano de execução na cache, o processo de utilizador define o custo atual igual ao custo original de compilação da consulta. Para planos de execução ad hoc, o processo do utilizador define o custo corrente a zero. Depois disso, cada vez que um processo de utilizador referencia um plano de execução, reinicia o custo atual para o custo original de compilação. Para planos de execução ad hoc, o processo do utilizador aumenta o custo atual. Para todos os planos, o valor máximo para o custo atual é o custo de compilação original.

Quando existe pressão de memória, o Mecanismo de Banco de Dados do SQL Server remove planos de execução da cache de planos. Para determinar quais planos remover, o Mecanismo de Banco de Dados do SQL Server examina repetidamente o estado de cada plano de execução e remove os planos quando seu custo atual é zero. Um plano de execução com custo atual zero não é removido automaticamente quando existe pressão de memória. Só é removido quando o Mecanismo de Banco de Dados do SQL Server examina o plano e quando o custo atual é zero. Ao examinar um plano de execução, o Mecanismo de Banco de Dados do SQL Server empurra o custo atual para zero, diminuindo o custo atual se uma consulta não estiver usando o plano no momento.

O Mecanismo de Banco de Dados do SQL Server continua a examinar os planos de execução até remover planos suficientes para satisfazer os requisitos de memória. Embora exista pressão de memória, um plano de execução pode ter seu custo aumentado e diminuído mais de uma vez. Quando a pressão de memória deixa de existir, o Mecanismo de Banco de Dados do SQL Server deixa de diminuir o custo atual dos planos de execução não utilizados. Todos os planos de execução ficam no cache do plano, mesmo que o seu custo seja zero.

O Mecanismo de Banco de Dados do SQL Server usa o monitor de recursos e os threads de trabalho do usuário para liberar memória do cache de plano em resposta à pressão de memória. O monitor de recursos e as threads de trabalho do utilizador podem examinar simultaneamente planos para diminuir o custo atual de cada plano de execução não utilizado. O monitor de recursos remove os planos de execução da cache de planos quando existe pressão global de memória. Ele libera memória para impor políticas para memória do sistema, memória de processo, memória do pool de recursos e tamanho máximo para todos os caches.

O tamanho máximo para todos os caches é uma função do tamanho do pool de buffers e não pode exceder a memória máxima do servidor. Para mais informações sobre a configuração da memória máxima do servidor, consulte a definição max server memory em sp_configure.

Os threads de trabalho do usuário removem os planos de execução do cache do plano quando existe pressão de memória de cache único. Eles impõem políticas para o tamanho máximo de cache único e o máximo de entradas de cache único.

Os exemplos a seguir ilustram quais planos de execução são removidos do cache de planos:

• Um plano de execução é frequentemente referenciado para que o seu custo nunca vá a zero. O plano fica no cache do plano e não é removido a menos que haja pressão de memória e o custo atual seja zero.
• Um plano de execução ad hoc é inserido e não é referenciado novamente antes de existir uma sobrecarga de memória. Como os planos ad hoc são inicializados com um custo atual zero, quando o Mecanismo de Banco de Dados do SQL Server examina o plano de execução, vê o custo atual zero e remove o plano da cache do plano. O plano de execução ad hoc mantém-se na cache do plano com custo corrente zero quando a pressão de memória não existe.

Para remover manualmente um único plano ou todos os planos do cache, use DBCC FREEPROCCACHE. Também pode usar o DBCC FREESYSTEMCACHE para limpar qualquer cache, incluindo a cache do plano. No SQL Server 2016 (13.x) e versões posteriores, use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE para limpar a cache de planos de procedimentos da base de dados em contexto.

Uma alteração em algumas definições de configuração através da sp_configure e reconfigure também causa a remoção de planos do cache de planos. Você pode encontrar a lista dessas definições de configuração na seção Comentários do artigo DBCC FREEPROCCACHE . Quando faz esta alteração de configuração, a seguinte mensagem informativa é escrita no registo de erros:

SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Recompilar planos de execução

Certas alterações numa base de dados podem tornar um plano de execução ineficiente ou inválido. O Database Engine deteta estas alterações e marca o plano como não válido. Um novo plano deve ser recompilado para a próxima conexão que executa a consulta. As condições que invalidam um plano incluem:

• Alterações a uma tabela ou vista referenciadas pela consulta (ALTER TABLE e ALTER VIEW).
• Alterações a um único procedimento, o que eliminaria todos os planos desse procedimento da cache (ALTER PROCEDURE).
• Alterações a quaisquer índices utilizados pelo plano de execução.
• Atualizações das estatísticas usadas pelo plano de execução, seja explicitamente geradas por uma instrução como UPDATE STATISTICS ou geradas automaticamente.
• Descartando um índice usado pelo plano de execução.
• Uma chamada explícita para sp_recompile.
• Grande número de alterações em chaves (geradas por INSERT ou DELETE instruções de outros usuários que modificam uma tabela referenciada pela consulta).
• Para tabelas com gatilhos, caso o número de linhas nas tabelas de inserção ou eliminação aumente significativamente.
• Executar um procedimento armazenado usando a WITH RECOMPILE opção.

A maioria das recompilações é necessária quer para correção da instrução, quer para obter planos de execução de consultas potencialmente mais rápidos.

Quando uma instrução dentro de um lote desencadeia a recompilação, apenas essa instrução é recompilada, não todo o lote. Esta regra aplica-se quer o lote seja submetido através de um procedimento armazenado, trigger, batch ad hoc ou instrução preparada.

A recompilação ao nível da instrução beneficia o desempenho porque, na maioria dos casos, um pequeno número de instruções causa recompilações e as suas penalizações associadas, em termos de tempo de CPU e bloqueios. Estas penalizações são assim evitadas para as outras instruções do lote que não precisam de recompilação.

O sql_statement_recompile evento expandido (XEvent) comunica recompilações ao nível das instruções. Este XEvent ocorre quando uma recompilação ao nível de declaração é exigida por qualquer tipo de processo em lote. Este requisito inclui procedimentos armazenados, triggers, lotes ad hoc e consultas. Os lotes podem ser submetidos através de várias interfaces, incluindo sp_executesql, SQL dinâmico, métodos Prepare ou métodos Execute.

A recompile_cause coluna de sql_statement_recompile XEvent contém um código inteiro que indica o motivo da recompilação. A lista seguinte contém as possíveis razões:

• Opções do cursor alteradas
• Compilação adiada
• FOR BROWSE permissão alterada
• OPTION (RECOMPILE) solicitado
• Plano parametrizado esvaziado
• Vista particionada alterada
• Plano que afeta a versão do banco de dados foi alterado
• Ambiente de notificação de consulta alterado
• Loja de Consultas sem o plano
• Falha ao forçar o plano do Repositório de Consultas
• Política de forçamento de plano do Repositório de Consultas alterada
• Conjunto remoto de linhas alterado
• Esquema alterado
• SET opção alterada
• Estatísticas alteradas
• Tabela temporária alterada

Observação

Quando defines a AUTO_UPDATE_STATISTICS opção da base de dados para ON, as consultas são recompiladas quando têm como alvo tabelas ou vistas indexadas cujas estatísticas são atualizadas ou cujas cardinalidades mudam significativamente desde a última execução. Esse comportamento se aplica a tabelas padrão definidas pelo usuário, tabelas temporárias e tabelas inseridas e excluídas criadas por gatilhos DML.

Definir AUTO_UPDATE_STATISTICS como OFF impede estas recompilações, mas estatísticas desatualizadas normalmente degradam o desempenho das consultas mais do que as recompilações. Para mais informações, consulte a opção AUTO_UPDATE_STATISTICS. As tabelas inseridas e eliminadas dos gatilhos DML INSTEAD OF são criadas em tempdb, pelo que seguem a configuração AUTO_UPDATE_STATISTICS de tempdb.

Utilize parâmetros explícitos para a reutilização do plano

A utilização de parâmetros, incluindo parâmetros nomeados em aplicações ADO.NET e marcadores de parâmetros em aplicações OLE DB e ODBC, pode aumentar a reutilização dos planos de execução.

Advertência

Sempre valide a entrada do usuário testando tipo, comprimento, formato e intervalo. Para obter mais informações, consulte injeção SQL.

A única diferença entre as duas SELECT afirmações a seguir são os valores que são comparados na WHERE cláusula:

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = 1;

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = 4;

A única diferença entre os planos de execução para essas consultas é o valor armazenado para a comparação com a ProductSubcategoryID coluna. Embora o objetivo seja que o Database Engine reconheça sempre que as instruções geram essencialmente o mesmo plano e reutilize os planos, o Database Engine por vezes não deteta esta semelhança em instruções T-SQL complexas.

Separar constantes da instrução T-SQL usando parâmetros ajuda o motor relacional a reconhecer planos duplicados. Utilize os parâmetros das seguintes formas:

• Em T-SQL, utilize sp_executesql:

  DECLARE @MyIntParm AS INT;
  
  SET @MyIntParm = 1;
  
  EXECUTE sp_executesql N'SELECT *
     FROM AdventureWorks2025.Production.Product
     WHERE ProductSubcategoryID = @Parm', N'@Parm INT', @MyIntParm;
  

  Este método é recomendado para scripts T-SQL, procedimentos armazenados ou triggers que geram instruções SQL dinamicamente.

• ADO.NET com SqlClient utiliza parâmetros nomeados, como @Parm. O OLE DB e o ODBC usam marcadores de parâmetros. Marcadores de parâmetros são pontos de interrogação (?) que substituem uma constante numa instrução SQL e estão ligados a uma variável do programa. Por exemplo, você faria o seguinte em um aplicativo ODBC:

  • Use SQLBindParameter para vincular uma variável inteira ao primeiro marcador de parâmetro em uma instrução SQL.

  • Coloque o valor inteiro na variável.

  • Execute a instrução, especificando o marcador de parâmetros (?):

    SQLExecDirect(hstmt,
      "SELECT *
      FROM AdventureWorks2025.Production.Product
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
    

  O SQL Server Native Client OLE DB Provider e o driver ODBC do SQL Server Native Client usam sp_executesql para enviar instruções para o SQL Server quando marcadores de parâmetros são usados em aplicações.

• Escreve procedimentos armazenados, que usam parâmetros por design.

Se não incorporar explicitamente parâmetros no design das suas aplicações, também pode confiar no Query Optimizer para parametrizar automaticamente certas consultas usando o comportamento padrão da parametrização simples. Como alternativa, você pode forçar o Otimizador de Consultas a considerar a parametrização de todas as consultas no banco de dados definindo a PARAMETERIZATION opção da ALTER DATABASE instrução como FORCED.

Parametrização simples

No Database Engine, a utilização de parâmetros ou marcadores de parâmetros em instruções T-SQL aumenta a capacidade do motor relacional de alinhar novas instruções T-SQL com planos de execução já existentes, previamente compilados.

Advertência

Sempre valide a entrada do usuário testando tipo, comprimento, formato e intervalo. Para obter mais informações, consulte injeção SQL.

Se uma instrução T-SQL for executada sem parâmetros, o Database Engine parametriza internamente a instrução para aumentar a possibilidade de corresponder a um plano de execução existente. Este processo chama-se parametrização. O padrão é a parametrização simples, e podes ativar a parametrização forçada para uma base de dados.

Considere esta afirmação:

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = 1;

Pode especificar o valor 1 no final da instrução como parâmetro. O motor relacional constrói o plano de execução para este lote como se fosse especificado um parâmetro em vez do valor 1. Devido a esta simples parametrização, o Database Engine reconhece que as duas instruções seguintes geram essencialmente o mesmo plano de execução e reutiliza o primeiro plano para a segunda sentença:

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = 1;

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = 4;

Ao processar instruções T-SQL complexas, o motor relacional pode ter dificuldade em determinar quais expressões podem ser parametrizadas. Para aumentar a capacidade do motor relacional de associar instruções T-SQL complexas a planos de execução existentes e não utilizados, especifique explicitamente os parâmetros usando sp_executesql ou marcadores de parâmetro.

Observação

Quando utiliza os +operadores , -, *, /, ou % aritméticos para realizar a conversão implícita ou explícita dos valores constantes int, smallint, tinyint ou bigint para os tipos de dados float, real, decimal ou numeric, o Database Engine aplica regras específicas para calcular o tipo e a precisão dos resultados das expressões. Estas regras diferem, dependendo de a consulta ser parametrizada. Portanto, expressões semelhantes em consultas podem, em alguns casos, produzir resultados diferentes.

No comportamento predefinido da parametrização simples, o Motor de Base de Dados parametriza uma classe relativamente pequena de consultas. No entanto, você pode especificar que todas as consultas em um banco de dados sejam parametrizadas, sujeitas a certas limitações, definindo a PARAMETERIZATIONALTER DATABASE opção do comando como FORCED. Esta alteração pode melhorar o desempenho de bases de dados que experienciam elevados volumes de consultas concorrentes ao reduzir a frequência das compilações de consultas.

Como alternativa, você pode especificar que uma única consulta e quaisquer outras que sejam sintaticamente equivalentes, mas difiram apenas em seus valores de parâmetro, sejam parametrizadas.

Sugestão

Quando utiliza uma solução de mapeamento objeto-relacional (ORM), como o Entity Framework (EF), consultas de aplicação como árvores manuais de consultas LINQ ou certas consultas SQL brutas podem não ser parametrizadas. Esta limitação afeta a reutilização do plano e a capacidade de acompanhar consultas na Query Store. Para obter mais informações, consulte Cache e parametrização de consultas EF e Consultas SQL Brutas do EF.

Parametrização forçada

Pode sobrepor o comportamento padrão de parametrização simples do Database Engine especificando que todas as instruções SELECT, INSERT, UPDATE e DELETE numa base de dados sejam parametrizadas, sujeitas a certas limitações. Ative a parametrização forçada definindo a PARAMETERIZATION opção como FORCED na ALTER DATABASE instrução. A parametrização forçada pode melhorar o desempenho de determinados bancos de dados, reduzindo a frequência de compilações e recompilações de consultas. Os bancos de dados que podem se beneficiar da parametrização forçada geralmente são aqueles que enfrentam grandes volumes de consultas simultâneas de fontes, como aplicativos de ponto de venda.

Quando defines a opção PARAMETERIZATION para FORCED, qualquer valor literal que apareça numa instrução SELECT, INSERT, UPDATE ou DELETE, submetido sob qualquer forma, é convertido num parâmetro durante a compilação da consulta. As exceções são literais que aparecem nas seguintes construções de consulta:

• INSERT...EXECUTE declarações.
• Instruções dentro dos corpos de procedimentos armazenados, gatilhos ou funções definidas pelo usuário. O Database Engine já reutiliza planos de consulta para estas rotinas.
• instruções preparadas que já estão parametrizadas na aplicação do lado do cliente.
• Instruções que contêm chamadas de método XQuery, em que o método surge num contexto em que seus argumentos normalmente seriam parametrizados, como numa WHERE cláusula. Se o método aparecer em um contexto onde seus argumentos não seriam parametrizados, o restante da instrução será parametrizado.
• Instruções dentro de um cursor T-SQL. SELECT( instruções dentro de cursores de API são parametrizadas.)
• Construções de consulta obsoletas.
• Qualquer instrução executada no contexto de ANSI_PADDING ou ANSI_NULLS definido como OFF.
• Declarações que contenham mais de 2.097 constantes que podem ser parametrizadas.
• Instruções que fazem referência a variáveis, como WHERE T.col2 >= @bb.
• Declarações que contêm a dica de consulta RECOMPILE.
• Declarações que contêm uma COMPUTE cláusula.
• Declarações que contêm uma WHERE CURRENT OF cláusula.

As seguintes cláusulas de consulta não são parametrizadas. Nestes casos, apenas as cláusulas não são parametrizadas. Outras cláusulas dentro da mesma consulta podem ser elegíveis para parametrização forçada.

• O <select_list> de qualquer instrução SELECT. Esta lista inclui SELECT listas de subconsultas e SELECT listas dentro de instruções INSERT.
• Instruções de subconsulta SELECT que aparecem dentro de uma IF declaração.
• As TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO ou FOR XML cláusulas de uma consulta.
• Argumentos, diretos ou como subexpressões, para OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, ou qualquer FULLTEXT operador.
• Os argumentos padrão e de escape_character de uma cláusula LIKE.
• O argumento de estilo de uma CONVERT cláusula.
• Constantes inteiras dentro de uma IDENTITY cláusula.
• Constantes especificadas usando a sintaxe da extensão ODBC.
• Expressões constantes dobráveis que são argumentos dos operadores +, -, *, / e %. Ao considerar a elegibilidade para parametrização forçada, o Database Engine considera uma expressão como sendo dobrável de forma constante quando uma das seguintes condições é verdadeira:
  • Nenhuma coluna, variável ou subconsulta aparece na expressão.
  • A expressão contém uma CASE cláusula.
• Argumentos para as cláusulas de dica de consulta, incluindo o argumento number_of_rows da FAST dica de consulta, o argumento number_of_processors da MAXDOP dica de consulta e o argumento number da MAXRECURSION dica de consulta.

A parametrização ocorre ao nível de instruções individuais em T-SQL. Em outras palavras, as instruções individuais em um lote são parametrizadas. Após a compilação, uma consulta parametrizada é executada no contexto do lote em que foi originalmente submetida. Se um plano de execução para uma consulta estiver em cache, pode determinar se a consulta foi parametrizada referenciando a sql coluna da sys.syscacheobjects vista de gestão dinâmica. Se uma consulta for parametrizada, os nomes e tipos de dados dos parâmetros surgem antes do texto do lote submetido nesta coluna, como (@1 TINYINT).

Observação

Os nomes dos parâmetros são arbitrários. Utilizadores ou aplicações não devem depender de uma ordem de nomes específica. Além disso, os seguintes itens podem mudar entre versões do SQL Server e atualizações cumulativas (CU): nomes dos parâmetros, a escolha dos literais parametrizados e o espaçamento no texto parametrizado.

Tipos de dados de parâmetros

Quando o Database Engine parametriza literais, os parâmetros são convertidos para os seguintes tipos de dados:

• Literais inteiros cujo tamanho, de outra forma, caberia dentro do tipo de dados int são parametrizados como int. Literais inteiros maiores que fazem parte de predicados que envolvem qualquer operador de comparação (incluindo <, <=, =, >, !=, ANY, >=, ALL, BETWEEN, <>, !>, SOME, !< e IN) parametrizam para numeric(38,0). Literais maiores que não fazem parte de predicados que envolvam operadores de comparação são parametrizados como numéricos, cuja precisão é apenas suficientemente grande para suportar o seu tamanho e cuja escala é 0.
• Literais numéricos de ponto fixo que são partes de predicados e que envolvem operadores de comparação parametrizam para numeric cuja precisão é 38 e cuja escala é apenas suficientemente grande para suportar o seu tamanho. Literais numéricos de ponto fixo que não fazem parte de predicados que envolvem operadores de comparação parametrizam para números cuja precisão e escala são apenas suficientemente grandes para suportar o seu tamanho.
• Literais numéricos de ponto flutuante parametrizam para float(53).
• Os literais de string não-Unicode parametrizam varchar(8000) se o literal estiver dentro de 8.000 caracteres, e varchar(max) se for maior do que 8.000 caracteres.
• Os literais das strings Unicode parametrizam para nvarchar(4000) se o literal se enquadrar em 4.000 caracteres Unicode, e para nvarchar(max) se for maior que 4.000 caracteres.
• Os literais binários parametrizam para varbinary(8000) se o literal couber em 8.000 bytes. Se for maior do que 8.000 bytes, é convertido para varbinary(max).
• Os literais monetários parametrizam para money.

Diretrizes para o uso de parametrização forçada

Considere os seguintes pontos ao definir a PARAMETERIZATION opção para FORCED:

• A parametrização forçada altera as constantes literais numa consulta para parâmetros ao compilar uma consulta. Portanto, o Otimizador de Consultas pode escolher planos subótimos para consultas. Em particular, é menos provável que o Otimizador de Consultas corresponda a uma exibição indexada ou a um índice em uma coluna calculada. Ele também pode escolher planos subótimos para consultas colocadas em tabelas particionadas e exibições particionadas distribuídas. Não use parametrização forçada para ambientes que dependem fortemente de visualizações indexadas e índices em colunas calculadas. De um modo geral, apenas profissionais experientes em bases de dados devem usar a PARAMETERIZATION FORCED opção após determinarem que não afeta negativamente o desempenho.
• As consultas distribuídas que fazem referência a mais de um banco de dados são qualificadas para parametrização forçada, desde que a PARAMETERIZATION opção esteja definida como FORCED no banco de dados cujo contexto a consulta está sendo executada.
• Definir a opção PARAMETERIZATION como FORCED limpa todos os planos de consulta da cache de planos de uma base de dados, exceto aqueles que estão atualmente a compilar, a recompilar ou em execução. Os planos para consultas que estão a compilar ou a correr durante a alteração de definição são parametrizados na próxima execução da consulta.
• Definir a PARAMETERIZATION opção é uma operação online que não requer bloqueios exclusivos ao nível da base de dados.
• A configuração atual da PARAMETERIZATION opção é preservada ao reanexar ou restaurar um banco de dados.

Pode sobrepor o comportamento da parametrização forçada especificando que a parametrização simples deve ser tentada para uma única consulta e para quaisquer outras consultas que sejam sintaticamente equivalentes, mas que diferem apenas nos valores dos seus parâmetros. Por outro lado, você pode especificar que a parametrização forçada seja tentada apenas em um conjunto de consultas sintaticamente equivalentes, mesmo que a parametrização forçada esteja desabilitada no banco de dados. Utilize guias de plano para este propósito.

Observação

Quando a PARAMETERIZATION opção é definida como FORCED, o relatório de mensagens de erro pode diferir de quando a PARAMETERIZATION opção está definida como SIMPLE: várias mensagens de erro podem ser relatadas sob parametrização forçada, onde menos mensagens seriam relatadas sob parametrização simples e os números de linha em que os erros ocorrem podem ser relatados incorretamente.

Quando a parametrização forçada está ativada, a parametrização simples ainda pode ocorrer. Por exemplo, a seguinte consulta não pode ser parametrizada de acordo com as regras de parametrização forçada:

SELECT *
FROM Person.Address
WHERE AddressID = 1 + 2;

No entanto, pode ser parametrizado de acordo com regras simples de parametrização. Quando a parametrização forçada é tentada, mas falha, a parametrização simples ainda é tentada posteriormente.

Preparar instruções SQL

O Database Engine suporta a preparação de instruções T-SQL antes da execução. Se uma aplicação precisar de executar uma instrução T-SQL várias vezes, pode usar a API da base de dados para:

• Prepare a declaração uma vez. Este passo compila a instrução T-SQL num plano de execução.
• Execute o plano de execução pré-compilado sempre que precisar de executar a instrução. Esta abordagem impede a recompilação da instrução T-SQL em cada execução após a primeira vez. A preparação e execução de instruções é controlada por funções e métodos de API. O SQL Server Native Client OLE DB Provider e o driver ODBC do SQL Server Native Client suportam o modelo prepare/execute para executar instruções T-SQL. Em uma solicitação de preparação, o provedor ou o driver envia a instrução para o SQL Server com uma solicitação para preparar a instrução. O Database Engine compila um plano de execução e devolve um handle desse plano ao fornecedor ou driver. Em uma solicitação de execução, o provedor ou o driver envia ao servidor uma solicitação para executar o plano associado ao identificador.

Declarações preparadas não podem criar objetos temporários no Database Engine. As instruções preparadas não podem fazer referência a procedimentos armazenados do sistema que criam objetos temporários, como tabelas temporárias. Esses procedimentos devem ser executados diretamente.

O uso excessivo do modelo preparar/executar pode degradar o desempenho. Se uma instrução for executada apenas uma vez, a execução direta requer apenas uma ida e volta na rede até ao servidor. Preparar e executar uma instrução T-SQL executada apenas uma vez requer uma ida e volta extra na rede: uma viagem para preparar a instrução e outra para a executar.

Preparar uma instrução é mais eficaz se usares marcadores de parâmetros. Por exemplo, suponha que um aplicativo é ocasionalmente solicitado a recuperar informações do produto do AdventureWorksLT2025 banco de dados de exemplo. Existem duas formas como a aplicação pode realizar esta tarefa.

Ao usar a primeira abordagem, a aplicação pode executar uma consulta separada para cada produto solicitado:

SELECT *
FROM Production.Product
WHERE ProductID = 63;

Usando a segunda abordagem, a aplicação executa as seguintes operações:

1. Prepara uma instrução que contém um marcador de parâmetro (?).

   SELECT * FROM Production.Product
   WHERE ProductID = ?;
   

2. Vincula uma variável de programa ao marcador de parâmetro.

3. Cada vez que as informações do produto são necessárias, preenche a variável vinculada com o valor da chave e executa a instrução.

A segunda abordagem é mais eficiente quando a instrução é executada mais de três vezes.

No Database Engine, o modelo preparar/executar não tem vantagem significativa de desempenho em relação à execução direta, devido à forma como o Database Engine reutiliza planos de execução. O Database Engine possui algoritmos eficientes para emparelhar instruções T-SQL atuais com planos de execução gerados para execuções anteriores da mesma instrução T-SQL. Se uma aplicação executar uma instrução T-SQL com marcadores de parâmetros várias vezes, o Database Engine reutiliza o plano de execução da primeira execução para a segunda e subsequentes execuções (a menos que o plano expire da cache de planos). O modelo de preparação/execução ainda tem os seguintes benefícios:

• Encontrar um plano de execução através de um handle identificador é mais eficiente do que os algoritmos usados para associar uma instrução T-SQL a planos de execução existentes.
• A aplicação pode controlar quando o plano de execução é criado e quando é reutilizado.
• O modelo prepare/execute é portátil para outros bancos de dados, incluindo versões anteriores do SQL Server.

Sensibilidade dos parâmetros

Sensibilidade aos parâmetros, também conhecida como parameter sniffing, refere-se a um processo em que o Database Engine deteta os valores atuais dos parâmetros durante a compilação ou recompilação, e os transmite ao Query Optimizer para, potencialmente, ajudar a gerar planos de execução de consultas mais eficientes.

Os valores dos parâmetros são detetados durante a compilação ou recompilação para os seguintes tipos de lotes:

• Procedimentos armazenados
• Consultas enviadas via sp_executesql
• Consultas preparadas

Para mais informações sobre a resolução de problemas de parameter sniffing, veja:

• Investigue e resolva problemas sensíveis a parâmetros
• Utilize parâmetros explícitos para a reutilização do plano
• Otimização do Plano Sensível a Parâmetros
• Resolver problemas de consultas com questões no plano de execução de consultas sensíveis a parâmetros no Banco de Dados SQL do Azure
• Resolução de problemas em consultas com planos de execução sensíveis a parâmetros na Azure SQL Managed Instance

Quando uma consulta no Database Engine utiliza a dica OPTION (RECOMPILE), o Query Optimizer transforma parâmetros e variáveis locais em constantes de tempo de compilação que podem ser sujeitas a constant folding e reduzidas a literais. Durante a compilação, o otimizador conhece e pode usar os valores atuais de tempo de execução dos parâmetros e variáveis locais tal como existem imediatamente antes dessa instrução. A OPTION (RECOMPILE) diretiva permite ao otimizador gerar um plano de consulta mais ótimo, adaptado aos valores específicos, e tirar partido dos melhores índices subjacentes em tempo de execução. Para parâmetros, esse processo não se refere aos valores originalmente passados para o lote ou procedimento armazenado, mas aos seus valores no momento da recompilação. Estes valores podem ser modificados dentro do procedimento antes de se chegar à instrução que inclui RECOMPILE. Este comportamento pode melhorar o desempenho de consultas com dados de entrada altamente variáveis ou enviesados, à custa de compilar a consulta sempre que esta é executada. Este custo pode ser elevado. Evite usar a RECOMPILE dica sem avaliar o seu impacto em toda a carga de trabalho.

Variáveis locais e estimativas de cardinalidade

Quando uma consulta usa variáveis locais, o Database Engine não consegue inferir os respetivos valores em tempo de compilação, por isso estima a cardinalidade usando estatísticas ou heurísticas disponíveis. Se existirem estatísticas, normalmente usa o valor All Density (também conhecido como densidade média) do histograma estatístico para estimar quantas linhas correspondem ao predicado. No entanto, se não houver estatísticas disponíveis para a coluna, o Database Engine recorre a estimativas heurísticas, como assumir 10% de seletividade para predicados de igualdade e 30% para desigualdades e intervalos. Estas estimativas podem levar a planos de execução menos precisos. Aqui está um exemplo de uma consulta que usa uma variável local.

DECLARE @ProductId AS INT = 100;

SELECT *
FROM Products
WHERE ProductId = @ProductId;

Neste caso, o Database Engine não utiliza o valor 100 para otimizar a consulta. Utiliza uma estimativa geral.

Processamento paralelo de consultas

O Database Engine fornece consultas paralelas para otimizar a execução de consultas e as operações de indexação para computadores que possuem mais do que um microprocessador (CPU). Como o Database Engine pode realizar uma consulta ou operação de indexação em paralelo, utilizando várias threads de trabalho do sistema operativo, a operação termina de forma rápida e eficiente.

Durante a otimização de consultas, o Database Engine procura consultas ou operações de índice que possam beneficiar da execução paralela. Para estas consultas, o Database Engine insere operadores de troca no plano de execução da consulta para preparar a consulta para execução paralela. Um operador de troca é um operador em um plano de execução de consulta que fornece gerenciamento de processos, redistribuição de dados e controle de fluxo. O operador exchange inclui os Distribute Streams operadores, Repartition Streams e Gather Streams lógicos como subtipos, um ou mais dos quais podem aparecer no output Showplan de um plano de consulta paralela.

Importante

Certos construtos inibem a capacidade do Database Engine de usar paralelismo em todo o plano de execução, ou em partes do plano de execução.

As construções que inibem o paralelismo incluem:

Construir	Descrição
UDFs escalares	Para mais informações sobre funções escalares definidas pelo utilizador, veja Criar Funções Definidas pelo Utilizador. A partir do SQL Server 2019 (15.x), o Mecanismo de Banco de Dados do SQL Server pode expandir estas funções inline e desbloquear o uso do paralelismo durante o processamento de consultas. Para mais informações sobre inline de UDFs escalares, veja Intelligent query processing in SQL databases.
Consulta remota	Para mais informações sobre Remote Query, consulte referência dos operadores do showplan lógico e físico.
Cursores dinâmicos	Para mais informações sobre cursores, veja DECLARE CURSOR.
Consultas recursivas	Para mais informações sobre recursão, consulte Diretrizes para Definir e Usar Expressões Recursivas de Tabelas Comuns e Recursão em T-SQL.
Funções com valor de tabela de várias instruções (MSTVFs)	Para mais informações sobre MSTVFs, consulte Criar funções definidas pelo utilizador (Database Engine).
TOP palavra-chave	Para obter mais informações, consulte TOP.

Um plano de execução de consulta pode conter o atributo NonParallelPlanReason no elemento QueryPlan , que descreve por que o paralelismo não foi usado. Os valores para este atributo incluem:

Valor de "NonParallelPlanReason"	Descrição
MaxDOPSetToOne	Grau máximo de paralelismo definido para 1.
EstimatedDOPIsOne	O grau estimado de paralelismo é 1.
NoParallelWithRemoteQuery	O paralelismo não é suportado para consultas remotas.
NoParallelDynamicCursor	Cursores dinâmicos não suportam planos paralelos.
NoParallelFastForwardCursor	Planos paralelos não suportados para cursores de avanço rápido.
NoParallelCursorFetchByBookmark	Não são suportados planos paralelos para cursores que buscam por marcador.
NoParallelCreateIndexInNonEnterpriseEdition	A criação de índice paralelo não é suportada para edições não Enterprise.
NoParallelPlansInDesktopOrExpressEdition	Planos paralelos não suportados para Desktop e Express Edition.
NonParallelizableIntrinsicFunction	Query está fazendo referência a uma função intrínseca não paralelizável.
CLRUserDefinedFunctionRequiresDataAccess	Paralelismo não suportado para um UDF CLR que requer acesso a dados.
TSQLUserDefinedFunctionsNotParallelizable	Query está fazendo referência a uma função definida pelo usuário T-SQL que não era paralelizável.
TableVariableTransactionsDoNotSupportParallelNestedTransaction	As transações de variáveis de tabela não suportam transações aninhadas paralelas.
DMLQueryReturnsOutputToClient	A consulta DML retorna a saída para o cliente e não é paralelizável.
MixedSerialAndParallelOnlineIndexBuildNotSupported	Combinação não suportada de planos seriais e paralelos para uma única compilação de índice on-line.
CouldNotGenerateValidParallelPlan	A verificação do plano paralelo falhou, falhando de volta ao serial.
NoParallelForMemoryOptimizedTables	Paralelismo não suportado para tabelas OLTP In-Memory referenciadas.
NoParallelForDmlOnMemoryOptimizedTable	Paralelismo não suportado para DML em uma tabela OLTP In-Memory.
NoParallelForNativelyCompiledModule	Paralelismo não suportado para módulos referenciados compilados nativamente.
NoRangesResumableCreate	A geração de faixas falhou durante uma operação de criação que pode ser retomada.

Depois que os operadores de câmbio são inseridos, o resultado é um plano de execução de consulta paralela. Um plano de execução de consulta paralela pode usar mais de um thread de trabalho. Um plano de execução serial, usado por uma consulta não paralela (serial), usa apenas um thread de trabalho para sua execução. O número real de threads de trabalho usados por uma consulta paralela é determinado na inicialização da execução do plano de consulta e depende da complexidade do plano e do grau de paralelismo.

O grau de paralelismo (DOP) especifica o número máximo de processadores que podem ser usados para executar uma tarefa paralela. O DOP não indica o número total de threads de trabalho usados por uma consulta. O limite de DOP é definido por tarefa. Não é um limite por solicitação ou por consulta. Isso significa que, durante a execução de uma consulta paralela, uma única solicitação pode gerar várias tarefas atribuídas a um agendador. Podem ser usados simultaneamente mais processadores do que o especificado pelo MAXDOP em qualquer ponto da execução da consulta, quando diferentes tarefas são executadas em simultâneo. Para obter mais informações, consulte o guia de arquitetura de threads e tarefas.

O Otimizador de Consultas não utiliza um plano de execução paralelo para uma consulta se alguma das seguintes condições for verdadeira:

• O plano de execução em série é trivial, ou não ultrapassa o limiar de custo para definição de paralelismo.

• O plano de execução serial tem um custo total estimado de subárvore menor do que qualquer plano de execução paralelo explorado pelo otimizador.

• A consulta contém operadores escalares ou relacionais que não podem ser executados em paralelo. Determinados operadores podem fazer com que uma seção do plano de consulta seja executada no modo serial ou todo o plano seja executado no modo serial.

Observação

O custo total estimado da subárvore de um plano paralelo pode ser menor do que o limite de custo para a definição de paralelismo. Ou seja, o custo total estimado da subárvore do plano serial foi superior a esse valor, e foi escolhido o plano de consulta com o custo total estimado da subárvore mais baixo.

Grau de paralelismo (DOP)

O Database Engine deteta automaticamente o melhor grau de paralelismo para cada instância de uma execução paralela de consultas ou uma operação de definição de dados de índice (DDL). Determina este valor com base nos seguintes critérios:

1. Se o SQL Server está sendo executado em um computador que tenha mais de um microprocessador ou CPU, como um computador de multiprocessamento simétrico (SMP). Somente computadores com mais de uma CPU podem usar consultas paralelas.

2. Se há linhas de execução suficientes disponíveis. Cada operação de consulta ou índice requer um certo número de threads de trabalho para ser executada. A execução de um plano paralelo requer mais threads de trabalho do que um plano serial, e o número de threads de trabalho necessários aumenta com o grau de paralelismo. Quando o requisito de thread worker do plano paralelo para um grau específico de paralelismo não pode ser satisfeito, o Mecanismo de Banco de Dados do SQL Server diminui automaticamente o grau de paralelismo, ou executa o plano paralelo em série.

3. O tipo de consulta ou operação de índice executada. As operações de índice que criam ou recriam um índice ou descartam um índice clusterizado e consultas que usam ciclos de CPU fortemente são os melhores candidatos para um plano paralelo. Por exemplo, junções de tabelas grandes, grandes agregações e classificação de grandes conjuntos de resultados são bons candidatos. Consultas básicas, frequentemente encontradas em aplicações de processamento de transações, consideram que a coordenação extra necessária para executar uma consulta em paralelo supera o potencial aumento de desempenho. Para distinguir entre consultas que se beneficiam do paralelismo e aquelas que não se beneficiam, o Mecanismo de Banco de Dados do SQL Server compara o custo estimado de execução da operação de consulta ou índice com o limite de custo para o valor do paralelismo. Os utilizadores podem alterar o valor predefinido de 5 usando sp_configure se testes adequados indicarem que um valor diferente é mais adequado para a carga de trabalho em execução.

4. Se há um número suficiente de linhas para processar. Se o Otimizador de Consulta determinar que o número de linhas é muito baixo, ele não apresentará operadores de câmbio para distribuir as linhas. Assim, os operadores são executados em série. A execução dos operadores em um plano serial evita cenários em que os custos de inicialização, distribuição e coordenação excedem os ganhos alcançados pela execução paralela do operador.

5. Se as estatísticas atuais estão disponíveis. Se o maior grau de paralelismo não for possível, os graus mais baixos são considerados antes que o plano paralelo seja abandonado. Por exemplo, quando crias um índice agrupado numa vista, as estatísticas não podem ser avaliadas, porque o índice agrupado ainda não existe. Nesse caso, o Mecanismo de Banco de Dados do SQL Server não pode fornecer o mais alto grau de paralelismo para a operação de índice. No entanto, alguns operadores, como classificação e digitalização, ainda podem se beneficiar da execução paralela.

Observação

As operações de índice paralelo só estão disponíveis nas edições SQL Server Enterprise, Developer e Evaluation.

No momento da execução, o Mecanismo de Banco de Dados do SQL Server determina se a carga de trabalho atual do sistema e as informações de configuração descritas anteriormente permitem a execução paralela. Se a execução paralela for garantida, o Mecanismo de Banco de Dados do SQL Server determinará o número ideal de threads de trabalho e distribuirá a execução do plano paralelo por esses threads de trabalho. Quando uma operação de consulta ou índice começa a ser executada em vários threads de trabalho para execução paralela, o mesmo número de threads de trabalho é usado até que a operação seja concluída. O Mecanismo de Banco de Dados do SQL Server reexamina o número ideal de decisões de thread de trabalho sempre que um plano de execução é recuperado do cache do plano. Por exemplo, uma execução de um plano paralelo em cache pode correr em série, uma execução posterior do mesmo plano pode usar três threads de trabalho, e uma terceira execução pode usar quatro threads de trabalho.

Os operadores de atualização (update) e exclusão (delete) num plano de execução de consulta paralela são executados em série; no entanto, a cláusula WHERE de uma instrução UPDATE ou DELETE pode ser executada em paralelo. As alterações de dados reais são então aplicadas em série ao banco de dados.

A partir do nível de compatibilidade de base de dados 110, a SELECT ... INTO instrução pode ser executada em paralelo. Outros tipos de operadores de inserção funcionam da mesma forma descrita em versões anteriores.

A partir do nível de compatibilidade da base de dados 130, a INSERT ... SELECT instrução pode ser executada em paralelo ao inserir em heaps ou índices clustered columnstore (CCI), e ao usar o TABLOCK hint. Inserções em tabelas temporárias locais (identificadas pelo # prefixo) e tabelas temporárias globais (identificadas por ## prefixos) também estão ativadas para paralelismo usando a TABLOCK dica. Para obter mais informações, veja INSERT.

Os cursores estáticos e orientados por conjuntos de teclas podem ser preenchidos por planos de execução paralelos. No entanto, o comportamento dos cursores dinâmicos pode ser fornecido apenas pela execução em série. O Otimizador de Consulta sempre gera um plano de execução serial para uma consulta que faz parte de um cursor dinâmico.

Substituir graus de paralelismo

O grau de paralelismo define o número de processadores a serem usados na execução do plano paralelo. Pode definir esta configuração em vários níveis:

1. Ao nível do servidor, utilizando a opção de configuração do servidor grau máximo de paralelismo (MAXDOP).

   Aplica-se a: SQL Server

   O SQL Server 2019 (15.x) introduziu recomendações automáticas para definir a opção de configuração do servidor MAXDOP durante o processo de instalação. A interface de usuário de configuração permite que você aceite as configurações recomendadas ou insira seu próprio valor. Para obter mais informações, consulte Configuração do Mecanismo de Banco de Dados - página MaxDOP.

2. Nível de carga de trabalho, utilizando a MAX_DOPopção de configuração do grupo de carga de trabalho Resource Governor.

   Aplica-se a: SQL Server

3. Ao nível da base de dados, utilizando a MAXDOPconfiguração com âmbito da base de dados.

   Aplica-se a: SQL Server, Base de Dados SQL do Azure e Azure SQL Managed Instance

4. Ao nível da instrução de consulta ou de índice, utilizando a MAXDOPsugestão de consulta ou a MAXDOP opção de índice. Por exemplo, pode usar a MAXDOP opção para controlar, aumentando ou reduzindo, o número de processadores dedicados a uma operação de indexação online. Desta forma, pode equilibrar os recursos usados por uma operação de índice com os dos utilizadores concorrentes.

   Aplica-se a: SQL Server, Base de Dados SQL do Azure e Azure SQL Managed Instance

Definir a max degree of parallelism opção para 0 (por defeito) permite que o Database Engine use todos os processadores disponíveis até um máximo de 64 processadores numa execução paralela do plano. Embora o Database Engine defina um alvo de execução de 64 processadores lógicos quando a MAXDOP opção está definida para 0, pode definir manualmente um valor diferente se necessário. Definir MAXDOP como 0 para consultas e índices permite ao Database Engine usar todos os processadores disponíveis, até um máximo de 64 processadores, para as consultas e os índices especificados numa execução paralela do plano. MAXDOP não é um valor imposto para todas as consultas paralelas, mas sim um alvo provisório para todas as consultas elegíveis para paralelismo. Isto significa que, se não houver threads worker suficientes disponíveis em tempo de execução, uma consulta pode ser executada com um grau de paralelismo inferior à opção de configuração do MAXDOP servidor.

Sugestão

Para mais informações, consulte as recomendações do MAXDOP para orientações sobre a configuração de MAXDOP ao nível do servidor, base de dados, consulta ou dica.

Exemplo de consulta paralela

A consulta seguinte conta o número de encomendas feitas num trimestre específico, começando a 1 de abril de 2024, e em que pelo menos uma linha da encomenda foi recebida pelo cliente depois da data acordada. Esta consulta lista a contagem dessas ordens agrupadas por cada prioridade de ordem e ordenadas em ordem de prioridade crescente.

Este exemplo usa nomes teóricos de tabelas e colunas.

SELECT o_orderpriority,
       COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2024/04/01'
      AND o_orderdate < DATEADD(mm, 3, '2024/04/01')
      AND EXISTS (SELECT *
                  FROM lineitem
                  WHERE l_orderkey = o_orderkey
                        AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority;

Suponha que os seguintes índices sejam definidos nas tabelas lineitem e orders.

CREATE INDEX l_order_dates_idx
    ON lineitem(l_orderkey, l_receiptdate, l_commitdate, l_shipdate);

CREATE UNIQUE INDEX o_datkeyopr_idx
    ON ORDERS(o_orderdate, o_orderkey, o_custkey, o_orderpriority);

Aqui está um possível plano paralelo gerado para a consulta mostrada anteriormente:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2024 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2024 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

A ilustração seguinte mostra um plano de consulta executado com um grau de paralelismo igual a 4 e envolvendo uma junção de duas tabelas.

O plano paralelo contém três operadores de paralelismo. Tanto o operador Index Seek do o_datkey_ptr índice quanto o operador Index Scan do l_order_dates_idx índice são executados em paralelo. Este processo produz vários fluxos exclusivos, que podem ser determinados a partir dos operadores de Paralelismo mais próximos acima dos operadores Index Scan e Index Seek, respetivamente. Ambos os operadores estão a reparticionar o tipo de permuta. Eles reorganizam os dados entre os fluxos e produzem o mesmo número de fluxos na sua saída que têm na sua entrada. Este número de fluxos é igual ao grau de paralelismo.

O operador de paralelismo acima do l_order_dates_idx operador Index Scan reparticiona os seus fluxos de entrada usando o valor de L_ORDERKEY como chave. Desta forma, os mesmos valores de L_ORDERKEY acabam no mesmo fluxo de saída. Ao mesmo tempo, os fluxos de saída mantêm a ordem na L_ORDERKEY coluna para atender ao requisito de entrada do operador Merge Join.

O operador de paralelismo acima do operador Index Seek reparticiona os seus fluxos de entrada usando o valor de O_ORDERKEY. Como a sua entrada não está ordenada nos valores da coluna O_ORDERKEY e esta coluna é a coluna de junção no operador Merge Join, o operador Sort entre os operadores de paralelismo e Merge Join assegura que a entrada está ordenada para o operador Merge Join nas colunas de junção. O operador Sort, assim como o operador Merge Join, é executado em paralelo.

O operador de paralelismo mais alto reúne resultados de vários fluxos em um único fluxo. As agregações parciais realizadas pelo operador Stream Aggregate sob o operador de paralelismo são então acumuladas num único valor SUM para cada diferente valor de O_ORDERPRIORITY no operador Stream Aggregate acima do operador de paralelismo. Como este plano de execução tem dois segmentos de troca, com grau de paralelismo igual a 4, utiliza oito threads de trabalho.

Para mais informações sobre os operadores usados neste exemplo, consulte a referência de operadores Logical and physical showplan.

Operações paralelas de índice

Planos de consulta para operações de índice que criam ou reconstroem um índice, ou eliminam um índice agrupado, podem suportar operações paralelas e multithreaded em computadores com múltiplos processadores.

Observação

As operações de índice em paralelo estão disponíveis apenas na edição Enterprise.

O Database Engine utiliza os mesmos algoritmos para determinar o grau de paralelismo (o número total de threads de trabalho separados a executar) para operações de indexação, tal como para outras consultas. A opção de configuração grau máximo de paralelismo do servidor define o grau máximo de paralelismo para uma operação de indexação. Pode sobrescrever o valor max degree of parallelism para operações individuais de índice definindo a opção de índice MAXDOP nas CREATE INDEX instruções, ALTER INDEX, DROP INDEX e ALTER TABLE.

Quando o Mecanismo de Banco de Dados do SQL Server constrói um plano de execução de índice, o número de operações paralelas é definido para o valor mais baixo entre estes valores:

• O número de processadores, ou CPUs, no computador.
• O número especificado na opção de configuração do servidor max degree of parallelism.
• O número de CPUs que não estão acima de um limiar de trabalho realizado para threads de trabalho do SQL Server.

Por exemplo, num computador com oito CPUs, mas onde max degree of parallelism está definido para 6, a operação de índice utiliza no máximo seis threads de trabalho paralelos. Se cinco das CPUs no computador excederem o limite de trabalho do SQL Server quando um plano de execução de índice for criado, o plano de execução especificará apenas três threads de trabalho paralelos.

As principais fases de uma operação de índice paralelo incluem:

• Um thread de trabalho coordenador verifica rápida e aleatoriamente a tabela para estimar a distribuição das chaves de índice. O thread de trabalho coordenador estabelece os limites de chave que criam um número de intervalos de chave igual ao grau de operações paralelas, onde cada intervalo de chave é estimado para cobrir números semelhantes de linhas. Por exemplo, se houver quatro milhões de linhas na tabela e o grau de paralelismo for 4, a thread de trabalho coordenadora determina os valores-chave que delimitam quatro conjuntos de linhas com 1 milhão de linhas em cada conjunto. Se o worker thread coordenador não conseguir estabelecer intervalos de chaves suficientes para usar todos os CPUs, isso reduz o grau de paralelismo em conformidade.
• O fio de trabalho coordenador despacha um número de fios de trabalho igual ao grau de paralelismo e aguarda que os fios de trabalho concluam as suas tarefas. Cada thread de trabalho verifica a tabela base usando um filtro que recupera apenas linhas com valores de chave dentro do intervalo atribuído ao thread de trabalho. Cada thread de execução cria uma estrutura de índice para as linhas no intervalo de chaves atribuído. No caso de um índice particionado, cada thread de trabalho cria um número especificado de partições. As partições não são compartilhadas entre threads de trabalho.
• Depois de todas as threads de trabalho paralelas estarem concluídas, a thread de trabalho coordenadora liga as subunidades de índice num único índice. Esta fase aplica-se apenas a operações de índice offline.

As instruções CREATE TABLE ou ALTER TABLE individuais podem ter múltiplas restrições que exigem a criação de um índice. Essas várias operações de criação de índice são executadas em série, embora cada operação de criação de índice individual possa ser uma operação paralela em um computador que tenha várias CPUs.

Arquitetura de consulta distribuída

O Database Engine suporta dois métodos para referenciar fontes heterogéneas de dados OLE DB em instruções T-SQL:

• Nomes de servidores vinculados

  Use os procedimentos armazenados do sistema sp_addlinkedserver e sp_addlinkedsrvlogin para atribuir um nome de servidor a uma fonte de dados OLE DB. Pode referenciar objetos nestes servidores vinculados em instruções T-SQL com nomes de quatro partes. Por exemplo, se um nome de servidor vinculado de DeptSQLSrvr for definido em relação a outra instância do SQL Server, a instrução a seguir fará referência a uma tabela nesse servidor:

  SELECT JobTitle,
         HireDate
  FROM DeptSQLSrvr.AdventureWorks2025.HumanResources.Employee;
  

  Também pode especificar o nome do servidor associado numa instrução OPENQUERY para abrir um conjunto de linhas a partir da fonte de dados OLE DB. Pode então referenciar este conjunto de linhas como uma tabela em instruções T-SQL.

• Nomes de conectores ad hoc

  Para referências pouco frequentes a uma fonte de dados, especifique as funções OPENROWSET ou OPENDATASOURCE com a informação necessária para se ligar ao servidor ligado. Pode então referenciar o conjunto de linhas da mesma forma que referenciar uma tabela nas instruções T-SQL:

  SELECT a.*
  FROM OPENROWSET (
      'Microsoft.Jet.OLEDB.4.0',
      'MSOffice\Access\Samples\Northwind.mdb';
      'Admin';
      '',
      Employees
  ) AS a;
  

O Database Engine utiliza o OLE DB para estabelecer a comunicação entre o motor relacional e o motor de armazenamento. O motor relacional decompõe cada instrução T-SQL numa série de operações em conjuntos de linhas básicas do OLE DB que o motor de armazenamento abre a partir das tabelas base. Este processo significa que o motor relacional também pode abrir rowsets OLE DB básicos em qualquer fonte de dados OLE DB.

O mecanismo relacional usa a interface de programação de aplicativo (API) OLE DB para abrir os conjuntos de linhas em servidores vinculados, buscar as linhas e gerenciar transações.

Para cada fonte de dados OLE DB a que acede como servidor ligado, o servidor que executa o SQL Server deve ter um provedor OLE DB. O conjunto de operações T-SQL que pode usar contra uma fonte de dados específica do OLE DB depende das capacidades do fornecedor do OLE DB.

Para cada instância do SQL Server, os membros da função de servidor fixa sysadmin podem ativar ou desativar o uso de nomes de conector ad hoc para um fornecedor de OLE DB usando a propriedade do SQL Server DisallowAdhocAccess. Quando ativas o acesso ad hoc, qualquer utilizador com sessão iniciada nessa instância pode executar instruções T-SQL contendo nomes de conectores ad hoc, referenciando qualquer fonte de dados na rede a que o fornecedor OLE DB possa aceder. Para controlar o acesso às fontes de dados, os membros do papel fixo de servidor sysadmin podem desativar o acesso ad hoc para esse fornecedor OLE DB, limitando assim os utilizadores apenas às fontes de dados referenciadas por nomes de servidores ligados definidos pelos administradores. Por padrão, o acesso ad hoc é habilitado para o provedor OLE DB do SQL Server e desabilitado para todos os outros provedores OLE DB.

Consultas distribuídas podem permitir que os utilizadores acedam a outra fonte de dados (por exemplo, ficheiros, fontes de dados não relacionais como o Active Directory, etc.) utilizando o contexto de segurança da conta Windows sob a qual o serviço SQL Server está a correr. O SQL Server representa o início de sessão de forma adequada para os inícios de sessão do Windows. No entanto, essa representação não é possível para os logins do SQL Server. Esta limitação pode potencialmente permitir que um utilizador de consulta distribuída aceda a outra fonte de dados para a qual não tem permissões, mas a conta sob a qual o serviço SQL Server está a correr tem permissões. Use sp_addlinkedsrvlogin para definir os logins específicos que estão autorizados a acessar o servidor vinculado correspondente. Este controlo não está disponível para nomes ad hoc, por isso tenha cautela ao ativar um fornecedor OLE DB para acesso ad hoc.

Quando possível, o Database Engine envia operações relacionais, como joins, restrições, projeções, ordenações e operações GROUP BY, para a fonte de dados OLE DB. O Database Engine não assume por predefinição o varrimento da tabela base no SQL Server e a realização das operações relacionais ele próprio. O Database Engine consulta o fornecedor do OLE DB para determinar o nível de gramática SQL que suporta e, com base nessa informação, envia o maior número possível de operações relacionais ao fornecedor.

O Database Engine especifica um mecanismo para um fornecedor OLE DB devolver estatísticas que indicam como os valores-chave são distribuídos dentro da fonte de dados OLE DB. Este mecanismo permite ao Database Engine Query Optimizer analisar melhor o padrão de dados na fonte de dados face aos requisitos de cada instrução T-SQL, aumentando a capacidade do Query Optimizer para gerar planos de execução ótimos.

Processamento de consultas em tabelas e índices particionados

O Database Engine suporta processamento de consultas com reconhecimento de partições para tabelas e índices particionados. Esta secção descreve como o processador de consultas lida com objetos particionados, como interpretar planos de execução que envolvem particionamento e boas práticas para melhorar o desempenho das consultas em objetos particionados.

Observação

Tabelas e índices particionados são suportados na edição SQL Server Standard, a partir do SQL Server 2016 (13.x) com o Service Pack 1.

Nova operação de busca com reconhecimento de partição

No Database Engine, o processador de consultas vê internamente uma tabela particionada como um índice multicoluna com PartitionID como coluna inicial. Uma coluna computada oculta PartitionID é usada internamente para representar a partição ID que contém uma linha específica. Por exemplo, assuma que a tabela T, definida como T(a, b, c), está particionada na coluna a, e tem um índice agrupado na coluna b. Esta tabela particionada é tratada como uma tabela não particionada com o esquema T(PartitionID, a, b, c) e um índice agrupado na chave composta (PartitionID, b). Esta representação permite ao Otimizador de Consultas realizar operações de procura baseadas em PartitionID em qualquer tabela ou índice particionado.

A eliminação da partição agora é feita nesta operação de busca.

Além disso, o Otimizador de Consultas é estendido de modo a que uma operação de procura ou varrimento com uma condição possa ser feita em PartitionID (como a coluna inicial lógica) e possivelmente noutras colunas de chave de índice. O processador de consultas executa então uma procura de segundo nível, com uma condição diferente, numa ou mais colunas adicionais, para cada valor distinto que cumpra a qualificação para a operação de procura de primeiro nível. Esta operação, chamada partition skip scan, permite ao Otimizador de Consultas realizar uma operação de procura ou varrimento baseada numa condição para determinar as partições a aceder, e uma operação de procura de índice de segundo nível dentro desse operador para devolver linhas dessas partições que cumpram uma condição diferente. Por exemplo, considere a consulta a seguir.

SELECT *
FROM T
WHERE a < 10
      AND b = 2;

Para este exemplo, assuma que a tabela T, definida como T(a, b, c), está particionada na coluna a, e tem um índice agrupado na coluna b. A seguinte função de partição define os limites das partições para a tabela T:

CREATE PARTITION FUNCTION myRangePF1(INT)
    AS RANGE LEFT
    FOR VALUES (3, 7, 10);

Para resolver a consulta, o processador de consultas executa uma operação de busca de primeiro nível para localizar todas as partições que contêm linhas que atendem à condição T.a < 10. Esta operação identifica as partições a aceder. Dentro de cada partição identificada, o processador realiza então uma busca de segundo nível no índice agrupado na coluna b para encontrar as linhas que cumprem a condição T.b = 2 e T.a < 10.

A ilustração seguinte é uma representação lógica da operação partition skip scan. Ele mostra tabela T com dados em colunas a e b. As partições são numeradas de 1 a 4 com os limites de partição mostrados por linhas verticais tracejadas. Uma operação de procura de primeiro nível para as partições (não mostrada na ilustração) determina que as partições 1, 2 e 3 cumprem a condição de procura implícita pelo particionamento definido para a tabela e o predicado na coluna a (T.a < 10). O percurso percorrido pela busca do segundo nível é ilustrado pela linha curva. Essencialmente, a operação procura em cada uma destas partições linhas que cumpram a condição b = 2. O custo total da operação é o mesmo de três buscas de índice separadas.

Exibir informações de particionamento em planos de execução de consulta

Examine os planos de execução das consultas em tabelas e índices particionados usando as instruções SET SHOWPLAN_XML T-SQL SET ou SET STATISTICS XML, ou utilizando a saída do plano gráfico de execução no SSMS. Por exemplo, pode mostrar o plano de execução em tempo de compilação selecionando Mostrar Plano de Execução Estimado na barra de ferramentas do Editor do Power Query e o plano de execução em tempo de execução selecionando Incluir Plano de Execução Real.

Ao utilizar estas ferramentas, pode encontrar a seguinte informação:

• Operações como varreduras, procuras, inserções, atualizações, fusões e eliminações que acedem a tabelas ou índices particionados.
• As partições acessadas pela consulta. Por exemplo, os planos de execução em tempo de execução incluem a contagem total de partições acedidas e os intervalos de partições contíguas que são acedidas.
• Quando uma operação de partition skip scan é usada numa operação de procura ou varrimento para recuperar dados de uma ou mais partições.

Melhorias nas informações de partição

O Database Engine fornece informação melhorada de particionamento tanto para planos de execução em tempo de compilação como em tempo de execução. Os planos de execução agora fornecem as seguintes informações:

• Um atributo opcional Partitioned que indica que um operador, como uma pesquisa indexada, análise, inserir, atualizar, intercalar ou eliminar, é executado numa tabela particionada.
• Um novo SeekPredicateNew elemento com um SeekKeys subelemento que inclui PartitionID como coluna de chave de índice principal e condições de filtro que especificam as pesquisas de intervalos em PartitionID. A presença de dois SeekKeys subelementos indica uma pesquisa por ignorar partição em PartitionID.
• Informações de resumo que fornecem uma contagem total das partições acessadas. Essas informações estão disponíveis apenas em planos de tempo de execução.

Para demonstrar como estas informações são exibidas na saída do plano de execução gráfica e no plano de execução XML, considere a seguinte consulta na tabela fact_sales particionada. Esta consulta atualiza dados em duas partições.

UPDATE fact_sales
    SET quantity = quantity - 2
WHERE date_id BETWEEN 20190802 AND 20190902;

A seguinte ilustração mostra as propriedades do operador Clustered Index Seek no plano de execução da consulta. Para ver a definição da fact_sales tabela e a definição da partição, veja Exemplo.

Atributo particionado

Quando um operador como um Index Seek é executado em uma tabela ou índice particionado, o Partitioned atributo aparece no plano de tempo de compilação e tempo de execução e é definido como True (1). O atributo não aparece quando está definido para False (0).

O Partitioned atributo pode aparecer nos seguintes operadores físicos e lógicos:

• Escaneamento de tabela
• Verificação de índice
• Busca de índice
• Inserir
• Atualização
• Suprimir
• Unir

Como mostrado na ilustração anterior, este atributo aparece nas propriedades do operador em que está definido. Na saída XML do Showplan, este atributo aparece como Partitioned="1" no RelOp nó do operador onde está definido.

Novo predicado de busca

Na saída do XML Showplan, o SeekPredicateNew elemento aparece no operador em que está definido. Ele pode conter até duas ocorrências do SeekKeys subelemento. O primeiro SeekKeys item especifica a operação de busca de primeiro nível no nível de ID de partição do índice lógico. Ou seja, esta operação de procura determina as partições que devem ser acedidas para satisfazer as condições da consulta. O segundo SeekKeys item especifica a busca de segundo nível que ocorre em cada partição identificada na busca de primeiro nível.

Informações de resumo da partição

Em planos de execução em tempo de execução, as informações de resumo de partição fornecem uma contagem das partições acessadas e a identidade das partições efetivamente acessadas. Você pode usar essas informações para verificar se as partições corretas são acessadas na consulta e se todas as outras partições são eliminadas da consideração.

São fornecidas as seguintes informações: Actual Partition Count, e Partitions Accessed.

Actual Partition Count é o número total de partições acessadas pela consulta.

Partitions Accessed, na saída do Showplan XML, é a informação de resumo da partição que aparece no novo RuntimePartitionSummary elemento no RelOp nó do operador onde está definida. O exemplo a seguir mostra o conteúdo do RuntimePartitionSummary elemento , indicando que duas partições totais são acessadas (partições 2 e 3).

<RunTimePartitionSummary>
    <PartitionsAccessed PartitionCount="2" >
        <PartitionRange Start="2" End="3" />
    </PartitionsAccessed>
</RunTimePartitionSummary>

Mostrar informação de partição utilizando outros métodos de plano de exibição

Os métodos SHOWPLAN_ALLShowplan , SHOWPLAN_TEXTe STATISTICS PROFILE não relatam as informações de partição descritas neste artigo, com a seguinte exceção. Como parte do SEEK predicado, as partições a serem acessadas são identificadas por um predicado de intervalo na coluna computada que representa o ID da partição. O exemplo a seguir mostra o predicado SEEK de um Clustered Index Seek operador. As partições 2 e 3 são acessadas e o operador de busca filtra as linhas que atendem à condição date_id BETWEEN 20190802 AND 20190902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),
        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)
                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20190802)
                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20190902))
                ORDERED FORWARD)

Interpretar planos de execução para pilhas particionadas

Uma pilha particionada é tratada como um índice lógico no ID da partição. Num plano de execução, a eliminação de partições num heap particionado é representada como um Table Scan operador com um SEEK predicado sobre o ID da partição. O exemplo a seguir mostra as informações de Showplan fornecidas:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretar planos de execução para junções colocadas

A colocação de junção pode ocorrer quando duas tabelas são particionadas usando a mesma função de particionamento ou equivalente e as colunas de particionamento de ambos os lados da junção são especificadas na condição de junção da consulta. O Otimizador de Consultas pode gerar um plano onde as partições de cada tabela que têm IDs de partição iguais são unidas separadamente. As junções colocadas podem ser mais rápidas do que as junções não colocadas porque podem exigir menos memória e tempo de processamento. O Otimizador de Consultas escolhe um plano não colocado ou um plano colocado com base em estimativas de custo.

Em um plano colocado, a Nested Loops junção lê uma ou mais partições de tabela ou índice unidas do lado interno. Os números dentro dos Constant Scan operadores representam os números de partição.

Quando planos paralelos para junções colocalizadas são gerados para tabelas ou índices particionados, um operador de paralelismo aparece entre os operadores de Constant Scan e os operadores de junção Nested Loops. Nesse caso, vários threads de trabalho no lado externo da junção leem e funcionam em uma partição diferente.

A ilustração abaixo demonstra um plano de consulta paralelo para uma junção colocalizada.

Estratégia de execução de consulta paralela para objetos particionados

O processador de consultas usa uma estratégia de execução paralela para consultas que selecionam objetos particionados. Como parte da estratégia de execução, o processador de consultas determina as partições de tabela necessárias para a consulta e a proporção de threads de trabalho a serem alocados para cada partição. Na maioria dos casos, o processador de consultas aloca um número igual ou quase igual de threads de trabalho para cada partição e, em seguida, executa a consulta em paralelo nas partições. Os parágrafos a seguir explicam a alocação de threads de trabalho com mais detalhes.

Se o número de threads de trabalho for menor que o número de partições, o processador de consultas atribuirá cada thread de trabalho a uma partição diferente, deixando inicialmente uma ou mais partições sem um thread de trabalho atribuído. Quando uma thread de trabalho termina a execução numa partição, o processador de consulta atribui-a à partição seguinte até que cada partição seja atribuída a uma única thread de trabalho. Esta atribuição é o único caso em que o processador de consultas realoca threads de trabalho para outras partições.

Mostra o thread de trabalho reatribuído após sua conclusão. Se o número de threads de trabalho for igual ao número de partições, o processador de consultas atribuirá um thread de trabalho a cada partição. Quando um thread de trabalho termina, ele não é realocado para outra partição.

Se o número de threads de trabalho for maior do que o número de partições, o processador de consultas alocará um número igual de threads de trabalho para cada partição. Se o número de threads de trabalho não for um múltiplo exato do número de partições, o processador de consulta aloca um thread de trabalho extra a algumas partições para usar todos os threads de trabalho disponíveis. Se houver apenas uma partição, todos os threads de trabalho são atribuídos a essa partição. No diagrama seguinte, existem quatro partições e 14 threads de trabalho. Cada partição tem três threads de trabalho atribuídos, e duas partições têm um thread de trabalho extra, totalizando 14 atribuições de threads de trabalho. Quando um thread de execução termina, não é reatribuído a outra partição.

Embora os exemplos anteriores sugiram uma forma direta de alocar threads de trabalho, a estratégia real é mais complexa e tem em conta outras variáveis que ocorrem durante a execução da consulta. Por exemplo, se a tabela for particionada e tiver um índice agrupado numa coluna A e uma consulta tiver a cláusula de predicado WHERE A IN (13, 17, 25), o processador da consulta aloca uma ou mais threads de trabalho a cada um destes três valores de procura (A = 13, A = 17 e A = 25) em vez de a cada partição da tabela. Só é necessário executar a consulta nas partições que contêm esses valores, e se todos estes predicados de procura estiverem na mesma partição de tabela, todos os threads de trabalho são atribuídos à mesma partição de tabela.

Para tomar outro exemplo, suponha que a tabela tem quatro partições na coluna A com pontos de fronteira (10, 20, 30), um índice na coluna B, e a consulta tem uma cláusula de predicado WHERE B IN (50, 100, 150). Como as partições das tabelas são baseadas nos valores de A, os valores de B podem ocorrer em qualquer uma das partições da tabela. Assim, o processador de consulta procura cada um dos três valores de B (50, 100, 150) em cada uma das quatro partições da tabela. O processador de consultas atribui threads de trabalho proporcionalmente para que possa executar cada uma destas 12 análises da consulta em paralelo.

Partições de tabela baseadas na coluna A	Procura pela coluna B em cada partição da tabela
Partição da Tabela 1: A < 10	B = 50, B = 100, B = 150
Partição da Tabela 2: A >= 10 AND A < 20	B = 50, B = 100, B = 150
Partição da Tabela 3: A >= 20 AND A < 30	B = 50, B = 100, B = 150
Partição da Tabela 4: A >= 30	B = 50, B = 100, B = 150

Melhores práticas

Para melhorar o desempenho das consultas que acedem a uma grande quantidade de dados provenientes de grandes tabelas e índices particionados, siga estas melhores práticas:

• Distribua cada partição em vários discos. Esta abordagem é especialmente relevante ao utilizar discos rígidos rotativos.

• Sempre que possível, use um servidor com memória principal suficiente para caber partições acessadas com freqüência, ou todas as partições na memória, para reduzir o custo de E/S.

• Se os dados que consultas não caberem na memória, comprime as tabelas e índices para reduzir o custo de I/O.

• Use um servidor com processadores rápidos e tantos núcleos de processador quanto você puder pagar, para aproveitar a capacidade de processamento de consultas paralelas.

• Verifique se o servidor tem largura de banda suficiente do controlador de E/S.

• Crie um índice clusterizado em cada tabela particionada grande para aproveitar as otimizações de varredura de árvore B.

• Ao carregar dados em massa em tabelas particionadas, siga as recomendações de boas práticas no white paper, The Data Loading Performance Guide.

Exemplo

O exemplo a seguir cria um banco de dados de teste contendo uma única tabela com sete partições. Para visualizar informação de particionamento tanto para planos de compilação como de execução, utilize as ferramentas descritas em Mostrar informação de particionamento nos planos de execução de consultas ao executar as consultas neste exemplo.

Observação

Este exemplo insere mais de 1 milhão de linhas na tabela. A execução deste exemplo pode levar vários minutos, dependendo do seu hardware. Antes de executar este exemplo, verifique se você tem mais de 1,5 GB de espaço em disco disponível.

USE master;
GO

IF DB_ID(N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO

CREATE DATABASE db_sales_test;
GO

USE db_sales_test;
GO

CREATE PARTITION FUNCTION pf_range_fact(INT)
    AS RANGE RIGHT
    FOR VALUES (20190801, 20190901, 20191001, 20191101, 20191201, 20200101);
GO

CREATE PARTITION SCHEME ps_fact_sales
    AS PARTITION pf_range_fact
    ALL TO ([PRIMARY]);
GO

CREATE TABLE fact_sales
(
    date_id INT,
    product_id INT,
    store_id INT,
    quantity INT,
    unit_price NUMERIC (7, 2),
    other_data CHAR (1000)
) ON ps_fact_sales (date_id);
GO

CREATE CLUSTERED INDEX ci
    ON fact_sales(date_id);
GO

PRINT 'Loading...';

SET NOCOUNT ON;

DECLARE @i AS INT;

SET @i = 1;

WHILE (@i < 1000000)
    BEGIN
        INSERT INTO fact_sales
        VALUES (20190800 + (@i % 30) + 1, @i % 10000, @i % 200, RAND() - 25, (@i % 3) + 1, '');
        SET @i += 1;
    END
GO

DECLARE @i AS INT;

SET @i = 1;

WHILE (@i < 10000)
    BEGIN
        INSERT INTO fact_sales
        VALUES (20190900 + (@i % 30) + 1, @i % 10000, @i % 200, RAND() - 25, (@i % 3) + 1, '');
        SET @i += 1;
    END

PRINT 'Done.';
GO

-- Two-partition query.
SET STATISTICS XML ON;
GO

SELECT date_id,
       SUM(quantity * unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20190802 AND 20190902
GROUP BY date_id;
GO

SET STATISTICS XML OFF;
GO

-- Single-partition query.
SET STATISTICS XML ON;
GO

SELECT date_id,
       SUM(quantity * unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20190801 AND 20190831
GROUP BY date_id;
GO

SET STATISTICS XML OFF;
GO

Conteúdo relacionado

• Referência aos operadores lógicos e físicos do showplan
• Visão geral dos Eventos Estendidos
• Práticas recomendadas para monitorar cargas de trabalho com o Query Store
• Estimativa de cardinalidade (SQL Server)
• Processamento inteligente de consultas em bancos de dados SQL
• Precedência do operador (Transact-SQL)
• Visão geral do plano de execução
• Performance Center para Motor de Base de Dados do SQL Server e Base de Dados SQL do Azure