Programação e otimização de consultas distribuídas (linked server)

Consultas distribuídas são aquelas em que são acessadas múltiplas fontes de dados, heterogêneas ou não, sendo que essas fontes de dados podem estar no mesmo computador ou mesmo em computadores diferentes; sistemas operacionais similares ou diferentes; mesmo gerenciador de banco de dados ou gerenciadores de bancos de dados diferentes.

p018_sumario

1. Introdução

Em um intervalo de tempo de poucos dias atendi a dois casos em que se questionava determinado assunto que, ao final, remetia a consultas distribuídas. Isto é, consultas em que parte dos dados está em outro banco de dados, sendo este residente em outra instância SQL Server ou mesmo outro gerenciador de banco de dados. Não se tratavam de bancos de dados originalmente concebidos como distribuídos mas sim de situações em que as informações estão em bancos de dados diferentes, às vezes de sistemas legados. Também percebo situações que se tornam cada vez mais frequentes, devido ao contexto de business intelligence (BI) em que diversas fontes heterogêneas são utilizadas para análises mais complexas.

As consultas distribuídas são aquelas em que são acessadas múltiplas fontes de dados, heterogêneas ou não, sendo que essas fontes de dados podem estar no mesmo computador ou mesmo em computadores diferentes, sistemas operacionais similares ou diferentes, mesmo gerenciador de banco de dados ou gerenciadores de bancos de dados diferentes.

p018_linked-server

Existem também as transações distribuídas, mas que não está nos objetivos deste artigo.

2. Sintaxe de consultas distribuídas

Utilizaremos dois bancos de dados para demonstração da sintaxe adicional utilizada nos casos de consultas distribuídas, cada qual em uma instância. O banco de dados de exemplo registra vendas, tendo as tabelas de produtos; vendas realizadas; e produtos vendidos em cada venda. Propositalmente a tabela de vendas estará em um banco de dados e a tabela de produtos vendidos em outro banco de dados.

As tabelas de vendas realizadas e de produtos disponíveis para venda estão no primeiro banco de dados, denominado neste artigo de banco_A. Antes de executar o código a seguir, abra o Management Studio (ou outra ferramenta semelhante), estabeleça conexão com uma das instâncias e, mantendo essa instância como ativa, abra então uma aba do editor de consultas T-SQL.

-- código #2.1
CREATE DATABASE banco_A;
go

USE banco_A;
go

CREATE TABLE dbo.Venda (
     cod_Venda int not null,
     data_Venda datetime2(0) not null
);
ALTER TABLE dbo.Venda
     add constraint I1_Venda primary key (cod_Venda);
go

CREATE TABLE dbo.Produto (
     cod_Produto smallint not null,
     deno_Produto varchar(35) not null,
     cod_Unidade tinyint not null,
     valor_Unitario decimal(5,2) not null
);
ALTER TABLE dbo.Produto
     add constraint I1_Produto primary key (cod_Produto);
go

Já a tabela de produtos vendidos em cada venda realizada está no segundo banco de dados, denominado neste artigo de banco_B. Antes de executar o código a seguir, estabeleça conexão com outra das instâncias e, mantendo essa instância como ativa, e abra então uma nova aba do editor de consultas T-SQL.

-- código #2.2
CREATE DATABASE banco_B;
go

USE banco_B;
go

CREATE TABLE dbo.Item_Venda (
     cod_Venda int not null,
     cod_Produto smallint not null,
     qtd_Itens decimal (5,2) not null,
     valor_Unitario decimal(5,2) not null
);
ALTER TABLE dbo.Item_Venda
     add constraint I1_Item_Venda primary key (cod_Venda, cod_Produto);
go

Já percebeu que, como a tabela de vendas está em um banco de dados e a tabela de itens vendidos está em outro banco de dados, a integridade referencial terá que ser implementada na aplicação. Isto para evitar cross-databases trigger.

Feche a aba do editor de consultas T-SQL em que foi criado o banco de dados banco_B e desconecte da instância em que ele foi criado.

2.1. Bancos de dados na mesma instância

É na cláusula FROM que definimos a identificação das tabelas. A sintaxe simplificada dessa cláusula é

FROM { <table_source> } [ ,...n ] }

<table_source> ::=
{
  [ database_name . [ schema_name ] . | schema_name . ]
    table_name [ [ AS ] table_alias ]
}

Segundo o diagrama BNF acima, a identificação da tabela pode ser nas seguintes formas:

  1. table_name
  2. schema_name.table_name
  3. database_name.schema_name.table_name
  4. database_name..table_name

Geralmente utilizamos a primeira forma, embora o recomendado seja informar sempre o nome do esquema, conforme a segunda forma.

Supondo que todas as tabelas estejam no mesmo banco de dados; temos então o seguinte código para listar as vendas e respectivos itens vendidos:

-- código #2.3
USE banco_A;
go

SELECT V.cod_Venda, V.data_Venda,
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner join dbo.Item_Venda as IV
            on IV.cod_Venda = V.cod_Venda
       inner join dbo.Produto as P
            on P.cod_Produto = IV.cod_Produto;

Nada de diferente, até o momento. O plano de execução é o seguinte:

p018_codigo #2.3

Mas agora vamos retornar ao contexto deste artigo, em que duas das tabelas estão em bancos de dados diferentes, só que considerando que ambos estão na mesma instância. Temos então que informar o nome do banco de dados caso ele não seja o que está em uso; ou seja, utilizar ou a forma 3 ou a forma 4.

-- código #2.4
USE banco_A;
SELECT V.cod_Venda, V.data_Venda,
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner join banco_B.dbo.Item_Venda as IV
           on IV.cod_Venda = V.cod_Venda
       inner join dbo.Produto as P
           on P.cod_Produto = IV.cod_Produto;

Quando se utilizam as 3 partes temos o que é denominado de fully qualified name (FQN).

p018_codigo #2.4

Ao comparar visualmente os planos de execução dos códigos #2.3 e #2.4 não se percebem diferenças entre eles.

2.2. Bancos de dados em instâncias diferentes, mesmo computador

Como fazemos para identificar as tabelas na cláusula FROM caso os bancos de dados estejam em instâncias diferentes de um mesmo computador? Não há como informar qual é o nome da instância na definição fully qualified name (FQN). Isso somente é possível utilizando a identificação de 4 partes:

server_name.database_name.schema_name.table_name

A diferença está na primeira parte, onde server_name indica o nome da instância. Observe que está server_name mas isto não significa que seja a identificação de outro computador mas sim da instância de banco de dados, seja no mesmo computador ou em outro computador.

Para estabelecer a conexão com a outra instância é utilizada a vinculação de servidores, ou linked server. Esse assunto será tratado a seguir mas antes, para continuarmos nos exemplos básicos, o seguinte código estabelece de forma simplificada a vinculação entre duas instâncias SQL Server:

-- código #2.5
USE banco_A;
EXECUTE master..sp_addlinkedserver
             @server= N'instancia2',
             @srvproduct= N'',
             @provider= N'SQLNCLI',
             @datasrc= N'SQL2012-VM1\INSTANCIA2';

No parâmetro @datasrc deve ser informado a denominação do servidor na rede e o nome da instância SQL Server onde está o banco de dados banco_B.

O nosso código que lista as vendas e respectivos itens vendidos fica então assim:

-- código #2.6
USE banco_A;
SELECT V.cod_Venda, V.data_Venda,
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner join instancia2.banco_B.dbo.Item_Venda as IV
           on IV.cod_Venda = V.cod_Venda
       inner join dbo.Produto as P
           on P.cod_Produto = IV.cod_Produto;

Foi uma alteração mínima no código T-SQL, ao utilizar a identificação de tabela usando a qualificação de 4 partes: instância, banco de dados, esquema e tabela. Mas no respectivo plano de execução

p018_codigo #2.6

Ao comparar visualmente os planos de execução dos códigos #2.4 e #2.6 percebemos a diferença na forma como os dados da tabela Item_Venda são obtidos: enquanto que naquele código anterior é através do operador “Clustered Index Scan”, neste último é utilizado o operador “Remote Query”. Este operador é utilizado quando parte do código da consulta é enviado para ser processado em outro banco de dados:

p018_operador remote query

Analisando as propriedades do operador Remote Query nesse plano de execução, temos que a seguinte consulta foi executada remotamente:

SELECT "Tbl1003"."cod_Venda" "Col1026",
       "Tbl1003"."cod_Produto" "Col1027",
       "Tbl1003"."qtd_Itens" "Col1028",
       "Tbl1003"."valor_Unitario" "Col1029"
  FROM "banco_B"."dbo"."Item_Venda" "Tbl1003"
  ORDER BY "Col1026" ASC

e o resultado dessa consulta retornado para a consulta principal. Todas as linhas da tabela remota foram lidas, por causa das características do código da consulta. O curioso é que o conjunto de dados dessa consulta remota foi previamente ordenado pela coluna cod_Venda no banco de dados remoto, antes de ser enviado para a consulta principal.

2.3. Bancos de dados em computadores diferentes

Como fazemos para identificar as tabelas na cláusula FROM caso os bancos de dados estejam em computadores diferentes? Da mesma forma que o item anterior, a solução está no uso da identificação de 4 partes:

server_name.database_name.schema_name.table_name

Para estabelecer a conexão com a outra instância novamente é utilizada a vinculação de servidores, ou linked server. No parâmetro @datasrc deve ser informado a identificação do servidor e o nome da instância SQL Server onde está o banco de dados banco_B. Esse outro computador pode estar na mesma rede local, fisicamente em um mesmo ambiente, ou até mesmo em locais físicos distantes, interligados pela internet ou por rede de comunicação própria. Com relação ao item anterior, o que muda é somente a definição do parâmetro @datasrc. O código de consulta é o código #2.6, do item anterior.

3. Conexão entre servidores

No capítulo anterior há um código T-SQL para estabelecer a vinculação entre duas instâncias. Entretanto, naquele ponto nada foi explicado sobre os comandos utilizados, parâmetros, ou mesmo sobre o funcionamento dessa vinculação. Agora, neste capítulo, vamos tratar justamente da vinculação de instâncias, que considero erroneamente denominada de vinculação de servidores (linked servers).

Como citado na introdução, as consultas distribuídas são aquelas em que são acessadas múltiplas fontes de dados, heterogêneas ou não, sendo que essas fontes de dados podem estar no mesmo computador ou mesmo em computadores diferentes, sistemas operacionais similares ou diferentes, mesmo gerenciador de banco de dados ou gerenciadores de bancos de dados diferentes.

3.1. Provedor OLE DB

Antes de entrar na conceituação de provedor OLE DB me parece necessário explicar o que é “OLE DB”. Conforme verbete na wikipedia sobre OLE DB, “OLE DB (Object Linking and Embedding, Database) é uma API projetada pela Microsoft que permite acesso a dados de uma variedade de fontes, de maneira uniforme. O objetivo original foi o de substituir drivers ODBC, ampliando seu alcance a uma variedade de fontes de dados que necessariamente não implementam SQL”. OLE DB conceitualmente é dividido em consumidores (consumers) e provedores (providers) onde estes, os provedores OLE DB, implementam a interface que permite que os dados da fonte externa sejam consumidos.

Provedores OLE DB retornam os dados como objetos tabulares denominados de rowsets. O SQL Server trata então os rowsets como se fossem tabelas do SQL Server, acessados normalmente através de comandos T-SQL.

É necessário que um provedor OLE DB esteja instalado no servidor que executa o SQL Server para cada tipo de fonte de dados OLE DB que será acessada. O conjunto de operações Transact-SQL que pode ser utilizado com fonte de dados OLE DB depende dos recursos do provedor OLE DB. A Microsoft disponibiliza conjunto de provedores OLE DB para alguns dos principais gerenciadores de bancos de dados, como Oracle Database e IBM DB2, e também para Access, Excel etc. Alguns fabricantes de gerenciadores de banco de dados também fornecem provedores OLE DB próprios.

3.2. Estabelecendo o vínculo

O acesso a fontes externas de dados é possível por dois métodos:

  1. vinculação de servidores: o procedimento armazenado sp_addlinkedserver é utilizado para estabelecer a vinculação, conforme demonstrado no item 2.2 deste artigo;
  2. conexão ad hoc: estabelecimento temporário de uma vinculação; utilizada nas funções OPENROWSET e OPENDATASOURCE.

Vinculação de servidores. A vinculação pode ser feita na interface gráfica do Management Studio ou então através de comando T-SQL, executando então o procedimento armazenado sp_addlinkedserver. Há alguns parâmetros, cujo preenchimento depende do provedor OLE DB e também do tipo da fonte de dados externa.

sp_addlinkedserver
    [ @server= ] 'server'
    [ , [ @srvproduct= ] 'product_name' ]
    [ , [ @provider= ] 'provider_name' ]
    [ , [ @datasrc= ] 'data_source' ]
    [ , [ @location= ] 'location' ]
    [ , [ @provstr= ] 'provider_string' ]
    [ , [ @catalog= ] 'catalog' ]

Resumidamente, eis a função de alguns dos parâmetros:

@server: permite definir a denominação da vinculação de servidores, para uso nos demais comandos;
@srvproduct: informe “SQL Server” se a fonte de dados externa está em instância SQL Server ou deixe em branco, caso contrário;
@provider: o nome padrão do provedor OLE DB com o qual ele está registrado localmente;
@datasrc: identificação da fonte de dados externa.

Na documentação do procedimento armazenado sp_addlinkedserver você encontra detalhes sobre todos os parâmetros e exemplos de utilização de fontes de dados externas variadas.

Podemos agora compreender o código de vinculação que foi utilizado no item 2.2:

-- código #3.1
EXECUTE master..sp_addlinkedserver
   @server= N'instancia2',
   @srvproduct= N'',
   @provider= N'SQLNCLI',
   @datasrc= N'SQL2012-VM1\INSTANCIA2';

Trata-se de vinculação que foi denominada de “instancia2”; como provedor OLE DB foi utilizado o SQLNCLI e definida a vinculação com a instância INSTANCIA2 do servidor SQL2012-VM1.

Conexão ad hoc. Neste artigo as conexões ad hoc não serão tratadas.

4. Opções de programação

As consultas distribuídas possuem um tratamento especial por parte do otimizador de consultas (query optimizer), quando o plano de execução está a ser gerado. Há vários passos adicionais que o otimizador de consultas executa, quase sempre gerando planos de execução mais complexos. Durante a fase de geração do plano de execução, o otimizador coleta estatísticas das tabelas remotas; isto depende, é claro, do gerenciador remoto de banco de dados bem como da permissão de acesso às estatísticas.

O plano de execução de consultas distribuídas pode conter operadores não usuais nos planos de execução das consultas locais. Os códigos das consultas presentes no item 2.2 deste artigo serão utilizados para descrever algumas diferenças.

4.1. Qualificação de 4 partes

A sintaxe utilizando a qualificação de 4 partes para acessar as tabelas é a forma tradicional de construir consultas distribuídas. Ela foi descrita em detalhes no item 2.2.

4.2. Função OPENQUERY()

Quando se constrói código SQL com consulta distribuída, há algumas técnicas para definir como a consulta remota será executada. Por exemplo, se há agrupamento de dados na tabela remota, pode-se fazer com que a consulta de agrupamento seja processada no servidor remoto (através de OPENQUERY, por exemplo), o que já reduz o tráfego de dados. Cada situação deve ser analisada, procurando o que pode ser realizado em processamento remoto ou não.

Agora que conhecemos a função OPENQUERY, será que obteremos alguma melhoria no tempo de execução do código #2.6 se substituirmos a junção remota com a tabela Item_Venda pela execução de uma consulta remota com a função OPENQUERY? Para avaliar esta questão, o código #2.6 foi reescrito para

-- código #3.2
USE banco_A;
SELECT V.cod_Venda, V.data_Venda,
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner join openquery(instancia2,
                            'SELECT cod_Venda, cod_Produto,
                                    qtd_Itens, valor_Unitario
                               from banco_B.dbo.Item_Venda') as IV
           on IV.cod_Venda = V.cod_Venda
       inner join dbo.Produto as P
           on P.cod_Produto = IV.cod_Produto;

que gerou o seguinte plano de execução:

p018_codigo #3.2

Para facilitar a comparação visual das diferenças entre os planos de execução dos códigos #2.6 e #3.2, temos os seguintes destaques:

p018_codigo #2.6 destaque
destaque do código #2.6
p018_codigo #3.2 destaque
destaque do código #3.2

No caso do código #2.6, o otimizador de consultas teve acesso às estatísticas da tabela remota, o que permitiu saber o número de linhas a ler na tabela Item_Venda (cerca de 196 mil linhas) e construir então o plano de execução sabendo de antemão o que o código T-SQL da consulta remota executaria; afinal, foi o otimizador que gerou o código T-SQL da consulta remota. Observe que a execução da consulta remota foi através do operador “Remote Query” e que a junção das tabelas Venda (local) e Item_Venda (remota) foi através do operador “Merge Join”.

Já no caso do código #3.2, o otimizador de consultas não tenta obter estatísticas remotas, pois o código da consulta remota está embutido na função OPENQUERY(). A estimativa de número de linhas que seriam lidas foi então de 10.000 linhas, que é o valor padrão definido pelo otimizador de consultas para o operador “Remote Scan”; um número bem inferior às 196 mil linhas lidas. Essas informações constam das propriedades do operador “Remote Scan”:

p018_codigo #3.2 remote scan

4.3. Dica de junção REMOTE

Pelas características das tabelas Venda e Item_Venda sabe-se que o número de linhas da tabela Item_Venda será sempre maior do que o número de linhas da tabela Venda; afinal, uma venda possui um ou mais itens vendidos. Sabendo-se deste fato, e considerando-se que a tabela Produto possui pouquíssimas linhas, há a possibilidade de reduzir o tráfego na rede fazendo com que o processamento ocorra na instância remota; neste caso são as tabelas Produto e Venda que serão transferidas para a instância remota. Isto se pode obter com a dica de junção REMOTE. O código #2.6 pode então ser reescrito para

-- código #3.3
USE banco_A;
SELECT V.cod_Venda, V.data_Venda,
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner remote join instancia2.banco_B.dbo.Item_Venda as IV
           on IV.cod_Venda = V.cod_Venda
       inner join dbo.Produto as P
           on P.cod_Produto = IV.cod_Produto;

Ao executar o código anterior obtém o seguinte plano de execução:

p018_codigo #3.3

As principais diferenças estão no operador de junção entre as tabelas Venda e Item_Venda, que passou a ser o “Nested Loops”, e no código T-SQL de leitura no operador “Remote Query”, que foi o seguinte:

SELECT "Tbl1003"."cod_Produto" "Col1009",
       "Tbl1003"."qtd_Itens" "Col1010",
       "Tbl1003"."valor_Unitario" "Col1011"
  FROM "banco_B"."dbo"."Item_Venda" "Tbl1003"
  WHERE "Tbl1003"."cod_Venda"=?

Observa-se que o otimizador de consultas acrescentou a cláusula WHERE, filtrando pela coluna cod_Venda. O que ocorreu é que, para cada linha da tabela Venda, o operador “Nested Loops” executou o código T-SQL presente em “Remote Query”. Isto inclusive se confirma pelo número de execuções do operador “Remote Query”, conforme destacado na figura abaixo:

p018_codigo #3.3 propriedades

Mas e o resultado final da execução do código #3.3, foi melhor ou pior do que o código #3.2? Eis os comparativos de tempos:

Código Tempo de CPU (ms) Tempo total (ms)
#2.6 3.415 9.600
#3.2 3.605 9.863
#3.3 17.806 34.658

O código #3.3, com INNER REMOTE JOIN ficou muuuito mais lento do que os outros dois, que tiveram performance semelhante. A melhora teórica esperada não ocorreu; qual o motivo? 😕

4.4. Outros testes

Enquanto pesquisava sobre o assunto, li comentários em artigos e fóruns de que o uso da construção de 4 partes gera plano de execução ineficiente quando na presença da cláusula WHERE limitando linhas. Comentários de que todas as linhas da tabela remota eram transferidas para o servidor da consulta principal e somente após os filtros eram aplicados neste servidor.

Eis o código #2.6 modificado, com o acréscimo da cláusula WHERE para listar somente as vendas que tiveram códigos internos de 1500 a 2500.

-- código #3.4
USE banco_A;
SELECT V.cod_Venda, V.data_Venda, 
       P.deno_Produto, IV.qtd_Itens, IV.valor_Unitario,
       (IV.qtd_Itens * IV.valor_Unitario) as total_Item
  from dbo.Venda as V
       inner join instancia2.banco_B.dbo.Item_Venda as IV
                  on IV.cod_Venda = V.cod_Venda 
       inner join dbo.Produto as P
                  on P.cod_Produto = IV.cod_Produto
  where V.cod_Venda between 1500 and 2500;

Após execução, eis o plano de execução real gerado:

p018_codigo #3.4

Ao comparar com o plano de execução do código #2.6, observa-se de imediato que a leitura da tabela Venda ocorreu através do operador “Clustered Index Seek”; isto se deve à presença do filtro na cláusula WHERE.

Mas e na tabela remota Item_Venda, todas as linhas foram lidas e enviadas para o servidor local, e somente após filtradas? Eis o que aparece nas propriedades do operador “Remote Query”:

p018_codigo #3.4 propriedades

No código T-SQL da consulta remota, que foi gerado pelo otimizador de consultas da instância de origem, observa-se que o conteúdo da tabela Item_Venda foi filtrado, antes de enviar o conjunto de linhas da tabela Item_Venda para a consulta principal. Ou seja, o otimizador de consultas do SQL Server foi inteligente o suficiente para detectar que o filtro aplicado na coluna cod_Venda da tabela local Venda também se aplicava à coluna cod_Venda da tabela remota Item_Venda.

Além dos testes realizados com o código #2.6 e suas variações #3.2 e #3.3, foram também realizados outros testes. Como os resultados comparativos obtidos não foram muito diferentes dos anteriormente detalhados, não se faz menção aos resultados deles neste artigo, de modo a não estender em demasia o artigo. Caso tenha interesse, seguem dois códigos que emitem o total de vendas, mês a mês.

O primeiro código emite o total mensal de vendas, usando a sintaxe de 4 partes:

-- código #3.5
USE banco_A;

SELECT year (V.data_Venda) as Ano, month (V.data_Venda) as Mês,
       sum (IV.qtd_Itens * IV.valor_Unitario) as total_Mês
  from dbo.Venda as V
       inner join instancia2.banco_B.dbo.Item_Venda as IV
         on IV.cod_Venda = V.cod_Venda
  group by year (V.data_Venda), month (V.data_Venda)
  order by Ano, Mês;

Já o seguinte código emite o mesmo relatório, mas utiliza a função OPENQUERY() para executar remotamente parte da somatória, reduzindo assim o número de linhas transferidas entre instâncias.

-- código #3.6
USE banco_A;

SELECT year (V.data_Venda) as Ano, month (V.data_Venda) as Mês,
       sum (IV.soma) as total_Mês
  from dbo.Venda as V
       inner join openquery(instancia2,
                            'SELECT cod_Venda,
                                    sum (qtd_Itens * valor_Unitario) as soma
                               from banco_B.dbo.Item_Venda
                               group by cod_Venda') as IV
          on IV.cod_Venda = V.cod_Venda
  group by year (V.data_Venda), month (V.data_Venda)
  order by Ano, Mês;

E então, o que você observou ao executar os códigos #3.5 e #3.6, após analisar os respectivos planos de execução e demais estatísticas de execução?

5. Observações finais

5.1. Contexto

Os códigos deste artigo foram testados nas versões 2008 e 2016 do SQL Server. Para cada versão foram utilizadas duas instâncias, com o mesmo idioma e nível de atualização. Além disso, o limite máximo de memória foi configurado o mesmo para todas as instâncias. Desta forma procurou-se criar condições semelhantes para os testes.

Os testes foram limitados ao gerenciador SQL Server.

5.2. Melhorias na performance de consultas distribuídas

Há poucos artigos sobre a programação e, principalmente, otimização de consultas distribuídas. Não sei se é um assunto tabu ou se pelo fato de ser mais difícil de testar e de analisar.

Não encontrei documentação oficial que explicasse em detalhes as diferenças entre os operadores “Remote Query” e “Remote Scan” e em quais situações o otimizador de consultas utiliza um ou outro. As informações obtidas na documentação são vagas. O que deduzi, e posso estar enganado, é que no caso do operador “Remote Query” o otimizador de consultas tem controle sobre o que ele vai executar bem como obteve as estatísticas associadas aos objetos tratados no operador; já com relação ao operador “Remote Scan” o otimizador de consultas não tem nenhuma informação sobre o que será executado ou mesmo retornado.

Estatísticas de distribuição. Conforme o documento “Requisitos de estatísticas de distribuição para provedores OLE DB”, para aprimorar a otimização de consultas distribuídas o SQL Server definiu extensões para a especificação OLE DB que os provedores de OLE DB podem utilizar para coletar estatísticas de distribuição nos conjuntos de linhas ou tabelas expostos por eles. As extensões de estatísticas de distribuição são criadas em torno de uma unidade chamada estatística. Cada tabela pode ter zero ou mais estatísticas e cada estatística informa dados para uma ou mais colunas. Uma estatística registra o seguinte:

  • a cardinalidade dos valores ou o número de valores exclusivos em cada coluna coberta pela estatística;
  • a cardinalidade dos valores concatenados de todas as colunas cobertas pela estatística;
  • opcionalmente, um histograma que relata informações sobre os vários intervalos de valores de chave da primeira coluna coberta pela estatística.

O otimizador de consultas do SQL Server utiliza então as estatísticas de distribuição em uma tentativa de reduzir a quantidade de dados que precisam ser transferidos entre o provedor OLE DB e o SQL Server.

De acordo com a documentação de DBCC SHOW_STATISTICS, para coletar as estatísticas o usuário deve ser proprietário da tabela ou deve ser membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_owner ou db_ddladmin. O SQL Server 2012, com atualização SP1, modifica essas restrições, ao permitir que usuários com a permissão SELECT obtenham as estatísticas. Para que as permissões SELECT sejam suficientes para executar o comando, é necessário atender aos seguintes requisitos:

  • os usuários devem ter permissões em todas as colunas do objeto de estatísticas;
  • os usuários devem ter permissão em todas as colunas em uma condição de filtro (se houver);

Ou seja, a informação desses dois documentos nos alerta para verificar qual é a versão das instâncias envolvidas e se o usuário local tem acesso às estatísticas dos objetos remotos. Atenção também para o sinalizador de rastreamento (trace flag) 9485: caso ele esteja ativo, não basta a permissão SELECT para DBCC SHOW_STATISTICS coletar as estatísticas.

Plano de execução. Quando se procura melhorar a performance de uma consulta SQL, o primeiro passo é analisar o plano de execução e isto também se aplica às consultas distribuídas. Como visto no capítulo anterior sobre opções de programação, é preciso analisar o operador remoto utilizado, o código T-SQL que ele executou, quais os valores das propriedades desse operador remoto, a forma de junção escolhida pelo otimizador de consultas entre o resultado remoto e as tabelas locais etc.

Uma ausência de informação é o plano de execução da consulta T-SQL executada remotamente; se for possível, extraia esse código do operador e o execute diretamente no servidor remoto, através de uma conexão direta. No caso de códigos enviados pela função OPENQUERY há como reescrever o código, otimizando-o, se possível.

Variações no código T-SQL. Ao escrever o código da consulta, comece pela sintaxe de 4 partes, usando a tabela remota como se fosse uma tabela local. Implemente também uma variação do código usando a função OPENQUERY. Compare então os planos de execução e as estatísticas de execução.

Replicação. Se uma consulta distribuída é executada com frequência, e o tempo de execução dela é considerado demorado, deve-se avaliar o uso de replicação para manter cópia local (somente leitura) dos dados remotos utilizados na consulta. É óbvio que antes de optar por esta solução deve-se analisar utilização local de espaço físico, segurança no acesso aos objetos replicados, demanda do canal de comunicação para manter atualizados os objetos replicados etc.

Cabe mencionar que a edição Express do SQL Server não oferece a opção de replicação, exceto como assinante.

Objetos de bancos de dados de outros gerenciadores de bancos de dados podem ser replicados, seja utilizando solução nativa (assinante de Oracle Database, por exemplo) ou software de terceiros.

5.3. Segurança na vinculação de instâncias

Mapeamento de logon. Ao estabelecer uma conexão de vinculação de servidores, o servidor de origem fornece um nome de logon e respectiva senha para conectar no servidor de destino. Para isso é necessário que previamente seja definido no servidor de origem um mapeamento de login, seja através do procedimento armazenado sp_addlinkedsrvlogin ou pela interface gráfica do Management Studio. Esse mapeamento de login, definido individualmente por vinculação de servidores, estabelece uma associação entre login no servidor de origem e login/senha no servidor de destino.

Ao executar uma consulta distribuída, o servidor SQL Server de origem pesquisa pelo respectivo mapeamento de login para o login local que está executando a consulta e envia então para o servidor de destino o par login/senha remotos, para autenticação no servidor de destino. Mas caso não tenha sido definido o mapeamento de login para a vinculação de servidores, é utilizado então o automapeamento, onde o padrão é emular as credenciais de segurança atuais do login local que está executando a consulta. Se a delegação de segurança de conta estiver disponível e o servidor vinculado permitir autenticação pelo windows, então os logins locais autenticados pelo Windows serão automapeados.

O recurso de vinculação de servidores, se não for corretamente configurado o mapeamento, pode acarretar em brechas de segurança no acesso ao recurso remoto. Pode ocorrer, por exemplo, que todo usuário da instância local tenha acesso aos objetos existentes na instância remota.

O primeiro passo é definir o mapeamento para as contas de login locais que terão permissão para acessar a instância remota, associando-as às contas do servidor de destino. Como exemplo, vamos associar a conta de login local Joao à conta de login remota acessoBancoB, cuja senha é senhaABB.

EXECUTE sp_addlinkedsrvlogin
            @rmtsrvname= 'instancia2',
            @useself= 'FALSE',
            @locallogin= 'instancia1\Joao',
            @rmtuser= 'acessoBancoB', @rmtpassword= 'senhaABB';

Após ter definido o mapeamento de todas as contas de login locais que terão permissão para acessar a instância remota, o passo seguinte é definir que as demais contas de login que não façam parte do mapeamento não possam se conectar à instância remota. No servidor de origem, onde está definida vinculação de servidores, executa-se o seguinte comando:

EXECUTE sp_addlinkedsrvlogin 'instancia2', NULL;

Entretanto, ao executar o comando acima ocorre erro 15600, com o procedimento armazenado recusando NULL para o parâmetro @useself, mesmo com tal opção estando disponível na sintaxe do procedimento armazenado. Ao analisar o código T-SQL do procedimento armazenado sp_addlinkedsrvlogin observa-se em determinado trecho que os únicos valores aceitos são ‘false’ e ‘true’, contradizendo assim a documentação. Então, a solução que encontrei foi a utilizar a interface gráfica para desativar o automapeamento, de modo a restringir o acesso ao servidor remoto somente às contas de login locais que estejam definidas no mapeamento.

p018_prop serv vinc 2016

Deve-se selecionar a opção “Não serão feitas” e a seguir pressionar o botão OK.

Kerberos Authentication. Para quem se interessar por uma forma mais completa de autenticação, sugiro a leitura do artigo Curse Of Linked Server Security And The Fix: Pass-through Authentication. É claro que ela não se aplica a todos os casos, pois depende do gerenciador de banco de dados remoto.

6. Fontes de consulta

Neste capítulo estão fontes de informação que foram utilizadas na confecção deste artigo bem como outras cuja consulta é recomendada.

6.1. Documentação Microsoft

6.2. Artigos

6.3. Vídeos

6.4. Glossário

7. Anexo

Os códigos a seguir foram utilizados para gerar a massa de dados utilizada nos testes.

Tabela de produtos:

-- código #7.1
USE banco_A;
INSERT into dbo.Produto
     (cod_Produto, deno_Produto, cod_Unidade, valor_Unitario)
     values
  (1, 'Abacaxi desidratado', 1, 60.00),
  (2, 'Açúcar demerara', 1, 3.00),
  (3, 'Alecrim', 1, 12.00),
  (4, 'Alho em flocos', 1, 12.50),
  (5, 'Ameixa sem caroço', 1, 11.00),
  (6, 'Arroz agulha integral, branco', 1, 3.50),
  (7, 'Boldo em folhas', 1, 16.00),
  (8, 'Canela inteira', 1, 24.00),
  (9, 'Castanha de baru', 1, 70.00),
  (10, 'Cebola granulada média', 1, 12.00),
  (11, 'Chimichurri com pimenta', 1, 16.00),
  (12, 'Colorau', 1, 2.80);

Tabela de vendas:

-- código #7.2
USE banco_A;
set nocount on;
declare @data_I datetime2(0), @data_F datetime2(0), @I int;
set @data_I= convert (datetime2(0), '2/1/2018', 103);
set @data_F= convert (datetime2(0), '31/12/2019', 103);
set @I = 1;
while @data_I < @data_F
  begin
  INSERT into dbo.Venda (cod_Venda, data_Venda) values
(@I, @data_I);
  set @data_I= dateadd (minute, +17, @data_I);
  set @I+= 1;
  end;

Tabela de itens de cada venda:

-- código #7.3
USE banco_B;
set nocount on;
INSERT into dbo.Item_Venda
     (cod_Venda, cod_Produto, qtd_Itens, valor_Unitario)
  SELECT cod_Venda, ((cod_Venda + B.I) % 12) +1,
         round( rand(cod_Venda) * 5, 2), 0
    from banco_A.dbo.Venda
         cross join (values (1), (2), (3), (4), (5)) as B (I)
    where B.I < ((rand(cod_Venda) * 5) +1);

UPDATE T1
  set valor_Unitario= T2.valor_Unitario
  from dbo.Item_Venda as T1
       inner join banco_A.dbo.Produto as T2 on T1.cod_Produto = T2.cod_Produto;
PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Programação e otimização de consultas distribuídas

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s