Atualmente temos 2 DBA's, eu e mais um cara e ambos sentimos a necessidade de um recurso adicional (ou dois). Eu conversei com meu gerente direto sobre esse assunto algumas vezes, mas estou tendo dificuldade em vender a ideia. Mais recentemente, nosso gerente compartilhou conosco que é muito bom criar nossa lista de “valores flexíveis” de coisas que queremos ou sentimos que precisamos fazer, mas o que realmente ajudará é criar “valores rígidos”, mostrando economia em dólares.

Você poderia me ajudar a entender, antes de mais nada, se nossa necessidade percebida é legítima e, em caso afirmativo, como fazemos para criar essa lista de “valores rígidos”. Acho que poderíamos inventar as coisas que precisamos fazer para economizar o dinheiro da empresa, mas isso poderia ser colocado no início de nossa lista de prioridades e muito mais estressante para nós no processo.

Para dar a você uma visão de alto nível do nosso ambiente, aqui estão as coisas que estou analisando em termos do que estamos fornecendo suporte.

Servidores de produção – Mais de 30
bancos de dados de produção – Mais de 200
versões de SQL – 2008/2008R2/2012, olhando para 2014 agora e 2016 no final do ano
Aplicativos (terceiros e desenvolvidos internamente) – Mais de 20
equipes de aplicativos com suporte – 6
virtualizações – 75%
Clusters físicos 25% virtualizados – mais de 3 planejados
Replicação – 1 servidor de distribuição, 2 servidores de assinatura, 24 assinaturas, 6 servidores de publicação, 12 publicações
Envio de logs – 8 primários + mais planejados, 4 secundários + mais planejados, 49 bancos de dados enviados de logs + mais Grupos de Disponibilidade planejados
– Nenhum no momento, mas explorando a possibilidade
Média de atualizações/instalações de aplicativos por ano que impulsionam a mudança de versão ou recursos de DBA – 2-3
Patching (SP, CU's) – Inexistente no momento, a menos que surja um problema Tamanho cumulativo do banco de dados –
Servidores de relatórios de 14 TB ou mais – Uma implantação de expansão consistindo em 2 servidores, nenhum de nós é bem versado no SSRS
Analysis Server – Dois servidores, nenhum de nós é bem versado em SSAS

Quais métricas devemos usar para provar ou refutar a necessidade de DBAs adicionais?

Minha intenção inicial era demonstrar nossa capacidade atual versus nossos projetos planejados e tarefas repetidas, mas precisamos demonstrar o ROI na contratação de recursos adicionais.

Muito novato no trabalho de banco de dados, então agradeço sua paciência com uma pergunta básica. Estou executando o SQL Server 2014 em minha máquina local e tenho uma pequena tabela e um aplicativo cliente básico para testar diferentes abordagens. Estou recebendo o que parece ser um bloqueio de tabela durante as instruções INSERT INTOe UPDATE. O cliente é um aplicativo ASP.NET com o seguinte código:

OleDbConnection cn = new OleDbConnection("Provider=SQLNCLI11; server=localhost\\SQLEXPRESS; Database=<my db>; user id=<my uid>; password=<my pwd>");
cn.Open();
OleDbTransaction tn = cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("INSERT INTO LAYOUTSv2 (LAYOUTS_name_t, LAYOUTS_enabled_b, LAYOUTS_data_m) VALUES ('name', '-1', 'data')", cn, tn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
int newkey = Decimal.ToInt32((decimal)cmd.ExecuteScalar());
Console.WriteLine("Created index " + newkey);
Thread.Sleep(15000);
tn.Commit();
tn = cn.BeginTransaction();
cmd.CommandText = "UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key='" + newkey + "'";
cmd.Transaction = tn;
cmd.ExecuteNonQuery();
Console.WriteLine("updated row");
Thread.Sleep(15000);
tn.Rollback();
cn.Close();

Eu corro este código, então do estúdio de gerenciamento eu corro SELECT * FROM LAYOUTSv2. Durante ambos os casos, quando a thread do cliente é pausada (ou seja, antes do commit/rollback), a consulta SELECT trava até que ocorra o commit/rollback.

A tabela tem o campo LAYOUTS_key atribuído como chave primária. Na janela de propriedades, mostra que é único e agrupado, com bloqueios de página e bloqueios de linha permitidos. A configuração de escalonamento de bloqueio para a tabela é Desativar... Tentei as outras configurações disponíveis de Tabela e AUTO sem alterações. Eu tentei SELECT ... WITH (NOLOCK)e isso retorna um resultado imediatamente, mas como é bem avisado aqui e em outros lugares, não é o que eu deveria estar fazendo. Eu tentei colocar a ROWLOCKdica nas declarações INSERTe UPDATE, mas nada mudou.

O comportamento que estou procurando é este: antes do commit de um INSERT, as consultas de outros threads leem todas as linhas, exceto a que está sendo INSERTed. Antes de confirmar uma UPDATEconsulta de outros encadeamentos, leia a versão inicial da linha que está sendo UPDATEeditada. Existe alguma maneira de eu fazer isso? Se eu precisar fornecer outras informações para esclarecer meu caso de uso, por favor me avise. Obrigado.

Estou seguindo o tutorial do postgresql , criei meu banco de dados, 2 tabelas, clima e cidades e adicionei algumas linhas de dados. Quando eu consulto meu banco de dados com esta instrução sql, por exemplo:

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

Eu deveria pegar isso:

     city     | temp_lo | temp_hi | prcp |    date
--------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

mas eu entendo isso:

San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29

Estou perdendo os nomes das colunas para todas as consultas. Por que o psql não mostra os nomes das colunas? Eu tenho algo para configurar ou isso é algum tipo de bug?

Preciso calcular a profundidade de um descendente de seu ancestral. Quando um registro possui object_id = parent_id = ancestor_id, ele é considerado um nó raiz (o ancestral). Eu tenho tentado obter uma WITH RECURSIVEconsulta em execução com o PostgreSQL 9.4 .

Eu não controlo os dados ou as colunas. O esquema de dados e tabelas vem de uma fonte externa. A tabela está crescendo continuamente . Agora em cerca de 30 mil registros por dia. Qualquer nó na árvore pode estar faltando e eles serão extraídos de uma fonte externa em algum momento. Eles geralmente são extraídos em created_at DESCordem, mas os dados são extraídos com trabalhos assíncronos em segundo plano.

Inicialmente, tínhamos uma solução de código para esse problema, mas agora com mais de 5 milhões de linhas, leva quase 30 minutos para ser concluída.

Exemplo de definição de tabela e dados de teste:

CREATE TABLE objects (
  id          serial NOT NULL PRIMARY KEY,
  customer_id integer NOT NULL,
  object_id   integer NOT NULL,
  parent_id   integer,
  ancestor_id integer,
  generation  integer NOT NULL DEFAULT 0
);

INSERT INTO objects(id, customer_id , object_id, parent_id, ancestor_id, generation)
VALUES (2, 1, 2, 1, 1, -1), --no parent yet
       (3, 2, 3, 3, 3, -1), --root node
       (4, 2, 4, 3, 3, -1), --depth 1
       (5, 2, 5, 4, 3, -1), --depth 2
       (6, 2, 6, 5, 3, -1), --depth 3
       (7, 1, 7, 7, 7, -1), --root node
       (8, 1, 8, 7, 7, -1), --depth 1
       (9, 1, 9, 8, 7, -1); --depth 2

Observe que object_idnão é único, mas a combinação (customer_id, object_id)é única.
Executando uma consulta como esta:

WITH RECURSIVE descendants(id, customer_id, object_id, parent_id, ancestor_id, depth) AS (
  SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
  FROM objects
  WHERE object_id = parent_id

  UNION

  SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
  FROM objects o
  INNER JOIN descendants d ON d.parent_id = o.object_id
  WHERE
    d.id <> o.id
  AND
    d.customer_id = o.customer_id
) SELECT * FROM descendants d;

Eu gostaria que a generationcoluna fosse definida como a profundidade que foi calculada. Quando um novo registro é adicionado, a coluna de geração é definida como -1. Existem alguns casos em que um parent_idpode não ter sido puxado ainda. Se parent_idnão existir, deve deixar a coluna de geração definida como -1.

Os dados finais devem ficar assim:

id | customer_id | object_id | parent_id | ancestor_id | generation
2    1             2           1           1            -1
3    2             3           3           3             0
4    2             4           3           3             1
5    2             5           4           3             2
6    2             6           5           3             3
7    1             7           7           7             0
8    1             8           7           7             1
9    1             9           8           7             2

O resultado da consulta deve ser atualizar a coluna de geração para a profundidade correta.

Comecei a trabalhar com as respostas a esta pergunta relacionada no SO .

Eu tenho um problema com uma quantidade enorme de INSERTs que estão bloqueando minhas operações SELECT.

Esquema

Eu tenho uma tabela assim:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,    
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

Eu também tenho este pequeno procedimento auxiliar, que me permite inserir ou atualizar (atualizar em caso de conflito) com o comando MERGE:

CREATE PROCEDURE [InsertOrUpdateInverterData]
    @InverterID bigint, @TimeStamp datetime
    , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
    MERGE [InverterData] AS TARGET
        USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
        AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
        ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
    WHEN MATCHED THEN
        UPDATE
        SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]              
    WHEN NOT MATCHED THEN
        INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB]) 
        VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

Uso

Agora executei instâncias de serviço em vários servidores que executam atualizações massivas chamando o [InsertOrUpdateInverterData]procedimento rapidamente.

Existe também um site que faz consultas SELECT na [InverterData]tabela.

Problema

Se eu fizer consultas SELECT na [InverterData]tabela, elas serão processadas em intervalos de tempo diferentes, dependendo do uso de INSERT das minhas instâncias de serviço. Se eu pausar todas as instâncias de serviço, o SELECT é extremamente rápido, se a instância executar uma inserção rápida, os SELECTs ficam muito lentos ou até mesmo um cancelamento de tempo limite.

tentativas

Eu fiz alguns SELECTs na [sys.dm_tran_locks]tabela para encontrar processos de bloqueio, como este

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Este é o resultado:

S = Compartilhado. A sessão de espera recebe acesso compartilhado ao recurso.

Pergunta

Por que os SELECTs estão bloqueados pelo [InsertOrUpdateInverterData]procedimento que está usando apenas comandos MERGE?

Tenho que usar algum tipo de transação com modo de isolamento definido dentro de [InsertOrUpdateInverterData]?

Atualização 1 (relacionada à pergunta de @Paul)

Baseie-se no relatório interno do servidor MS-SQL sobre as [InsertOrUpdateInverterData]seguintes estatísticas:

• Tempo médio de CPU: 0,12ms
• Média de processos de leitura: 5,76 por/s
• Média de processos de gravação: 0,4 por/s

Com base nisso, parece que o comando MERGE está ocupado principalmente com operações de leitura que bloquearão a tabela!(?)

Atualização 2 (relacionada à pergunta de @Paul)

A [InverterData]tabela possui as seguintes estatísticas de armazenamento:

• Espaço para dados: 26.901,86 MB
• Contagem de linhas: 131.827.749
• Particionado: verdadeiro
• Contagem de partições: 62

Aqui está o conjunto de resultados sp_WhoIsActive (quase) completo:

SELECTcomando

• dd hh:mm:ss.mss: 00 00:01:01.930
• id_sessão: 73
• wait_info: (12629ms)LCK_M_S
• CPU: 198
• blocking_session_id: 146
• lê: 99.368
• escreve: 0
• estado: suspenso
• open_tran_count: 0

[InsertOrUpdateInverterData]comando de bloqueio

• dd hh:mm:ss.mss: 00 00:00:00.330
• ID da sessão: 146
• wait_info: NULL
• CPU: 3.972
• blocking_session_id: NULL
• lê: 376,95
• escreve: 126
• estado: dormindo
• open_tran_count: 1

Eu tenho uma tabela que conterá cerca de 15 milhões de registros. Agora preciso adicionar um índice à tabela.

Adicionar um índice levará algum tempo para atualizar cada entrada na tabela.

Estou bastante confuso se adicionar o índice causará tempo de inatividade.

Se sim, então como posso superar o tempo de inatividade?

Eu quero ter uma pesquisa rápida com base em se duas colunas são iguais. Tentei usar uma coluna computada com um índice, mas o SQL Server parece não usá-lo. Se eu apenas usar uma coluna de bits preenchida estaticamente com um índice, obterei a busca de índice esperada.

Parece que há algumas outras perguntas como essa por aí, mas nenhuma se concentrou em por que um índice não seria usado.

Tabela de teste:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

E a Consulta:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

E os planos de execução resultantes:

Eu tenho esta tabela de banco de dados que deve manter o índice de texto completo atualizado. No entanto, não estou vendo isso acontecer (sem erro no log porque o último log que vejo foi quando o acionei manualmente).

Aqui está o que estou vendo...

mas na própria mesa...

Poderia ser por isso que não está acontecendo automaticamente?

Tenho dois pacotes SSIS que são executados agendados durante a noite (via SQL Server Agent) como parte de uma implantação SSIS maior, sem problemas. Tudo está usando a autenticação do Windows, e o trabalho agendado pertence a um administrador de sistema (bem, eu) e é executado como a conta de serviço do SQL Server Agent.

Portanto, os dados vão essencialmente da source system ~> transit db ~> staging ~> NDSnoite para o dia.

Os dois pacotes SSIS que me interessam, lidam com as partes transit db ~> staginge staging ~> NDS, respectivamente, para um conjunto específico de dados.

Um usuário de domínio (não-sysadmin) faz algo no source systeme que empurra os dados interessantes para o transit db, então preciso de uma maneira de buscar esses dados atualizados durante o horário de trabalho para atualizar o NDS: foi decidido que a maneira mais simples dessa pessoa acionar esse ETL foi clicando em um botão em uma pasta de trabalho do Excel habilitada para macro, que se conecta ao SQL Server via ODBC (usando autenticação do Windows) e executa um procedimento armazenado.

O procedimento armazenado se parece com isso:

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

O procedimento armazenado "irmã" em [msdb] se parece com isto:

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

Este usuário [SqlAgentProxy] é um usuário do Windows que criei em [msdb] fora do login do usuário do domínio, ao qual dei executepermissão para este UpdateMaterialInventoryprocedimento. Isso evita ter que conceder ao usuário do domínio executepermissão para msdb.dbo.sp_start_job, o que seria excessivo.

O trabalho do SQL Agent NDS-ManualMaterialInventorypertence ao usuário do domínio e tem 2 etapas, cada uma do tipo [SQL Server Integration Services Package], configurada para Executar como SSISProxy .

SSISProxyé um proxy do SQL Server Agent mapeado para o subsistema [SQL Server Integration Services Package], usando o nome da credencial SSISProxyCredentials. O login do usuário do domínio foi adicionado aos principais da conta Proxy .

Eles SSISProxyCredentialsforam criados com a identidade do mesmo usuário de domínio que está executando todo o SSIS ETL durante a noite e sua senha foi verificada quatro vezes.

Agora, se eu executar isso:

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

Eu recebo esta saída:

Job 'NDS-ManualMaterialInventory' started successfully.

No entanto, a história do trabalho está contando uma história muito menos encorajadora:

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

E detalhes da etapa 1:

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider 
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

O trabalho falha e nada é registrado em nenhum lugar.

Se eu alterar o proprietário do trabalho para ser eu mesmo e alterar a execução das etapas para a conta de serviço do SQL Server Agent, o trabalho será executado, bem-sucedido e registrará 1.067 linhas em [Metadata].[dbo].[sysssislog].

Parece que há algo errado com a configuração do proxy/credenciais. Qual parte estou fazendo errado?

Existe alguma maneira de ter uma LIKEconsulta Postgres em um campo ARRAY?

Atualmente eu quero algo assim:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Atualmente mais baixo não é necessário tanto. No entanto, deve encontrar UM campo correspondente dentro do ARRAY. É mesmo possível?

Atualmente eu uso uma visão materializada para gerar a tabela "list" com um JOIN e um ARRAY_AGG(), já que eu JOIN uma tabela onde mais valores poderiam estar na tabela certa. O que duplicaria os campos da tabela à esquerda, o que não é o que eu quero.

Edite é assim que eu crio a visão (realmente lenta e feia):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id, 
       a.oa_nr, 
       a.date_deleted, 
       a.lock, 
       a.sds_nr, 
       a.kd_art_nr, 
       a.kd_art_index, 
       a.kd_art_extend, 
       a.surface, 
       a.execution, 
       a.surface_area, 
       a.cu_thickness, 
       a.endintensity, 
       a.drilling, 
       array_agg(o.id::text) AS offer_list 
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr 
GROUP BY .....;  

Também preciso retornar os IDs da task_offertabela.