Guia da Arquitetura de Processamento de ConsultasQuery Processing Architecture Guide

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine processa consultas em diversas arquiteturas de armazenamento de dados, como tabelas locais, particionadas e distribuídas entre vários servidores.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. Os tópicos a seguir descrevem como o SQL ServerSQL Server processa consultas e otimiza a reutilização de consultas por meio do cache de planos de execução.The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

Modos de execuçãoExecution modes

O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine pode processar instruções Transact-SQLTransact-SQL usando dois modos de processamento diferentes:The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine can process Transact-SQLTransact-SQL statements using two distinct processing modes:

  • Execução em modo de linhaRow mode execution
  • Execução em modo de loteBatch mode execution

Execução em modo de linhaRow mode execution

A execução em modo de linha é um método de processamento de consulta usado com tabelas RDMBS tradicionais, nas quais os dados são armazenados em formato de linha.Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. Quando uma consulta é executada e acessa dados em tabelas com armazenamento em linha, os operadores de árvore de execução e os operadores filho leem cada linha necessária, em todas as colunas especificadas no esquema de tabela.When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. De cada linha que é lida, SQL ServerSQL Server recupera então as colunas que são necessárias para o conjunto de resultados, conforme referenciado por uma instrução SELECT, um predicado JOIN ou um predicado de filtro.From each row that is read, SQL ServerSQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.

Observação

A execução em modo de linha é muito eficiente para cenários OLTP, mas pode ser menos eficiente na verificação de grandes quantidades de dados, por exemplo, em cenários de Data Warehouse.Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.

Execução em modo de loteBatch mode execution

A execução em modo de lote é um método de processamento de consulta usado para processar várias linhas simultaneamente (por isso o termo lote).Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). Cada coluna em um lote é armazenada como um vetor em uma área separada da memória, de modo que o processamento em modo de lote é baseado em vetor.Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. O processamento em modo de lote também usa algoritmos que são otimizados para CPUs de vários núcleos e maior taxa de transferência de memória, características encontradas no hardware moderno.Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

A execução em modo de lote é estreitamente integrada ao formato de armazenamento columnstore e otimizada com base nele.Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. O processamento em modo de lote opera nos dados compactados quando possível e elimina o operador de troca usado pela execução em modo de linha.Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. O resultado é um melhor paralelismo e um desempenho mais rápido.The result is better parallelism and faster performance.

Quando uma consulta é executada em modo de lote e acessa dados em índices columnstore, os operadores de árvore de execução e operadores filho leem várias linhas juntas em segmentos de coluna.When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL ServerSQL Server lê apenas as colunas necessárias para o resultado, conforme referenciado por uma instrução SELECT, predicado JOIN ou predicado de filtro.reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
Para obter mais informações sobre índices columnstore, consulte Arquitetura de índice columnstore.For more information on columnstore indexes, see Columnstore Index Architecture.

Observação

A execução em modo de lote é muito eficiente em cenários de Data Warehouse, em que grandes quantidades de dados são lidas e agregadas.Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

Processamento de instruções SQLSQL Statement Processing

O processamento de uma única instrução Transact-SQLTransact-SQL é o modo mais básico para o SQL ServerSQL Server executar instruções Transact-SQLTransact-SQL.Processing a single Transact-SQLTransact-SQL statement is the most basic way that SQL ServerSQL Server executes Transact-SQLTransact-SQL statements. As etapas usadas para processar uma única instrução SELECT que referencia apenas as tabelas base locais (nenhuma exibição ou tabelas remotas) ilustram o processo básico.The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

Precedência de operador lógicoLogical Operator Precedence

Quando mais de um operador lógico é usado em uma instrução, NOT é avaliado primeiro, em seguida, AND e, finalmente, OR.When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Operadores aritméticos e bit a bit são tratados antes dos operadores lógicos.Arithmetic, and bitwise, operators are handled before logical operators. Para obter mais informações, confira Operator Precedence (Precedência de operador).For more information, see Operator Precedence.

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 em relação a OR.In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over 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.You can change the meaning of the query by adding parentheses to force evaluation of the OR first. A consulta a seguir só encontra produtos nos modelos 20 e 21 que são vermelhos.The following query finds only products under models 20 and 21 that are red.

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

Usar parênteses, até mesmo quando eles 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.Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. Não há penalidade de desempenho significativa usando parênteses.There is no significant performance penalty in using parentheses. O exemplo a seguir é mais legível que o exemplo original, embora eles sejam sintaticamente semelhantes.The following example is more readable than the original example, although they are syntactically the same.

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

Otimizando instruções SELECTOptimizing SELECT statements

Uma instrução SELECT não é de procedimento; ela não determina as etapas exatas que o servidor de banco de dados deve usar para recuperar os dados solicitados.A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. Isso significa que o servidor de banco de dados deve analisar a instrução para determinar o modo mais eficiente para extrair os dados solicitados.This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. Isso é conhecido como otimização da instrução SELECT .This is referred to as optimizing the SELECT statement. O componente que faz isso é chamado de Otimizador de Consulta.The component that does this is called the Query Optimizer. A entrada do Otimizador de Consulta consiste em uma consulta, o esquema de banco de dados (definições de tabela e de índice) e as estatísticas de banco de dados.The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. A saída do Otimizador de Consulta é um plano de execução de consulta, às vezes chamado de plano de consulta ou apenas de plano.The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. O conteúdo de um plano de consulta é descrito posteriormente com mais detalhe neste tópico.The contents of a query plan are described in more detail later in this topic.

As entradas e as saídas do Otimizador de Consulta durante a otimização de uma única instrução SELECT são ilustradas no seguinte diagrama:The inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

query_processor_io

Uma instrução SELECT define apenas o seguinte:A SELECT statement defines only the following:

  • O formato do conjunto de resultados.The format of the result set. Isso é especificado principalmente na lista de seleção.This is specified mostly in the select list. Porém, outras cláusulas como ORDER BY e GROUP BY também afetam a forma final do conjunto de resultados.However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • As tabelas que contêm os dados de origem.The tables that contain the source data. Isso é especificado na cláusula FROM .This is specified in the FROM clause.
  • A forma pela qual as tabelas estão logicamente relacionadas à finalidade da instrução SELECT .How the tables are logically related for the purposes of the SELECT statement. Isso é definido nas especificações de junção, que podem ser exibidas na cláusula WHERE ou em uma cláusula ON seguida de FROM.This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • As condições que as linhas das tabelas de origem devem satisfazer para serem qualificadas para a instrução SELECT .The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. Essas são especificadas nas cláusulas WHERE e HAVING .These are specified in the WHERE and HAVING clauses.

Um plano de execução de consulta é uma definição do seguinte:A query execution plan is a definition of the following:

  • A sequência em que as tabelas de origem são acessadas.The sequence in which the source tables are accessed.
    Normalmente, há muitas sequências pelas quais o servidor de banco de dados pode acessar as tabelas base para criar o conjunto de resultados.Typically, there are many sequences in which the database server can access the base tables to build the result set. Por exemplo, se a instrução SELECT fizesse referência a três tabelas, o servidor de banco de dados poderia acessar TableAprimeiro, usar os dados de TableA para extrair as linhas correspondentes de TableBe usar os dados de TableB para extrair dados de TableC.For example, if the SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. As outras sequências em que o servidor de banco de dados poderia acessar as tabelas são:The other sequences in which the database server could access the tables are:
    TableC, TableB, TableAouTableC, TableB, TableA, or
    TableB, TableA, TableCouTableB, TableA, TableC, or
    TableB, TableC, TableAouTableB, TableC, TableA, or
    TableC, TableA, TableBTableC, TableA, TableB

  • Os métodos usados para extrair dados de cada tabela.The methods used to extract data from each table.
    Geralmente, há métodos diferentes para acessar os dados em cada tabela.Generally, there are different methods for accessing the data in each table. Se forem necessárias apenas algumas linhas com valores de chave específicos, o servidor de banco de dados poderá usar um índice.If only a few rows with specific key values are required, the database server can use an index. Se forem necessárias todas as linhas da tabela, o servidor de banco de dados poderá ignorar os índices e executar um exame na tabela.If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. Se forem necessárias todas as linhas de uma tabela, mas houver um índice cujas colunas de chave estão em um ORDER BY, executando um exame de índice em vez de um exame de tabela, uma classificação separada do conjunto de resultados poderá ser salva.If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. Se uma tabela for muito pequena, os exames de tabela poderão ser o método mais eficiente para quase todos os acessos à tabela.If a table is very small, table scans may be the most efficient method for almost all access to the table.

O processo de selecionar um plano de execução de muitos planos possíveis é chamado de otimização.The process of selecting one execution plan from potentially many possible plans is referred to as optimization. O otimizador de consulta é um dos componentes mais importantes de um sistema de banco de dados SQL.The Query Optimizer is one of the most important components of a SQL database system. Enquanto alguma sobrecarga estiver sendo usada pelo otimizador de consulta para analisar a consulta e selecionar um plano, ela será salva várias vezes quando o otimizador de consulta escolher um plano de execução eficiente.While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. Por exemplo, duas empresas de construção podem oferecer projetos idênticos para uma casa.For example, two construction companies can be given identical blueprints for a house. Se, no início, uma empresa ficar alguns dias planejando como a casa será construída, e a outra empresa começar a construir sem planejamento, a empresa que gasta algumas horas para planejar o projeto provavelmente terminará primeiro.If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

O Otimizador de Consulta do SQL ServerSQL Server é baseado em custo.The SQL ServerSQL Server Query Optimizer is a cost-based Query Optimizer. Cada plano de execução possível tem um custo associado em termos de quantidade de recursos de computação usados.Each possible execution plan has an associated cost in terms of the amount of computing resources used. O otimizador de consulta deve analisar os possíveis planos e escolher o que tenha o menor custo estimado.The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Algumas instruções SELECT complexas têm milhares de planos de execução possíveis.Some complex SELECT statements have thousands of possible execution plans. Nesses casos, o otimizador de consulta não analisa todas as combinações possíveis.In these cases, the Query Optimizer does not analyze all possible combinations. Em vez disso, usa algoritmos complexos para encontrar um plano de execução que tenha um custo razoavelmente próximo do custo mínimo possível.Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

O Otimizador de Consulta do SQL ServerSQL Server não escolhe apenas o plano de execução com o menor custo de recurso, ele escolhe o plano que retorna resultados o mais rápido possível ao usuário com um custo razoável em recursos.The SQL ServerSQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. Por exemplo, o processamento de uma consulta em paralelo normalmente usa mais recursos que o processamento em série, mas completa a consulta de forma mais rápida.For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. O Otimizador de Consulta do SQL ServerSQL Server usará um plano de execução paralelo para retornar os resultados se a carga do servidor não for afetada adversamente.The SQL ServerSQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

O Otimizador de Consulta do SQL ServerSQL Server se baseia nas estatísticas de distribuição ao estimar os custos de recurso de métodos diferentes para extrair informações de uma tabela ou um índice.The SQL ServerSQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. As estatísticas de distribuição são mantidas para colunas e índices, além de reter as informações sobre a densidade1 dos dados subjacentes.Distribution statistics are kept for columns and indexes, and hold information on the density1 of the underlying data. Isso é usado para indicar a seletividade dos valores em um índice ou uma coluna específica.This is used to indicate the selectivity of the values in a particular index or column. Por exemplo, em uma tabela que representa carros, muitos carros têm o mesmo fabricante, mas cada carro tem um VIN (número de identificação de veículo) exclusivo.For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). Um índice no VIN é mais seletivo que um índice no fabricante, porque o VIN tem densidade menor que o fabricante.An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density then manufacturer. Se as estatísticas de índice não forem atuais, o otimizador de consulta poderá não fazer a melhor escolha para o estado atual da tabela.If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. Para saber mais sobre densidades, confira Estatísticas.For more information about densities, see Statistics.

1 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.1 Density defines the distribution of unique values that exist in the data, or the average number of duplicate values for a given column. Conforme a densidade diminui, aumenta a seletividade de um valor.As density decreases, selectivity of a value increases.

O Otimizador de Consulta do SQL ServerSQL Server é importante porque ele habilita o servidor de banco de dados a ajustar dinamicamente conforme as alterações das condições no banco de dados sem exigir a entrada de um programador ou administrador de banco de dados.The SQL ServerSQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. Isso habilita os programadores a se concentrarem na descrição do resultado final da consulta.This enables programmers to focus on describing the final result of the query. Eles podem confiar que o Otimizador de Consulta do SQL ServerSQL Server criará um plano de execução eficiente para o estado do banco de dados toda vez que a instrução for executada.They can trust that the SQL ServerSQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

Processando uma instrução SELECTProcessing a SELECT Statement

As etapas básicas usadas pelo SQL ServerSQL Server para processar uma única instrução SELECT incluem o seguinte:The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. O analisador examina a instrução SELECT e a divide em unidades lógicas, como palavras-chave, expressões, operadores e identificadores.The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. Uma árvore de consulta, às vezes chamada de árvore de sequência, é criada descrevendo as etapas lógicas necessárias para transformar os dados de origem no formato solicitado pelo conjunto de resultados.A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
  3. O otimizador de consulta analisa modos diferentes pelos quais as tabelas de origem podem ser acessadas.The Query Optimizer analyzes different ways the source tables can be accessed. Ele seleciona a série de etapas que retorna os resultados mais rapidamente e usa menos recursos.It then selects the series of steps that returns the results fastest while using fewer resources. A árvore de consulta é atualizada para registrar essa série exata de etapas.The query tree is updated to record this exact series of steps. A versão final, otimizada da árvore de consulta é chamada de plano de execução.The final, optimized version of the query tree is called the execution plan.
  4. O mecanismo relacional é iniciado com a execução do plano de execução.The relational engine starts executing the execution plan. Como as etapas que exigem dados das tabelas base são processadas, o mecanismo relacional solicita que o mecanismo de armazenamento rejeite os dados dos conjuntos de linhas solicitados do mecanismo relacional.As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
  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 ao cliente.The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

Dobragem de constantes e avaliação de expressõesConstant Folding and Expression Evaluation

O SQL ServerSQL Server avalia algumas expressões constantes antecipadamente para melhorar o desempenho de consulta.SQL ServerSQL Server evaluates some constant expressions early to improve query performance. Isto é chamado de dobra constante.This is referred to as constant folding. Uma constante é um Transact-SQLTransact-SQL literal, como 3, 'ABC', ' 2005 – 12 – 31', 1.0e3 ou 0x12345678.A constant is a Transact-SQLTransact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

Expressões dobráveisFoldable Expressions

O SQL ServerSQL Server usa a dobra constante com os seguintes tipos de expressões:SQL ServerSQL Server uses constant folding with the following types of expressions:

  • Expressões aritméticas, como 1+1, 5/3*2, que contêm apenas constantes.Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.
  • Expressões lógicas, como 1=1 and 1>2 AND 3>4, que contêm apenas constantes.Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
  • Funções internas consideradas dobráveis pelo SQL ServerSQL Server, inclusive CAST e CONVERT.Built-in functions that are considered foldable by SQL ServerSQL Server, including CAST and CONVERT. Geralmente, uma função intrínseca será dobrável se for uma função de suas entradas apenas e não outras informações contextuais, como opções SET, configurações de idioma, opções de banco de dados e chaves de codificação.Generally, an intrinsic function is foldable if it is a function of its inputs only and not other contextual information, such as SET options, language settings, database options, and encryption keys. Funções não determinísticas não são dobráveis.Nondeterministic functions are not foldable. Funções internas determinísticas são dobráveis, com algumas exceções.Deterministic built-in functions are foldable, with some exceptions.

Observação

Há uma exceção para tipos de objeto grandes.An exception is made for large object types. Se o tipo de saída do processo de dobra for um tipo de objeto grande (text, image, nvarchar(max), varchar(max) ou varbinary(max)), então SQL ServerSQL Server não dobrará a expressão.If the output type of the folding process is a large object type (text, image, nvarchar(max), varchar(max), or varbinary(max)), then SQL ServerSQL Server does not fold the expression.

Expressões não dobráveisNonfoldable Expressions

Todos os outros tipos de expressão são não dobráveis.All other expression types are not foldable. Especificamente, os tipos seguintes de expressões não são dobráveis:In particular, the following types of expressions are not foldable:

  • Expressões não constantes, cujo resultado depende do valor de uma coluna.Nonconstant expressions such as an expression whose result depends on the value of a column.
  • Expressões cujos resultados dependem de um variável local ou parâmetro, como @x.Expressions whose results depend on a local variable or parameter, such as @x.
  • Funções não determinísticas.Nondeterministic functions.
  • Funções definidas pelo usuário (ambos Transact-SQLTransact-SQL e CLR).User-defined functions (both Transact-SQLTransact-SQL and CLR).
  • Expressões cujos resultados dependem de configurações de idioma.Expressions whose results depend on language settings.
  • Expressões cujos resultados dependem de opções SET.Expressions whose results depend on SET options.
  • Expressões cujos resultados dependem de opções de configuração do servidor.Expressions whose results depend on server configuration options.

Exemplos expressões de constantes desdobráveis e não desdobráveisExamples of Foldable and Nonfoldable Constant Expressions

Considere a consulta a seguir.Consider the following query:

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 opção de banco de dados PARAMETERIZATION não for definida como FORCED para a consulta, então a expressão 117.00 + 1000.00 será avaliada e substituída por seu resultado, 1117.00, antes que a consulta seja compilada.If the PARAMETERIZATION database option is not set to FORCED for this query, then the expression 117.00 + 1000.00 is evaluated and replaced by its result, 1117.00, before the query is compiled. Os benefícios da dobra constante incluem o seguinte:Benefits of this constant folding include the following:

  • A expressão não precisa ser avaliada repetidamente em tempo de execução.The expression does not have to be evaluated repeatedly at run time.
  • O valor da expressão depois de avaliada é usado pelo Otimizador de Consulta para estimar o tamanho do conjunto de resultados da porção da consulta TotalDue > 117.00 + 1000.00.The value of the expression after it is evaluated is used by the Query Optimizer to estimate the size of the result set of the portion of the query 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 SQL ServerSQL Server não dobra expressões que envolvem funções definidas pelo usuário, mesmo quando são determinísticas.On the other hand, if dbo.f is a scalar user-defined function, the expression dbo.f(100) is not folded, because SQL ServerSQL Server does not fold expressions that involve user-defined functions, even if they are deterministic. Para obter mais informações sobre parametrização, consulte Parametrização forçada mais adiante neste artigo.For more information on parameterization, see Forced Parameterization later in this article.

Avaliação de expressãoExpression Evaluation

Além disso, algumas expressões que não são constantes desdobráveis, mas cujos argumentos são conhecidos no tempo de compilação, sejam esses argumentos parâmetros ou constantes, são avaliadas pelo avaliador de tamanho do conjunto de resultados (cardinalidade) que é parte do otimizador durante a otimização.In addition, some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization.

Especificamente, serão avaliados as seguintes funções internas e operadores especiais em tempo de compilação se todas as suas entradas forem conhecidas: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST e CONVERT.Specifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST, and CONVERT. Os seguintes operadores também serão avaliados em tempo de compilação se todas as suas entradas forem conhecidas:The following operators are also evaluated at compile time if all their inputs are known:

  • Operadores aritméticos: +, -, *, /, unary -Arithmetic operators: +, -, *, /, unary -
  • Operadores lógicos: AND, OR, NOTLogical Operators: AND, OR, NOT
  • Operadores de comparação: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULLComparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Nenhuma outra função ou operador será avaliada pelo Otimizador de Consulta durante a estimativa de cardinalidade.No other functions or operators are evaluated by the Query Optimizer during cardinality estimation.

Exemplos avaliação de expressão em tempo de compilaçãoExamples of Compile-Time Expression Evaluation

Considere este procedimento armazenado:Consider this stored procedure:

USE AdventureWorks2014;
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 esperada do conjunto de resultados para a condição OrderDate > @d+1.During optimization of the SELECT statement in the procedure, the Query Optimizer tries to evaluate the expected cardinality of the result set for the condition OrderDate > @d+1. A expressão @d+1 não é uma constante dobrável, porque @d é um parâmetro.The expression @d+1 is not constant-folded, because @d is a parameter. Entretanto, no momento da otimização, o valor do parâmetro é conhecido.However, at optimization time, the value of the parameter is known. Isso permite que o Otimizador de Consulta calcule precisamente o tamanho do conjunto de resultados, o que o ajuda a selecionar um bom plano de consulta.This allows the Query Optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

Agora considere um exemplo semelhante ao anterior, exceto pelo fato de que a variável local @d2 substitui @d+1 na consulta e a expressão é avaliada em uma instrução SET, e não na consulta.Now consider an example similar to the previous one, except that a local variable @d2 replaces @d+1 in the query and the expression is evaluated in a SET statement instead of in the query.

USE AdventureWorks2014;
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 instrução SELECT em MyProc2 é otimizada em SQL ServerSQL Server, o valor de @d2 não é conhecido.When the SELECT statement in MyProc2 is optimized in SQL ServerSQL Server, the value of @d2 is not known. Portanto, o Otimizador de Consulta usa uma estimativa padrão para a seletividade de OrderDate > @d2 (nesse caso, 30 por cento).Therefore, the Query Optimizer uses a default estimate for the selectivity of OrderDate > @d2, (in this case 30 percent).

Processando outras instruçõesProcessing Other Statements

As etapas básicas descritas para o processamento de uma instrução SELECT se aplicam a outras instruções Transact-SQLTransact-SQL, como INSERT, UPDATE e DELETE.The basic steps described for processing a SELECT statement apply to other Transact-SQLTransact-SQL statements such as INSERT, UPDATE, and DELETE. As instruçõesUPDATE e DELETE devem ser direcionadas ao conjunto de linhas a ser modificado ou excluído.UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. 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 instrução SELECT .The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. Ambas as instruções UPDATE e INSERT podem conter instruções SELECT inseridas que fornecem os valores de dados a serem atualizados ou inseridos.The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

Até as instruções DDL (linguagem de definição de dados), como CREATE PROCEDURE ou ALTER TABLE, são resolvidas no final para uma série de operações relacionais nas tabelas de catálogo de sistema e, algumas vezes, (como ALTER TABLE ADD COLUMN) nas tabelas de dados.Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

Tabelas de trabalhoWorktables

Talvez o mecanismo relacional precise criar uma tabela de trabalho para executar uma operação lógica especificada em uma instrução Transact-SQLTransact-SQL.The relational engine may need to build a worktable to perform a logical operation specified in an Transact-SQLTransact-SQL statement. As tabelas de trabalho são tabelas internas usadas para manter resultados intermediários.Worktables are internal tables that are used to hold intermediate results. As tabelas de trabalho são geradas para determinadas consultas GROUP BY, ORDER BYou UNION .Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. Por exemplo, se uma cláusula ORDER BY fizer referência a colunas que não são abordadas por nenhum índice, o mecanismo relacional pode precisar gerar uma tabela de trabalho para classificar o conjunto de resultados na ordem solicitada.For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Algumas vezes as tabelas de trabalho também são usadas como spools que mantêm temporariamente o resultado da execução de uma parte de um plano de consulta.Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. As tabelas de trabalho são criadas em tempdb e são eliminadas automaticamente quando não são mais necessárias.Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Resolução de exibiçãoView Resolution

O processador de consultas do SQL ServerSQL Server trata as exibições indexadas e não indexadas de forma diferente:The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • As linhas de uma exibição indexada são armazenadas no banco de dados no mesmo formato de uma tabela.The rows of an indexed view are stored in the database in the same format as a table. Se o otimizador de consulta decidir usar uma exibição indexada em um plano de consulta, a exibição indexada será tratada da mesma forma que uma tabela base.If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
  • Somente a definição de uma exibição não indexada é armazenada, e não as linhas da exibição.Only the definition of a nonindexed view is stored, not the rows of the view. O Otimizador de Consulta incorpora a lógica da definição de exibição no plano de execução criado para a instrução Transact-SQLTransact-SQL que referencia a exibição não indexada.The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the Transact-SQLTransact-SQL statement that references the nonindexed view.

A lógica usada pelo Otimizador de Consulta do SQL ServerSQL Server para decidir quando usar uma exibição indexada é semelhante à lógica usada para decidir quando usar um índice em uma tabela.The logic used by the SQL ServerSQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. Se os dados na exibição indexada abrangerem toda ou parte da instrução Transact-SQLTransact-SQL e o Otimizador de Consulta determinar que um índice na exibição é o caminho de acesso de baixo custo, o Otimizador de Consulta escolherá o índice independentemente de a exibição ser referenciada pelo nome na consulta.If the data in the indexed view covers all or part of the Transact-SQLTransact-SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

Quando uma instrução Transact-SQLTransact-SQL referencia uma exibição não indexada, o analisador e o otimizador de consulta analisam a origem da instrução Transact-SQLTransact-SQL e a exibição. Depois, as resolvem em um único plano de execução.When an Transact-SQLTransact-SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the Transact-SQLTransact-SQL statement and the view and then resolve them into a single execution plan. Não há um plano para a instrução Transact-SQLTransact-SQL e um plano separado para a exibição.There is not one plan for the Transact-SQLTransact-SQL statement and a separate plan for the view.

Por exemplo, considere a seguinte exibição:For example, consider the following view:

USE AdventureWorks2014;
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 exibição, essas duas instruções Transact-SQLTransact-SQL executam as mesmas operações nas tabelas base e produzem os mesmos resultados:Based on this view, both of these Transact-SQLTransact-SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2014.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 AdventureWorks2014.HumanResources.Employee AS e 
JOIN AdventureWorks2014.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2014.Person.Person AS p
ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

O recurso Plano de Execução do SQL ServerSQL Server Management Studio mostra que o mecanismo relacional cria o mesmo plano de execução para as duas instruções SELECT.The SQL ServerSQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

Usando dicas com exibiçõesUsing Hints with Views

As dicas colocadas em exibições em uma consulta podem entrar em conflito com outras dicas descobertas quando a exibição é expandida para acessar suas tabelas base.Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. Quando isso ocorre, a consulta retorna um erro.When this occurs, the query returns an error. Por exemplo, considere a seguinte exibição que contém uma dica de tabela em sua definição:For example, consider the following view that contains a table hint in its definition:

USE AdventureWorks2014;
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:Now suppose you enter this query:

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

Há uma falha na consulta, porque a dica SERIALIZABLE aplicada na exibição Person.AddrState na consulta é propagada nas tabelas Person.Address e Person.StateProvince na exibição ao ser expandida.The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. No entanto, a expansão da exibição também revela a dica NOLOCK em Person.Address.However, expanding the view also reveals the NOLOCK hint on Person.Address. Como há conflito das dicas SERIALIZABLE e NOLOCK , a consulta resultante está incorreta.Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

As dicas de tabela PAGLOCK, NOLOCK, ROWLOCK, TABLOCKou TABLOCKX entram em conflito umas com as outras, assim como as dicas de tabela HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE .The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

As dicas podem ser propagadas pelos níveis de exibições aninhadas.Hints can propagate through levels of nested views. Por exemplo, suponha que uma consulta se aplique à dica HOLDLOCK em uma v1.For example, suppose a query applies the HOLDLOCK hint on a view v1. Quando v1 é expandida, observamos que a exibição v2 faz parte da sua definição.When v1 is expanded, we find that view v2 is part of its definition. A definição dev2inclui uma dica NOLOCK em uma de suas tabelas base.v2's definition includes a NOLOCK hint on one of its base tables. Mas essa tabela também herda a dica HOLDLOCK da consulta na exibição v1.But this table also inherits the HOLDLOCK hint from the query on view v1. Como há conflito nas dicas NOLOCK e HOLDLOCK , há falha na consulta.Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

Quando a dica FORCE ORDER é usada em uma consulta que contém uma exibição, a ordem de junção das tabelas na exibição é determinada pela posição da exibição na construção ordenada.When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. Por exemplo, a seguinte consulta faz a seleção a partir de três tabelas e uma exibição:For example, the following query selects from three tables and a view:

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 abaixo:And View1 is defined as shown in the following:

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, TableA, TableB, Table3.The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

Resolvendo índices em exibiçõesResolving Indexes on Views

Assim como com qualquer índice, o SQL ServerSQL Server escolhe usar uma exibição indexada em seu plano de consulta apenas se o Otimizador de Consulta determinar que isso é benéfico.As with any index, SQL ServerSQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

Podem ser criadas exibições indexadas em qualquer edição do SQL ServerSQL Server.Indexed views can be created in any edition of SQL ServerSQL Server. Em certas edições de algumas versões do SQL ServerSQL Server, o Otimizador de Consulta considera automaticamente a exibição indexada.In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. Em certas edições de algumas versões do SQL ServerSQL Server, para usar uma exibição indexada, é necessário usar a dica de tabela NOEXPAND.In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. Para fins de esclarecimento, veja a documentação de cada versão.For clarification, see the documentation for each version.

O Otimizador de Consulta do SQL ServerSQL Server usa uma exibição indexada quando as seguintes condições forem atendidas:The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • Estas opções de sessão são definidas como ON:These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • A opção de sessão NUMERIC_ROUNDABORT está definida como OFF.The NUMERIC_ROUNDABORT session option is set to OFF.
  • O otimizador de consulta encontra uma correspondência entre os elementos e as colunas de índice de exibição na consulta, tais como:The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • Predicados de critérios de pesquisa na cláusula WHERESearch condition predicates in the WHERE clause
    • Operações de uniãoJoin operations
    • Funções de agregaçãoAggregate functions
    • CláusulasGROUP BYGROUP BY clauses
    • Referências de tabelaTable references
  • O custo estimado do uso do índice é o custo mais baixo de qualquer mecanismo de acesso considerado pelo otimizador de consulta.The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • Toda tabela referenciada na consulta (diretamente ou ao expandir 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 aplicado na consulta.Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.

Observação

As dicas READCOMMITTED e READCOMMITTEDLOCK sempre são dicas diferentes consideradas nesse contexto, independentemente do nível de isolamento da transação atual.The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

Diferentemente dos requisitos das opções SET e dicas de tabela, essas são as mesmas regras que o otimizador de consulta usa para determinar se um índice de tabela abrange uma consulta.Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. Não é necessário especificar mais nada na consulta para uma exibição indexada a ser utilizada.Nothing else has to be specified in the query for an indexed view to be used.

Uma consulta não precisa referenciar explicitamente uma exibição indexada na cláusula FROM para que o otimizador de consulta use a exibição indexada.A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. Se a consulta tiver referências a colunas nas tabelas base, que também estão presentes na exibição indexada, e o otimizador de consulta estimar que o uso da exibição indexada fornecerá o menor custo de mecanismo de acesso, o otimizador de consulta escolherá a exibição indexada, semelhante ao modo pelo qual escolhe índices de tabela base quando eles não são referenciados diretamente em uma consulta.If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. O otimizador de consulta pode escolher a exibição quando ela contém colunas que não são referenciadas pela consulta, contanto que a exibição ofereça a opção de menor custo para cobrir uma ou mais das colunas especificadas na consulta.The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

O otimizador de consulta trata uma exibição indexada referenciada na cláusula FROM como uma exibição padrão.The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. O otimizador de consulta expande a definição da exibição da consulta no início do processo de otimização.The Query Optimizer expands the definition of the view into the query at the start of the optimization process. Depois, a correspondência da exibição indexada é executada.Then, indexed view matching is performed. 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.The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. O Otimizador de Consulta escolhe a alternativa de menor custo.The Query Optimizer chooses the lowest-cost alternative.

Usando dicas com exibições indexadasUsing Hints with Indexed Views

Você pode evitar que os índices de exibições sejam usados para uma consulta usando a dica de consulta EXPAND VIEWS . Ou, então, pode usar a dica de tabela NOEXPAND para forçar o uso de um índice para uma exibição indexada especificada na cláusula FROM de uma consulta.You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. Porém, deve deixar o otimizador de consulta determinar dinamicamente os melhores métodos de acesso a serem usados para cada consulta.However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. Limite seu uso de EXPAND e NOEXPAND a casos específicos em que os testes têm mostrado que melhoram o desempenho consideravelmente.Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

A opção EXPAND VIEWS especifica que o otimizador de consulta não usa nenhum índice de exibição para a consulta inteira.The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

Quando NOEXPAND é especificado para uma exibição, o otimizador de consulta considera o uso de qualquer índice definido na exibição.When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. ONOEXPAND especificado com a cláusula INDEX() opcional força o otimizador de consulta a usar os índices especificados.NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. ONOEXPAND pode ser especificado apenas para uma exibição indexada e não pode ser especificado para uma exibição não indexada.NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

Quando NOEXPAND ou EXPAND VIEWS não é especificado em uma consulta que contém uma exibição, a exibição é expandida para acessar as tabelas subjacentes.When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. Se a consulta que compõe a exibição tiver quaisquer dicas de tabela, as dicas serão propagadas às tabelas subjacentes.If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (Esse processo é explicado com mais detalhes em Resolução de exibição.) Contanto que o conjunto de dicas existente nas tabelas subjacentes da exibição sejam idênticos, a consulta será elegível para ser correspondida a uma exibição indexada.(This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. Na maioria das vezes, essas dicas corresponderão umas às outras porque estão sendo diretamente herdadas da exibição.Most of the time, these hints will match each other, because they are being inherited directly from the view. No entanto, se a consulta referenciar tabelas em vez de exibições e as dicas aplicadas diretamente nessas tabelas não forem idênticas, a consulta não será elegível para correspondência com uma exibição indexada.However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. Se as dicas INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCKou XLOCK forem aplicadas às tabelas referenciadas na consulta depois da expansão da exibição, a consulta não será elegível para a correspondência da exibição indexada.If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

Se uma dica de tabela na forma de INDEX (index_val[ ,...n] ) fizer referência a uma exibição em uma consulta, e você não especificar a dica NOEXPAND , a dica de índice será ignorada.If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. Para especificar o uso de um determinado índice, use NOEXPAND.To specify use of a particular index, use NOEXPAND.

Geralmente, quando o Otimizador de Consulta corresponde uma exibição indexada a uma consulta, as dicas especificadas nas tabelas ou exibições da consulta são aplicadas diretamente à exibição indexada.Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. Se o otimizador de consulta optar por não usar uma exibição indexada, qualquer dica será propagada diretamente às tabelas referenciadas na exibição.If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. Para saber mais, veja Resolução de exibição.For more information, see View Resolution. Essa propagação se aplica a dicas de união.This propagation does not apply to join hints. Elas são aplicadas somente em sua posição original na consulta.They are applied only in their original position in the query. As dicas de união não são consideradas pelo otimizador de consulta quando há correspondência entre as consultas e as exibições indexadas.Join hints are not considered by the Query Optimizer when matching queries to indexed views. Se um plano de consulta usar uma exibição indexada que corresponde a parte de uma consulta que contém uma dica de junção, esta não será usada no plano.If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

Não são permitidas dicas nas definições de exibições indexadas.Hints are not allowed in the definitions of indexed views. No modo de compatibilidade 80 e superior, o SQL ServerSQL Server ignora as dicas em definições de exibição indexada quando as mantêm, ou ao executar consultas que usam exibições indexadas.In compatibility mode 80 and higher, SQL ServerSQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. Embora o uso de dicas em definições de exibição indexada não produza um erro de sintaxe no modo de compatibilidade 80, elas são ignoradas.Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

Resolvendo exibições particionadas distribuídasResolving Distributed Partitioned Views

O processador de consultas do SQL ServerSQL Server otimiza o desempenho das exibições particionadas distribuídas.The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. O aspecto mais importante de desempenho de exibição particionada distribuída é minimizar a quantidade de dados transferida entre servidores membro.The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

O SQL ServerSQL Server cria planos inteligentes e dinâmicos que usam de forma eficaz as consultas distribuídas para acessar dados de tabelas de membro remoto:SQL ServerSQL Server builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • O Processador de Consultas usa o OLE DB primeiro para recuperar as definições de restrição de verificação de cada tabela de membro.The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. Isso permite ao processador de consultas mapear a distribuição de valores da chave entre as tabelas de membro.This allows the query processor to map the distribution of key values across the member tables.
  • O Processador de consulta compara os principais intervalos especificados em uma cláusula WHERE da instrução Transact-SQLTransact-SQL com o mapa que mostra como as linhas são distribuídas nas tabelas de membro.The Query Processor compares the key ranges specified in an Transact-SQLTransact-SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. O processador de consultas cria um plano de execução de consulta que usa consultas distribuídas para recuperar apenas essas linhas remotas exigidas para completar a instrução Transact-SQLTransact-SQL.The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the Transact-SQLTransact-SQL statement. O plano de execução também é criado de forma que qualquer acesso a tabelas de membro remoto, tanto para dados quanto para metadados, seja adiado até as informações serem exigidas.The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

Por exemplo, considere um sistema em que uma tabela de clientes é particionada entre Server1 (CustomerID de 1 até 3299999), Server2 (CustomerID de 3300000 até 6599999) e Server3 (CustomerID de 6600000 até 9999999).For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Considere o plano de execução criado para esta consulta executada em Server1:Consider the execution plan built for this query executed on Server1:

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

O plano de execução para esta consulta extrai as linhas com valores da chave CustomerID de 3200000 até 3299999 da tabela de membro local, e emite uma consulta distribuída para recuperar as linhas com valores da chave de 3300000 até 3400000 do Server2.The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

O Processador de Consultas do SQL ServerSQL Server também pode criar lógica dinâmica em planos de execução de consulta para instruções Transact-SQLTransact-SQL em que os valores de chave não são conhecidos quando o plano precisa ser criado.The SQL ServerSQL Server Query Processor can also build dynamic logic into query execution plans for Transact-SQLTransact-SQL statements in which the key values are not known when the plan must be built. Por exemplo, considere este procedimento armazenado:For example, consider this stored procedure:

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

O SQL ServerSQL Server não pode prever qual valor de chave será fornecido pelo parâmetro @CustomerIDParameter sempre que o procedimento for executado.SQL ServerSQL Server cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Como o valor da chave não pode ser previsto, o processador de consultas também não pode prever qual tabela de membro precisará ser acessada.Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. Para lidar com isso, o SQL ServerSQL Server cria um plano de execução que tem lógica condicional, conhecido como filtros dinâmicos, para controlar qual tabela de membro será acessada, com base no valor de parâmetro de entrada.To handle this case, SQL ServerSQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. Supondo que o procedimento armazenado GetCustomer foi executado no Server1, a lógica do plano de execução poderá ser representada como mostrado a seguir:Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

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 ServerSQL Server cria esses tipos de planos de execução dinâmicos até para consultas que não são parametrizadas.SQL ServerSQL Server sometimes builds these types of dynamic execution plans even for queries that are not parameterized. O Otimizador de Consulta pode parametrizar uma consulta para que o plano de execução possa ser reutilizado.The Query Optimizer may parameterize a query so that the execution plan can be reused. Se o Otimizador de Consulta parametrizar uma consulta que referencia uma exibição particionada, ele já não poderá supor que as linhas exigidas serão provenientes de uma tabela base especificada.If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. Ele terá de usar filtros dinâmicos no plano de execução.It will then have to use dynamic filters in the execution plan.

Execução de procedimento armazenado e disparadorStored Procedure and Trigger Execution

O SQL ServerSQL Server armazena apenas a origem de procedimentos armazenados e disparadores.SQL ServerSQL Server stores only the source for stored procedures and triggers. Quando um procedimento armazenado ou disparador é executado primeiro, a origem é compilada em um plano de execução.When a stored procedure or trigger is first executed, the source is compiled into an execution plan. Se o procedimento armazenado ou o disparador for executado novamente antes de o plano de execução envelhecer na memória, o mecanismo relacional detectará o plano existente e o reutilizará.If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. Se o plano envelhecer fora da memória, um plano novo será criado.If the plan has aged out of memory, a new plan is built. Esse processo é semelhante ao processo que o SQL ServerSQL Server segue para todas as instruções Transact-SQLTransact-SQL.This process is similar to the process SQL ServerSQL Server follows for all Transact-SQLTransact-SQL statements. A vantagem de desempenho principal que os procedimentos armazenados e os disparadores têm no SQL ServerSQL Server, comparada com lotes de Transact-SQLTransact-SQL dinâmico, é que suas instruções Transact-SQLTransact-SQL são sempre as mesmas.The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic Transact-SQLTransact-SQL is that their Transact-SQLTransact-SQL statements are always the same. Portanto, o mecanismo relacional as corresponde facilmente com qualquer plano de execução existente.Therefore, the relational engine easily matches them with any existing execution plans. O planos de procedimento armazenado e disparador são reutilizados facilmente.Stored procedure and trigger plans are easily reused.

O plano de execução de procedimentos armazenados e disparadores é executado separadamente do plano de execução do lote que chama o procedimento armazenado ou aciona o disparador.The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. Isso permite uma grande reutilização de planos de execução de procedimento armazenado e disparador.This allows for greater reuse of the stored procedure and trigger execution plans.

Reutilização e armazenamento em cache do plano de execuçãoExecution Plan Caching and Reuse

O SQL ServerSQL Server tem um pool de memória usado para armazenar planos de execução e buffers de dados.SQL ServerSQL Server has a pool of memory that is used to store both execution plans and data buffers. A porcentagem do pool alocada a planos de execução ou buffers de dados flutua dinamicamente, dependendo do estado do sistema.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. A parte do pool de memória usada para armazenar os planos de execução é conhecida como cache de planos.The part of the memory pool that is used to store execution plans is referred to as the plan cache.

Os planos de execução do SQL ServerSQL Server têm os componentes principais a seguir:SQL ServerSQL Server execution plans have the following main components:

  • Plano de execução de consulta Query Execution Plan
    A maior parte do plano de execução é uma estrutura de dados somente leitura reentrante usada por qualquer número de usuários.The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. Isso é conhecido como plano de consulta.This is referred to as the query plan. Nenhum contexto de usuário é armazenado no plano de consulta.No user context is stored in the query plan. Nunca há mais de uma ou duas cópias do plano de consulta na memória: uma cópia para todas as execuções em série e outra para todas as execuções paralelas.There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. A cópia paralela cobre todas as execuções paralelas, independentemente do grau de paralelismo.The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • Contexto de execução Execution Context
    Cada usuário que está executando a consulta atualmente tem uma estrutura de dados que retém os dados específicos para a sua execução, como valores de parâmetro.Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. Esta estrutura de dados é conhecida como contexto de execução.This data structure is referred to as the execution context. As estruturas de dados de contexto de execução são reutilizadas.The execution context data structures are reused. Se um usuário executar uma consulta e uma das estruturas não estiver sendo usada, ela será reinicializada com o contexto do usuário novo.If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

Quando qualquer instrução Transact-SQLTransact-SQL for executada no SQL ServerSQL Server, primeiro o Mecanismo relacional examinará o cache de planos para verificar se há um plano de execução existente para a mesma instrução Transact-SQLTransact-SQL.When any Transact-SQLTransact-SQL statement is executed in SQL ServerSQL Server, the Relational Engine first looks through the plan cache to verify that an existing execution plan for the same Transact-SQLTransact-SQL statement exists. A instrução Transact-SQLTransact-SQL será qualificada como existente se ela corresponder a uma instrução Transact-SQLTransact-SQL executada anteriormente com um plano armazenado em cache, caractere por caractere.The Transact-SQLTransact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQLTransact-SQL statement with a cached plan, character per character. O SQL ServerSQL Server reutiliza qualquer plano existente que encontrar, diminuindo as despesas de recompilação da instrução Transact-SQLTransact-SQL.SQL ServerSQL Server reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQLTransact-SQL statement. Se não houver nenhum plano de execução, o SQL ServerSQL Server gerará um plano de execução novo para a consulta.If no existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

Observação

Algumas instruções Transact-SQLTransact-SQL não são armazenadas em cache, como instruções de operação em massa em execução em rowstore ou instruções que contêm literais de cadeia de caracteres maiores que 8 KB.Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size.

SQL ServerSQL Server tem um algoritmo eficiente para localizar planos de execução existentes para qualquer instrução Transact-SQLTransact-SQL específica.has an efficient algorithm to find any existing execution plans for any specific Transact-SQLTransact-SQL statement. Na maioria dos sistemas, os recursos mínimos usados por esta varredura são inferiores aos recursos salvos graças à reutilização de planos existentes em vez da compilação de cada instrução Transact-SQLTransact-SQL.In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every Transact-SQLTransact-SQL statement.

Os algoritmos para corresponder as instruções Transact-SQLTransact-SQL novas a planos de execução existentes não utilizados no cache exigem que todas as referências de objeto sejam totalmente qualificadas.The algorithms to match new Transact-SQLTransact-SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. Por exemplo, suponha que Person é o esquema padrão do usuário que executa as instruções SELECT abaixo.For example, assume that Person is the default schema for the user executing the below SELECT statements. Embora neste exemplo não seja necessário que a tabela Person seja totalmente qualificada para executar, isso significa que a segunda instrução não tem correspondência com um plano existente, mas tem com a terceira:While in this example it is not required that the Person table is fully qualified to execute, it means that the second statement is not matched with an existing plan, but the third is matched:

SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

Removendo planos de execução do cache de planosRemoving Execution Plans from the Plan Cache

Os planos de execução permanecem no cache de planos enquanto houver memória suficiente para armazená-los.Execution plans remain in the plan cache as long as there is enough memory to store them. Quando há pressão de memória, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine usa uma abordagem baseada em custo para determinar quais planos de execução devem ser removidos do cache de planos.When memory pressure exists, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. Para tomar uma decisão baseada em custo, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine aumenta e reduz uma variável de custo atual para cada plano de execução de acordo com os fatores a seguir.To make a cost-based decision, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

Quando um processo de usuário insere um plano de execução no cache, esse processo define o custo atual igual ao custo de compilação da consulta original. Para planos de execução ad hoc, o processo de usuário define o custo atual como zero.When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to 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 como igual ao custo de compilação original; para planos de execução ad hoc, o processo de usuário aumenta o custo atual.Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. Para todos os planos, o valor máximo do custo atual é o custo de compilação original.For all plans, the maximum value for the current cost is the original compile cost.

Quando há pressão de memória, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine responde removendo planos de execução do cache de planos.When memory pressure exists, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine responds by removing execution plans from the plan cache. Para determinar quais planos remover, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examina repetidamente o estado de cada plano de execução e remove planos quando seu custo atual é igual a zero.To determine which plans to remove, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. Um plano de execução com custo atual igual a zero não é removido automaticamente quando há pressão de memória. Ele é removido apenas quando o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examina o plano, e o custo atual é igual a zero.An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examines the plan and the current cost is zero. Ao examinar um plano de execução, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine impulsiona o custo atual em direção a zero por meio da redução do custo atual, se uma consulta não estiver usando o plano no momento.When examining an execution plan, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examina repetidamente os planos de execução até que o suficiente tenha sido removido para atender às necessidades de memória.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. Embora haja pressão de memória, o custo de um plano de execução pode ser aumentado e reduzido mais de uma vez.While memory pressure exists, an execution plan may have its cost increased and decreased more than once. Quando não houver mais pressão de memória, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine para de reduzir o custo atual de planos de execução não utilizados e todos os planos de execução permanecem no cache de planos, mesmo que seu custo seja igual a zero.When memory pressure no longer exists, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine usa o monitor de recursos e os threads de trabalho do usuário para liberar memória do cache de planos em resposta à pressão de memória.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. O monitor de recursos e os threads de trabalho do usuário podem examinar planos em execução simultânea para diminuir o custo de cada plano de execução não utilizado.The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. O monitor de recursos remove planos de execução do cache de planos quando há pressão de memória global.The resource monitor removes execution plans from the plan cache when global memory pressure exists. Ele libera memória para aplicar políticas para a memória do sistema, a memória do processo, a memória do pool de recursos e o tamanho máximo de todos os caches.It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

O tamanho máximo de todos os caches é uma função do tamanho do pool de buffers e não pode exceder a memória máxima do servidor.The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. Para saber mais sobre como configurar a memória máxima do servidor, veja a definição de max server memory em sp_configure.For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

Os threads de trabalho do usuário removem planos de execução do cache de planos quando há pressão de memória de cache único.The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. Eles aplicam políticas de tamanho máximo de cache único e do número máximo de entradas do cache único.They enforce policies for maximum single cache size and maximum single cache entries.

Os exemplos a seguir ilustram quais planos de execução são removidos do cache de planos:The following examples illustrate which execution plans get removed from the plan cache:

  • Um plano de execução é referenciado frequentemente para que seu custo nunca seja zerado.An execution plan is frequently referenced so that its cost never goes to zero. O plano permanece no cache de planos e não é removido a menos que haja pressão de memória e o custo atual seja zero.The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • Um plano de execução ad hoc é inserido e não é referenciado novamente até que haja pressão de memória.An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Como os planos ad hoc são inicializados com um custo atual igual a zero, quando o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examina o plano de execução, ele vê o custo atual igual a zero e remove o plano do cache de planos.Since ad-hoc plans are initialized with a current cost of zero, when the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. O plano de execução ad hoc permanece no cache de planos com um custo atual igual a zero quando não há pressão de memória.The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

Para remover manualmente um único plano ou todos os planos do cache, use DBCC FREEPROCCACHE.To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. Começando pelo SQL Server 2016 (13.x)SQL Server 2016 (13.x), o ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE para limpar o cache (plano) de procedimento para o banco de dados no escopo.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

Recompilando planos de execuçãoRecompiling Execution Plans

Certas alterações em um banco de dados podem fazer com que a execução de um plano seja ineficaz ou inválida, com base no novo estado do banco de dados.Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. O SQL ServerSQL Server detecta as alterações que invalidam um plano de execução e marca o plano como inválido.SQL ServerSQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. Um plano novo deve ser recompilado para a próxima conexão que executar a consulta.A new plan must then be recompiled for the next connection that executes the query. As condições que invalidam um plano incluem o seguinte:The conditions that invalidate a plan include the following:

  • Alterações feitas em uma tabela ou exibição referenciadas pela consulta (ALTER TABLE e ALTER VIEW).Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • As alterações feitas em um único procedimento, o que descartaria todos os planos para esse procedimento do cache (ALTER PROCEDURE).Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Alterações em quaisquer índices usadas pelo plano de execução.Changes to any indexes used by the execution plan.
  • Atualizações em estatísticas usadas pelo plano de execução, geradas explicitamente de uma instrução, como UPDATE STATISTICSou geradas automaticamente.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Cancelando um índice usado pelo plano de execução.Dropping an index used by the execution plan.
  • Uma chamada explícita para sp_recompile.An explicit call to sp_recompile.
  • Números grandes de alterações para chaves (gerados por instruções INSERT ou DELETE de outros usuários que modificam a tabela referenciada pela consulta).Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • Para tabelas com disparadores, se o número de linhas nas tabelas inseridas ou excluídas aumentar consideravelmente.For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executar um procedimento armazenado usando a opção WITH RECOMPILE .Executing a stored procedure using the WITH RECOMPILE option.

A maioria das recompilações é necessária para exatidão da instrução ou para obter planos de execução de consulta potencialmente mais rápidos.Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

No SQL ServerSQL Server 2000, sempre que uma instrução dentro de um lote causar recompilação, o lote inteiro será recompilado, seja enviado por um procedimento armazenado, gatilho, lote ad hoc ou instrução preparada.In SQL ServerSQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. No SQL Server 2005 (9.x)SQL Server 2005 (9.x) e posterior, apenas a instrução dentro do lote que causa a recompilação é recompilada.Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), only the statement inside the batch that causes recompilation is recompiled. Por causa dessa diferença, as contagens de recompilação das versões SQL ServerSQL Server 2000 e posteriores são incomparáveis.Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. Além disso, há mais tipos de recompilações no SQL Server 2005 (9.x)SQL Server 2005 (9.x) e posterior devido ao seu conjunto de recursos expandido.Also, there are more types of recompilations in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later because of its expanded feature set.

A recompilação em nível de instrução beneficia o desempenho porque, na maioria dos casos, um número pequeno de instruções provoca recompilações e as penalidades associadas, em termos de bloqueios e tempo de CPU.Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. Portanto, essas penalidades são evitadas nas outras instruções do lote que não precisam ser recompiladas.These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

O evento estendido do sql_statement_recompile (xEvent) relata recompilações no nível da instrução.The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. Esse xEvent ocorre quando uma recompilação no nível de instrução é exigida por qualquer tipo de lote.This xEvent occurs when a statement-level recompilation is required by any kind of batch. Isso inclui procedimentos armazenados, disparadores, lotes ad hoc e consultas.This includes stored procedures, triggers, ad hoc batches and queries. Lotes podem ser enviados por meio de diversas interfaces, incluindo sp_executesql, SQL dinâmico e os métodos Prepare ou Execute.Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. A coluna recompile_cause do xEvent sql_statement_recompile contém um código de inteiro que indica o motivo da recompilação.The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. A tabela a seguir contém os possíveis motivos:The following table contains the possible reasons:

Esquema alteradoSchema changed Estatísticas alteradasStatistics changed
Compilação adiadaDeferred compile Alteração da opção SETSET option changed
Alteração da tabela temporáriaTemporary table changed Conjunto de linhas remoto alteradoRemote rowset changed
Alteração da permissão FOR BROWSEFOR BROWSE permission changed Ambiente de notificação de consulta alteradoQuery notification environment changed
Alteração da exibição particionadaPartitioned view changed Opções de cursor alteradasCursor options changed
OPTION (RECOMPILE) solicitadoOPTION (RECOMPILE) requested Liberação do plano parametrizadoParameterized plan flushed
Alteração do plano que afeta a versão do banco de dadosPlan affecting database version changed Alteração da política de imposição do plano do Repositório de ConsultasQuery Store plan forcing policy changed
Falha da imposição do plano do Repositório de ConsultasQuery Store plan forcing failed Plano do Repositório de Consultas ausenteQuery Store missing the plan

Observação

Em versões do SQL ServerSQL Server em que o xEvents não está disponível, o evento de rastreamento SQL ServerSQL Server Profiler SP:Recompile pode ser usado para a mesma finalidade do relatório de recompilações no nível de instrução.In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. O evento de rastreamento SQL:StmtRecompile também relata recompilações no nível de instrução e esse evento de rastreamento também podem ser usado para rastrear e depurar recompilações.The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. Considerando que SP:Recompile é gerado apenas para procedimentos armazenados e disparadores, o SQL:StmtRecompile é gerado para procedimentos armazenados, disparadores, lotes ad hoc, lotes que são executados usando sp_executesql, consultas preparadas e SQL dinâmico.Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. A coluna EventSubClass de SP:Recompile e SQL:StmtRecompile contém um código inteiro que indica o motivo da recompilação.The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. Os códigos descritos aqui.The codes are described here.

Observação

Quando a opção do banco de dados AUTO_UPDATE_STATISTICS for definida como ON, as consultas serão recompiladas quando destinadas a tabelas ou exibições indexadas cujas estatísticas foram atualizadas ou cujas cardinalidades foram alteradas significativamente desde a última execução.When the AUTO_UPDATE_STATISTICS database option is set to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. Esse comportamento se aplica a tabelas padrão definidas pelo usuário, tabelas temporárias e tabelas inseridas e excluídas criadas por disparadores de DML.This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. Se o desempenho de consulta for afetado por recompilações excessivas, considere a alteração dessa configuração para OFF.If query performance is affected by excessive recompilations, consider changing this setting to OFF. Quando a opção do banco de dados AUTO_UPDATE_STATISTICS for definida como OFF, não ocorrerá nenhuma recompilação com base em estatísticas ou alterações de cardinalidade, com exceção das tabelas inseridas e excluídas criadas por disparadores de DML INSTEAD OF.When the AUTO_UPDATE_STATISTICS database option is set to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Como essas tabelas são criadas em tempdb, a recompilação de consultas que as acessam depende da configuração de AUTO_UPDATE_STATISTICS em tempdb.Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Observe que no SQL ServerSQL Server 2000, as consultas continuam a recompilação com base nas alterações de cardinalidade para as tabelas inseridas e excluídas do gatilho DML, mesmo quando essa configuração estiver definida como OFF.Note that in SQL ServerSQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF.

Reutilização de Parâmetros e Plano de ExecuçãoParameters and Execution Plan Reuse

O uso de parâmetros, inclusive de marcadores de parâmetro em aplicativos ADO, OLE DB e ODBC, pode aumentar a reutilização de planos de execução.The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Aviso

O uso de parâmetros ou marcadores de parâmetro para manter valores digitados pelo usuário final é mais seguro que a concatenação dos valores em uma cadeia de caracteres executada posteriormente usando um método API de acesso a dados, a instrução EXECUTE ou o procedimento armazenado sp_executesql .Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

A única diferença entre as duas instruções SELECT a seguir são os valores comparados na cláusula WHERE :The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

A única diferença entre os planos de execução dessas consultas é o valor armazenado para a comparação com a coluna ProductSubcategoryID .The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. Enquanto a meta for para o SQL ServerSQL Server sempre reconhecer que as instruções geram essencialmente o mesmo plano e reutilizam os planos, às vezes, o SQL ServerSQL Server não detecta isso em instruções Transact-SQLTransact-SQL complexas.While the goal is for SQL ServerSQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL ServerSQL Server sometimes does not detect this in complex Transact-SQLTransact-SQL statements.

A separação de constantes da instrução Transact-SQLTransact-SQL usando parâmetros ajuda o mecanismo relacional a reconhecer planos duplicados.Separating constants from the Transact-SQLTransact-SQL statement by using parameters helps the relational engine recognize duplicate plans. Você pode usar parâmetros dos seguintes modos:You can use parameters in the following ways:

  • Em Transact-SQLTransact-SQL, usesp_executesql:In Transact-SQLTransact-SQL , use sp_executesql:

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

    Esse método é recomendado para scripts Transact-SQLTransact-SQL, procedimentos armazenados ou gatilhos que geram instruções SQL dinamicamente.This method is recommended for Transact-SQLTransact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • ADO, OLE DB e ODBC usam marcadores de parâmetro.ADO, OLE DB, and ODBC use parameter markers. Marcadores de parâmetro são marcas de interrogação (?) que substituem uma constante em uma instrução SQL e são associados a uma variável de programa.Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. Por exemplo, você faria o seguinte em um aplicativo de ODBC:For example, you would do the following in an ODBC application:

    • Use o SQLBindParameter para associar uma variável de inteiro ao primeiro marcador de parâmetro em uma instrução SQL.Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • Coloque o valor inteiro na variável.Put the integer value in the variable.
    • Execute a instrução, especificando o marcador de parâmetro (?):Execute the statement, specifying the parameter marker (?):
    SQLExecDirect(hstmt, 
       "SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = ?",
       SQL_NTS);
    

    O SQL ServerSQL Server Native Client OLE DB Provider e o driver SQL ServerSQL Server Native Client ODBC incluídos no SQL ServerSQL Server usam sp_executesql para enviar instruções ao SQL ServerSQL Server quando os marcadores de parâmetro são usados em aplicativos.The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • Para criar procedimentos armazenados que usam parâmetros por design.To design stored procedures, which use parameters by design.

Se você não criar parâmetros explicitamente com o design de seus aplicativos, também poderá contar com o Otimizador de Consulta do SQL ServerSQL Server para parametrizar determinadas consultas automaticamente usando o comportamento padrão da parametrização simples.If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL ServerSQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. Outra opção é forçar o Otimizador de Consulta a considerar a parametrização de todas as consultas no banco de dados, configuração a opção PARAMETERIZATION da instrução ALTER DATABASE como FORCED.Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

Quando a parametrização forçada estiver habilitada, a parametrização simples ainda poderá acontecer.When forced parameterization is enabled, simple parameterization can still occur. Por exemplo, a consulta a seguir não pode ser parametrizada de acordo com as regras de parametrização forçada:For example, the following query cannot be parameterized according to the rules of forced parameterization:

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

Porém, ela pode ser parametrizada de acordo com as regras de parametrização simples.However, it can be parameterized according to simple parameterization rules. Quando se tenta usar a parametrização forçada, mas ela falha, há uma tentativa subsequente de parametrização simples.When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

Parametrização SimplesSimple Parameterization

No SQL ServerSQL Server, o uso de parâmetros ou marcadores de parâmetro nas instruções Transact-SQL aumenta a capacidade do mecanismo relacional de corresponder as instruções Transact-SQLTransact-SQL novas com planos de execução existentes compilados anteriormente.In SQL ServerSQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQLTransact-SQL statements with existing, previously-compiled execution plans.

Aviso

O uso de parâmetros ou marcadores de parâmetro para manter valores digitados pelo usuário final é mais seguro que a concatenação dos valores em uma cadeia de caracteres executada posteriormente usando um método API de acesso a dados, a instrução EXECUTE ou o procedimento armazenado sp_executesql .Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

Se uma instrução Transact-SQLTransact-SQL for executada sem parâmetros, o SQL ServerSQL Server parametrizará a instrução internamente para aumentar a possibilidade de correspondência com um plano de execução existente.If a Transact-SQLTransact-SQL statement is executed without parameters, SQL ServerSQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. Esse processo é chamado de parametrização simples.This process is called simple parameterization. No SQL ServerSQL Server 2000, o processo era conhecido como parametrização automática.In SQL ServerSQL Server 2000, the process was referred to as auto-parameterization.

Considere esta instrução:Consider this statement:

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

O valor 1 ao final da instrução pode ser especificado como um parâmetro.The value 1 at the end of the statement can be specified as a parameter. O mecanismo relacional cria o plano de execução para este lote como se um parâmetro tivesse sido especificado no lugar do valor 1.The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Devido a essa parametrização simples, o SQL ServerSQL Server reconhece que as duas instruções a seguir geram essencialmente o mesmo plano de execução e reutilizam o primeiro plano para a segunda instrução:Because of this simple parameterization, SQL ServerSQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

Ao processar instruções Transact-SQLTransact-SQL complexas, o mecanismo relacional pode ter dificuldade em determinar quais expressões podem ser parametrizadas.When processing complex Transact-SQLTransact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. Para aumentar a capacidade do mecanismo relacional de corresponder instruções Transact-SQLTransact-SQL complexas com planos de execução não utilizados existentes, explicitamente especifique os parâmetros que usam marcadores sp_executesql ou de parâmetro.To increase the ability of the relational engine to match complex Transact-SQLTransact-SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

Observação

Quando os operadores aritméticos +, -, *, /, ou % são usados para executar 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 ServerSQL Server aplica regras específicas para calcular o tipo e a precisão dos resultados da expressão.When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL ServerSQL Server applies specific rules to calculate the type and precision of the expression results. Porém, essas regras diferem, dependendo se a consulta for parametrizada ou não.However, these rules differ, depending on whether the query is parameterized or not. Portanto, as expressões semelhantes em consultas podem, em alguns casos, produzir resultados diferentes.Therefore, similar expressions in queries can, in some cases, produce differing results.

No comportamento padrão de parametrização simples, o SQL ServerSQL Server parametriza uma classe relativamente pequena de consultas.Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. No entanto, você pode especificar que todas as consultas de um banco de dados sejam parametrizadas, sujeitas a determinadas limitações, configurando a opção PARAMETERIZATION do comando ALTER DATABASE para FORCED.However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. Isso pode melhorar o desempenho dos bancos de dados que suportam grandes volumes de consultas simultâneas, reduzindo a frequência de compilações de consultas.Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

Alternativamente, você pode especificar que, uma consulta única e quaisquer outras que sejam sintaticamente equivalentes e apenas diferem nos valores de parâmetro, sejam parametrizadas.Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

Parametrização ForçadaForced Parameterization

É possível substituir o comportamento padrão da parametrização simples do SQL ServerSQL Server especificando que todas as instruções SELECT, INSERT, UPDATE e DELETE em um banco de dados tenham parâmetros e sejam sujeitas a determinadas limitações.You can override the default simple parameterization behavior of SQL ServerSQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. A parametrização forçada é habilitada pela configuração da opção PARAMETERIZATION como FORCED na instrução ALTER DATABASE .Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. A parametrização forçada pode melhorar o desempenho de alguns bancos de dados reduzindo a frequência de compilações e recompilações de consulta.Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Os bancos de dados que podem se beneficiar da parametrização forçada geralmente são aqueles em que há suporte a grandes volumes de consultas simultâneas de origens tais como aplicativos de ponto-de-venda.Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

Quando a opção PARAMETERIZATION é definida como FORCED, qualquer valor literal exibido em uma instrução SELECT, INSERT, UPDATEou DELETE , enviado de qualquer forma, é convertido em um parâmetro durante a compilação de consulta.When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. As exceções são literais exibidos nas seguintes construções de consulta:The exceptions are literals that appear in the following query constructs:

  • InstruçõesINSERT...EXECUTE .INSERT...EXECUTE statements.
  • Instruções nos corpos de procedimentos armazenados, gatilhos ou funções definidas pelo usuário.Statements inside the bodies of stored procedures, triggers, or user-defined functions. O SQL ServerSQL Server já reutiliza os planos de consulta para essas rotinas.SQL ServerSQL Server already reuses query plans for these routines.
  • Instruções preparadas que já foram parametrizadas no aplicativo cliente.Prepared statements that have already been parameterized on the client-side application.
  • Instruções que contêm chamadas do método XQuery, onde o método é exibido em um contexto em que seus argumentos normalmente seriam parametrizados, como uma cláusula WHERE .Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. Se o método for exibido em um contexto em que seus argumentos não serão parametrizados, o restante da instrução será parametrizado.If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Instruções dentro de um cursor Transact-SQLTransact-SQL.Statements inside a Transact-SQLTransact-SQL cursor. (As instruçõesSELECT são parametrizadas em cursores de API.)(SELECT statements inside API cursors are parameterized.)
  • Construções da consulta preterida.Deprecated query constructs.
  • Qualquer instrução executada no contexto de ANSI_PADDING ou ANSI_NULLS definida como OFF.Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • Instruções que contêm mais de 2.097 literais elegíveis para parametrização.Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Instruções que fazem referência a variáveis, como WHERE T.col2 >= @bb.Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Instruções que contêm a dica de consulta RECOMPILE .Statements that contain the RECOMPILE query hint.
  • Instruções que contêm uma cláusula COMPUTE .Statements that contain a COMPUTE clause.
  • Instruções que contêm uma cláusula WHERE CURRENT OF .Statements that contain a WHERE CURRENT OF clause.

Além disso, as cláusulas de consulta a seguir não são parametrizadas.Additionally, the following query clauses are not parameterized. Observe que nesses casos, somente as cláusulas não são parametrizadas.Note that in these cases, only the clauses are not parameterized. Outras cláusulas dentro da mesma consulta podem ser elegíveis para parametrização forçada.Other clauses within the same query may be eligible for forced parameterization.

  • A <select_list> de qualquer instrução SELECT.The <select_list> of any SELECT statement. Isso inclui as listas SELECT de subconsultas e listas SELECT dentro de instruções INSERT.This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • Instruções SELECT de subconsulta exibidas dentro de uma instrução IF .Subquery SELECT statements that appear inside an IF statement.
  • As cláusulas L TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTOou FOR XMde uma consulta.The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • Argumentos, diretos ou como subexpressões, para OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXMLou qualquer operador FULLTEXT .Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • Os argumentos pattern e escape_character de uma cláusula LIKE .The pattern and escape_character arguments of a LIKE clause.
  • O argumento style de uma cláusula CONVERT .The style argument of a CONVERT clause.
  • As constantes de número inteiro dentro de uma cláusula IDENTITY .Integer constants inside an IDENTITY clause.
  • Constantes especificadas usando a sintaxe da extensão ODBC.Constants specified by using ODBC extension syntax.
  • Expressões de constantes desdobráveis que são argumentos dos operadores +, -, *, / e %.Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. Ao considerar a elegibilidade da parametrização forçada, o SQL ServerSQL Server considera que uma expressão é de constante dobrável quando qualquer uma das seguintes condições é verdadeira:When considering eligibility for forced parameterization, SQL ServerSQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • Nenhuma coluna, variável ou subconsulta é exibida na expressão.No columns, variables, or subqueries appear in the expression.
    • A expressão contém uma cláusula CASE .The expression contains a CASE clause.
  • Argumentos para cláusulas de dica de consulta.Arguments to query hint clauses. Incluem o argumento number_of_rows da dica de consulta FAST , o argumento number_of_processors da dica de consulta MAXDOP e o argumento number da dica de consulta MAXRECURSION .These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

A parametrização ocorre no nível das instruções Transact-SQLTransact-SQL individuais.Parameterization occurs at the level of individual Transact-SQLTransact-SQL statements. Em outras palavras, são parametrizadas instruções individuais em lote.In other words, individual statements in a batch are parameterized. Após a compilação, uma consulta parametrizada é executada no contexto do lote em que foi enviado originalmente.After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. Se um plano de execução de uma consulta for armazenado em cache, você poderá determinar se a consulta foi parametrizada referenciando a coluna sql da exibição de gerenciamento dinâmico sys.syscacheobjects.If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. Se uma consulta for parametrizada, os nomes e tipos de dados de parâmetros serão exibidos antes do texto do lote enviado nessa coluna, como (@1 tinyint).If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

Observação

Os nomes de parâmetro são arbitrários.Parameter names are arbitrary. Os usuários ou os aplicativos não devem confiar em uma ordem de nomenclatura específica.Users or applications should not rely on a particular naming order. Além disso, o seguinte pode ser alterados entre versões do SQL ServerSQL Server e atualizações do Service Pack: Nomes de parâmetro, a opção de literais com parâmetros e o espaçamento no texto com parâmetros.Also, the following can change between versions of SQL ServerSQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

Tipos de dados de parâmetrosData Types of Parameters

Quando o SQL ServerSQL Server parametriza literais, os parâmetros são convertidos nos seguintes tipos de dados:When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • Literais inteiros cujo tamanho pode ser ajustado no tipo de dados int com parâmetros em int. Os literais inteiros grandes que fazem parte de predicados que envolvem um operador de comparação (incluindo <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN e IN) são parametrizados em numeric (38,0).Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). Os literais grandes que não fazem parte de predicados que envolvem operadores de comparação parametrizados em numeric, cuja precisão é grande o suficiente para oferecer suporte ao seu tamanho e cuja escala é 0.Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • Literais numéricos de ponto fixo que não fazem parte de predicados que envolvem operadores de comparação parametrizados em numeric, cuja precisão é 38 e cuja escala é grande o suficiente para oferecer suporte ao seu tamanho.Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. Literais numéricos de ponto fixo que não fazem parte de predicados que envolvem operadores de comparação parametrizados em numeric, cuja precisão e escala são grandes o suficiente para oferecer suporte ao seu tamanho.Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • Literais numéricos de ponto de flutuação parametrizados em float(53).Floating point numeric literals parameterize to float(53).
  • Literais de cadeia de caracteres não Unicode parametrizados em varchar(8000), caso o literal caiba em 8000 caracteres, e em varchar(max), se ele for maior do que 8000 caracteres.Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • Literais de cadeia de caracteres Unicode parametrizados em nvarchar(4000), caso o literal caiba em 4000 caracteres, e em nvarchar(max), se ele for maior do que 4000 caracteres.Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • Literais binários parametrizados em varbinary(8000), caso o literal caiba em 8000 bytes.Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. Se for maior do que 8000 bytes, será convertido em varbinary(max).If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • Literais de moeda parametrizados em money.Money type literals parameterize to money.

Diretrizes para Uso da Parametrização ForçadaGuidelines for Using Forced Parameterization

Considere as seguintes diretrizes ao definir a opção PARAMETERIZATION como FORCED:Consider the following when you set the PARAMETERIZATION option to FORCED:

  • A parametrização forçada altera as constantes literais em uma consulta para parâmetros ao compilar uma consulta.Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Portanto, o otimizador de consulta poderia escolher planos com qualidade inferior para consultas.Therefore, the Query Optimizer might choose suboptimal plans for queries. Em particular, é menos provável que o otimizador de consulta efetue uma correspondência entre uma consulta uma exibição indexada ou um índice em uma coluna computada.In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. Além disso, ele pode escolher planos com qualidade inferior para consultas inseridas em tabelas particionadas e exibições particionadas distribuídas.It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. A parametrização forçada não deve ser usada em ambientes que dependem excessivamente de exibições indexadas e índices em colunas computadas.Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Via de regra, a opção PARAMETERIZATION FORCED só deve ser usada por administradores de banco de dados experientes depois de determinarem que isso não afeta o desempenho de forma negativa.Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • As consultas distribuídas que referenciam mais de um banco de dados são elegíveis para parametrização forçada, contanto que a opção PARAMETERIZATION seja definida como FORCED no banco de dados cujo contexto está sendo executado pela consulta.Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
  • A definição da opção PARAMETERIZATION como FORCED libera todos os planos de consulta do cache de plano de um banco de dados, menos os que estão sendo compilados, recompilados ou em execução.Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. Os planos de consultas que estiverem sendo compilados ou em execução durante a mudança de configuração serão parametrizados da próxima vez que a consulta for executada.Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • A definição da opção PARAMETERIZATION é uma operação online que não exige nenhum bloqueio exclusivo no nível de banco de dados.Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • A configuração atual da opção PARAMETERIZATION é preservada ao anexar novamente ou restaurar um banco de dados.The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

É possível substituir o comportamento da parametrização forçada especificando a tentativa da parametrização simples em uma única consulta, e em quaisquer outras que sejam sintaticamente equivalentes, mas diferem apenas nos valores de parâmetro.You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Reciprocamente, pode-se especificar a tentativa da parametrização forçada em apenas um conjunto de consultas sintaticamente equivalentes, mesmo se a parametrização forçada estiver desabilitada no banco de dados.Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. Guias de plano são usados para essa finalidade.Plan guides are used for this purpose.

Observação

Quando a opção PARAMETERIZATION é definida como FORCED, o relatório de mensagens de erro pode ser diferente de quando a opção PARAMETERIZATION está configurada para SIMPLE: podem ser relatadas várias mensagens de erro na parametrização forçada, em que poucas mensagens seriam informadas na parametrização simples, e o número de linhas nas quais ocorrem erros pode ser relatado incorretamente.When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

Preparando instruções SQLPreparing SQL Statements

O mecanismo relacional SQL ServerSQL Server apresenta suporte completo na preparação de instruções Transact-SQLTransact-SQL antes de elas serem executadas.The SQL ServerSQL Server relational engine introduces full support for preparing Transact-SQLTransact-SQL statements before they are executed. Se um aplicativo tiver que executar uma instrução Transact-SQLTransact-SQL várias vezes, poderá usar a API do banco de dados para fazer o seguinte:If an application has to execute an Transact-SQLTransact-SQL statement several times, it can use the database API to do the following:

  • Preparar a instrução uma vez.Prepare the statement once. Esse procedimento compila a instrução Transact-SQLTransact-SQL em um plano de execução.This compiles the Transact-SQLTransact-SQL statement into an execution plan.
  • Executar o plano de execução pré-compilado sempre que tiver de executar a instrução.Execute the precompiled execution plan every time it has to execute the statement. Isso evita a necessidade de recompilar a instrução Transact-SQLTransact-SQL em cada execução depois da primeira vez.This prevents having to recompile the Transact-SQLTransact-SQL statement on each execution after the first time.
    A preparação e a execução de instruções são controladas por funções e métodos de API.Preparing and executing statements is controlled by API functions and methods. Elas não fazem parte da linguagem Transact-SQLTransact-SQL.It is not part of the Transact-SQLTransact-SQL language. O modelo de preparação/execução para executar instruções Transact-SQLTransact-SQL é compatível com o SQL ServerSQL Server Native Client OLE DB Provider e o driver SQL ServerSQL Server Native Client ODBC.The prepare/execute model of executing Transact-SQLTransact-SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. Em uma solicitação de preparação, o provedor ou o driver envia a instrução ao SQL ServerSQL Server com uma solicitação para preparar a instrução.On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. O SQL ServerSQL Server compila um plano de execução e retorna um identificador desse plano para o provedor ou driver.SQL ServerSQL Server compiles an execution plan and returns a handle for that plan to the provider or 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.On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

As instruções preparadas não podem ser usadas para criar objetos temporários no SQL ServerSQL Server.Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. As instruções preparadas não podem fazer referência aos procedimentos armazenados do sistema que criam objetos temporários, como tabelas temporárias.Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. Esses procedimentos devem ser executados diretamente.These procedures must be executed directly.

O uso excessivo do modelo de preparação/execução pode diminuir o desempenho.Excess use of the prepare/execute model can degrade performance. 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.If a statement is executed only once, a direct execution requires only one network round-trip to the server. A preparação e a execução de uma instrução Transact-SQLTransact-SQL executadas apenas uma vez exigem uma viagem de ida e volta adicional da rede; uma viagem para preparar a instrução e uma viagem para executá-la.Preparing and executing an Transact-SQLTransact-SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

A preparação de uma instrução é mais eficaz se forem utilizados marcadores de parâmetro.Preparing a statement is more effective if parameter markers are used. Por exemplo, supondo que a um aplicativo seja pedido ocasionalmente a recuperação de informações de produto do banco de dados de exemplo AdventureWorks .For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. Há dois modos para o aplicativo fazer isso.There are two ways the application can do this.

Usando o primeiro modo, o aplicativo pode executar uma consulta separada para cada produto solicitado:Using the first way, the application can execute a separate query for each product requested:

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

Usando o segundo modo, o aplicativo faz o seguinte:Using the second way, the application does the following:

  1. Prepara uma instrução contendo um marcador de parâmetro (?):Prepares a statement that contains a parameter marker (?):
    SELECT * FROM AdventureWorks2014.Production.Product  
    WHERE ProductID = ?;
    
  2. Associa uma variável de programa ao marcador de parâmetro.Binds a program variable to the parameter marker.
  3. Sempre que as informações de produto são necessárias, preenche a variável de associação com o valor da chave e executa a instrução.Each time product information is needed, fills the bound variable with the key value and executes the statement.

O segundo modo é mais eficiente quando a instrução é executada mais de três vezes.The second way is more efficient when the statement is executed more than three times.

No SQL ServerSQL Server, o modelo de preparação/execução não tem uma vantagem de desempenho considerável sobre a execução direta, devido ao modo como o SQL ServerSQL Server reutiliza os planos de execução.In SQL ServerSQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL ServerSQL Server reuses execution plans. O SQL ServerSQL Server tem algoritmos eficientes para corresponder as instruções Transact-SQLTransact-SQL atuais aos planos de execução, que são gerados para execuções anteriores da mesma instrução Transact-SQLTransact-SQL.SQL ServerSQL Server has efficient algorithms for matching current Transact-SQLTransact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQLTransact-SQL statement. Se um aplicativo executar uma instrução Transact-SQLTransact-SQL com marcadores de parâmetro várias vezes, o SQL ServerSQL Server reutilizará o plano de execução da primeira execução para a segunda e as execuções subsequentes (a menos que o plano fique mais antigo que o cache de planos).If an application executes a Transact-SQLTransact-SQL statement with parameter markers multiple times, SQL ServerSQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). O modelo de preparação/execução ainda possui estes benefícios:The prepare/execute model still has these benefits:

  • A localização de um plano de execução por um identificador é mais eficiente que os algoritmos usados para corresponder uma instrução Transact-SQLTransact-SQL aos planos de execução existentes.Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an Transact-SQLTransact-SQL statement to existing execution plans.
  • O aplicativo pode controlar quando o plano de execução é criado e quando é reutilizado.The application can control when the execution plan is created and when it is reused.
  • O modelo de preparação/execução é portátil para outros bancos de dados, inclusive para versões anteriores do SQL ServerSQL Server.The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

Detecção de ParâmetrosParameter Sniffing

“Detecção de parâmetro” refere-se a um processo no qual o SQL ServerSQL Server “fareja” os valores de parâmetro atuais durante a compilação ou recompilação e os passa para o Otimizador de Consulta para que eles podem ser usados para gerar planos de execução de consulta possivelmente mais eficientes."Parameter sniffing" refers to a process whereby SQL ServerSQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

Valores de parâmetro são detectados durante a compilação ou recompilação para os seguintes tipos de lotes:Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • Procedimentos armazenadosStored procedures
  • Consultas enviadas por meio de sp_executesqlQueries submitted via sp_executesql
  • Consultas preparadasPrepared queries

Para obter mais informações sobre como solucionar problemas de detecção de parâmetro incorreto, veja Solucionar problemas de consultas com problemas de plano de execução de consulta sensível a parâmetro.For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

Observação

Para consultas que utilizam a dica RECOMPILE, tanto os valores de parâmetros quanto os valores atuais das variáveis locais são detectados.For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. Os valores detectados (de parâmetros e de variáveis locais) são aqueles que existem no local no lote antes da instrução com a dica RECOMPILE.The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. Especificamente para parâmetros, os valores que acompanha a chamada de invocação de lote não são detectados.In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Processamento paralelo de consultasParallel Query Processing

O SQL ServerSQL 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).SQL ServerSQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Como o SQL ServerSQL Server pode executar uma consulta ou uma operação de índice em paralelo usando vários threads de trabalho do sistema operacional, a operação pode ser executada de forma rápida e eficiente.Because SQL ServerSQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

Durante a otimização da consulta, SQL ServerSQL Server procura consultas ou operações de índice que poderiam se beneficiar da execução paralela.During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. Para essas consultas, o SQL ServerSQL Server insere operadores de troca no plano de execução de consulta para preparar a consulta para a execução paralela.For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. Um operador de troca é um operador em um plano de execução de consulta que fornece gerenciamento de processo, redistribuição de dados e controle de fluxo.An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. O operador de troca inclui como subtipos os operadores lógicos Distribute Streams, Repartition Streamse Gather Streams dos quais um ou mais podem aparecer na saída do Plano de Execução de um plano de consulta para uma consulta paralela.The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

Importante

Determinados construtos inibem a capacidade do SQL ServerSQL Server de aproveitar o paralelismo em todo o plano de execução, ou partes ou o plano de execução.Certain constructs inhibit SQL ServerSQL Server's ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

Os constructos que inibem o paralelismo incluem:Constructs that inhibit parallelism include:

Depois que os operadores de troca são inseridos, o resultado é um plano de execução da consulta paralela.After exchange operators are inserted, the result is a parallel-query execution plan. Um plano de execução de consulta paralela pode usar mais de um thread de trabalho.A parallel-query execution plan can use more than one worker thread. Um plano de execução em série, usado por uma consulta não paralela, usa só um thread de trabalho para sua execução.A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. O número real de threads de trabalho usado por uma consulta paralela é determinado na inicialização da execução do plano de consulta e pela complexidade do plano e seu grau de paralelismo.The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. O grau de paralelismo 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 usadas.Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. O valor do grau de paralelismo é definido no nível de servidor e pode ser modificado usando-se o procedimento armazenado no sistema sp_configure.The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. Esse valor pode ser substituído por consulta individual ou instruções de índice especificando-se a dica de consulta MAXDOP ou a opção de índice MAXDOP .You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

O Otimizador de Consulta do SQL ServerSQL Server não usa um plano de execução paralela para uma consulta se alguma das condições a seguir for verdadeira:The SQL ServerSQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • O custo de execução em série da consulta não é suficientemente alto para se considerar um plano de execução paralelo alternativo.The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • Um plano de execução em série é considerado mais rápido que qualquer plano de execução paralelo possível para a consulta em questão.A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • A consulta contém operadores escalares ou relacionais que não podem ser executados em paralelo.The query contains scalar or relational operators that cannot be run in parallel. Certos operadores podem fazer com que uma seção do plano de consulta seja executada no modo em série ou que todo o plano seja executado no modo em série.Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Grau de ParalelismoDegree of Parallelism

O SQL ServerSQL Server detecta 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) do índice.SQL ServerSQL Server automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. Isso é feito baseado nos seguintes critérios:It does this based on the following criteria:

  1. Se o SQL ServerSQL Server estiver sendo executado em um computador que tenha mais de um microprocessador ou mais de uma CPU, como um computador SMP (multiprocessamento simétrico).Whether SQL ServerSQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    Apenas computadores que têm mais de uma CPU podem usar consultas paralelas.Only computers that have more than one CPU can use parallel queries.

  2. Se há threads de trabalho suficientes disponíveis.Whether sufficient worker threads are available.
    Cada operação de consulta ou índice exige um determinado número de threads de trabalho para execução.Each query or index operation requires a certain number of worker threads to execute. A execução de um plano paralelo exige mais threads de trabalho que um plano serial e o número de threads de trabalho exigidos aumenta conforme o grau de paralelismo.Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. Quando o requisito de thread de trabalho do plano paralelo de um grau específico de paralelismo não puder ser atendido, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine diminuirá automaticamente o grau de paralelismo ou abandonará completamente o plano paralelo no contexto de carga de trabalho especificado.When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. Depois, ele executará o plano consecutivo (um thread de trabalho).It then executes the serial plan (one worker thread).

  3. O tipo de operação de consulta ou de índice executada.The type of query or index operation executed.
    As operações de índice que criam ou reconstroem um índice, ou descartam um índice cluster e as consultas que usam ciclos de CPU frequentemente são as melhores opções para um plano paralelo.Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. Por exemplo, junções de tabelas grandes, agregações grandes e classificação de conjuntos de resultados grandes são boas alternativas.For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. As consultas simples, frequentemente encontradas em aplicativos de processamento de transações, localizam a coordenação adicional exigida para executar uma consulta em paralelo que supera o aumento de desempenho potencial.Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. Para distinguir as consultas que se beneficiam de paralelismo das que não se beneficiam, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine compara o custo estimado da execução da operação de consulta ou índice com o valor limite de custo para paralelismo.To distinguish between queries that benefit from parallelism and those that do not benefit, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Os usuários podem alterar o valor padrão 5 usando sp_configure se os testes adequados descobriram que um valor diferente é mais adequado para a carga de trabalho em execução.Users can change the default value of 5 using sp_configure if proper testing found that a different value is better suited for the running workload.

  4. Se houver um número suficiente de linhas para processar.Whether there are a sufficient number of rows to process.
    Se o otimizador de consulta determinar que o número de linhas é muito baixo, não apresentará os operadores de troca para distribuir as linhas.If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Por conseguinte, os operadores serão executados em série.Consequently, the operators are executed serially. A execução dos operadores em um plano consecutivo evita cenários quando os custos de inicialização, distribuição e coordenação excedem os ganhos alcançados pela execução de operador paralela.Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. Se as estatísticas de distribuição atuais estiverem disponíveis.Whether current distribution statistics are available.
    Se o grau mais alto de paralelismo não for possível, os graus inferiores serão considerados antes de o plano paralelo ser abandonado.If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    Por exemplo, quando você criar um índice cluster em uma exibição, não poderão ser avaliadas estatísticas de distribuição, porque o índice cluster ainda não existirá.For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. Nesse caso, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine não poderá fornecer o grau mais alto de paralelismo para a operação de índice.In this case, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. Porém, alguns operadores, como de classificação e verificação, ainda poderão se beneficiar da execução paralela.However, some operators, such as sorting and scanning, can still benefit from parallel execution.

Observação

As operações de índice paralelas somente estão disponíveis nas edições Enterprise, Developer e Evaluation do SQL ServerSQL Server.Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

No tempo de execução, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine determina se as informações de carga de trabalho de sistema atual e de configuração previamente descritas permitem a execução paralela.At execution time, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. Se a execução paralela estiver garantida, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine determinará o número ideal de threads de trabalho e espalhará a execução do plano paralelo por esses threads de trabalho.If parallel execution is warranted, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. Quando uma operação de consulta ou índice for iniciada executando em threads de trabalho múltiplos para execução paralela, o mesmo número de threads de trabalho será usado até que a operação seja concluída.When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine reexamina o número ideal de decisões de thread de trabalho sempre que um plano de execução for recuperado do cache de planos.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. Por exemplo, uma execução de uma consulta pode resultar no uso de um plano consecutivo, uma execução posterior da mesma consulta pode resultar em um plano paralelo que usa três threads de trabalho e uma terceira execução pode resultar em um plano paralelo que usa quatro threads de trabalho.For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

Em um plano de execução de consulta paralelo, os operadores de inserção, atualização e exclusão são executados em série.In a parallel query execution plan, the insert, update, and delete operators are executed serially. Porém, a cláusula WHERE de uma instrução UPDATE ou DELETE, ou a parte SELECT de uma instrução INSERT pode ser executada em paralelo.However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. As alterações de dados reais são, depois, aplicadas em série ao banco de dados.The actual data changes are then serially applied to the database.

Cursores estáticos e controlados por conjunto de chaves podem ser populados por planos de execução paralelos.Static and keyset-driven cursors can be populated by parallel execution plans. Porém, o comportamento dos cursores dinâmicos só pode ser fornecido por meio da execução consecutiva.However, the behavior of dynamic cursors can be provided only by serial execution. O otimizador de consulta sempre gera um plano de execução consecutivo para uma consulta que faz parte de um cursor dinâmico.The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Substituindo graus de paralelismoOverriding Degrees of Parallelism

Você pode usar a opção de configuração de servidor grau máximo de paralelismo (MAXDOP) (ALTER DATABASE SCOPED CONFIGURATION no Banco de Dados SQLSQL Database) para limitar o número de processadores a serem usados na execução do plano paralelo.You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on Banco de Dados SQLSQL Database ) to limit the number of processors to use in parallel plan execution. O grau máximo da opção paralelismo pode ser substituído por instruções de operação de índice e de consulta individual, especificando a dica de consulta MAXDOP ou a opção de índice MAXDOP.The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP fornece mais controle sobre operações de índice e consultas individuais.MAXDOP provides more control over individual queries and index operations. Por exemplo, você pode usar a opção MAXDOP para controlar, aumentando ou reduzindo, o número de processadores dedicado a uma operação de índice online.For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. Desse modo, você pode equilibrar os recursos usados por uma operação de índice com aquele dos usuários simultâneos.In this way, you can balance the resources used by an index operation with those of the concurrent users.

A configuração da opção de grau máximo de paralelismo como 0 (padrão) permite que o SQL ServerSQL Server use todos os processadores disponíveis até um máximo de 64 processadores em uma execução de plano paralelo.Setting the max degree of parallelism option to 0 (default) enables SQL ServerSQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. Embora SQL ServerSQL 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.Although SQL ServerSQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. Configurar MAXDOP como 0 para consultas e índices permite ao SQL ServerSQL Server usar todos os processadores disponíveis até um máximo de 64 processadores para as consultas ou índices específicos em uma execução de plano paralela.Setting MAXDOP to 0 for queries and indexes allows SQL ServerSQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. MAXDOP não é um valor de imposto para todas as consultas em paralelo, mas sim um destino provisório para todas as consultas qualificadas para paralelismo.MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. Isso significa que, se não houver threads de trabalho disponíveis suficientes no tempo de execução, uma consulta poderá ser executada com um grau menor de paralelismo que a opção da configuração de servidor MAXDOP.This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

Consulte este artigo do Suporte da Microsoft para ver as práticas recomendadas de configuração do MAXDOP.Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

Exemplo de consulta paralelaParallel Query Example

A consulta a seguir conta o número de ordens emitidas em um trimestre específico, iniciando no dia 1º de abril de 2000, e no qual pelo menos um item de linha da ordem foi recebido pelo cliente depois da data confirmada.The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. Essa consulta lista a contagem de tais ordens agrupadas por cada prioridade de ordem e classificada em ordem de prioridade crescente.This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

Esse exemplo usa nomes teóricos de tabela e de coluna.This example uses theoretical table and column names.

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 índices a seguir estão definidos nas tabelas lineitem e orders:Assume the following indexes are defined on the lineitem and orders tables:

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 há um possível plano paralelo gerado para a consulta mostrada anteriormente:Here is one possible parallel plan generated for the query previously shown:

|--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.The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

parallel_plan

O plano paralelo contém três operadores de paralelismo.The parallel plan contains three parallelism operators. O operador Index Seek do índice o_datkey_ptr e o operador Index Scan do índice l_order_dates_idx são executados em paralelo.Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. Isso produz vários fluxos exclusivos.This produces several exclusive streams. Isso pode ser determinado com base nos operadores Parallelism mais próximos acima dos operadores Index Scan e Index Seek, respectivamente.This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Ambos estão reparticionando o tipo de troca.Both are repartitioning the type of exchange. Ou seja, eles estão apenas embaralhando novamente os dados entre os fluxos e produzindo na saída o mesmo número de fluxos existente na entrada.That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. Esse número de fluxos é igual ao grau de paralelismo.This number of streams is equal to the degree of parallelism.

O operador de paralelismo acima do operador l_order_dates_idx Index Seek está reparticionando seus fluxos de entrada usando o valor de L_ORDERKEY como chave.The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. Desse modo, os mesmos valores de L_ORDERKEY terminam no mesmo fluxo de saída.In this way, the same values of L_ORDERKEY end up in the same output stream. Ao mesmo tempo, os fluxos de saída mantêm a ordem na coluna L_ORDERKEY para atender o requisito de entrada do operador Merge Join.At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

O operador de paralelismo acima do operador Index Seek está reparticionando seus fluxos de entrada usando o valor de O_ORDERKEY.The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. Como 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 os operadores de paralelismo e Merge Join verificam se a entrada é classificada para o operador Merge Join nas colunas de junção.Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. O operador Sort, assim como o operador Merge Join, é executado em paralelo.The Sort operator, like the Merge Join operator, is performed in parallel.

O operador de paralelismo superior reúne resultados de vários fluxos em um único fluxo.The topmost parallelism operator gathers results from several streams into a single stream. As agregações parciais executadas pelo operador Stream Aggregate abaixo do operador de paralelismo são acumuladas em um único valor SUM de cada valor diferente de O_ORDERPRIORITY no operador Stream Aggregate acima do operador de paralelismo.Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. Como esse plano tem dois segmentos de troca com grau de paralelismo igual a 4, ele usa oito threads de trabalho.Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

Para obter mais informações sobre os operadores usados neste exemplo, consulte a Referência de operadores físicos e lógicos do plano de execução.For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

Operações de índice paraleloParallel Index Operations

Os planos de consulta criados para as operações de índice que criam ou recompilar um índice, ou removem um índice clusterizado, permitem operações multi-threaded de trabalho paralelas em computadores que tenham vários microprocessadores.The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

Observação

As operações de índice paralelas somente estão disponíveis no Enterprise Edition, a partir de SQL Server 2008SQL Server 2008.Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

O SQL ServerSQL 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 que em outras consultas.SQL ServerSQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. O grau máximo de paralelismo para uma operação de índice está sujeito à opção de configuração de servidor grau máximo de paralelismo .The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. É possível substituir o valor do grau máximo 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.You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

Quando o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine cria um plano de execução de índice, o número de operações paralelas é definido como o menor valor entre:When the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • O número de microprocessadores ou CPUs no computador.The number of microprocessors, or CPUs in the computer.
  • O número especificado na opção de configuração de servidor grau máximo de paralelismo.The number specified in the max degree of parallelism server configuration option.
  • O número de CPUs que ainda não ultrapassou o limite de trabalho executado para threads de trabalho SQL ServerSQL Server.The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

Por exemplo, em um computador que tem oito CPUs, mas que o grau máximo de paralelismo está definido como 6, não são gerados mais do que seis threads de trabalho paralelos para uma operação de índice.For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. Se cinco das CPUs no computador excederem o limite de trabalho do SQL ServerSQL Server quando um plano de execução de índice for criado, o plano de execução especificará somente três threads de trabalho paralelos.If five of the CPUs in the computer exceed the threshold of SQL ServerSQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

As fases principais de uma operação de índice paralela incluem o seguinte:The main phases of a parallel index operation include the following:

  • Um thread de trabalho coordenador que examina a tabela de forma rápida e aleatória para calcular a distribuição das chaves do índice.A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. O thread de trabalho coordenador estabelece os limites de chave que criarão um número de intervalos de chave igual ao grau de operações paralelas, em que cada intervalo de chave é calculado para cobrir números semelhantes de linhas.The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. Por exemplo, se houver quatro milhões de linhas na tabela e o grau de paralelismo for 4, o thread de trabalho coordenador determinará os valores de chave que delimitam quatro conjuntos de linhas com 1 milhão de linhas em cada conjunto.For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. Se não for possível estabelecer intervalos de chave suficientes para usar todas as CPUs, o grau de paralelismo será reduzido adequadamente.If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • O thread de trabalho coordenador despacha um número de threads de trabalho igual para o grau de operações paralelas e espera que esses threads de trabalho concluam o trabalho deles.The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. Cada thread de trabalho examina a tabela base usando um filtro que recupera apenas as linhas com valores de chave dentro do intervalo atribuído ao thread de trabalho.Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. Cada thread de trabalho cria uma estrutura de índice para as linhas em seu intervalo de chave.Each worker thread builds an index structure for the rows in its key range. No caso de um índice particionado, cada thread de trabalho cria um número especificado de partições.In the case of a partitioned index, each worker thread builds a specified number of partitions. Não são compartilhadas partições entre threads de trabalho.Partitions are not shared among worker threads.
  • Após a conclusão de todos os threads de trabalho paralelos, o thread de trabalho coordenador conecta as subunidades de índice em um único índice.After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. Essa fase só se aplica a operações de índice offline.This phase applies only to offline index operations.

As instruções CREATE TABLE ou ALTER TABLE individuais podem ter várias restrições que exigem a criação de um índice.Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. Essas operações de criação de vários índices 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 com várias CPUs.These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

Arquitetura de consulta distribuídaDistributed Query Architecture

O Microsoft SQL ServerSQL Server oferece suporte a dois métodos para referenciar fontes de dados OLE DB heterogêneas em instruções Transact-SQLTransact-SQL:Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQLTransact-SQL statements:

  • Nomes de servidor vinculadoLinked server names
    Os procedimentos armazenados do sistema sp_addlinkedserver e sp_addlinkedsrvlogin são usados para fornecer um nome de servidor a uma fonte de dados OLE DB.The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. Os objetos desses servidores vinculados podem ser referenciados nas instruções Transact-SQLTransact-SQL que usam nomes de quatro partes.Objects in these linked servers can be referenced in Transact-SQLTransact-SQL statements using four-part names. Por exemplo, se um nome do servidor vinculado do DeptSQLSrvr for definido em relação a outra instância do SQL ServerSQL Server, as seguintes referências de instrução farão referência a uma tabela naquele servidor:For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL ServerSQL Server, the following statement references a table on that server:

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

    O nome de servidor vinculado também pode ser especificado em uma instrução OPENQUERY para abrir um conjunto de linhas da fonte de dados OLE DB.The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. Esse conjunto de linhas pode ser referenciado como uma tabela nas instruções Transact-SQLTransact-SQL.This rowset can then be referenced like a table in Transact-SQLTransact-SQL statements.

  • Nomes de conector ad hocAd hoc connector names
    Para referências de pouca frequência a uma fonte de dados, são especificadas as funções OPENROWSET ou OPENDATASOURCE com as informações necessárias para a conexão com o servidor vinculado.For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. O conjunto de linhas pode ser referenciado do mesmo modo que uma tabela é referenciada nas instruções Transact-SQLTransact-SQL:The rowset can then be referenced the same way a table is referenced in Transact-SQLTransact-SQL statements:

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

O SQL ServerSQL Server usa o OLE DB para se comunicar entre o mecanismo relacional e o mecanismo de armazenamento.SQL ServerSQL Server uses OLE DB to communicate between the relational engine and the storage engine. O mecanismo relacional divide cada instrução Transact-SQLTransact-SQL em uma série de operações nos conjuntos de linhas OLE DB simples abertos pelo mecanismo de armazenamento das tabelas base.The relational engine breaks down each Transact-SQLTransact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. Isso significa que o mecanismo relacional também pode abrir os conjuntos de linhas OLE DB simples em qualquer fonte de dados OLE DB.This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
O mecanismo relacional usa a API (interface de programação de aplicativo) do OLE DB para abrir os conjuntos de linhas em servidores vinculados, buscar as linhas e gerenciar as transações.The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

Para cada fonte de dados OLE DB acessada como um servidor vinculado, é necessário que um provedor OLE DB esteja presente no servidor que executa o SQL ServerSQL Server.For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL ServerSQL Server. O conjunto de operações Transact-SQLTransact-SQL que pode ser usado com uma fonte de dados OLE DB específica depende dos recursos do provedor OLE DB.The set of Transact-SQLTransact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

Para cada instância do SQL ServerSQL Server, os membros da função de servidor fixa sysadmin podem habilitar ou desabilitar o uso de nomes de conector ad hoc para um provedor OLE DB que usa a propriedade DisallowAdhocAccess do SQL ServerSQL Server.For each instance of SQL ServerSQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL ServerSQL Server DisallowAdhocAccess property. Quando o acesso ad hoc é habilitado, qualquer usuário com logon naquela instância pode executar instruções Transact-SQLTransact-SQL que contêm nomes de conector ad hoc, fazendo referência a qualquer fonte de dados na rede que pode ser acessada usando aquele provedor OLE DB.When ad-hoc access is enabled, any user logged on to that instance can execute Transact-SQLTransact-SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. Para controlar o acesso a fontes de dados, os membros da função sysadmin podem desabilitar o acesso ad-hoc ao provedor OLE DB, limitando os usuários às fontes de dados referenciadas pelos nomes de servidores vinculados definidos pelos administradores.To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. Por padrão, o acesso ad hoc é habilitado para o provedor OLE DB do SQL ServerSQL Server e desabilitado para todos os outros provedores OLE DB.By default, ad-hoc access is enabled for the SQL ServerSQL Server OLE DB provider, and disabled for all other OLE DB providers.

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 Active Directory e assim por diante) que usa o contexto de segurança da conta do Microsoft Windows no qual o serviço do SQL ServerSQL Server está sendo executado.Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. O SQL ServerSQL Server representa o logon adequadamente para logons do Windows; porém, isso não é possível para logons do SQL ServerSQL Server.SQL ServerSQL Server impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. Isso pode permitir potencialmente 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 em que o serviço SQL ServerSQL Server está sendo executada tem permissões.This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL ServerSQL Server service is running does have permissions. Use sp_addlinkedsrvlogin para definir os logons específicos autorizados para acessar o servidor vinculado correspondente.Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. Esse controle não está disponível para nomes ad hoc, portanto, tome cuidado ao habilitar um provedor OLE DB para acesso ad hoc.This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

Quando possível, o SQL ServerSQL Server envia operações relacionais como junções, restrições, projeções, classificações e operações de agrupar por para a fonte de dados OLE DB.When possible, SQL ServerSQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. O SQL ServerSQL Server não assume o padrão de examinar a tabela base no SQL ServerSQL Server e executar as operações relacionais em si.SQL ServerSQL Server does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. O SQL ServerSQL Server consulta o provedor OLE DB para determinar o nível de gramática SQL ao qual ele dá suporte e, com base nessas informações, envia o máximo possível de operações relacionais para o provedor.SQL ServerSQL Server queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

O SQL ServerSQL Server especifica um mecanismo para um provedor OLE DB retornar estatísticas que indicam como os valores de chave são distribuídos em uma fonte de dados OLE DB.SQL ServerSQL Server specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. Isso permite ao Otimizador de Consulta do SQL ServerSQL Server analisar melhor o padrão de dados na fonte de dados em relação aos requisitos de cada instrução Transact-SQLTransact-SQL, aumentando a capacidade do Otimizador de Consulta de gerar planos de execução otimizados.This lets the SQL ServerSQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each Transact-SQLTransact-SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

Aperfeiçoamentos de processamento de consultas em tabelas e índices particionadosQuery Processing Enhancements on Partitioned Tables and Indexes

O SQL Server 2008SQL Server 2008 melhorou o desempenho do processamento de consultas em tabelas particionadas para muitos planos paralelos, alterou a maneira como os planos paralelos e seriais são representados e aprimorou as informações de particionamento fornecidas nos planos de execução de tempo de compilação e tempo de execução.SQL Server 2008SQL Server 2008 improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. Este tópico descreve esses aperfeiçoamentos, fornece orientação sobre como interpretar os planos de execução de consultas de tabelas e índices particionados e fornece as práticas recomendadas para aperfeiçoar o desempenho de consultas em objetos particionados.This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

Observação

Há suporte para tabelas e índices particionados apenas nas edições Enterprise, Developer e Evaluation do SQL ServerSQL Server.Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

Operação de busca com reconhecimento de nova partiçãoNew Partition-Aware Seek Operation

No SQL ServerSQL Server, a representação interna de uma tabela particionada é alterada para que a tabela pareça ao processador de consultas um índice de várias colunas com PartitionID como coluna principal.In SQL ServerSQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID é uma coluna computada oculta, usada internamente para representar o ID da partição que contém uma linha específica.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. Por exemplo, suponha que a tabela T, definida como T(a, b, c), seja particionada na coluna a, e tenha um índice clusterizado na coluna b.For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. No SQL ServerSQL 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 de composição (PartitionID, b).In SQL ServerSQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). Isso permite que o Otimizador de Consulta execute operações de busca baseadas em PartitionID em qualquer tabela ou índice particionado.This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

Agora a eliminação de partição está concluída na operação de busca.Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (como a coluna lógica principal) e possivelmente em outras colunas de chave de índice e, depois, uma busca de segundo nível, com uma condição diferente, possa ser realizada em uma ou mais colunas adicionais, para cada valor diferente que atenda à qualificação para a operação de busca de primeiro nível.In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. Ou seja, essa operação, chamada de busca seletiva, permite que o otimizador de consulta realize uma operação de busca ou de exame baseada em uma condição para determinar as partições a serem acessadas e uma operação de busca de segundo nível no operador para retornar linhas dessas partições que atendam a uma condição diferente.That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. Por exemplo, considere a consulta abaixo.For example, consider the following query.

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

Para esse exemplo, suponha que a tabela T, definida como T(a, b, c), seja particionada na coluna a e tenha um índice clusterizado na coluna b.For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. Os limites de partição da tabela T são definidos pela seguinte função de partição:The partition boundaries for table T are defined by the following partition function:

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

Para solucionar a consulta, o processador de consulta realiza uma operação de busca de primeiro nível para encontrar todas as partições que contenham linhas que atendam à condição T.a < 10.To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. Isso identifica as partições a serem acessadas.This identifies the partitions to be accessed. Em cada partição identificada, o processador realiza 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.Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

A ilustração a seguir é uma representação lógica da operação de busca seletiva.The following illustration is a logical representation of the skip scan operation. Ela mostra a tabela T com dados nas colunas a e b.It shows table T with data in columns a and b. As partições são numeradas de 1 a 4 com os limites de partição mostrados por linhas verticais tracejadas.The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. Uma operação de busca de primeiro nível nas 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.A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. Ou seja, T.a < 10.That is, T.a < 10. O caminho atravessado pela parte da busca de segundo nível da operação de busca seletiva é ilustrado pela linha curva.The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. Essencialmente, a operação de busca seletiva procura, em cada uma destas partições, por linhas que atendam à condição b = 2.Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. O custo total da operação de busca seletiva é igual ao de três buscas de índice separadas.The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

Exibindo informações sobre particionamento em planos de execução de consultasDisplaying Partitioning Information in Query Execution Plans

Os planos de execução de consultas em tabelas e índices particionados podem ser examinados usando instruções SET SHOWPLAN_XML e SET STATISTICS XML de Transact-SQLTransact-SQL e SET ou usando a saída do plano de execução gráfica no SQL ServerSQL Server Management Studio.The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQLTransact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL ServerSQL Server Management Studio. Por exemplo, é possível exibir o plano de execução de tempo de compilação clicando no botão Exibir Plano de Execução Estimado na barra de ferramentas do Editor de Consultas e exibir o plano de tempo de execução, clicando no botão Incluir Plano de Execução Real.For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

Usando essas ferramentas, você pode averiguar as seguintes informações:Using these tools, you can ascertain the following information:

  • As operações como scans, seeks, inserts, updates, mergese deletes que acessam tabelas ou índices particionados.The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • As partições acessadas pela consulta.The partitions accessed by the query. 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 nos planos de execução de tempo de execução.For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • Quando a operação de busca seletiva é usada em uma operação de busca ou de exame para recuperar dados de uma ou mais partições.When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

Aprimoramentos das informações sobre partiçõesPartition Information Enhancements

O SQL ServerSQL Server fornece informações aperfeiçoadas de particionamento para planos de execução de tempo de compilação e tempo de execução.SQL ServerSQL Server provides enhanced partitioning information for both compile-time and run-time execution plans. Agora, os planos de execução fornecem as seguintes informações:Execution plans now provide the following information:

  • Um atributo opcional Partitioned que indica que um operador, como seek, scan, insert, update, mergeou delete, é executado em uma tabela particionada.An optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
  • Um novo elemento SeekPredicateNew com um subelemento SeekKeys que inclui PartitionID como a coluna de chave de índice à esquerda e as condições de filtro que especificam buscas de intervalo em PartitionID.A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. A presença de dois subelementos SeekKeys indica que uma operação de busca seletiva no PartitionID é usada.The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • Informações resumidas que fornecem uma contagem total das partições acessadas.Summary information that provides a total count of the partitions accessed. Essas informações só estão disponíveis em planos de tempo de execução.This information is available only in run-time plans.

Para demonstrar como essas informações são exibidas tanto na saída do plano de execução gráfica, quanto na saída do Plano de Execução XML, considere a seguinte consulta na tabela particionada fact_sales.To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. Esta consulta atualiza dados em duas partições.This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

A ilustração a seguir mostra as propriedades do operador Clustered Index Seek no plano de execução de tempo de compilação para essa consulta.The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. Para exibir a definição da tabela fact_sales e a definição de partição, veja "Exemplo" neste tópico.To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Atributo particionadoPartitioned Attribute

Quando um operador como um Index Seek é executado em uma tabela ou índice particionado, o atributo Partitioned é exibido no plano de tempo de compilação e de tempo de execução e é definido como True (1).When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). O atributo não é exibido quando é definido como False (0).The attribute does not display when it is set to False (0).

O atributo Partitioned pode ser exibido nos seguintes operadores físicos e lógicos:The Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

Conforme mostrado na ilustração anterior, esse atributo é exibido nas propriedades do operador no qual ele é definido.As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. Na saída Plano de Execução XML, esse atributo é exibido como Partitioned="1" no nó RelOp do operador no qual é definido.In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

Novo predicado de buscaNew Seek Predicate

Na saída Plano de Execução XML, o elemento SeekPredicateNew aparece no operador no qual está definido.In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. Ele pode conter até duas ocorrências de subelemento SeekKeys .It can contain up to two occurrences of the SeekKeys sub-element. O primeiro item SeekKeys especifica a operação de busca de primeiro nível no nível da ID da partição do índice lógico.The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. Ou seja, essa busca determina as partições que devem ser acessadas para atender as condições da consulta.That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. O segundo item SeekKeys especifica a parte de busca de segundo nível da operação de busca seletiva que ocorre em cada partição identificada na busca de primeiro nível.The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

Informações resumidas sobre partiçõesPartition Summary Information

Nos planos de execução de tempo de execução, as informações resumidas sobre partições fornecem uma contagem das partições acessadas e da identidade das partições reais acessadas.In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. É possível 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 do exame.You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

As informações a seguir são fornecidas: Actual Partition Counte Partitions Accessed.The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count é o número total de partições acessadas pela consulta.Actual Partition Count is the total number of partitions accessed by the query.

Partitions Accessed, na saída Plano de Execução XML, são as informações de resumo da partição que são exibidas no novo elemento RuntimePartitionSummary no nó RelOp do operador no qual ele é definido.Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. O exemplo a seguir demonstra o conteúdo do elemento RuntimePartitionSummary , indicando que é acessado o total de duas partições (partições 2 e 3).The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Exibindo informações sobre partições usando outros métodos de Plano de ExecuçãoDisplaying Partition Information by Using Other Showplan Methods

Os métodos de Plano de Execução SHOWPLAN_ALL, SHOWPLAN_TEXTe STATISTICS PROFILE não reportam as informações sobre partições descritas neste tópico, com a seguinte exceção.The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. Como parte do predicado SEEK , as partições a serem acessadas são identificadas por um predicado de intervalo na coluna computada representando a ID da partição.As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. A exemplo a seguir mostra o predicado SEEK para um operador Clustered Index Seek .The following example shows the SEEK predicate for a Clustered Index Seek operator. 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.Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition 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)

Interpretando planos de execução para heaps particionadosInterpreting Execution Plans for Partitioned Heaps

Um heap particionado é tratado como um índice lógico na ID da partição.A partitioned heap is treated as a logical index on the partition ID. A eliminação de partição em um heap particionado é representada em um plano de execução como um operador Table Scan com um predicado SEEK na ID da partição.Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. O exemplo a seguir mostra as informações fornecidas sobre o Plano de Execução:The following example shows the Showplan information provided:

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

Interpretando planos de execução para junções colocadasInterpreting Execution Plans for Collocated Joins

Uma colocação de junção pode ocorrer quando duas tabelas são particionadas usando a mesma função de particionamento ou função equivalente e as colunas de particionamento de ambos os lados da junção são especificadas na condição de junção da consulta.Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. O otimizador de consulta pode gerar um plano em que as partições de cada uma das tabelas que tenham IDs de partição iguais sejam unidas separadamente.The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. As junções colocadas podem ser mais rápidas que as junções não colocadas porque podem exigir menos memória e tempo de processamento.Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. O Otimizador de Consulta escolhe um plano não colocado ou colocado com base em estimativas de custo.The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

Em um plano colocado, a junção Nested Loops lê uma ou mais partições de índice e tabela unidas a partir da parte interna.In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Os números dos operadores Constant Scan representam os números de partições.The numbers within the Constant Scan operators represent the partition numbers.

Quando planos paralelos para junções colocadas são gerados para tabelas ou índices particionados, um operador Parallelism é exibido entre os operadores de junção Constant Scan e Nested Loops .When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. Nesse caso, cada um dos vários threads de trabalho da parte externa da junção lê e trabalha em uma partição diferente.In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

A ilustração a seguir demonstra um plano de consulta paralelo para uma junção colocada.The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

Estratégias de execução de consulta paralela para objetos particionadosParallel Query Execution Strategy for Partitioned Objects

O processador de consulta usa uma estratégia de execução paralela para consultas selecionadas a partir de objetos particionados.The query processor uses a parallel execution strategy for queries that select from partitioned objects. Como parte da estratégia de execução, o processador de consulta determina as partições de tabela necessárias para a consulta e a proporção de threads de trabalho para alocar em cada partição.As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. Na maioria dos casos, o processador de consulta aloca um número igual ou aproximado de threads de trabalho para cada partição e executa a consulta paralelamente por meio das partições.In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. Os parágrafos a seguir explicam a alocação de thread de trabalho com mais detalhes.The following paragraphs explain worker thread allocation in greater detail.

thread de trabalho1

Se o número de threads de trabalho é menor que o número de partições, o processador de consulta atribui cada thread de trabalho a uma partição diferente, inicialmente, mantendo uma ou mais partições sem um thread atribuído de trabalho.If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. Quando um thread de trabalho termina a execução em uma partição, o processador de consulta o atribui para a próxima partição até que tenha sido atribuído um único thread de trabalho para cada partição.When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. Esse é o único caso em que o processador de consulta realoca threads de trabalho a outras partições.This is the only case in which the query processor reallocates worker threads to other partitions.
Mostra o thread de trabalho reatribuído após a conclusão.Shows worker thread reassigned after it finishes. Se o número de threads de trabalho é igual ao número de partições, o processador de consulta atribui um thread de trabalho para cada partição.If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. Quando um thread de trabalho é concluído, ele não é realocado para outra partição.When a worker thread finishes, it is not reallocated to another partition.

thread de trabalho2

Se o número de threads de trabalho é maior que o número de partições, o processador de consulta aloca um número igual de threads de trabalho para cada partição.If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. Se o número de threads de trabalho não é um múltiplo exato do número de partições, o processador de consulta aloca um thread de trabalho adicional a algumas partições, de forma a usar todos os threads de trabalho disponíveis.If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. Observe que se houver apenas uma partição, todos os threads de trabalho serão atribuídos a ela.Note that if there is only one partition, all worker threads will be assigned to that partition. No diagrama a seguir, há quatro partições e 14 threads de trabalho.In the diagram below, there are four partitions and 14 worker threads. Cada partição tem 3 threads de trabalho atribuídos e duas partições têm um thread de trabalho adicional, com um total de 14 threads de trabalho atribuídos.Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. Quando um thread de trabalho é concluído, ele não é reatribuído para outra partição.When a worker thread finishes, it is not reassigned to another partition.

thread de trabalho3

Embora o exemplo anterior sugira um modo objetivo para alocar threads de trabalho, a estratégia real é mais complexa e serve para outras variáveis que ocorrem durante a execução da consulta.Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. Por exemplo, se a tabela estiver 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 consulta alocará um ou mais threads de trabalho a cada um destes três valores de busca (A=13, A=17 e A=25) em vez de cada partição de tabela.For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. Só será necessário executar a consulta nas partições que tiverem esses valores. Se todos esses predicados de busca estiverem na mesma partição de tabela, todos os threads de trabalho serão atribuídos para a mesma partição de tabela.It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

Vejamos outro exemplo, vamos supor que a tabela possui quatro partições na coluna A com pontos de limite (10, 20, 30), um índice na coluna B e a consulta tem uma cláusula de predicado WHERE B IN (50, 100, 150).To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). Como as partições da tabela têm base nos valores de A, os valores de B podem ocorrer em qualquer uma das partições da tabela.Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Dessa forma, o processador de consulta buscará cada um dos três valores de B (50, 100, 150) em cada uma das quatro partições de tabela.Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. O processador de consulta atribuirá threads de trabalho proporcionalmente para que possa executar cada um desses 12 exames de consulta em paralelo.The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

As partições de tabela baseadas na coluna ATable partitions based on column A Buscas para coluna B em cada partição de tabelaSeeks for column B in each table partition
Partição de tabela 1: A < 10Table Partition 1: A < 10 B=50, B=100, B=150B=50, B=100, B=150
Partição de tabela 2: A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150B=50, B=100, B=150
Partição de tabela 3: A >= 20 AND A < 30Table Partition 3: A >= 20 AND A < 30 B=50, B=100, B=150B=50, B=100, B=150
Partição de tabela 4: A >= 30Table Partition 4: A >= 30 B=50, B=100, B=150B=50, B=100, B=150

Práticas recomendadasBest Practices

Para melhorar o desempenho das consultas que acessam uma grande quantidade de dados de grandes tabelas e índices particionados, recomendamos as seguintes práticas:To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • Distribuir cada partição em muitos discos.Stripe each partition across many disks. Isso é especialmente relevante ao usar discos de rotação.This is especially relevant when using spinning disks.
  • Quando possível, usar um servidor com memória principal suficiente para ajustar as partições acessadas com frequência ou todas as partições na memória para reduzir o custo de E/S.When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • Se os dados que você consultar não se ajustarem na memória, compacte as tabelas e os índices.If the data you query will not fit in memory, compress the tables and indexes. Isso reduzirá o custo de E/S.This will reduce I/O cost.
  • Usar um servidor com processadores rápidos e o máximo possível de núcleos de processador, para se beneficiar da capacidade de processamento de consultas paralelas.Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • Verificar se o servidor tem largura de banda suficiente do controlador de E/S.Ensure the server has sufficient I/O controller bandwidth.
  • Criar um índice clusterizado em todas as tabelas particionadas grandes para beneficiar-se de otimizações de exames da árvore B.Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • Seguir as práticas recomendadas no documento The Data Loading Performance Guide(Guia de Desempenho de Carregamento de Dados) quando estiver carregando dados em massa em tabelas particionadas.Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

ExemploExample

O exemplo a seguir cria um banco de dados de teste que contém uma única tabela com sete partições.The following example creates a test database containing a single table with seven partitions. Use as ferramentas descritas anteriormente quando estiver executando as consultas descritas neste exemplo para exibir informações sobre particionamento para planos de tempo de compilação e de tempo de execução.Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

Observação

Este exemplo insere mais de 1 milhão de linhas na tabela.This example inserts more than 1 million rows into the table. A execução deste exemplo pode demorar vários minutos, dependendo de seu hardware.Running this example may take several minutes depending on your hardware. Antes de executar este exemplo, verifique se há mais de 1.5 GB de espaço em disco disponível.Before executing this example, verify that you have more than 1.5 GB of disk space available.

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

Leitura adicionalAdditional Reading

Referência de operadores físicos e lógicos de plano de execuçãoShowplan Logical and Physical Operators Reference
Eventos estendidosExtended Events
Melhor prática com o Repositório de ConsultasBest Practice with the Query Store
Estimativa de cardinalidadeCardinality Estimation
Processamento de consulta inteligente Intelligent query processing
Precedência de operador Operator Precedence
Planos de execução Execution Plans
Central de desempenho do Mecanismo de Banco de Dados do SQL Server e do Banco de Dados SQL do AzurePerformance Center for SQL Server Database Engine and Azure SQL Database