Iain Samuel McLean Elder's questions

Escrevi duas funções para responder à primeira pergunta de dever de casa do dia 3 de Seven Databases in Seven Weeks .

Crie um procedimento armazenado onde você pode inserir o título de um filme ou o nome do ator de sua preferência, e ele retornará as cinco principais sugestões com base nos filmes em que o ator estrelou ou em filmes com gêneros semelhantes.

Minha primeira tentativa é correta, mas lenta. Pode levar até 2000 ms para retornar um resultado.

CREATE OR REPLACE FUNCTION suggest_movies(IN query text, IN result_limit integer DEFAULT 5)
  RETURNS TABLE(movie_id integer, title text) AS
$BODY$
WITH suggestions AS (

  SELECT
    actors.name AS entity_term,
    movies.movie_id AS suggestion_id,
    movies.title AS suggestion_title,
    1 AS rank
  FROM actors
  INNER JOIN movies_actors ON (actors.actor_id = movies_actors.actor_id)
  INNER JOIN movies ON (movies.movie_id = movies_actors.movie_id)

  UNION ALL

  SELECT
    searches.title AS entity_term,
    suggestions.movie_id AS suggestion_id,
    suggestions.title AS suggestion_title,
    RANK() OVER (PARTITION BY searches.movie_id ORDER BY cube_distance(searches.genre, suggestions.genre)) AS rank
  FROM movies AS searches
  INNER JOIN movies AS suggestions ON
    (searches.movie_id <> suggestions.movie_id) AND
    (cube_enlarge(searches.genre, 2, 18) @> suggestions.genre)
)
SELECT suggestion_id, suggestion_title
FROM suggestions
WHERE entity_term = query
ORDER BY rank, suggestion_id
LIMIT result_limit;
$BODY$
LANGUAGE sql;

Minha segunda tentativa é correta e rápida. Eu o otimizei empurrando o filtro do CTE para baixo em cada parte da união.

Eu removi esta linha da consulta externa:

WHERE entity_term = query

Eu adicionei esta linha à primeira consulta interna:

WHERE actors.name = query

Eu adicionei esta linha à segunda consulta interna:

WHERE movies.title = query

A segunda função leva cerca de 10ms para retornar o mesmo resultado.

Nada difere no banco de dados além das definições de função.

Por que o PostgreSQL produz planos tão diferentes para essas duas consultas logicamente equivalentes?

O EXPLAIN ANALYZEplano da primeira função fica assim:

                                                                                       Limit  (cost=7774.18..7774.19 rows=5 width=44) (actual time=1738.566..1738.567 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=332.56..7337.19 rows=19350 width=285) (actual time=7.113..1577.823 rows=383024 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=332.56..996.80 rows=11168 width=33) (actual time=7.113..22.258 rows=11168 loops=1)
                 ->  Hash Join  (cost=332.56..885.12 rows=11168 width=33) (actual time=7.110..19.850 rows=11168 loops=1)
                       Hash Cond: (movies_actors.movie_id = movies.movie_id)
                       ->  Hash Join  (cost=143.19..514.27 rows=11168 width=18) (actual time=4.326..11.938 rows=11168 loops=1)
                             Hash Cond: (movies_actors.actor_id = actors.actor_id)
                             ->  Seq Scan on movies_actors  (cost=0.00..161.68 rows=11168 width=8) (actual time=0.013..1.648 rows=11168 loops=1)
                             ->  Hash  (cost=80.86..80.86 rows=4986 width=18) (actual time=4.296..4.296 rows=4986 loops=1)
                                   Buckets: 1024  Batches: 1  Memory Usage: 252kB
                                   ->  Seq Scan on actors  (cost=0.00..80.86 rows=4986 width=18) (actual time=0.009..1.681 rows=4986 loops=1)
                       ->  Hash  (cost=153.61..153.61 rows=2861 width=19) (actual time=2.768..2.768 rows=2861 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 146kB
                             ->  Seq Scan on movies  (cost=0.00..153.61 rows=2861 width=19) (actual time=0.003..1.197 rows=2861 loops=1)
           ->  Subquery Scan on "*SELECT* 2"  (cost=6074.48..6340.40 rows=8182 width=630) (actual time=1231.324..1528.188 rows=371856 loops=1)
                 ->  WindowAgg  (cost=6074.48..6258.58 rows=8182 width=630) (actual time=1231.324..1492.106 rows=371856 loops=1)
                       ->  Sort  (cost=6074.48..6094.94 rows=8182 width=630) (actual time=1231.307..1282.550 rows=371856 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: external sort  Disk: 21584kB
                             ->  Nested Loop  (cost=0.27..3246.72 rows=8182 width=630) (actual time=0.047..909.096 rows=371856 loops=1)
                                   ->  Seq Scan on movies searches  (cost=0.00..153.61 rows=2861 width=315) (actual time=0.003..0.676 rows=2861 loops=1)
                                   ->  Index Scan using movies_genres_cube on movies suggestions_1  (cost=0.27..1.05 rows=3 width=315) (actual time=0.016..0.277 rows=130 loops=2861)
                                         Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
   ->  Sort  (cost=436.99..437.23 rows=97 width=44) (actual time=1738.565..1738.566 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..435.38 rows=97 width=44) (actual time=1281.905..1738.531 rows=43 loops=1)
               Filter: (entity_term = 'Die Hard'::text)
               Rows Removed by Filter: 382981
 Total runtime: 1746.623 ms

O EXPLAIN ANALYZEplano da segunda consulta fica assim:

 Limit  (cost=43.74..43.76 rows=5 width=44) (actual time=1.231..1.234 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=4.86..43.58 rows=5 width=391) (actual time=1.029..1.141 rows=43 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=4.86..20.18 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                 ->  Nested Loop  (cost=4.86..20.16 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                       ->  Nested Loop  (cost=4.58..19.45 rows=2 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                             ->  Index Scan using actors_name on actors  (cost=0.28..8.30 rows=1 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                                   Index Cond: (name = 'Die Hard'::text)
                             ->  Bitmap Heap Scan on movies_actors  (cost=4.30..11.13 rows=2 width=8) (never executed)
                                   Recheck Cond: (actor_id = actors.actor_id)
                                   ->  Bitmap Index Scan on movies_actors_actor_id  (cost=0.00..4.30 rows=2 width=0) (never executed)
                                         Index Cond: (actor_id = actors.actor_id)
                       ->  Index Scan using movies_pkey on movies  (cost=0.28..0.35 rows=1 width=19) (never executed)
                             Index Cond: (movie_id = movies_actors.movie_id)
           ->  Subquery Scan on "*SELECT* 2"  (cost=23.31..23.40 rows=3 width=630) (actual time=0.982..1.081 rows=43 loops=1)
                 ->  WindowAgg  (cost=23.31..23.37 rows=3 width=630) (actual time=0.982..1.064 rows=43 loops=1)
                       ->  Sort  (cost=23.31..23.31 rows=3 width=630) (actual time=0.963..0.971 rows=43 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: quicksort  Memory: 28kB
                             ->  Nested Loop  (cost=4.58..23.28 rows=3 width=630) (actual time=0.808..0.916 rows=43 loops=1)
                                   ->  Index Scan using movies_title on movies searches  (cost=0.28..8.30 rows=1 width=315) (actual time=0.025..0.027 rows=1 loops=1)
                                         Index Cond: (title = 'Die Hard'::text)
                                   ->  Bitmap Heap Scan on movies suggestions_1  (cost=4.30..14.95 rows=3 width=315) (actual time=0.775..0.844 rows=43 loops=1)
                                         Recheck Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
                                         ->  Bitmap Index Scan on movies_genres_cube  (cost=0.00..4.29 rows=3 width=0) (actual time=0.750..0.750 rows=44 loops=1)
                                               Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
   ->  Sort  (cost=0.16..0.17 rows=5 width=44) (actual time=1.230..1.231 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..0.10 rows=5 width=44) (actual time=1.034..1.187 rows=43 loops=1)
 Total runtime: 1.410 ms

Hoje Seven Databases in Seven Weeks me apresentou aos índices por operador.

Você pode indexar strings para padrões correspondentes às consultas anteriores criando um text_pattern_opsíndice de classe de operador, desde que os valores sejam indexados em letras minúsculas.

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

Usamos o text_pattern_opsporque o título é do tipo texto. Se você precisar indexar varchars, chars ou nomes, use as operações relacionadas: varchar_pattern_ops, bpchar_pattern_opse name_pattern_ops.

Achei o exemplo muito confuso. Por que é útil fazer isso?

Se a coluna for do tipo texto, os outros tipos (varchar, char, name) não seriam convertidos em texto antes de serem usados ​​como um valor de pesquisa?

Como esse índice se comporta de maneira diferente de um que usa o operador padrão?

CREATE INDEX moves_title_pattern ON movies (lower(title));

Estou tentando produzir um relatório de uso para alguns procedimentos armazenados em meu banco de dados. A view view sys.dm_exec_procedure_stats contém um pouco do que eu preciso, então decidi começar por aqui.

Metadados de procedimento de chaves do SQL Server por ID de objeto. Conheço apenas os nomes dos procedimentos, então primeiro tenho que mapear os nomes para IDs usando a função OBJECT_ID .

Existem vários procedimentos para verificar, então quero colocar seus nomes em uma tabela temporária antes de processá-los.

Achei que seria legal pegar o id do objeto de uma coluna computada na tabela temporária, então criei uma tabela assim:

USE msdb;
GO

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

Com essa tabela no lugar, a maneira óbvia de obter as contagens de execução ficou assim:

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

Mas ele retorna um conjunto de resultados vazio!

Esta consulta mostra por que o conjunto de resultados está vazio:

SELECT name, [object_id]
FROM #procs;

A coluna object_id é NULL, então uma junção interna não produzirá linhas:

name                 object_id
-------------------- -----------
dbo.sp_send_dbmail   NULL
dbo.sp_add_job       NULL
dbo.sp_start_job     NULL

Não é que as colunas calculadas sejam quebradas para tabelas temporárias. Se eu adicionar uma coluna computada para inverter o nome, ela produzirá o valor correto quando selecioná-la:

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);

SELECT name, [object_id], reverse_name
FROM #procs;

Resultado:

name                 object_id   reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail   NULL        liambd_dnes_ps.obd
dbo.sp_add_job       NULL        boj_dda_ps.obd
dbo.sp_start_job     NULL        boj_trats_ps.obd

Por que OBJECT_ID retorna NULL aqui?

Eu mantenho um banco de dados de arquivo que armazena dados históricos em exibições particionadas. A coluna de particionamento é um datetime. Cada tabela sob a exibição armazena um mês de dados.

Restringimos os eventos em cada tabela com uma restrição de verificação na coluna de data e hora. Isso permite que o otimizador limite as tabelas que são pesquisadas para consultas que filtram na coluna de data e hora do evento.

Os nomes das restrições de verificação foram gerados pelo SQL Server, portanto, é difícil saber o que elas fazem olhando seus nomes.

Eu quero que os nomes das restrições tenham o formato 'CK_TableName_Partition'.

Posso gerar um script de renomeação usando esta consulta e copiando dados da coluna sql_text. A cláusula WHERE corresponde a restrições de verificação cujos nomes parecem ter sido gerados pelo SQL Server:

SELECT
  checks.name AS check_name,
  tabs.name AS table_name,
  skemas.name AS schema_name,
  cols.name AS column_name,
  N'
EXECUTE sys.sp_rename
  @objname = N''' + skemas.name + N'.' + checks.name + N''',
  @newname = N''CK_' + tabs.name + N'_Partition'',
  @objtype = ''OBJECT'';' AS sql_text
FROM sys.check_constraints AS checks
INNER JOIN sys.tables AS tabs ON
  tabs.object_id = checks.parent_object_id
INNER JOIN sys.schemas AS skemas ON
  skemas.schema_id = tabs.schema_id
INNER JOIN sys.columns AS cols ON
  tabs.object_id = cols.object_id AND
  cols.column_id = checks.parent_column_id
WHERE checks.name LIKE (
  N'CK__' + SUBSTRING(tabs.name, 1, 9) +
  N'__' + SUBSTRING(cols.name, 1, 5) +
  N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2
)
ORDER BY table_name;

A saída se parece com isto:

check_name  table_name  schema_name column_name sql_text
CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT';
...
CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';

O resultado da consulta parece estar correto e o servidor a executa rapidamente.

Mas o nó raiz do plano de execução tem um aviso:

A conversão de tipo na expressão (CONVERT_IMPLICIT(nvarchar(128),[o].[nome],0)) pode afetar "CardinalityEstimate" na escolha do plano de consulta

O que isso significa neste contexto? Um filtro tão complexo está confundindo o otimizador? É algo que eu deveria me preocupar?

Estou tentando encontrar uma expressão confiável e eficiente para calcular quantos dígitos decimais são necessários para escrever um número inteiro positivo.

Matematicamente, o número de dígitos decimais em um inteiro né 1 + floor(log(n)), onde log é o logaritmo comum (base 10).

Existem várias maneiras de construir uma expressão equivalente usando funções internas, mas algumas delas fornecem resultados incorretos. Alguém pode explicar por quê?

Aqui está um exemplo.

Como calcular o log?

A maneira mais simples de calcular o logaritmo comum é usar a LOG10função.

Se preferir uma função para todos os logaritmos, você pode usar a LOGfunção e especificar a base 10 com o segundo parâmetro.

Antes de 2012, a função do SQL Server LOGcalculava apenas o log natural (base e=2,71828...). Você pode calcular o logaritmo para uma base arbitrária de um número dividindo o logaritmo natural do número pelo logaritmo natural da base.

A consulta a seguir calcula todas as três expressões para alguns valores de exemplo:

SELECT
  Number,
  LOG(Number, 10) AS LogAB,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Resultado:

Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932

Escolhi os valores 999, 1000 e 1001 porque 1000 é um ponto onde o número de dígitos aumenta. 999 tem 3 dígitos, 1000 tem 4.

O valor das três expressões é visivelmente o mesmo e parece correto.

Vamos passar para o degrau do chão.

Como calcular o piso?

Você pode obter o piso de cada log no exemplo anterior usando uma consulta como esta:

SELECT
  Number,
  FLOOR(LOG(Number, 10)) AS FloorLogAB,
  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Resultado:

Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3

Os valores de cada expressão para 999 e 1001 são iguais e corretos. Se somarmos 1 a cada valor, teríamos uma contagem de 3 dígitos em 999 e uma contagem de 4 dígitos em 1001.

Os valores para 1000 não são os mesmos! Se adicionarmos 1 a cada valor, teremos uma contagem de 4 dígitos em 1000 se usarmos a LOG10função e uma contagem de 3 dígitos se usarmos a LOGfunção em qualquer uma das formas.

Há uma incoerência aqui!

Como FLOOR(3) pode ser igual a 2?

A implicação é clara: usar a LOGfunção me daria uma contagem incorreta para alguns valores, então devo usar a LOG10função.

Mas o valor de cada expressão de log em si é idêntico e correto. Por que a função floor produz valores diferentes de sua entrada?

O agrupamento padrão do meu servidor é Latin1_General_CI_AS, conforme determinado por esta consulta:

SELECT SERVERPROPERTY('Collation') AS Collation;

Fiquei surpreso ao descobrir que, com esse agrupamento, posso corresponder a caracteres não numéricos em strings usando o predicado LIKE '[0-9]'.

Por que no agrupamento padrão isso acontece? Não consigo pensar em um caso em que isso seria útil. Sei que posso contornar o comportamento usando um agrupamento binário, mas parece uma maneira estranha de implementar o agrupamento padrão.

A filtragem de dígitos produz caracteres não dígitos

Posso demonstrar o comportamento criando uma coluna que contém todos os valores possíveis de caractere de byte único e filtrando os valores com o predicado de correspondência de dígitos.

A instrução a seguir cria uma tabela temporária com 256 linhas, uma para cada ponto de código na página de código atual:

WITH P0(_) AS (SELECT 0 UNION ALL SELECT 0),
P1(_) AS (SELECT 0 FROM P0 AS L CROSS JOIN P0 AS R),
P2(_) AS (SELECT 0 FROM P1 AS L CROSS JOIN P1 AS R),
P3(_) AS (SELECT 0 FROM P2 AS L CROSS JOIN P2 AS R),
Tally(Number) AS (
  SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM P3
)
SELECT Number AS CodePoint, CHAR(Number) AS Symbol
INTO #CodePage
FROM Tally
WHERE Number >= 0 AND Number <= 255;

Cada linha contém o valor inteiro do ponto de código e o valor do caractere do ponto de código. Nem todos os valores de caracteres podem ser exibidos - alguns dos pontos de código são estritamente caracteres de controle. Aqui está uma amostra seletiva da saída de SELECT CodePoint, Symbol FROM #CodePage:

0   
1   
2   
...
32   
33  !
34  "
35  #
...
48  0
49  1
50  2
...
65  A
66  B
67  C
...
253 ý
254 þ
255 ÿ

Eu esperaria ser capaz de filtrar a coluna Symbol para encontrar caracteres de dígitos usando um predicado LIKE e especificando o intervalo de caracteres '0' a '9':

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]';

Produz uma saída surpreendente:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9
178 ²
179 ³
185 ¹
188 ¼
189 ½
190 ¾

O conjunto de pontos de código 48 a 57 é o que eu espero. O que me surpreende é que os símbolos para sobrescritos e frações também estão incluídos no conjunto de resultados!

Pode haver uma razão matemática para pensar em expoentes e frações como números, mas parece errado chamá-los de dígitos.

Usando o agrupamento binário como uma solução alternativa

Entendo que, para obter o resultado esperado, posso forçar o agrupamento binário correspondente Latin1_General_BIN:

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]' COLLATE Latin1_General_BIN;

O conjunto de resultados inclui apenas os pontos de código 48 a 57:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9

Arquivamento para evitar o esgotamento do ID

Estamos prestes a ficar sem IDs em uma das tabelas do nosso sistema de registro OLTP. É meu trabalho encontrar uma maneira de 'arquivar' os dados de linha existentes na tabela completa e em todas as tabelas de referência para que possamos continuar registrando novos dados nas tabelas.

A maneira mais rápida de arquivar dados em uma tabela é simplesmente renomear a tabela de destino e todos os objetos dependentes -- tabelas de junção, índices, restrições. É rápido e mantém os dados intactos. Para terminar o trabalho, temos que criar novas cópias vazias de todos os objetos com seus nomes antigos. Se fizermos todas essas operações em uma transação, os procedimentos armazenados que inserem novos dados não falharão devido à falta de tabelas.

Uma tentativa de resolver usando o PowerShell

Montei um script do PowerShell para gerar um script T-SQL para executar a operação de arquivamento.

O script não está gerando tantas instruções de renomeação quanto deveria e não entendo o porquê.

O script carrega o SMO, define os nomes dos objetos de destino e configura uma conexão somente de captura com o servidor - isso é para que eu possa capturar comandos de renomeação para inspeção posterior:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'CLOUDCORP\LOGGING'
$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

Em seguida, ele cria uma matriz de objetos que serão arquivados por renomeação. A própria tabela de destino, todas as tabelas de referência e todas as chaves e todos os índices dessas tabelas devem ser arquivados:

$Database = $Server.Databases[$DatabaseName]

$Table = @($Database.Tables[$TableName, $TableSchemaName])

$ReferencingTables = $Database.Tables.ForeignKeys |
    ? { $_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName } |
    % { $_.Parent } | Sort ID | Get-Unique

$TablesToArchive = $Table + $ReferencingTables

$ObjectsToArchive = $TablesToArchive + $TablesToArchive.Indexes + $TablesToArchive.ForeignKeys

A parte do problema é a próxima. Para cada objeto a ser arquivado, tento capturar uma instrução de renomeação.

$ObjectsToArchive | % { $_.Rename($_.Name + '_archive') }
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text

Saída inesperada

Depois de executar isso no meu banco de dados, a matriz $ObjectsToArchivecontém 48 itens. A expressão $objectsToArchive.Nameproduz uma lista como esta:

tbDataRequests
tbDataRequestPenguinServers
tbDataRequestLegs
tbDataRequestPaidDataRequests
tbDataRequestResponses
tbDataRequestUUIDMappings
tbDataRequestRouteNodes
tbRequestDataRequestQ
tbRequestDataRequests
IX_DataRequests_DataRequestDT
PK_DataRequests
PK_DataRequestPenguinServers
PK_DataRequestLegs
IX_DataRequestPaidDataRequests_PaidDataRequestID
PK_DataRequestPaidDataRequests
IX_DataRequestResponses_ScrapeResponseID
PK_DataRequestResponses
IX_DataRequestUUIDMappings_DataRequestID
PK_DataRequestUUIDMappings
PK_Geo_DataRequestRouteNodes
IX_RequestDataRequestQ_FK_RequestUUIDID
PK_RequestDataRequestQ
IX_RequestDataRequests_DataRequestID
PK_RequestDataRequests
FK_DataRequests_OrderType_OrderTypeID
FK_DataRequests_DatePairs_DatePairID
FK_DataRequests_CustomerCounts_CustomerCountID
FK_DataRequests_Routes_RouteID
FK_DataRequests_DataClients_DataClientID
FK_DataRequests_UserCountries_UserCountryID
FK_DataRequests_Websites_WebsiteID
FK_DataRequestPenguinServers_PenguinServers_JacqiardServerID
FK_DataRequestPenguinServers_DataRequests_DataRequestID
FK_DataRequestLegs_DatePairs_DatePairID
FK_DataRequestLegs_LegTypes_LegTypeID
FK_DataRequestLegs_Routes_RouteID
FK_DataRequestLegs_DataRequests_DataRequestID
FK_DataRequestPaidDataRequests_PaidDataRequests_PaidDataRequestID
FK_DataRequestPaidDataRequests_DataRequests_DataRequestID
FK_DataRequestResponses_DataRequests_DataRequestID
FK_DataRequestResponses_ScrapeResponses_ScrapeResponseID
FK_DataRequestUUIDMappings_DataRequests_DataRequestID
FK_DataRequestUUIDMappings_DataRequestUUIDs_Scrape RequestUUIDID
FK_Geo_DataRequestRouteNodes_DataRequests_DataRequestID
FK_RequestDataRequestQ_RequestUUIDs_RequestUUIDID
FK_RequestDataRequestQ_DataRequests_DataRequestID
FK_RequestDataRequests_Requests_RequestID
FK_RequestDataRequests_DataRequests_RequestID

Deve haver uma instrução de renomeação para cada objeto neste array. No entanto, a propriedade CapturedSql contém apenas 10 instruções de renomeação. A expressão $RenameCommands | ? { $_ -like '*sp_rename*' }produz uma lista como esta:

EXEC dbo.sp_rename @objname = N'[Logging].[tbDataRequests]', @newname = N'tbDataRequests_archive', @objtype = N'OBJECT'
EXEC sp_rename N'[Logging].[tbDataRequests].[IX_DataRequests_DataRequestDT]', N'IX_DataRequests_DataRequestDT_archive', N'INDEX'
EXEC sp_rename N'[Logging].[tbDataRequests].[PK_DataRequests]', N'PK_DataRequests_archive', N'INDEX'
EXEC sp_rename N'[FK_DataRequests_OrderType_OrderTypeID]', N'FK_DataRequests_OrderType_OrderTypeID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DatePairs_DatePairID]', N'FK_DataRequests_DatePairs_DatePairID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_CustomerCounts_CustomerCountID]', N'FK_DataRequests_CustomerCounts_CustomerCountID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Routes_RouteID]', N'FK_DataRequests_Routes_RouteID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DataClients_DataClientID]', N'FK_DataRequests_DataClients_DataClientID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_UserCountries_UserCountryID]', N'FK_DataRequests_UserCountries_UserCountryID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Websites_WebsiteID]', N'FK_DataRequests_Websites_WebsiteID_archive', N'OBJECT'

Ele está criando scripts de instruções de renomeação para Logging.tbDataRequests e suas chaves e índices, mas não para nenhum dos outros objetos.

O que eu estou fazendo errado aqui?

O .NET Runtime Optimization Service ( mscorsvw) continua consumindo toda a memória da minha estação de trabalho desde que instalei o SQL Server 2012 Service Pack 1.

Consome cerca de 1,6 GB de memória antes de esgotar toda a memória disponível:

Posso interromper o processo, mas isso acontece novamente quando reinicio o computador ou espero o tempo suficiente.

A Techdows sugere o uso do .NET Native Image Generator ( ngen) para resolver o problema.

Eu abri um prompt de comando e executei comandos como este:

cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319.1
ngen executeQueuedItems

O consumo de memória mscorsvwcaiu para cerca de 20-30 MB e ngenconsumiu toda a memória restante.

ngengerado apenas mensagens de erro até que eu matei o processo. As primeiras linhas de saída ficaram assim:

Microsoft (R) CLR Native Image Generator - Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.
Failed to load dependency Microsoft.DataWarehouse of assembly
Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.VisualStudio.DataTools.Interop of assembly 
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.Data.ConnectionUI of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The located
assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)

Todas as mensagens dizem que o sistema não pôde localizar um assembly ou que uma definição de manifesto não corresponde a uma referência.

Como solução alternativa, desabilitei os Serviços do Windows clr_optimization_v4.0.30319_32e clr_optimization_v4.0.30319_64.

Isso oculta o problema em vez de resolvê-lo e impede que todos os assemblies .NET sejam otimizados.

O que mais posso tentar para resolver este problema?

Digamos que você queira consultar um banco de dados para descobrir os tipos de gatilho que ele contém. Uma maneira de fazer isso é usar a função OBJECTPROPERTY em todos os objetos acionadores do banco de dados.

Às vezes, a função OBJECTPROPERTY produz um resultado confuso. Sua saída parece depender do contexto do banco de dados.

A consulta de exemplo a seguir retorna uma linha para cada um dos gatilhos sysmail no msdb:

SELECT
  object_id,
  name,
  OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS IsInsertTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS IsUpdateTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS IsDeleteTrigger
FROM msdb.sys.objects
WHERE
  [type] = 'TR' AND
  name LIKE 'trig_sysmail_%';
GO

A intenção é descobrir qual ação DML disparará cada gatilho. Por exemplo, a IsInsertTriggercoluna contém 1 se o acionador for definido como AFTER INSERT, e 0 caso contrário.

Quando executo a consulta no contexto do msdb, o conjunto de resultados contém 0 ou 1 em cada uma das colunas computadas. Se parece com isso:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         0               1               0
745105745   trig_sysmail_account         0               1               0
761105802   trig_sysmail_profileaccount  0               1               0
777105859   trig_sysmail_profile_delete  0               0               1
793105916   trig_sysmail_servertype      0               1               0
809105973   trig_sysmail_server          0               1               0
825106030   trig_sysmail_configuration   0               1               0
841106087   trig_sysmail_mailitems       0               1               0
857106144   trig_sysmail_attachments     0               1               0
873106201   trig_sysmail_log             0               1               0

Quando executo a consulta no contexto do mestre, o conjunto de resultados contém NULL em cada uma das colunas computadas. Se parece com isso:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         NULL            NULL            NULL
745105745   trig_sysmail_account         NULL            NULL            NULL
761105802   trig_sysmail_profileaccount  NULL            NULL            NULL
777105859   trig_sysmail_profile_delete  NULL            NULL            NULL
793105916   trig_sysmail_servertype      NULL            NULL            NULL
809105973   trig_sysmail_server          NULL            NULL            NULL
825106030   trig_sysmail_configuration   NULL            NULL            NULL
841106087   trig_sysmail_mailitems       NULL            NULL            NULL
857106144   trig_sysmail_attachments     NULL            NULL            NULL
873106201   trig_sysmail_log             NULL            NULL            NULL

O MSDN observa que a função OBJECTPROPERTY retorna NULL quando:

1. o nome da propriedade não é válido.
2. o ID do objeto não é válido.
3. id é um tipo de objeto sem suporte para a propriedade especificada.
4. o chamador não tem permissão para visualizar os metadados do objeto.

Posso descartar os motivos 1 e 3 porque a consulta retorna o resultado correto no contexto do msdb.

A princípio, pensei que poderia ser um problema de permissões entre bancos de dados (motivo 4), mas sou administrador do sistema no servidor.

Isso deixa o motivo 2, que me deixa com estas perguntas:

O ID do objeto é inválido em uma consulta entre bancos de dados?

A função OBJECTPROPERTY de qual banco de dados está sendo chamada?

O primeiro lote do script a seguir chama o procedimento armazenado sp_trace_createcom parâmetros na ordem da documentação; o segundo lote troca as posições dos parâmetros @tracefilee @options:

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:\temp\TestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

O primeiro lote cria um novo rastreamento, seleciona seu id e fecha o rastreamento. Um conjunto de resultados é retornado:

@new_trace_id
2

O segundo lote falha com um erro:

Msg 214, Nível 16, Estado 3, Procedimento sp_trace_create, Linha 1 O procedimento espera o parâmetro '@tracefile' do tipo 'nvarchar(256)'.

Por que a ordem dos parâmetros afeta a saída do procedimento armazenado sp_trace_create? E por que ele falha com uma mensagem de erro tão enganosa?

Minha organização atualizou recentemente todas as suas instâncias de produção do SQL Server 2008 para o Service Pack 3 , atualização cumulativa 1 . É minha responsabilidade garantir que nossos ambientes de banco de dados de desenvolvimento estejam executando a mesma versão que a produção, minimizando o tempo de inatividade e a interrupção de nossos desenvolvedores.

Cada ambiente de desenvolvimento contém seis instâncias do SQL Server. Temos oito ambientes de desenvolvimento compartilhados igualmente entre duas máquinas. Para cada instância, preciso instalar o Service Pack 3 e, em seguida, instalar a atualização cumulativa 1. Cada instalador reinicia o serviço do Windows da instância durante o processo de atualização, portanto, preciso avisar com antecedência aos desenvolvedores que usam a instância. O instalador demora muito para se configurar, então estou atualizando cada ambiente em lote para agilizar o processo.

Como uma máquina hospeda várias instâncias, uma reinicialização potencialmente interrompe todos os desenvolvedores que usam a máquina, mesmo que a instância não esteja sendo atualizada. Portanto, quero evitar reinicializações desnecessárias. Mas somente na primeira execução a instalação me disse "Você deve reiniciar o computador para concluir a instalação do SQL Server".

Onde o instalador não me diz que devo reiniciar a máquina, não quero reiniciar desnecessariamente. Meu colega DBA sênior me disse que eu deveria reiniciar a máquina host de qualquer maneira, para manter o ambiente 'limpo'.

Que coisas ruins podem acontecer se eu não reiniciar a máquina host após atualizar uma instância?

Eu uso o SQL Server Management Studio para examinar a definição de objetos em meu banco de dados.

Por exemplo, para examinar a definição da tabela dbo.MSreplication_options no banco de dados mestre, eu clicaria com o botão direito do mouse na tabela no Object Explorer e escolheria 'Script Table as... > CREATE to > New Query Editor Window... '.

Como você pode ver na captura de tela do estado atual do meu Management Studio, uma nova guia é criada para um novo script chamado "SQLQuery9.sql", que contém a definição da tabela.

A essa altura, já examinei alguns outros objetos dessa maneira e esqueci o que a maioria dos outros scripts chamados "SQLQuery1.sql", "SQLQuery2.sql" e assim por diante contém.

Como posso configurar o management studio para dar ao script um nome mais descritivo, talvez contendo o nome do objeto, como "dbo.MSreplication_options.sql", por exemplo.

Minha versão do Management Studio é 10.0.5500.0.