Mikael Eriksson's questions

É possível combinar uma "versão mais recente" do arquivo LDF com uma versão mais antiga do arquivo MDF e manter as alterações registradas no LDF? Vamos supor que não houve backups de log no tempo, pois acho que isso tornaria isso realmente impossível.

Passos:

1. Banco de dados em modo de recuperação total
2. Faça um backup completo
3. Faça algumas alterações
4. Desanexar banco de dados
5. Faça uma cópia de arquivo do arquivo LDF
6. Restaurar do backup completo na etapa 2
7. Desanexar banco de dados
8. Faça uma cópia de arquivo do arquivo MDF
9. Jogue fora o backup (passo importante)
10. Excluir o banco de dados
11. De alguma forma, faça o MDF e o LDF tocarem juntos com a alteração feita na etapa 3 ainda em vigor.

Apenas anexar o MDF e o LDF dá um erro:

O número de varredura de log (38:195:41) passado para o banco de dados de varredura de log 'mic' não é válido.

Eu também tentei uma sequência de

1. Criar banco de dados
2. Colocar banco de dados offline
3. Substitua os arquivos MDF e LDF
4. Colocar online
5. Faça um backup de log
6. Soltar banco de dados
7. Criar banco de dados apenas com arquivo MDF anexado
8. Faça backup completo
9. Restaurar backup completo (norecovery)
10. Restaurar o backup de log da Etapa 5

Tenho uma memória vívida de que isso funcionou bem por um tempo, mas depois não funcionou mais.

O log nesse conjunto de backup termina em LSN 38000000021300001, que é muito cedo para ser aplicado ao banco de dados. Um backup de log mais recente que inclui LSN 39000000006500001 pode ser restaurado.

Consulta:

declare @X xml = '
<item ID = "0"/>
<item ID = "1"/>
<item/>
<item/>';

select I.X.value('@ID', 'int')
from @X.nodes('/item') as I(X);

Resultado:

-----------
0
1
NULL
NULL

Plano de execução:

A ramificação superior divide o XML em quatro linhas e a ramificação inferior busca o valor do atributo ID.

O que me parece estranho é o número de linhas retornadas do operador Stream Aggregate. As 2 linhas que vêm do Filtro são os IDatributos do primeiro e segundo itemnós no XML. O Stream Aggregate retorna quatro linhas, uma para cada linha de entrada, transformando efetivamente o Inner Join em Outer Join.

Isso é algo que o Stream Aggregate também faz em outras circunstâncias ou é apenas algo estranho acontecendo ao fazer consultas XML?

Não consigo ver nenhuma dica na versão XML do plano de consulta de que esse Stream Aggregate deva se comportar de maneira diferente de qualquer outro Stream Aggregate que observei antes.

Configurar:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

insert into dbo.T(XMLDoc)
select (
       select N.Number
       for xml path(''), type
       )
from (
     select top(10000) row_number() over(order by (select null)) as Number
     from sys.columns as c1, sys.columns as c2
     ) as N;

XML de amostra para cada linha:

<Number>314</Number>

A tarefa da consulta é contar o número de linhas Tcom um valor especificado de <Number>.

Existem duas maneiras óbvias de fazer isso:

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

Acontece que value()requer exists()duas definições de caminho diferentes para o índice XML seletivo funcionar.

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  pathSQL = '/Number' as sql int singleton,
  pathXQUERY = '/Number' as xquery 'xs:double' singleton
);

A sqlversão é para value()e a xqueryversão é para exist().

Você pode pensar que um índice como esse forneceria um plano com uma boa busca, mas os índices XML seletivos são implementados como uma tabela de sistema com a chave primária de Tcomo a chave principal da chave agrupada da tabela de sistema. Os caminhos especificados são colunas esparsas nessa tabela. Se você deseja um índice dos valores reais dos caminhos definidos, precisa criar índices seletivos secundários, um para cada expressão de caminho.

create xml index SIX_T_pathSQL on dbo.T(XMLDoc)
  using xml index SIX_T for (pathSQL);

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

O plano de consulta para o exist()faz uma busca no índice XML secundário seguido por uma pesquisa de chave na tabela do sistema para o índice XML seletivo (não sei por que isso é necessário) e, finalmente, faz uma pesquisa Tpara garantir que realmente haja linhas lá dentro. A última parte é necessária porque não há restrição de chave estrangeira entre a tabela do sistema e T.

O plano para a value()consulta não é tão legal. Ele faz uma varredura de índice clusterizado Tcom uma junção de loops aninhados contra uma busca na tabela interna para obter o valor da coluna esparsa e, finalmente, filtra o valor.

Se um índice seletivo deve ser usado ou não é decidido antes da otimização, mas se um índice seletivo secundário deve ser usado ou não é uma decisão baseada em custo pelo otimizador.

Por que o índice seletivo secundário não é usado quando a cláusula where é filtrada value()?

Atualizar:

As consultas são semanticamente diferentes. Se você adicionar uma linha com o valor

<Number>313</Number>
<Number>314</Number>` 

a exist()versão contaria 2 linhas e a values()consulta contaria 1 linha. Mas com as definições de índice como elas são especificadas aqui usando a singletondiretiva SQL Server impedirá que você adicione uma linha com vários <Number>elementos.

Isso, no entanto, não nos permite usar a values()função sem especificar [1]para garantir ao compilador que obteremos apenas um único valor. Essa [1]é a razão pela qual temos um Top N Sort no value()plano.

Parece que estou me aproximando de uma resposta aqui ...

Parece que a velocidade de execução do T-SQL depende da latência da conexão de rede com o servidor. Presumi que, se o SQL Server não tiver nada para relatar ao cliente, ele será executado até que seja concluído, mas o teste mostra outra história.

create procedure UselessLoop
  @I int
as
declare @D datetime = getdate()
while @I > 0 set @I -= 1
print datediff(millisecond, @D, getdate())

exec UselessLoop 100000

Server    Milliseconds
local     53
nearby    63
faraway   660

exec UselessLoop 1000000

Server    Milliseconds
local     546
nearby    640
faraway   6183

Os testes são executados no mesmo servidor de diferentes computadores usando o SSMS. O local é executado a partir do servidor, o local é executado na mesma rede local e o distante é executado a partir de outro escritório a 500 km de distância conectado com fibra de 1 gigabit.

Obviamente, há alguma comunicação acontecendo entre o SQL Server e o cliente que depende diretamente do número de instruções executadas.

Usei o Wireshark para ver o que é transportado e não posso dizer que entendo muito, mas era um tcp.stream trocando um total de 26 MB em 22740 pacotes.

Que tal uma função inútil?

create function dbo.UDFUselessLoop(@I int)
returns int
as
begin
  declare @D datetime = getdate()
  while @I > 0 set @I -= 1
  return datediff(millisecond, @D, getdate())
end

print dbo.UDFUselessLoop(1000000)

Ele é executado em 406 milissegundos, independentemente de onde é executado. Parece que não há comunicação com o cliente no loop.

No SQL Server 2008, o tipo de dados de data foi adicionado.

A conversão de uma datetimecoluna para dateé sargável e pode usar um índice na datetimecoluna.

select *
from T
where cast(DateTimeCol as date) = '20130101';

A outra opção que você tem é usar um intervalo.

select *
from T
where DateTimeCol >= '20130101' and
      DateTimeCol < '20130102'

Essas consultas são igualmente boas ou uma deve ser preferida à outra?

create table T(ID int identity primary key)
insert into T default values
insert into T default values

go

select cast(ID as varchar(10)) as ID
from T
where ID = 1

A consulta acima possui um aviso no plano de consulta.

<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" />
</Warnings>

Por que tem o aviso?

Como uma conversão na lista de campos pode afetar a estimativa de cardinalidade?