Guia de arquitetura de processamento de consultas

Aplica-se a:SQL ServerAzure SQL DatabaseAzure 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 seções a seguir abordam como o SQL Server processa consultas e otimiza a reutilização de consultas por meio do cache do plano de execução.

Modos de execução

O Mecanismo de Banco de Dados do SQL Server pode processar instruções Transact-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 de linha é um método de processamento de consulta usado com tabelas RDBMS tradicionais, onde os dados são armazenados em formato de linha. Quando uma consulta é executada e acessa dados em tabelas de armazenamento de linha, os operadores de árvore de execução e os operadores filho leem cada linha necessária, em todas as colunas especificadas no esquema da tabela. A partir de cada linha lida, o SQL Server recupera as colunas necessárias para o conjunto de resultados, conforme referenciado por uma instrução SELECT, predicado JOIN ou predicado de filtro.

Observação

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

Execução em modo de lote

A execução em modo de lote é um método de processamento de consulta usado para processar várias linhas juntas (daí o termo lote). 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 batch também usa algoritmos otimizados para CPUs multi-core e maior taxa de transferência de memória que são encontrados em hardware moderno.

Quando foi introduzido pela primeira vez, a execução em modo de lote estava intimamente integrada e otimizada em torno do formato de armazenamento columnstore. No entanto, a partir do SQL Server 2019 (15.x) e do Banco de Dados SQL do Azure, a execução em modo de lote não requer mais índices columnstore. Para obter mais informações, consulte Modo em lote no rowstore.

O processamento em modo batch opera em dados compactados quando possível e elimina o operador de troca usado pela execução do modo de linha. O resultado é um melhor paralelismo e um desempenho mais rápido.

Quando uma consulta é executada em modo de lote e acessa dados em índices columnstore, os operadores da árvore de execução e os operadores filho leem várias linhas juntas nos segmentos de colunas. O SQL Server lê apenas as colunas necessárias para o resultado, conforme referenciado por uma instrução SELECT, predicado JOIN ou predicado de filtro. Para obter mais informações sobre índices columnstore, consulte Columnstore Index Architecture.

Observação

A execução em modo batch é muito eficiente em cenários de Data Warehousing, onde grandes quantidades de dados são lidas e agregadas.

Processamento de instruções SQL

O processamento de uma única instrução Transact-SQL é a maneira mais básica de o SQL Server executar Transact-SQL instruções. As etapas usadas para processar uma única SELECT instrução que faz referência apenas a tabelas base locais (sem exibições ou tabelas remotas) ilustram o processo básico.

Precedência do operador lógico

Quando mais de um operador lógico é usado em uma instrução, NOT é avaliado primeiro, depois ANDe, finalmente OR. Os operadores aritméticos e bitwise são manipulados antes dos operadores lógicos. Para obter mais informações, consulte Precedência do operador.

No exemplo a seguir, a condição de cor pertence 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

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

O uso de parênteses, mesmo quando não são necessários, pode melhorar a legibilidade das consultas e reduzir a chance de cometer um erro sutil devido à precedência do operador. Não há penalidade de desempenho significativa no uso de parênteses. O exemplo a seguir é mais legível do que o exemplo original, embora eles sejam sintaticamente os mesmos.

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. Isso significa que o servidor de banco de dados deve analisar a instrução para determinar a maneira mais eficiente de extrair os dados solicitados. Isso é conhecido como otimização da SELECT declaração. O componente que faz isso é chamado de Otimizador de Consulta. A entrada para o Otimizador de Consulta consiste na consulta, no esquema do banco de dados (definições de tabela e índice) e nas estatísticas do banco de dados. A saída do Otimizador de Consulta é um plano de execução de consulta, às vezes chamado de plano de consulta ou plano de execução. O conteúdo de um plano de execução é descrito com mais detalhes mais adiante neste artigo.

As entradas e saídas do Otimizador de Consulta durante a otimização de uma única SELECT instrução são ilustradas no diagrama a seguir:

Uma SELECT instrução define apenas o seguinte:

• 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 nas especificações de junção, que podem 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 é uma definição do seguinte:

• A sequência na qual as tabelas de origem são acessadas.
  Normalmente, há muitas sequências nas quais o servidor de banco de dados pode acessar as tabelas base para criar 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 apenas algumas linhas com valores de chave específicos forem necessárias, o servidor de banco de dados poderá usar um índice. Se todas as linhas da tabela forem necessárias, o servidor de banco de dados poderá ignorar os índices e executar uma verificação de tabela. Se todas as linhas de uma tabela forem necessárias, mas houver um índice cujas colunas de chave estejam em um ORDER BY, executar uma verificação de índice em vez de uma verificação de tabela poderá salvar um tipo separado do conjunto de resultados. Se uma tabela for muito pequena, os scans de tabela podem ser o método mais eficiente para quase todos os acessos à tabela.

• Os métodos usados para realizar cálculos e como filtrar, agregar e classificar dados de cada tabela.
  Como os dados são acessados a partir de tabelas, há diferentes métodos para executar cálculos sobre dados, como calcular valores escalares, e para agregar e classificar dados conforme definido no texto da consulta, por exemplo, ao usar uma cláusula GROUP BY ou ORDER BY e como filtrar dados, por exemplo, ao usar uma cláusula WHERE ou HAVING.

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 alguma sobrecarga seja usada pelo Otimizador de Consulta para analisar a consulta e selecionar um plano, essa sobrecarga normalmente é salva várias vezes quando o Otimizador de Consulta 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 gasta alguns dias no início para planejar como vai construir a casa, e a outra empresa começa a construir sem planejamento, a empresa que leva tempo para planejar seu projeto provavelmente terminará primeiro.

O SQL Server Query Optimizer é 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 SELECT têm milhares de planos de execução possíveis. 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 SQL Server Query Optimizer não escolhe apenas o plano de execução com o menor custo de recursos; Ele escolhe o plano que retorna os resultados para o usuário com um custo razoável em recursos e que retorna os resultados mais rapidamente. Por exemplo, processar uma consulta em paralelo normalmente usa mais recursos do que processá-la em série, mas conclui a consulta mais rapidamente. O Otimizador de Consulta do SQL Server usará um plano de execução paralela para retornar resultados se a carga no servidor não for afetada negativamente.

O Otimizador de Consulta do SQL Server depende de estatísticas de distribuição quando estima os custos de recursos de diferentes métodos para extrair informações de uma tabela ou índice. As estatísticas de distribuição são mantidas para colunas e índices e contêm informações sobre a densidade¹ dos dados subjacentes. Isso é usado para indicar a seletividade dos valores em um determinado índice ou coluna. 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 SQL Server Query Optimizer é importante porque permite que o servidor de banco de dados se ajuste dinamicamente às condições variáveis no banco de dados sem exigir a entrada de um programador ou administrador de banco de dados. Isso permite que os programadores se concentrem na descrição do resultado final da consulta. Eles podem confiar que o SQL Server Query Optimizer criará um plano de execução eficiente para o estado do banco de dados sempre que a instrução for executada.

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, conforme produzido pelo Otimizador de Consultas.
• O Plano de Execução Real, que é o mesmo que o plano compilado mais o seu contexto de execução. Isso inclui informações de tempo de execução disponíveis após a conclusão da execução, como avisos de execução, ou em versões mais recentes do Mecanismo de Banco de Dados, o tempo decorrido e o tempo da CPU usados durante a execução.
• As Estatísticas de Consulta em Tempo Real, que é a mesma que o plano compilado mais seu contexto de execução. Isso inclui informações de tempo de execução durante o progresso da execução e é atualizado a cada segundo. As informações de tempo de execução incluem, por exemplo, o número real de linhas que fluem através dos operadores.

Processar uma instrução SELECT

As etapas básicas que o SQL Server usa para processar uma única instrução SELECT incluem o seguinte:

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 consulta, às vezes chamada de árvore de sequência, é construída descrevendo as etapas lógicas necessárias para transformar os dados de origem no formato exigido pelo conjunto de resultados.
3. O Otimizador de Consultas analisa diferentes maneiras pelas quais as tabelas de origem podem ser acessadas. Em seguida, ele seleciona a série de etapas que retornam os resultados mais rapidamente, usando menos recursos. A árvore de consulta é atualizada para registrar essa série exata de etapas. A versão final otimizada da árvore de consulta é chamada de plano de execução.
4. O mecanismo relacional começa a executar o plano de execução. À medida que as etapas que exigem dados das tabelas base são processadas, o mecanismo relacional solicita que o mecanismo de armazenamento passe dados dos conjuntos de linhas solicitados do mecanismo relacional.
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 SQL Server avalia algumas expressões constantes antecipadamente para melhorar o desempenho da consulta. Esta técnica de otimização usada pelo otimizador de consulta visa simplificar as 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. Isto é referido como dobragem constante. Uma constante é um Transact-SQL literal, como 3, 'ABC', '2005-12-31', 1.0e3, ou 0x12345678. Por exemplo, faça esta consulta:

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

Aqui, 30 * 12 é uma expressão constante. O SQL Server pode avaliar isso durante a compilação e reescrever a consulta internamente como:

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

Expressões dobráveis

O SQL Server usa dobramento 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 internas que são consideradas dobráveis pelo SQL Server, incluindo CAST e CONVERT. Geralmente, uma função intrínseca é dobrável se for uma função apenas de suas entradas e não de outras informações contextuais, como opções SET, configurações de idioma, opções de banco de dados e chaves de criptografia. 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 associados a tipos CLR definidos pelo usuário e funções CLR definidas pelo usuário com valor escalar determinístico (começando com SQL Server 2012 (11.x)). 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 dobragem for um tipo de objeto grande (text,ntext, image, nvarchar(max), varchar(max), varbinary(max) ou XML), o SQL Server não dobrará a 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 Transact-SQL definidas pelo utilizador¹.
• Expressões cujos resultados dependem das configurações de idioma.
• Expressões cujos resultados dependem das opções SET.
• Expressões cujos resultados dependem das opções de configuração do servidor.

¹ Antes do SQL Server 2012 (11.x), as funções determinísticas de CLR com valor escalar definidas pelo usuário e os métodos dos tipos definidos pelo usuário CLR não eram dobráveis.

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

Considere a seguinte consulta:

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 de banco de dados não estiver definida para FORCED esta consulta, a expressão 117.00 + 1000.00 será avaliada e substituída por seu resultado, 1117.00antes que a consulta seja compilada. Os benefícios desta dobragem constante incluem o seguinte:

• A expressão não precisa ser avaliada repetidamente em tempo de execução.
• O valor da expressão depois de avaliada é usado pelo Otimizador de Consulta para estimar o tamanho do conjunto de resultados da parte da consulta TotalDue > 117.00 + 1000.00.

Por outro lado, se dbo.f for uma função escalar definida pelo usuário, a expressão dbo.f(100) não será dobrada, porque o SQL Server não dobra expressões que envolvem funções definidas pelo usuário, mesmo que elas sejam determinísticas. Para obter mais informações sobre parametrização, consulte Parametrização forçada mais adiante neste artigo.

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: <, >, <=, >=, <>, LIKE, IS 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 AdventureWorks2022;
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. Isso permite que o Otimizador de Consultas estime com precisão o tamanho do conjunto de resultados, o que o ajuda a selecionar um bom plano de consulta.

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

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

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

Processar outras declarações

As etapas básicas descritas para processar uma SELECT instrução aplicam-se a outras instruções Transact-SQL, como INSERT, UPDATEe DELETE. UPDATE e DELETE ambas as instruções têm de visar o conjunto de linhas a serem modificadas ou eliminadas. 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. As instruções UPDATE e INSERT podem conter instruções SELECT incorporadas que fornecem os valores de dados a serem atualizados ou inseridos.

Mesmo as instruções DDL (Data Definition Language), como CREATE PROCEDURE ou ALTER TABLE, são finalmente traduzidas em uma série de operações relacionais nas tabelas do catálogo do sistema e, às vezes (tal como ALTER TABLE ADD COLUMN), nas tabelas de dados.

Mesas de trabalho

O mecanismo relacional pode precisar criar uma tabela de trabalho para executar uma operação lógica especificada em uma instrução Transact-SQL. As tabelas de trabalho são tabelas internas que são usadas para armazenar resultados intermediários. As tabelas de trabalho são geradas para determinadas consultas de GROUP BY, ORDER BY ou UNION. Por exemplo, se uma ORDER BY cláusula fizer referência a colunas que não são cobertas por nenhum índice, o Mecanismo Relacional pode precisar gerar uma tabela de trabalho para classificar 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 incorporadas tempdb e descartadas automaticamente quando não são mais necessárias.

Ver resolução

O processador de consultas do SQL Server trata modos de exibição indexados e não indexados de forma diferente:

• As linhas de um modo de exibição indexado são armazenadas no banco de dados no mesmo formato de uma tabela. Se o Otimizador de Consulta decidir usar um modo de exibição indexado em um plano de consulta, o modo de exibição indexado será tratado 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 Otimizador de Consulta incorpora a lógica da definição de exibição no plano de execução que ele cria para a instrução Transact-SQL que faz referência à exibição não indexada.

A lógica usada pelo SQL Server Query Optimizer para decidir quando usar um modo de exibição indexado é semelhante à lógica usada para decidir quando usar um índice em uma tabela. Se os dados no modo de exibição indexado cobrirem todo ou parte da instrução Transact-SQL e o Otimizador de Consulta determinar que um índice no modo de exibição é o caminho de acesso de baixo custo, o Otimizador de Consulta escolherá o índice independentemente de o modo de exibição ser referenciado pelo nome na consulta.

Quando uma instrução Transact-SQL faz referência a uma exibição não indexada, o analisador e o Otimizador de Consulta analisam a origem da instrução Transact-SQL e da exibição e, em seguida, resolvem-nas em um único plano de execução. Não há um plano separado para a declaração Transact-SQL e para a visualização.

Por exemplo, considere a seguinte visualização:

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

Com base nessa visão, ambas as instruções Transact-SQL executam as mesmas operações nas tabelas base e produzem os mesmos resultados:

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

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

O recurso Showplan do SQL Server Management Studio mostra que o mecanismo relacional cria o mesmo plano de SELECT execução para ambas as instruções.

Usar sugestões com modos de exibição

As dicas colocadas em modos de exibição em uma consulta podem entrar em conflito com outras dicas descobertas quando o modo de exibição é expandido para acessar suas tabelas base. Quando isso ocorre, a consulta retorna um erro. Por exemplo, considere a seguinte vista que contém uma dica de tabela na sua definição:

USE AdventureWorks2022;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
    s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince 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 dica SERIALIZABLE que é aplicada na vista Person.AddrState na consulta é propagada para ambas as tabelas Person.Address e Person.StateProvince na vista quando esta é 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 de tabela PAGLOCK, NOLOCK, ROWLOCK, TABLOCK ou TABLOCKX entram em conflito entre si, assim como as dicas de tabela HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD ou 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 é expandida, descobrimos que a visão v2 faz parte de 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 a dica FORCE ORDER é usada em uma consulta que contém um modo de exibição, a ordem de junção das tabelas dentro do modo de exibição é determinada pela posição do modo de exibição na construção ordenada. 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 como mostrado a seguir:

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 SQL Server opta por usar um modo de exibição indexado em seu plano de consulta somente se o Otimizador de Consulta determinar que é benéfico fazer isso.

As exibições indexadas podem ser criadas em qualquer edição do SQL Server. Em algumas edições de algumas versões mais antigas do SQL Server, o Otimizador de Consultas considera automaticamente o modo de exibição indexado. Em algumas edições de algumas versões mais antigas do SQL Server, é necessário usar a dica de tabela NOEXPAND para utilizar uma visualização indexada. O uso automático de um modo de exibição indexado pelo otimizador de consulta é suportado apenas em edições específicas do SQL Server. O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure também oferecem suporte ao uso automático de modos de exibição indexados sem especificar a dica NOEXPAND.

O Otimizador de Consulta do SQL Server usa uma exibição indexada quando as seguintes condições são atendidas:

• 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 como OFF.
• O Otimizador de Consulta encontra uma correspondência entre as colunas de índice de exibição e os elementos na consulta, como os seguintes:
  • Predicados da condição de pesquisa na cláusula WHERE
  • 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. Nada mais precisa ser especificado na consulta para que um modo de exibição indexado seja usado.

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 NOEXPAND é especificado para um modo de exibição, o Otimizador de Consulta considera o uso de quaisquer índices definidos no modo de exibição. NOEXPAND especificada com a cláusula opcional INDEX() força o Otimizador de Consulta a usar os índices especificados. NOEXPAND pode ser especificado apenas para um modo de exibição indexado e não pode ser especificado para um modo de exibição não indexado. O uso automático de um modo de exibição indexado pelo otimizador de consulta é suportado apenas em edições específicas do SQL Server. O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure também 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. (Esse processo é explicado com mais detalhes em View Resolution.) Desde que o conjunto de dicas que existe nas tabelas subjacentes do modo de exibição sejam idênticos entre si, a consulta é elegível para ser correspondida com um modo de exibição indexado. Na maioria das vezes, essas pistas combinarão entre si, porque estão sendo herdadas diretamente da visão. 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 faz a correspondência entre um modo de exibição indexado e uma consulta, todas as dicas especificadas nas tabelas ou exibições na consulta são aplicadas diretamente ao modo de exibição indexado. Se o Otimizador de Consultas optar por não usar um modo de exibição indexado, todas as dicas serão propagadas diretamente para as tabelas referenciadas no modo de exibição. Para obter mais informações, consulte Exibir 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. Os hints de junção não são considerados pelo Otimizador de Consultas 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. No modo de compatibilidade 80 e superior, o SQL Server ignora dicas dentro de definições de exibição indexada ao mantê-las ou ao executar consultas que usam exibições indexadas. Embora o uso de dicas em definições de exibição indexadas não produza um erro de sintaxe no modo de compatibilidade 80, elas são ignoradas.

Para obter mais informações, consulte Sugestões para tabelas (Transact-SQL).

Resolver vistas particionadas distribuídas

O processador de consultas do SQL Server otimiza o desempenho de exibições 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 SQL Server cria planos inteligentes e dinâmicos que fazem uso eficiente de consultas distribuídas para acessar dados de tabelas de membros remotos:

• O Processador de Consultas primeiro usa o OLE DB para recuperar as definições de restrição de verificação de cada tabela membro. Isso permite que o processador de consultas mapeie a distribuição de valores de chave entre as tabelas de membros.
• O Processador de Consultas compara os intervalos de chaves especificados em uma cláusula de instrução WHERE Transact-SQL com o mapa que mostra como as linhas são distribuídas nas tabelas de membros. Em seguida, o processador de consultas cria um plano de execução de consulta que usa consultas distribuídas para recuperar apenas as linhas remotas necessárias para concluir a instrução Transact-SQL. O plano de execução também é construído de tal forma que qualquer acesso a tabelas de membros remotos, para dados ou metadados, é adiado até que as informações sejam necessárias.

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 Processador de Consultas do SQL Server também pode criar lógica dinâmica em planos de execução de consulta para instruções Transact-SQL nas quais os valores de chave não são conhecidos quando o plano deve ser criado. Por exemplo, considere este procedimento armazenado:

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

O SQL Server não pode prever qual valor de chave será fornecido pelo @CustomerIDParameter parâmetro sempre que o procedimento for executado. Como o valor da chave não pode ser previsto, o processador de consultas também não pode prever qual tabela de membros terá que ser acessada. Para lidar com esse caso, o SQL Server cria um plano de execução que tem lógica condicional, conhecida como filtros dinâmicos, para controlar qual tabela membro é acessada, com base no valor do parâmetro de entrada. Supondo que o GetCustomer procedimento armazenado foi executado no Server1, a lógica do plano de execução pode ser representada conforme mostrado a seguir:

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

Às vezes, o SQL Server cria esses tipos 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 Consulta parametrizar uma consulta fazendo referência a uma exibição particionada, o Otimizador de Consulta não poderá mais presumir que as linhas necessárias virão de uma tabela base especificada. Em seguida, terá de utilizar filtros dinâmicos no plano de execução.

Procedimento armazenado e execução de gatilho

O SQL Server armazena apenas a origem para procedimentos armazenados e gatilhos. 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 não é mais lembrado, um novo plano é elaborado. Esse processo é semelhante ao processo que o SQL Server segue para todas as instruções Transact-SQL. A principal vantagem de desempenho que os procedimentos armazenados e gatilhos têm no SQL Server em comparação com lotes de Transact-SQL dinâmico é que suas instruções Transact-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. Isso permite uma maior reutilização do procedimento armazenado e aciona os planos de execução.

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

O SQL Server tem um pool de memória que é usado para armazenar planos de execução e buffers de dados. A porcentagem do pool alocada para planos de execução ou buffers de dados flutua dinamicamente, dependendo do estado do sistema. A parte do pool de memória usada para armazenar planos de execução é chamada de cache de plano.

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

• O armazenamento de cache de Planos de Objetos (OBJCP) é utilizado para planos relacionados com objetos persistentes, como procedimentos armazenados, funções e gatilhos.
• O armazenamento de cache de Planos SQL (SQLCP) é usado para planos relacionados a consultas autoparametrizadas, dinâmicas ou preparadas.

A consulta abaixo fornece informações sobre o uso de memória para esses dois armazenamentos de cache:

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

Observação

O cache de planos possui dois armazenamentos adicionais que não são utilizados para guardar planos.

• O armazenamento em cache de Bound Trees (PHDR) usado para estruturas de dados na compilação de planos para visões, restrições e valores padrão. Estas estruturas são conhecidas como Árvores Encadernadas ou Árvores Álgebrizadoras.
• O armazenamento de cache de Procedimentos Armazenados Estendidos (XPROC) usado para procedimentos predefinidos do sistema, como sp_executeSql ou xp_cmdshell, que são definidos usando uma DLL, não usando instruções Transact-SQL. A estrutura armazenada em cache contém apenas o nome da função e o nome da DLL na qual o procedimento é implementado.

Os planos de execução do SQL Server 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

    Em versões mais recentes do Mecanismo de Banco de Dados, as informações sobre os objetos de estatística que foram usados para Estimativa de cardinalidade também são armazenadas.

  • Quais objetos de suporte devem ser criados, como tabelas de trabalho ou arquivos de trabalho no tempdb. Nenhuma informação de contexto de usuário ou tempo de execução é armazenada no plano de consulta. Nunca há mais de uma ou duas cópias do plano de consulta na memória: uma cópia para todas as execuções seriais e outra para todas as execuções paralelas. A cópia paralela abrange todas as execuções paralelas, independentemente do seu grau de paralelismo.

• Contexto de execução
  Cada usuário que está executando a consulta no momento tem uma estrutura de dados que contém os dados específicos para sua execução, como valores de parâmetro. Essa 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.

  

Quando qualquer instrução Transact-SQL é executada no SQL Server, o Mecanismo de Banco de Dados primeiro examina o cache de plano para verificar se existe um plano de execução existente para a mesma instrução Transact-SQL. A instrução Transact-SQL qualifica-se como existente se corresponder literalmente a uma instrução Transact-SQL executada anteriormente com um plano armazenado em cache, caractere por caractere. O SQL Server reutiliza qualquer plano existente que encontrar, salvando a sobrecarga de recompilar a instrução Transact-SQL. Se não existir nenhum plano de execução, o SQL Server gerará um novo plano de execução para a consulta.

Observação

Os planos de execução para algumas instruções Transact-SQL não são persistidos no cache do plano, como instruções de operação em massa em execução em rowstore ou instruções contendo literais de cadeia de caracteres maiores que 8 KB de tamanho. Esses planos só existem enquanto a consulta está sendo executada.

O SQL Server tem um algoritmo eficiente para localizar quaisquer planos de execução existentes para qualquer instrução Transact-SQL específica. Na maioria dos sistemas, os recursos mínimos que são usados por essa análise são menores do que os recursos que são salvos por ser capaz de reutilizar os planos existentes em vez de compilar cada instrução Transact-SQL.

Os algoritmos para corresponder novas instruções Transact-SQL a planos de execução não utilizados e existentes no cache de planos exigem que todas as referências de objeto sejam totalmente qualificadas. Por exemplo, suponha que Person é o esquema padrão para o usuário que executa as instruções abaixo SELECT . Embora neste exemplo não seja necessário que a Person tabela esteja totalmente qualificada para ser executada, isso significa que a segunda instrução não corresponde a um plano existente, mas a terceira é correspondida:

USE AdventureWorks2022;
GO
SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

Alterar qualquer uma das seguintes opções SET para uma determinada execução afetará a capacidade de reutilizar planos, porque o Mecanismo de Banco de Dados executa dobramento constante e essas opções afetam os resultados de tais expressões:

ANSI_NULL_DFLT_OFF

FORCEPLAN

ARITHABORT

DATAINICIO

ANSI_PADDING

AbortarArredondamentoNumérico

Configuração ANSI_NULL_DFLT_ON

IDIOMA

CONCAT_NULL_YIELDS_NULL (Concatenação de NULL resulta em NULL)

FORMATO DE DATA

ANSI_WARNINGS (avisos ANSI)

IDENTIFICADOR_COM_ASPAS

ANSI_NULLS

NO_BROWSETABLE

ANSI_DEFAULTS (padrões predefinidos do ANSI)

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 de consulta é um valor de hash binário calculado no texto Transact-SQL de uma consulta e é usado para identificar de forma exclusiva as consultas.

Um plano compilado pode ser recuperado do cache do plano usando um identificador de plano, que é um identificador transitório que permanece constante apenas enquanto o plano permanece no cache. O identificador de plano é um valor de hash derivado do plano compilado de todo o lote. O identificador de plano para um plano compilado permanece o mesmo, mesmo se uma ou mais instruções no lote forem recompiladas.

Observação

Se um plano foi compilado para um lote em vez de uma única instrução, o plano para instruções individuais no lote pode ser recuperado usando o identificador de plano e os deslocamentos de instrução. O sys.dm_exec_requests DMV contém as colunas statement_start_offset e statement_end_offset para cada registo, que se referem à instrução atualmente em execução de um lote ou objeto persistente. Para obter mais informações, consulte sys.dm_exec_requests (Transact-SQL). O sys.dm_exec_query_stats DMV também contém estas colunas para cada registro, que se referem à posição de uma declaração dentro de um lote ou objeto persistente. Para obter mais informações, consulte sys.dm_exec_query_stats (Transact-SQL).

O texto real Transact-SQL de um lote é armazenado num espaço de memória distinto do cache de plano, chamado de cache do SQL Manager (SQLMGR). O texto Transact-SQL para um plano compilado pode ser recuperado do cache do gerenciador SQL usando um identificador SQL, que é um identificador transitório que permanece constante apenas enquanto pelo menos um plano que faz referência a ele permanece no cache do plano. O identificador sql é um valor de hash derivado de todo o texto do lote e é garantidamente único para cada lote.

Observação

Como um plano compilado, o texto Transact-SQL é armazenado por lote, incluindo os comentários. O identificador sql contém o hash MD5 de todo o texto do lote e é garantido que será exclusivo para cada lote.

A consulta abaixo fornece informações sobre o uso de memória para o cache do gerenciador SQL:

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

Há uma relação 1:N entre um identificador de SQL e identificadores de plano. Tal condição ocorre quando a chave de cache para os planos compilados é diferente. Isso pode ocorrer devido à alteração nas opções SET 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

EXEC usp_SalesByCustomer 10
GO

Verifique o que pode ser encontrado no cache do plano usando a consulta abaixo:

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 execute o procedimento armazenado com um parâmetro diferente, mas sem outras alterações no contexto de execução:

EXEC usp_SalesByCustomer 8
GO

Verifique novamente o que pode ser encontrado 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

Observe que o usecounts aumentou para 2, o que significa que o mesmo plano armazenado em cache foi reutilizado as-is, porque as estruturas de dados do contexto de execução foram reutilizadas. Agora altere a SET ANSI_DEFAULTS opção e execute o procedimento armazenado usando o mesmo parâmetro.

SET ANSI_DEFAULTS OFF
GO

EXEC usp_SalesByCustomer 8
GO

Verifique novamente o que pode ser encontrado no cache do plano. 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 registro, que é o plano executado uma vez com SET ANSI_DEFAULTS OFF.
• A usecounts coluna mostra o valor 2 no segundo registro, que é 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 em chamadas que tenham o mesmo conjunto de opções de SET. O novo identificador de plano é necessário porque o contexto de execução foi reinicializado devido a opções SET alteradas. Mas isso não desencadeia uma recompilação: ambas as entradas se referem ao mesmo plano e consulta, como evidenciado pelos mesmos query_plan_hash e query_hash valores.

O que isso efetivamente significa é que temos duas entradas de plano no cache correspondentes ao mesmo lote, e salienta a importância de garantir que as opções SET que afetam o cache do plano sejam idênticas, quando as mesmas consultas são executadas repetidamente, para otimizar a reutilização do plano e manter o tamanho do cache do plano no mínimo necessário.

Sugestão

Uma armadilha comum é que clientes diferentes podem ter valores padrão diferentes para as opções SET. Por exemplo, uma ligação estabelecida através do SQL Server Management Studio define QUOTED_IDENTIFIER automaticamente como ON, enquanto o SQLCMD define QUOTED_IDENTIFIER como OFF. Executar as mesmas consultas desses dois clientes resultará em vários planos (conforme descrito no exemplo acima).

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 armazená-los. Quando existe pressão de memória, o Mecanismo de Banco de Dados do SQL Server usa uma abordagem baseada em custo para determinar quais planos de execução devem ser removidos do cache do plano. Para tomar uma decisão baseada em custo, o Mecanismo de Banco de Dados do SQL Server aumenta e diminui uma variável de custo atual para cada plano de execução de acordo com os seguintes fatores.

Quando um processo de usuário insere um plano de execução no cache, o processo do usuário define o custo atual igual ao custo de compilação da consulta original; Para planos de execução ad hoc, o processo do usuário define o custo atual como zero. Depois disso, cada vez que um processo de usuário faz referência a um plano de execução, ele redefine o custo atual para o custo de compilação original; Para planos de execução ad hoc, o processo do usuário 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 responde removendo planos de execução do 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; ele é removido somente quando o Mecanismo de Banco de Dados do SQL Server examina o plano e 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 examina repetidamente os planos de execução até que tenham sido removidos o suficiente 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 não existe mais, o Mecanismo de Banco de Dados do SQL Server para de diminuir o custo atual dos planos de execução não utilizados e todos os planos de execução permanecem no cache do plano, mesmo que 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 os threads de trabalho do usuário podem examinar planos executados simultaneamente 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 obter mais informações sobre a configuração da memória máxima do servidor, consulte a definição de 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 permanece 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 de zero, quando o Mecanismo de Banco de Dados do SQL Server examinar o plano de execução, verá o custo atual zero e removerá o plano do Cache de Planos. O plano de execução ad hoc permanece na cache de plano com custo atual zero quando não houver pressão de memória.

Para remover manualmente um único plano ou todos os planos do cache, use DBCC FREEPROCCACHE. DBCC FREESYSTEMCACHE também pode ser usado para limpar qualquer cache, incluindo cache de plano. A partir do SQL Server 2016 (13.x), pode usar o ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE para limpar o cache de procedimentos (plano) do banco de dados em questão.

pt-PT: Uma alteração em algumas definições de configuração via sp_configure e reconfigure também fará com que os planos sejam removidos do cache de planos. Você pode encontrar a lista dessas definições de configuração na seção Comentários do artigo DBCC FREEPROCCACHE . Uma alteração de configuração como essa registrará a seguinte mensagem informativa no log 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 em um banco de dados podem fazer com que um plano de execução seja ineficiente ou inválido, com base no novo estado do banco de dados. O SQL Server deteta as alterações que invalidam um plano de execução e marca o plano como invá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 o seguinte:

• Alterações feitas em uma tabela ou exibição referenciada pela consulta (ALTER TABLE e ALTER VIEW).
• Alterações feitas num único procedimento, que removeriam todos os planos para esse procedimento do cache (ALTER PROCEDURE).
• Alterações a quaisquer índices utilizados pelo plano de execução.
• Atualizações sobre estatísticas usadas pelo plano de execução, geradas explicitamente a partir de 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 para a correção da declaração ou para obter planos de execução de consulta potencialmente mais rápidos.

Nas versões do SQL Server anteriores a 2005, sempre que uma instrução dentro de um lote causa recompilação, todo o lote, seja enviado por meio de um procedimento armazenado, gatilho, lote ad hoc ou instrução preparada, era recompilado. A partir do SQL Server 2005 (9.x), somente a instrução dentro do lote que dispara a recompilação é recompilada. Além disso, há tipos adicionais de recompilações no SQL Server 2005 (9.x) e posterior devido ao seu conjunto de recursos expandido.

A recompilação em nível de declaração beneficia o desempenho porque, na maioria dos casos, um pequeno número de declarações provoca recompilações e as penalidades associadas, em termos de tempo de CPU e bloqueios. Essas penalidades são, portanto, evitadas para as outras declarações do lote que não precisam ser recompiladas.

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. Isso inclui procedimentos armazenados, gatilhos, lotes ad hoc e consultas. Os lotes podem ser enviados através de várias interfaces, incluindo sp_executesqlSQL 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 tabela a seguir contém os possíveis motivos:

Esquema alterado

Estatísticas alteradas

Compilação adiada

Opção SET alterada

Tabela temporária alterada

Conjunto de linhas remoto alterado

FOR BROWSE permissão alterada

Ambiente de notificação de consulta alterado

Vista particionada alterada

Opções do cursor alteradas

OPTION (RECOMPILE) solicitado

Plano parametrizado lavado

Plano que afeta a versão do banco de dados alterada

Plano do Repositório de Consultas forçando a política alterada

Falha ao forçar o plano do Repositório de Consultas

Loja de Consultas sem o plano

Observação

Em versões do SQL Server em que XEvents não estão disponíveis, o evento de rastreamento SP:Recompile do SQL Server Profiler pode ser usado para a mesma finalidade de relatar recompilações no nível da instrução.

O evento de rastreamento SQL:StmtRecompile também relata recompilações ao nível da instrução, e ele também pode ser usado para monitorizar e depurar recompilações.

Considerando que SP:Recompile gera apenas para procedimentos armazenados e gatilhos, SQL:StmtRecompile gera para procedimentos armazenados, gatilhos, lotes ad hoc, lotes que são executados usando consultas preparadas e sp_executesqlSQL dinâmico. A coluna EventSubClass de SP:Recompile e SQL:StmtRecompile contém um código inteiro que indica o motivo da recompilação. Os códigos estão descritos aqui.

Observação

Quando a AUTO_UPDATE_STATISTICS opção de banco de dados é definida como ON, as consultas são recompiladas quando visam tabelas ou exibições indexadas cujas estatísticas foram atualizadas ou cujas cardinalidades mudaram 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. Se o desempenho da consulta for afetado por recompilações excessivas, considere alterar essa configuração para OFF. Quando a AUTO_UPDATE_STATISTICS opção de banco de dados é definida como OFF, nenhuma recompilação ocorre com base em estatísticas ou alterações de cardinalidade, com exceção das tabelas inseridas e excluídas que são criadas por gatilhos DML INSTEAD OF . Como essas tabelas são criadas no tempdb, a recompilação das consultas que as acessam depende da configuração de AUTO_UPDATE_STATISTICS in tempdb.

No SQL Server anterior a 2005, as consultas continuam a ser recompiladas com base em alterações de cardinalidade nas tabelas inseridas e excluídas pelo gatilho DML, mesmo quando essa configuração é OFF.

Reutilização de parâmetros e plano de execução

O uso de parâmetros, incluindo marcadores de parâmetros em aplicativos ADO, OLE DB e ODBC, pode aumentar a reutilização de planos de execução.

Advertência

Usar parâmetros ou marcadores de parâmetros para armazenar valores inseridos por utilizadores finais é mais seguro do que concatenar os valores numa string que é executada usando um método da API de acesso a dados, a instrução EXECUTE ou o procedimento armazenado sp_executesql.

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 AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

SELECT *
FROM AdventureWorks2022.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 SQL Server sempre reconheça que as instruções geram essencialmente o mesmo plano e reutilizam os planos, o SQL Server às vezes não deteta isso em instruções Transact-SQL complexas.

Separar constantes da instrução Transact-SQL usando parâmetros ajuda o mecanismo relacional a reconhecer planos duplicados. Você pode usar parâmetros das seguintes maneiras:

• Em Transact-SQL , utilize sp_executesql:

  DECLARE @MyIntParm INT
  SET @MyIntParm = 1
  EXEC sp_executesql
     N'SELECT *
     FROM AdventureWorks2022.Production.Product
     WHERE ProductSubcategoryID = @Parm',
     N'@Parm INT',
     @MyIntParm
  

  Esse método é recomendado para Transact-SQL scripts, procedimentos armazenados ou gatilhos que geram instruções SQL dinamicamente.

• ADO, OLE DB e ODBC usam marcadores de parâmetro. Os marcadores de parâmetro são pontos de interrogação (?) que substituem uma constante em uma instrução SQL e estão vinculados a uma variável de 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âmetro (?):

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

  Os componentes OLE DB Provider e o driver ODBC do SQL Server Native Client, incluídos no SQL Server, utilizam sp_executesql para enviar instruções ao SQL Server quando são usados marcadores de parâmetros em aplicações.

• Para projetar procedimentos armazenados, que usam parâmetros de forma intencional.

Se você não criar parâmetros explicitamente no design de seus aplicativos, também poderá confiar no SQL Server Query Optimizer para parametrizar automaticamente determinadas consultas usando o comportamento padrão de 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.

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.

Parametrização simples

No SQL Server, o uso de parâmetros ou marcadores de parâmetros em instruções Transact-SQL aumenta a capacidade do mecanismo relacional de fazer a correspondência entre novas instruções Transact-SQL com planos de execução existentes compilados anteriormente.

Advertência

Usar parâmetros ou marcadores de parâmetros para armazenar valores introduzidos por utilizadores finais é mais seguro do que concatenar os valores numa string que é executada usando um método de API de acesso a dados, a EXECUTE instrução ou o sp_executesql procedimento armazenado.

Se uma instrução Transact-SQL for executada sem parâmetros, o SQL Server parametrizará a instrução internamente para aumentar a possibilidade de compará-la com um plano de execução existente. Este processo é chamado de parametrização simples. Nas versões do SQL Server anteriores a 2005, o processo era conhecido como parametrização automática.

Considere esta afirmação:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

O valor 1 no final da instrução pode ser especificado como um parâmetro. O mecanismo relacional constrói o plano de execução para este lote como se um parâmetro tivesse sido especificado no lugar do valor 1. Devido a essa parametrização simples, o SQL Server reconhece que as duas instruções a seguir geram essencialmente o mesmo plano de execução e reutiliza o primeiro plano para a segunda instrução:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 4;

Ao processar instruções Transact-SQL complexas, o mecanismo relacional pode ter dificuldade em determinar quais expressões podem ser parametrizadas. Para aumentar a capacidade do mecanismo relacional de realizar correspondência de instruções Transact-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 os operadores aritméticos +, -, *, / ou % são usados para realizar a conversão implícita ou explícita de valores constantes int, smallint, tinyint ou bigint para os tipos de dados float, real, decimal ou numérico, o SQL Server aplica regras específicas para calcular o tipo e a precisão dos resultados da expressão. No entanto, essas regras diferem, dependendo se a consulta é parametrizada ou não. Portanto, expressões semelhantes em consultas podem, em alguns casos, produzir resultados diferentes.

Sob o comportamento padrão de parametrização simples, o SQL Server 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. Isso pode melhorar o desempenho de bancos de dados que apresentam grandes volumes de consultas simultâneas, reduzindo 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

Ao usar uma solução de Object-Relational Mapping (ORM), como o Entity Framework (EF), consultas de aplicativos como árvores de consulta LINQ manuais ou certas consultas SQL brutas podem não ser parametrizadas, o que afeta a reutilização do plano e a capacidade de rastrear consultas no Repositório de Consultas. Para obter mais informações, consulte Cache e parametrização de consultas EF e Consultas SQL Brutas do EF.

Parametrização forçada

Você pode substituir o comportamento padrão de parametrização simples do SQL Server especificando que todas as instruções SELECT, INSERT, UPDATE e DELETE num banco de dados sejam parametrizadas, sujeitas a determinadas limitações. A parametrização forçada é ativada 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 a opção PARAMETERIZATION é definida como FORCED, qualquer valor literal que apareça em uma instrução SELECT, INSERT, UPDATE, ou DELETE enviada, em qualquer forma, é convertido em um 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 SQL Server já reutiliza planos de consulta para essas rotinas.
• Instruções preparadas que já foram parametrizadas no aplicativo 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 Transact-SQL. SELECT( instruções dentro de cursores de API são parametrizadas.)
• Construções de consulta obsoletas.
• Qualquer instrução que seja executada no contexto de ANSI_PADDING ou ANSI_NULLS definida 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.

Além disso, 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 SELECT declaração. Isso inclui SELECT listas de subconsultas e SELECT listas de instruções internas 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 SQL Server considera uma expressão como dobrável constante quando uma das seguintes condições for verdadeira:
  • Nenhuma coluna, variável ou subconsulta aparece na expressão.
  • A expressão contém uma CASE cláusula.
• Argumentos para cláusulas de dicas de consulta. Eles incluem o argumento number_of_rows da dica FAST de consulta, o argumento number_of_processors da dica MAXDOP de consulta e o argumento número da dica MAXRECURSION de consulta.

A parametrização ocorre ao nível das declarações de Transact-SQL individuais. 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 no qual foi originalmente enviada. Se um plano de execução para uma consulta estiver armazenado em cache, você poderá determinar se a consulta foi parametrizada fazendo referência à coluna sql da sys.syscacheobjects exibição de gerenciamento dinâmico. Se uma consulta for parametrizada, os nomes e tipos de dados dos parâmetros vêm antes do texto do lote enviado nesta coluna, como (@1 tinyint).

Observação

Os nomes dos parâmetros são arbitrários. Os usuários ou aplicativos não devem depender de uma ordem de nomenclatura específica. Além disso, o seguinte pode ser alterado entre versões de atualizações do SQL Server e do Service Pack: nomes de parâmetros, a escolha de literais parametrizados e o espaçamento no texto parametrizado.

Tipos de dados de parâmetros

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

• Literais inteiros cujo tamanho caberia dentro do tipo de dados int parametrizam para int. Literais inteiros maiores que são partes de predicados que envolvem qualquer operador de comparação (inclui <, <=, =, !=, >, >=, !<, !>, <>, ALL, ANY, SOME, BETWEEN e IN) parametrizam para numérico(38,0). Literais maiores que não são partes de predicados que envolvem operadores de comparação parametrizam para numéricos cuja precisão é grande o suficiente para suportar seu tamanho e cuja escala é 0.
• Literais numéricos de ponto fixo que fazem parte de predicados com operadores de comparação são parametrizados para valores numéricos cuja precisão é 38 e cuja escala é apenas grande o suficiente para suportar o seu tamanho. Literais numéricos de ponto fixo que não são partes de predicados que envolvem operadores de comparação parametrizam para numéricos cuja precisão e escala são grandes o suficiente para suportar seu tamanho.
• Os literais numéricos de ponto flutuante são parametrizados para float(53).
• Os literais de cadeia de caracteres não-Unicode definem-se como varchar(8000) se o literal de cadeia couber dentro de 8.000 caracteres, e como varchar(max) se for maior que 8.000 caracteres.
• Os literais de cadeia de caracteres Unicode parametrizam para nvarchar(4000) caso o literal tenha até 4.000 caracteres Unicode, e para nvarchar(max) caso o literal exceda 4.000 caracteres.
• Literais binários parametrizam para varbinary(8000) se o literal se encaixa dentro de 8.000 bytes. Se for maior que 8.000 bytes, ele é convertido em varbinary(max).
• Os literais do tipo dinheiro parametrizam para o dinheiro.

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

Considere o seguinte ao definir a PARAMETERIZATION opção como FORÇADO:

• A parametrização forçada, na verdade, altera as constantes literais em uma 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. A parametrização forçada não deve ser usada para ambientes que dependem fortemente de exibições indexadas e índices em colunas computadas. Geralmente, a opção PARAMETERIZATION FORCED só deve ser usada por profissionais experientes de banco de dados, depois de determinarem que isso 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 PARAMETERIZATION opção para FORCED descarrega todos os planos de consulta do cache de planos de um banco de dados, exceto aqueles que estão atualmente a compilar, recompilar ou a executar. Os planos para consultas que estão sendo compiladas ou executadas durante a alteração de configuração são parametrizados na próxima vez que a consulta for executada.
• Definir a opção PARAMETERIZATION é uma operação on-line 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.

Você pode substituir o comportamento da parametrização forçada especificando que a parametrização simples seja tentada em uma única consulta e quaisquer outras que sejam sintaticamente equivalentes, mas difiram apenas em seus valores de parâmetro. 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. Os guias de planos são utilizados para este fim.

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.

Preparar instruções SQL

O mecanismo relacional do SQL Server introduz suporte completo para preparar instruções Transact-SQL antes de serem executadas. Se um aplicativo tiver que executar uma instrução Transact-SQL várias vezes, ele poderá usar a API do banco de dados para fazer o seguinte:

• Prepare a declaração uma vez. Isso compila a instrução Transact-SQL em um plano de execução.
• Execute o plano de execução pré-compilado sempre que seja necessário executar a instrução. Isso evita ter que recompilar a instrução Transact-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. Não faz parte da linguagem Transact-SQL. O modelo de preparação/execução de instruções Transact-SQL é suportado pelo provedor OLE DB do SQL Server Native Client e pelo driver ODBC do SQL Server Native Client. 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 SQL Server compila um plano de execução e retorna um identificador para esse plano para o provedor 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.

As instruções preparadas não podem ser usadas para criar objetos temporários no SQL Server. 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 de preparação/execução pode prejudicar o desempenho. Se uma instrução for executada apenas uma vez, uma execução direta exigirá apenas uma viagem de ida e volta da rede para o servidor. Preparar e executar uma instrução Transact-SQL executada apenas uma vez requer uma viagem de ida e volta extra da rede; uma viagem para preparar a declaração e uma viagem para executá-la.

A preparação de uma instrução é mais eficaz se forem usados marcadores de parâmetro. Por exemplo, suponha que um aplicativo é ocasionalmente solicitado a recuperar informações do produto do AdventureWorks banco de dados de exemplo. Há duas maneiras de o aplicativo fazer isso.

Usando a primeira maneira, o aplicativo pode executar uma consulta separada para cada produto solicitado:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductID = 63;

Usando a segunda maneira, o aplicativo faz o seguinte:

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

   SELECT * FROM AdventureWorks2022.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 forma é mais eficiente quando a instrução é executada mais de três vezes.

No SQL Server, o modelo de preparação/execução não tem vantagem de desempenho significativa em relação à execução direta, devido à maneira como o SQL Server reutiliza os planos de execução. O SQL Server tem algoritmos eficientes para fazer a correspondência entre instruções Transact-SQL atuais e planos de execução gerados para execuções anteriores da mesma instrução Transact-SQL. Se um aplicativo executar uma instrução Transact-SQL com marcadores de parâmetro várias vezes, o SQL Server reutilizará o plano de execução da primeira execução para a segunda e execuções subsequentes (a menos que o plano envelhece a partir do cache do plano). O modelo de preparação/execução ainda tem os seguintes benefícios:

• A identificação de um plano de execução por um identificador é mais eficiente do que os algoritmos usados para corresponder uma instrução Transact-SQL a planos de execução existentes.
• O aplicativo pode controlar quando o plano de execução é criado e quando ele é reutilizado.
• O modelo prepare/execute é portátil para outros bancos de dados, incluindo versões anteriores do SQL Server.

Sensibilidade dos parâmetros

A sensibilidade aos parâmetros, também conhecida como "deteção de parâmetros", refere-se a um processo pelo qual o SQL Server "deteta" os valores de parâmetros atuais durante a compilação ou recompilação e os passa para o Otimizador de Consultas para que eles possam ser usados para gerar planos de execução de consulta potencialmente 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 obter mais informações sobre como solucionar problemas de deteção de parâmetros, consulte:

• Investigue e resolva problemas sensíveis a parâmetros
• Reutilização de Parâmetros e Plano de Execução
• 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 SQL Server usa a OPTION (RECOMPILE) dica, o otimizador de consulta transforma variáveis locais e de parâmetro em constantes de tempo de compilação que podem ser dobradas e reduzidas a literais. Isso significa que, durante a compilação, o otimizador conhece e pode usar os valores de tempo de execução atuais de parâmetros e variáveis locais como eles existem imediatamente antes dessa instrução. O OPTION (RECOMPILE) permite que o otimizador gere um plano de consulta mais otimizado adaptado aos valores específicos e aproveite os 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. Esses valores podem ter sido modificados dentro do procedimento antes de chegar à instrução que inclui RECOMPILE. Esse comportamento pode melhorar o desempenho de consultas com dados de entrada altamente variáveis ou distorcidos.

Variáveis locais

Quando uma consulta usa variáveis locais, o SQL Server não pode detetar seus valores em tempo de compilação, portanto, 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 nenhuma estatística estiver disponível para a coluna, o SQL Server recorrerá a estimativas heurísticas, como assumir 10% seletividade para predicados de igualdade e 30% para desigualdades e intervalos, o que pode levar a planos de execução menos precisos. Aqui está um exemplo de uma consulta que usa uma variável local.

DECLARE @ProductId INT = 100;
SELECT * FROM Products WHERE ProductId = @ProductId;

Nesse caso, o SQL Server não usa o valor 100 para otimizar a consulta. Utiliza uma estimativa geral.

Processamento paralelo de consultas

O SQL Server fornece consultas paralelas para otimizar a execução de consultas e operações de índice para computadores que têm mais de um microprocessador (CPU). Como o SQL Server pode executar uma operação de consulta ou índice em paralelo usando vários threads de trabalho do sistema operacional, a operação pode ser concluída de forma rápida e eficiente.

Durante a otimização de consultas, o SQL Server procura consultas ou operações de índice que possam se beneficiar da execução paralela. Para essas consultas, o SQL Server insere operadores de câmbio 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

Certas construções inibem a capacidade do SQL Server de usar paralelismo em todo o plano de execução, ou partes ou no plano de execução.

As construções que inibem o paralelismo incluem:

• UDFs escalares
  Para obter mais informações sobre funções escalares definidas pelo usuário, consulte Criar funções definidas pelo usuário. A partir do SQL Server 2019 (15.x), o Mecanismo de Banco de Dados do SQL Server tem a capacidade de inserir essas funções e desbloquear o uso de paralelismo durante o processamento de consultas. Para obter mais informações sobre o inlining UDF escalar, consulte Processamento inteligente de consultas em bancos de dados SQL.

• Consulta remota
  Para obter mais informações sobre a consulta remota, consulte Referência de operadores lógicos e físicos do plano de exibição.

• Cursores dinâmicos
  Para obter mais informações sobre cursores, consulte DECLARE CURSOR.

• Consultas recursivas
  Para obter mais informações sobre recursão, consulte Diretrizes para definir e usar expressões de tabela comuns recursivas e recursão no T-SQL.

• Funções com valor de tabela de várias instruções (MSTVFs)
  Para obter mais informações sobre MSTVFs, consulte Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

• Palavra-chave principal
  Para obter mais informações, consulte TOP (Transact-SQL).

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 fixado em 1.
EstimadoDOPIsOne	O grau estimado de paralelismo é 1.
Sem Paralelo com Consulta Remota	O paralelismo não é suportado para consultas remotas.
NoParallelDynamicCursor	Cursores dinâmicos não suportam planos paralelos.
Cursor de Avanço Rápido Paralelo Não Disponível	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.
Criação Paralela de Índice Não Permitida na Edição Não Empresarial (NoParallelCreateIndexInNonEnterpriseEdition)	A criação de índice paralelo não é suportada para edições não Enterprise.
SemPlanosParalelosNaEdiçãoDesktopOuExpress	Planos paralelos não suportados para Desktop e Express Edition.
FunçãoIntrínsecaNãoParalelizável	Query está fazendo referência a uma função intrínseca não paralelizável.
A função definida pelo usuário CLR requer acesso a dados (CLRUserDefinedFunctionRequiresDataAccess)	Paralelismo não suportado para um UDF CLR que requer acesso a dados.
TSQLFunçõesDefinidasPeloUsuárioNãoParalelizáveis	Query está fazendo referência a uma função definida pelo usuário T-SQL que não era paralelizável.
TransaçõesDeVariáveisDeTabelaNãoSuportamTransaçõesAninhadasParalelas	As transações de variáveis de tabela não suportam transações aninhadas paralelas.
ConsultaDMLRetornaSaídaAoCliente	A consulta DML retorna a saída para o cliente e não é paralelizável.
Construção de Índice Online Misturada Serial e Paralela Não Suportada	Combinação não suportada de planos seriais e paralelos para uma única compilação de índice on-line.
NãoFoiPossívelGerarPlanoParaleloVálido	A verificação do plano paralelo falhou, falhando de volta ao serial.
SemParalelismoParaTabelasOtimizadasEmMemória	Paralelismo não suportado para tabelas OLTP In-Memory referenciadas.
Sem Paralelismo Para DML Em Tabela Otimizada na Memória	Paralelismo não suportado para DML em uma tabela OLTP In-Memory.
Não Paralelo Para Módulo Nativamente Compilado	Paralelismo não suportado para módulos referenciados compilados nativamente.
NoRangesResumable Criar	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 é determinado pela complexidade do plano e pelo grau de paralelismo.

O grau de paralelismo (DOP) determina o número máximo de CPUs que estão sendo usadas; isso não significa o número de threads de trabalho que estão sendo usados. O limite de DOP é definido por tarefa. Não é um de solicitação por ou por limite de 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. Mais processadores do que os especificados pelo MAXDOP podem ser usados simultaneamente em qualquer ponto de execução da consulta, quando diferentes tarefas são executadas simultaneamente. Para obter mais informações, consulte o Thread and Task Architecture Guide.

O Otimizador de Consulta do SQL Server não usa um plano de execução paralela para uma consulta se qualquer uma das seguintes condições for verdadeira:

• O plano de execução serial é trivial ou não excede o limite de custo para a configuraçã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. Isso indica que o custo total estimado da subárvore do plano serial o excedeu, e o plano de consulta com o menor custo total estimado da subárvore foi escolhido.

Grau de paralelismo (DOP)

O SQL Server deteta automaticamente o melhor grau de paralelismo para cada instância de uma execução de consulta paralela ou operação DDL (linguagem de definição de dados de índice). Fá-lo 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 de trabalho 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 o grau de paralelismo automaticamente ou abandona completamente o plano paralelo no contexto de carga de trabalho especificado. Em seguida, executa o plano serial (um thread de trabalho).

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 simples, freqüentemente encontradas em aplicativos de processamento de transações, acham que a coordenação adicional 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 usuários podem alterar o valor padrão de 5 usando sp_configure se o teste adequado descobriu 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 de distribuição 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 você cria um índice clusterizado em um modo de exibição, as estatísticas de distribuição não podem ser avaliadas, porque o índice clusterizado 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 uma consulta pode resultar no uso de um plano serial, uma execução posterior da mesma consulta pode resultar em um plano paralelo usando três threads de trabalho e uma terceira execução pode resultar em um plano paralelo usando 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.

Até o SQL Server 2012 (11.x), o operador insert também é executado em série. No entanto, a parte SELECT de uma instrução INSERT 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 SQL Server 2014 (12.x) e do nível de compatibilidade do banco de dados 110, a instrução pode ser executada SELECT ... INTO em paralelo. Outras formas de operadores de inserção funcionam da mesma maneira descrita para o SQL Server 2012 (11.x).

A partir do SQL Server 2016 (13.x) e do nível de compatibilidade da base de dados 130, a instrução INSERT ... SELECT pode ser executada em paralelo ao inserir em heaps ou em índices columnstore clusterizados (CCI), utilizando a dica TABLOCK. Inserções em tabelas temporárias locais (identificadas pelo prefixo #) e tabelas temporárias globais (identificadas por prefixos ##) também são habilitadas para paralelismo usando a dica TABLOCK. Para obter mais informações, consulte INSERT (Transact-SQL).

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. Esta configuração pode ser definida em vários níveis:

1. Nível do servidor, usando a opção de configuração do servidor max degree of parallelism (MAXDOP).
   Aplica-se a: SQL Server

   Observação

   O SQL Server 2019 (15.x) apresenta 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, usando a opção de configuração do grupo de carga de trabalho do MAX_DOPAdministrador de Recursos.
   Aplica-se a: SQL Server

3. Nível de banco de dados, usando a configuração de escopo do banco de dadosMAXDOP.
   Aplica-se a: SQL Server e Banco de Dados SQL do Azure

4. Nível da declaração de consulta ou índice, usando a dica de consulta MAXDOP ou a opção de índice MAXDOP. Por exemplo, você pode usar a opção MAXDOP para controlar, aumentando ou reduzindo, o número de processadores dedicados a uma operação de índice online. Dessa forma, você pode equilibrar os recursos usados por uma operação de índice com os dos usuários simultâneos.
   Aplica-se a: SQL Server e Banco de Dados SQL do Azure

Definir a opção de grau máximo de paralelismo como 0 (padrão) permite que o SQL Server use todos os processadores disponíveis até um máximo de 64 processadores em uma execução de plano paralelo. Embora o SQL Server defina um destino de tempo de execução de 64 processadores lógicos quando a opção MAXDOP é definida como 0, um valor diferente pode ser definido manualmente, se necessário. Definir MAXDOP como 0 para consultas e índices permite que o SQL Server use todos os processadores disponíveis até um máximo de 64 processadores para determinadas consultas ou índices em uma execução de plano paralelo. MAXDOP não é um valor imposto para todas as consultas paralelas, mas sim um destino provisório para todas as consultas qualificadas para paralelismo. Isso significa que, se não houver threads de trabalho suficientes disponíveis em tempo de execução, uma consulta poderá ser executada com um grau menor de paralelismo do que a opção de configuração do servidor MAXDOP.

Sugestão

Para obter mais informações, consulte as recomendações do MAXDOP para obter diretrizes sobre como configurar o MAXDOP no nível do servidor, banco de dados, consulta ou dica.

Exemplo de consulta paralela

A consulta a seguir conta o número de pedidos feitos em um trimestre específico, a partir de 1º de abril de 2000, e no qual pelo menos um item de linha do pedido foi recebido pelo cliente depois da data confirmada. 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 >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/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 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 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 abaixo 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. Isso produz vários fluxos exclusivos. Isso pode ser determinado a partir dos operadores de paralelismo mais próximos acima dos operadores Index Scan e Index Seek, respectivamente. Ambos estão reorganizando o tipo de troca. Ou seja, eles estão apenas reorganizando dados entre os fluxos e produzindo o mesmo número de fluxos em sua saída que eles têm em 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 está reparticionando seus fluxos de entrada usando o valor de L_ORDERKEY como uma 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 está reparticionando seus fluxos de entrada usando o valor de O_ORDERKEY. Como a sua entrada não é classificada nos valores da coluna O_ORDERKEY e esta é a coluna de junção no operador Merge Join, o operador Sort entre o paralelismo e os operadores Merge Join garante que a entrada seja classificada 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 tem dois segmentos de troca, com grau de paralelismo igual a 4, ele usa oito threads de trabalho.

Para obter mais informações sobre os operadores usados neste exemplo, consulte a Referência de operadores lógicos e físicos do Showplan.

Operações paralelas de índice

Os planos de consulta criados para as operações de índice que criam ou reconstroem um índice, ou descartam um índice clusterizado, permitem operações encadeadas paralelas e com vários trabalhadores em computadores com vários microprocessadores.

Observação

As operações de índice paralelo só estão disponíveis no Enterprise Edition, começando com o SQL Server 2008 (10.0.x).

O SQL Server usa os mesmos algoritmos para determinar o grau de paralelismo (o número total de threads de trabalho separados a serem executados) para operações de índice como faz para outras consultas. O grau máximo de paralelismo para uma operação de índice está sujeito à opção de configuração do servidor de grau máximo de paralelismo. Você pode substituir o grau máximo de valor de paralelismo para operações de índice individuais definindo a opção de índice MAXDOP nas instruções CREATE INDEX, ALTER INDEX, DROP INDEX e ALTER TABLE.

Quando o Mecanismo de Banco de Dados do SQL Server cria um plano de execução de índice, o número de operações paralelas é definido como o menor valor entre os seguintes:

• O número de microprocessadores ou CPUs no computador.
• O número especificado na configuração do servidor na opção de grau máximo de paralelismo.
• O número de CPUs que ainda não ultrapassou um limite de trabalho executado para threads de trabalho do SQL Server.

Por exemplo, em um computador que tenha oito CPUs, mas onde o grau máximo de paralelismo é definido como 6, não mais do que seis threads de trabalho paralelos são gerados para uma operação de índice. 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 o seguinte:

• 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 criarão um número de intervalos de chaves igual ao grau de operações paralelas, onde cada intervalo de chaves é 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, o thread do trabalhador coordenador determinará os valores-chave que delimitam quatro conjuntos de linhas com 1 milhão de linhas em cada conjunto. Se não for possível estabelecer intervalos de chaves suficientes para usar todas as CPUs, o grau de paralelismo será reduzido de acordo.
• 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 que todos os threads de trabalho paralelos forem concluídos, o thread de trabalho coordenador conecta as subunidades de índice em um único índice. Esta fase aplica-se apenas a operações de índice offline.

Instruções individuais CREATE TABLE ou ALTER TABLE individuais podem ter várias restrições que exigem que um índice seja criado. 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 Microsoft SQL Server oferece suporte a dois métodos para referenciar fontes de dados OLE DB heterogêneas em instruções Transact-SQL:

• Nomes de servidores vinculados
  Os procedimentos sp_addlinkedserver armazenados do sistema e sp_addlinkedsrvlogin são usados para dar um nome de servidor a uma fonte de dados OLE DB. Os objetos nesses servidores vinculados podem ser referenciados em instruções Transact-SQL usando 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.AdventureWorks2022.HumanResources.Employee;
  

  O nome do servidor vinculado também pode ser especificado em uma OPENQUERY instrução para abrir um conjunto de linhas da fonte de dados OLE DB. Esse conjunto de linhas pode então ser referenciado como uma tabela em instruções Transact-SQL.

• Nomes de conectores ad hoc
  Para referências pouco frequentes a uma fonte de dados, as OPENROWSET funções ou OPENDATASOURCE são especificadas com as informações necessárias para se conectar ao servidor vinculado. O conjunto de linhas pode então ser referenciado da mesma forma que uma tabela é referenciada em Transact-SQL instruções:

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

O SQL Server usa o OLE DB para se comunicar entre o mecanismo relacional e o mecanismo de armazenamento. O mecanismo relacional divide cada instrução Transact-SQL em uma série de operações em conjuntos de linhas OLE DB simples abertos pelo mecanismo de armazenamento a partir das tabelas base. Isso significa que o mecanismo relacional também pode abrir conjuntos de linhas OLE DB simples 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 acessada como um servidor vinculado, um provedor OLE DB deve estar presente no servidor que executa o SQL Server. O conjunto de operações Transact-SQL que podem ser usadas em uma fonte de dados OLE DB específica depende dos recursos do provedor OLE DB.

Para cada instância do SQL Server, os sysadmin membros da função de servidor fixa podem habilitar ou desabilitar o uso de nomes de conector ad hoc para um provedor OLE DB usando a propriedade SQL Server DisallowAdhocAccess . Quando o acesso ad hoc está habilitado, qualquer usuário conectado a essa instância pode executar instruções Transact-SQL contendo nomes de conector ad hoc, fazendo referência a qualquer fonte de dados na rede que possa ser acessada usando esse provedor OLE DB. Para controlar o acesso a fontes de dados, os membros da função podem desabilitar o acesso ad hoc sysadmin para esse provedor OLE DB, fazendo com que os utilizadores tenham acesso apenas às fontes de dados referenciadas por nomes de servidores associados 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.

As consultas distribuídas podem permitir que os usuários acessem outra fonte de dados (por exemplo, arquivos, fontes de dados não relacionais, como o Ative Directory, etc.) usando o contexto de segurança da conta do Microsoft Windows na qual o serviço do SQL Server está sendo executado. O SQL Server assume a identidade do logon apropriadamente para logons do Windows; no entanto, isso não é possível para logons do SQL Server. Isso pode potencialmente permitir que um usuário de consulta distribuída acesse outra fonte de dados para a qual ele não tem permissões, mas a conta sob a qual o serviço do SQL Server está sendo executado tem permissões. Use sp_addlinkedsrvlogin para definir os logins específicos que estão autorizados a acessar o servidor vinculado correspondente. Esse controle não está disponível para nomes ad hoc, portanto, tenha cuidado ao habilitar um provedor OLE DB para acesso ad hoc.

Quando possível, o SQL Server envia operações relacionais, como junções, restrições, projeções, classificações e agrupar por operações, para a fonte de dados OLE DB. O SQL Server não usa como padrão verificar a tabela base no SQL Server e executar as operações relacionais em si. O SQL Server consulta o provedor OLE DB para determinar o nível de gramática SQL ao qual ele oferece suporte e, com base nessas informações, envia por push o maior número possível de operações relacionais para o provedor.

O SQL Server especifica um mecanismo para um provedor OLE DB retornar estatísticas indicando como os valores de chave são distribuídos na fonte de dados OLE DB. Isso permite que o Otimizador de Consulta do SQL Server analise melhor o padrão de dados na fonte de dados em relação aos requisitos de cada instrução Transact-SQL, aumentando a capacidade do Otimizador de Consulta de gerar planos de execução ideais.

Aprimoramentos de processamento de consultas em tabelas e índices particionados

O SQL Server 2008 (10.0.x) melhorou o desempenho do processamento de consultas em tabelas particionadas para muitos planos paralelos, altera a maneira como os planos paralelos e seriais são representados e aprimorou as informações de particionamento fornecidas nos planos de execução em tempo de compilação e execução. Este artigo descreve essas melhorias, fornece orientação sobre como interpretar os planos de execução de consulta de tabelas e índices particionados e fornece práticas recomendadas para melhorar o desempenho da consulta em objetos particionados.

Observação

Até o SQL Server 2014 (12.x), tabelas e índices particionados são suportados apenas nas edições SQL Server Enterprise, Developer e Evaluation. A partir do SQL Server 2016 (13.x) SP1, tabelas e índices particionados também têm suporte na edição Standard do SQL Server.

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

No SQL Server, a representação interna de uma tabela particionada é alterada para que a tabela apareça para o processador de consultas como um índice de várias colunas com PartitionID a coluna principal. Uma coluna computada oculta PartitionID é usada internamente para representar a partição ID que contém uma linha específica. Por exemplo, suponha que a tabela T, definida como T(a, b, c), está particionada na coluna a e tem um índice agrupado na coluna b. No SQL Server, essa tabela particionada é tratada internamente como uma tabela não particionada com o esquema T(PartitionID, a, b, c) e um índice clusterizado na chave (PartitionID, b)composta. Isso permite que o Otimizador de Consultas execute operações de busca com base em PartitionID qualquer tabela ou índice particionado.

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

Além disso, o Otimizador de Consulta é estendido para que uma operação de busca ou verificação com uma condição possa ser feita em PartitionID (como a coluna lógica à esquerda) e possivelmente em outras colunas de chave de índice, e então uma busca de segundo nível, com uma condição diferente, pode ser feita em uma ou mais colunas adicionais, para cada valor distinto que atenda à qualificação para a operação de busca de primeiro nível. Ou seja, essa operação, chamada de skip scan, permite que o Otimizador de Consulta execute uma operação de busca ou verificação com base em uma condição para determinar as partições a serem acessadas e uma operação de busca de índice de segundo nível dentro desse operador para retornar linhas dessas partições que atendem a uma condição diferente. Por exemplo, considere a consulta a seguir.

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

Neste exemplo, suponha que a tabela T, definida como T(a, b, c), está particionada na coluna a e tem um índice clusterizado na coluna b. Os limites de partição para a tabela T são definidos pela seguinte função de partição:

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. Isso identifica as partições a serem acessadas. Dentro de cada partição identificada, o processador então executa uma busca de segundo nível no índice clusterizado na coluna b para encontrar as linhas que atendem à condição T.b = 2 e T.a < 10.

A ilustração a seguir é uma representação lógica da operação 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 busca de primeiro nível para as partições (não mostrada na ilustração) determinou que as partições 1, 2 e 3 atendem à condição de busca implícita pelo particionamento definido para a tabela e o predicado na coluna a. Ou seja, T.a < 10. O caminho percorrido pelo segmento de busca de segundo nível da operação de varredura por salto é ilustrado pela linha curva. Essencialmente, a operação skip scan procura em cada uma dessas partições linhas que atendam à condição b = 2. O custo total da operação de skip scan é o mesmo de três buscas de índice separadas.

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

Os planos de execução de consultas em tabelas e índices particionados podem ser examinados usando as instruções SET Transact-SQL SET SHOWPLAN_XML ou SET STATISTICS XML, ou usando a saída do plano de execução gráfico no SQL Server Management Studio. Por exemplo, você pode exibir o plano de execução em tempo de compilação selecionando Exibir Plano de Execução Estimado na barra de ferramentas do Editor de Consultas e o plano de tempo de execução selecionando Incluir Plano de Execução Real.

Usando essas ferramentas, você pode verificar as seguintes informações:

• As operações como scans, seeks, inserts, updates, merges, e deletes que acessam tabelas ou índices particionados.
• As partições acessadas pela consulta. Por exemplo, a contagem total de partições acessadas e os intervalos de partições contíguas que são acessadas estão disponíveis durante a execução dos planos.
• Quando a operação de skip scan é usada numa operação de procura ou varredura para recuperar dados de uma ou mais partições.

Melhorias nas informações de partição

O SQL Server fornece informações de particionamento aprimoradas para planos de execução em tempo de compilação e 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 seek, scan, insert, update, merge ou delete, é 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 que é usada uma operação de skip scan 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 20080802 AND 20080902;

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

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 é exibido quando está definido como 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, esse atributo é exibido nas propriedades do operador no qual está definido. Na saída XML Showplan, esse atributo aparece como Partitioned="1" no nó RelOp do operador em que está definido.

Novo predicado de busca

Na saída XML Showplan, o SeekPredicateNew elemento aparece no operador no qual 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, essa busca determina as partições que devem ser acessadas para satisfazer as condições da consulta. O segundo SeekKeys item especifica a parte de busca de segundo nível da operação de escanear salto que ocorre dentro de cada partição identificada no procedimento de 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 XML Showplan, é a informação de resumo da partição que aparece no elemento RuntimePartitionSummary no nó RelOp do operador onde está definido. 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>

Exibir as informações de partição usando outros métodos Showplan

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 20080802 AND 20080902.

|--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] >= (20080802)
                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))
                ORDERED FORWARD)

Interpretar planos de execução para pilhas particionadas

Uma pilha particionada é tratada como um índice lógico no ID da partição. A eliminação de partição num amontoado particionado é representada num plano de execução como um operador Table Scan com um predicado SEEK 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 um thread de trabalho termina de ser executado em uma partição, o processador de consultas o atribui à próxima partição até que cada partição tenha recebido um único thread de trabalho. Este é o único caso em que o processador de consultas redistribui 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 consultas alocará um thread de trabalho adicional a algumas partições para usar todos os threads de trabalho disponíveis. Se houver apenas uma partição, todos os threads de trabalho serão atribuídos a essa partição. No diagrama abaixo, há quatro partições e 14 threads de trabalho. Cada partição tem 3 threads de trabalho atribuídos, e duas das partições têm um thread de trabalho adicional, resultando num total de 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 acima sugiram uma maneira direta de alocar threads de trabalho, a estratégia real é mais complexa e leva em conta outras variáveis que ocorrem durante a execução da consulta. Por exemplo, se a tabela for particionada e tiver um índice clusterizado na coluna A e uma consulta tiver a cláusula de predicado WHERE A IN (13, 17, 25), o processador de consultas alocará um ou mais threads de execução para cada um desses três valores de busca (A=13, A=17 e A=25) em vez de cada partição de tabela. Só é necessário executar a consulta nas partições que contêm esses valores, e se todos esses predicados de busca estiverem na mesma partição de tabela, todas as tarefas de trabalho serão atribuídas à mesma partição de tabela.

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

Partições de tabela baseadas na coluna A	Procura a coluna B em cada partição de tabela
Partição de tabela 1: A < 10	B=50, B=100, B=150
Partição de tabela 2: A >= 10 E A < 20	B=50, B=100, B=150
Partição de tabela 3: A >= 20 E 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 de consultas que acessam uma grande quantidade de dados de grandes tabelas e índices particionados, recomendamos as seguintes práticas recomendadas:

• Distribua cada partição em vários discos. Isso é especialmente relevante ao usar discos giratórios.
• 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 consultados não couber na memória, compacte as tabelas e os índices. Isso reduzirá o custo de E/S.
• 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.
• Siga as recomendações de práticas recomendadas no white paper, The Data Loading Performance Guide, ao carregar dados em massa em tabelas particionadas.

Exemplo

O exemplo a seguir cria um banco de dados de teste contendo uma única tabela com sete partições. Use as ferramentas descritas anteriormente ao executar as consultas neste exemplo para exibir informações de particionamento para planos de tempo de compilação e de tempo de execução.

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
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
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 int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() - 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@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 20080802 AND 20080902
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 20080801 AND 20080831
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