All perguntas(dba)

Consultar o sys.dm_tran_locksDMV nos mostra quais sessões (SPIDs) estão mantendo bloqueios em recursos como tabela, página e linha.

Para cada bloqueio adquirido, existe alguma maneira de determinar qual instrução SQL (excluir, inserir, atualizar ou selecionar) causou esse bloqueio?

Eu sei que a most_recent_query_handlecoluna do sys.dm_exec_connectionsDMV nos dá o texto da última consulta executada, mas várias vezes outras consultas foram executadas antes na mesma sessão (SPID) e ainda estão mantendo bloqueios.

Já utilizo o sp_whoisactiveprocedimento (de Adam Machanic) e ele mostra apenas a consulta que está no buffer de entrada no momento (pense DBCC INPUTBUFFER @spid), que nem sempre (e no meu caso geralmente nunca) é a consulta que adquiriu o bloqueio.

Por exemplo:

1. abrir transação/sessão
2. exec uma instrução (que mantém um bloqueio em um recurso)
3. exec outra instrução na mesma sessão
4. abra outra transação/sessão e tente modificar o recurso bloqueado na etapa 2.

O sp_whoisactiveprocedimento apontará a instrução na etapa 3, que não é a responsável pelo bloqueio e, portanto, não é útil.

Essa pergunta surgiu ao fazer uma análise usando o recurso Blocked Process Reports , para encontrar a causa raiz dos cenários de bloqueio na produção. Cada transação executa várias consultas e, na maioria das vezes, a última (que é mostrada no buffer de entrada no BPR) raramente é a que mantém o bloqueio.

Eu tenho uma pergunta de acompanhamento: Estrutura para identificar efetivamente as consultas de bloqueio

Digamos que esta seja a data de amostra proveniente de uma junção de 2 tabelas. O banco de dados é Postgres 9.6

id  product_id  invoice_id  amount       date
1    PROD1       INV01       2          01-01-2018
2    PROD2       INV02       3          01-01-2018
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

Quero saber se é possível de forma otimizada:

1. Obtenha todos os PRODx com seus respectivos INVx que possuem a data mais recente, mas por product_id. Observe que os registros não utilizados de um dia podem ser relatados para um novo. Isso significa:

id  product_id  invoice_id  amount       date
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

2. Obtenha valores somados diários para cada PRODx, mas preencha as lacunas com os anteriores se o dia não existir.

Isso significa:

 product_id    amount       date
   PROD1         2          01-01-2018
   PROD2         3          01-01-2018
   PROD1         2          02-01-2018
   PROD2         3          02-01-2018
   PROD1         2          03-01-2018
   PROD2         3          03-01-2018
   PROD1         2          04-01-2018
   PROD2         3          04-01-2018
   PROD1         3          05-01-2018
   PROD2         3          05-01-2018
   PROD1         3          06-01-2018
   PROD2         3          06-01-2018
   PROD1         3          07-01-2018
   PROD2         3          07-01-2018
   PROD1         3          08-01-2018
   PROD2         7          08-01-2018

Alguns pensamentos:

1. Para a primeira pergunta eu poderia obter o max(date)para cada PRODx e escolher para cada PRODx as linhas que possuem o date=with max(date)mas eu queria saber se existe uma maneira mais rápida de obter isso dado um grande número de registros no banco de dados

2. Para a segunda pergunta, eu poderia gerar uma série de datas para o intervalo necessário e depois usar WITH rows Ase fazer o agrupamento de consultas por product_ide sumpor quantidade e depois selecionar para cada data os valores anteriores rowscom a limit 1mas isso também não soa tão otimizado.

Aguardo qualquer entrada. Obrigada.

Edição posterior: Tentando experimentar DISTINCT ON ().

• Se tiver distinct on(product_id, invoice_id), não recebo apenas os mais recentes para a data mais recente. Se houver fatura_ids no passado, além da data mais recente, eles serão devolvidos
• Se eu tiver distinct on (product_id)então ele retorna da data mais recente, mas como normal, apenas as últimas linhas mesmo que no último dia eu tenha duas posições para PROD1.

Basicamente, preciso de algo como 'Preciso para a data mais recente, todos os product_ids e seus invoice_ids, tendo em mente que um product_id pode ter vários invoice_ids'

Edição posterior 2:

Executar uma consulta como para a primeira pergunta parece ser razoavelmente rápido:

select product_id, invoice_id, amount
from mytable inner join myOtherTable on...
             inner join (select max(date) as last_date, product_id 
                         from mytable 
                         group by product_id) sub on mytable.date = 
                         sub.last_date 

Estou tendo problemas ao tentar alterar dinamicamente o contexto do SSMS para o banco de dados especificado no SQL dinâmico:

EXEC sys.sp_executesql N'USE db1 ' ;

Ele é executado com sucesso, mas o contexto do banco de dados do SSMS não muda.

Eu tentei uma pequena modificação no acima assim

DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql

Novamente, ele é executado com sucesso, mas o banco de dados não muda.

Estou com problemas com o planejamento de consultas do PostgreSQL 9.6. Minha consulta está assim:

SET role plain_user;

SELECT properties.*
FROM properties
JOIN entries_properties
  ON properties.id = entries_properties.property_id
JOIN structures
  ON structures.id = entries_properties.entry_id 
WHERE structures."STRUKTURBERICHT" != ''
  AND properties."COMPOSITION" LIKE 'Mo%'
  AND (
    properties."NAME" LIKE '%VASP-ase-preopt%'
    OR properties."CALCULATOR_ID" IN (7,22,25)
  )
AND properties."TYPE_ID" IN (6)

Eu tenho a segurança em nível de linha habilitada para as tabelas usadas acima.

• com set enable_nestloop = True, o planejador de consultas executa a junção do Nested Loop com um tempo total de execução de cerca de 37 segundos: https://explain.depesz.com/s/59BR

• com set enable_nestloop = False, o método Hash Join é usado e o tempo de consulta é de cerca de 0,3 segundos: https://explain.depesz.com/s/PG8E

Eu fiz VACUUM ANALYZEantes de executar as consultas, mas não ajudou.

Eu sei que não é uma boa prática set enable_nestloop = False, e quaisquer outras opções semelhantes para o planejador. Mas como eu poderia "convencer" o planejador a usar junções de hash sem desabilitar loops aninhados?

Reescrever a consulta é uma opção.

Se eu executar a mesma consulta em uma função que ignora o RLS, ela será executada muito rapidamente. A política de segurança em nível de linha tem esta aparência:

CREATE POLICY properties_select
ON properties
FOR SELECT
USING (
  (
    properties.ouid = get_current_user_id()
    AND properties.ur
  )
  OR (
    properties.ogid in (select get_current_groups_id())
    AND properties.gr
  )
  OR properties.ar
);

Quaisquer idéias ou sugestões seriam muito apreciadas.

A página que descreve as novidades do Postgres 10 menciona “Tabelas de transição para gatilhos”.

Tabelas de transição para gatilhos

Esse recurso torna AFTER STATEMENTos gatilhos úteis e de alto desempenho, expondo, conforme apropriado, as linhas antigas e novas às consultas. Antes desse recurso, os AFTER STATEMENTgatilhos não tinham acesso direto a eles, e as soluções alternativas eram bizantinas e tinham desempenho ruim. Muita lógica de gatilho agora pode ser escrita como AFTER STATEMENT, evitando a necessidade de fazer as dispendiosas trocas de contexto em cada linha que os gatilhos FOR EACH ROW exigem.

O que é uma tabela de transição?

Uma vez por semana, nas últimas 5 semanas, na mesma hora do dia (de manhã cedo, pode ser baseado na atividade do usuário quando as pessoas estão começando a usá-lo), o SQL Server 2016 (AWS RDS, espelhado) começa a expirar muito consultas.

UPDATE STATISTICS em todas as tabelas sempre corrige isso imediatamente.

Após a primeira vez, fiz com que atualizasse todas as estatísticas em todas as mesas todas as noites (em vez de semanalmente), mas ainda aconteceu (cerca de 8 horas após a atualização das estatísticas, mas não todos os dias).

Na última vez, habilitei o Repositório de Consultas para ver se conseguia encontrar qual era o plano de consulta/consulta específico. Acho que consegui resumir em um:

Depois de encontrar essa consulta, adicionei um índice recomendado que estava faltando nessa consulta não usada com frequência (mas que toca em muitas tabelas usadas com frequência).

O plano de consulta ruim estava fazendo uma varredura de índice (em uma tabela com apenas 10 mil linhas). Outros planos de consulta que retornavam em milissegundos costumavam fazer a mesma varredura. O plano de consulta mais recente, após criar o novo índice, faz apenas buscas. Mas mesmo sem esse índice, 99% das vezes, ele estava retornando em alguns milissegundos, mas depois, semanalmente, demorava > 40 segundos.

• Ruim que expira: http://brentozar.com/pastetheplan/?id=rymaWt56e
• Planos anteriores que não expiram: http://brentozar.com/pastetheplan/?id=HyN7ftcpe
• Novo plano com novo índice: http://brentozar.com/pastetheplan/?id=ryLuGKcag

Isso começou a acontecer depois de mudar para o SQL Server 2016 a partir de 2012.

DBCC CHECKDB não retorna erros.

1. O novo índice resolverá o problema, fazendo com que ele nunca mais escolha o plano ruim?
2. Devo "forçar" o plano que funciona bem agora?
3. Como posso garantir que isso não aconteça com outra consulta/plano?
4. Isso é um sintoma de um problema maior?

Índices que acabei de adicionar:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Texto completo da consulta:

https://pastebin.com/Z5szPBfu (gerado por LINQ, posso/devo ser capaz de otimizar as colunas selecionadas, mas deve ser irrelevante para este problema)

A [:ascii:]classe funciona no Postgres? Ele não está listado em sua ajuda , no entanto, vejo exemplos na web que o utilizam.

Eu tenho um banco de dados UTF-8, onde collation e c_typ e são en_US.UTF-8, e a versão do Postgres é 9.6.2. Quando procuro linhas não ASCII como esta:

select title from wallabag_entry where title ~ '[^[:ascii:]]';

Recebo símbolos Unicode e não Unicode (saída completa está aqui ) :

Сталинская правозащитница: мать Меленкова бабушка Настя
Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?
Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев
Как комиссар Крекшин в 1740-е чуть не отменил историю России
Have you heard of Saint Death? Don’t pray to her.
Архаїчна українська мова: перевага чи недолік?
Гренада не их
China’s marriage rate is plummeting because women are choosing autonomy over 

O que há de errado com esta consulta?

Quais são os motivos para um plano estar ausente do cache para procedimentos armazenados?

1. WITH RECOMPILE
2. SQL dinâmico
3. Código criptografado
4. Mudanças significativas de dados
5. Atualizar estatísticas
6. O que mais?

Eu trabalhei em 2 servidores (SQL Server 2008 R2 e SQL Server 2012) recentemente que não tinham planos em cache para procedimentos armazenados com muitos recursos. Muitas, talvez todas, as instruções dentro dos procedimentos armazenados também não tinham planos em cache. Alguns dos procedimentos armazenados são executados com bastante frequência, algumas vezes por segundo.

Nenhuma pressão de memória. Um dos servidores tem muito mais hardware do que o necessário.

Eu pensei que os planos ausentes eram devido a criações de tabelas temporárias no meio dos procedimentos armazenados, mas isso parece ser uma informação antiga do SQL Server 2000 ou anterior. A partir do SQL Server 2005, as recompilações acontecem no nível de instrução para as instruções após o DDL. Isso é verdade em todos os casos ou ainda pode acontecer em versões mais recentes?

O que mais poderia ser o culpado pelos planos ausentes? Eu dei uma olhada em alguns artigos sobre este tópico, mas nada parece se encaixar.

Otimizar para cargas de trabalho adhoc está habilitado no servidor que estou analisando esta semana. Um dos procedimentos armazenados é executado apenas uma vez por dia. Eu tenho o código para isso. Não tenho o código para aquele que está sendo executado mais de 100 vezes por minuto, mas posso obtê-lo. Não poderei postar o código, mas posso descrevê-lo em relação à minha pergunta.

Não acredito que alguém esteja liberando o cache de procedimento ou descartando buffers limpos. Este cliente está usando o Solarwinds DPA como uma de suas ferramentas de monitoramento. O DPA capturou um dos planos de execução para a instrução no proc armazenado que é chamado uma vez por dia. Essa declaração tem uma quantidade enorme de leituras devido à WHEREcláusula não-sargável. Se o DPA capturou a instrução, então é um plano estimado e estava no cache do plano ao mesmo tempo. Simplesmente não está lá quando estamos solucionando problemas. Vou fazer com que eles comecem a se registrar sp_WhoIsActiveem uma mesa.

estou usando sp_BlitzCache. (Eu trabalho para Brent Ozar Unlimited) Isso mostrará o plano para todo o procedimento armazenado, bem como os planos para as instruções individuais, se existirem. Se eles não existirem, haverá um aviso "Não foi possível encontrar um plano para esta consulta. Os possíveis motivos para isso incluem SQL dinâmico, RECOMPILEdicas e código criptografado". E esse aviso está nas declarações também.

TF 2371 não está no lugar. Estou olhando para as estatísticas de espera. O servidor está bastante entediado. O PLE é superior a 130.000.

Agora tenho o código para mais 2 procedimentos armazenados. Um deles é usar SQL dinâmico exec (@sql)para que saibamos porque não existe um plano para isso. Mas o outro, e este é o que está rodando mais de 100 vezes por minuto, não tem nada fora do comum. A única coisa que se destaca nesse é que as tabelas temporárias estão sendo criadas no meio de mais de 1000 linhas de código. Ele também chama um monte de procedimentos armazenados filhos.

Em relação ao Plan Caching no SQL Server 2008 , não estou vendo nenhum literal >= 8k, mas um dos procedimentos armazenados tem um comentário sobre uma inserção em massa logo antes de chamar outro procedimento armazenado. Mas a inserção em massa não aparece no procedimento armazenado externo que estou vendo. A seção "Limiar de recompilação" do artigo é interessante. O que estou vendo para as tabelas temporárias são toneladas de INSERTs (que podem resultar em milhões de linhas), algumas atualizações e exclusões. Portanto, muitos dados são alterados nas tabelas temporárias. Milhões.

Vamos fazer algumas suposições:

Tenho uma tabela assim:

 a | b
---+---
 a | -1
 a | 17
  ...
 a | 21
 c | 17
 c | -3
  ...
 c | 22

Fatos sobre meu set:

• O tamanho de toda a tabela é de ~ 10 ¹⁰ linhas.

• Eu tenho ~ 100k linhas com valor ana coluna a, semelhante para outros valores (por exemplo c).

• Isso significa ~ 100k valores distintos na coluna 'a'.

• A maioria das minhas consultas lerá todos ou a maioria dos valores de um determinado valor em a, por exemplo select sum(b) from t where a = 'c'.

• A tabela é escrita de forma que os valores consecutivos estejam fisicamente próximos (ou está escrito em ordem, ou assumimos que CLUSTERfoi usado naquela tabela e coluna a).

• A tabela raramente é atualizada, estamos apenas preocupados com a velocidade de leitura.

• A tabela é relativamente estreita (digamos ~25 bytes por tupla, + 23 bytes de sobrecarga).

Agora a pergunta é: que tipo de índice devo usar? Meu entendimento é:

• BTree Meu problema aqui é que o índice BTree será enorme, pois até onde eu sei, ele armazenará valores duplicados (é necessário, pois não pode assumir que a tabela está classificada fisicamente). Se o BTree for enorme, acabo tendo que ler tanto o índice quanto as partes da tabela para as quais o índice aponta. (Podemos usar fillfactor = 100para diminuir um pouco o tamanho do índice.)

• BRIN Meu entendimento é que posso ter um pequeno índice aqui às custas de ler páginas inúteis. Usar um pequeno pages_per_rangesignifica que o índice é maior (o que é um problema com o BRIN já que preciso ler todo o índice), ter um grande pages_per_rangesignifica que vou ler muitas páginas inúteis. Existe uma fórmula mágica para encontrar um bom valor pages_per_rangeque leve em conta esses trade-offs?

• GIN/GiST Não tenho certeza se eles são relevantes aqui, pois são usados ​​principalmente para pesquisa de texto completo, mas também ouvi dizer que eles são bons em lidar com chaves duplicadas. Um GINou GiSTíndice ajudaria aqui?

Outra questão é, o Postgres usará o fato de que uma tabela é CLUSTERed (supondo que não haja atualizações) no planejador de consulta (por exemplo, por busca binária pelas páginas inicial/final relevantes)? Um pouco relacionado, posso apenas armazenar todas as minhas colunas em um BTree e descartar a tabela completamente (ou conseguir algo equivalente, acredito que sejam índices clusterizados no SQL Server)? Existe algum índice híbrido BTree/BRIN que ajudaria aqui?

Prefiro evitar o uso de matrizes para armazenar meus valores, pois minha consulta acabará menos legível dessa maneira (eu entendo que isso reduziria o custo dos 23 bytes por sobrecarga de tupla, reduzindo o número de tuplas).

Eu quero criar um banco de dados usando qualquer um dos RDBMS possíveis. Terá uma tabela com aproximadamente 150 colunas. O objetivo é realizar a busca do vizinho mais próximo de alguns outros objetos. Então é um NNS no espaço de 150 dimensões.

Já tentei usar alguns métodos óbvios como distâncias L1 ou L2 mas claro que demora muito para tabelas com muitas linhas. Também tentei olhar para o KD-tree (note que não testei) e PG-Strom, mas eles não são uma boa solução para dados com muitas dimensões.

Posso de alguma forma melhorar a velocidade da pesquisa descrita usando métodos matemáticos (como KD-tree) ou métodos técnicos (como PG-Strom)?

Vou tentar usar qualquer RDBMS que permita melhorar a velocidade do NNS. Mas MySQL e PostgreSQL são os DBMS mais apropriados para mim.