Stuart Blackler's questions

Em meu aplicativo, preciso executar um padrão de bloqueio distribuído. Como já temos uma instância do SQL Server para usar, decidimos que seria mais fácil implementar o bloqueio na camada SQL da nossa aplicação web.

Um bloqueio pode ser obtido com base em várias condições, incluindo:

• O tipo de bloqueio solicitado
• Um identificador de aplicativo arbitrário

Para todos os efeitos, trate as duas condições acima como inttipos de dados.

Nesse padrão, desejamos tratar todos os nossos bloqueios como FIFO, o que acredito que o SERIALIZABLEnível de isolamento nos dará.

Aqui está como propomos realizar o "bloqueio":

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF EXISTS (SELECT 1 FROM locks WHERE LockType = @LockType AND ApplicationIdentifier = @ApplicationIdentifier)
    BEGIN
        -- Awesome, the lock will be acquired
        INSERT INTO locks OUTPUT INSERTED.LockId VALUES (2,3)
    END
ELSE
    BEGIN
        -- Someone already has the lock
        SELECT -1
    END

SET TRANSACTION ISOLATION LEVEL READ COMITTED

E o "desbloqueio":

DELETE FROM locks WHERE LockId = @LockId

Então minha dúvida é dupla:

1. Preciso fazer o "desbloqueio" SERIALIZABLEtambém?
2. Existem outras abordagens que eu poderia usar/qualquer coisa que eu tenha esquecido?

O SQL Server pode ser 2008/2012

Estou tentando entender melhor as diferenças entre os comandos DELETEe . TRUNCATEMinha compreensão dos internos é algo como:

DELETE-> o mecanismo de banco de dados encontra e remove a linha das páginas de dados relevantes e todas as páginas de índice onde a linha é inserida. Assim, quanto mais índices, mais demora a exclusão.

TRUNCATE-> simplesmente remove todas as páginas de dados da tabela em massa, tornando esta uma opção mais eficiente para excluir o conteúdo de uma tabela.

Supondo que o acima esteja correto (por favor, corrija-me se não):

1. Como diferentes modos de recuperação afetam cada instrução? Se houver algum efeito
2. Ao excluir, todos os índices são verificados ou apenas aqueles onde a linha está? Eu diria que todos os índices são verificados (e não procurados?)
3. Como os comandos são replicados? O comando SQL é enviado e processado em cada assinante? Ou o MSSQL é um pouco mais inteligente do que isso?

Estou apenas lendo a documentação dos exames do SQL Server 2012 e vi o seguinte ponto:

caso contra isnull contra coalescer

Agora, eu sei COMO usar cada um, mas não sei QUANDO usar cada um. Por favor, alguém poderia esclarecer. Obrigado.

Obs. Por favor, poderíamos ter uma tag para perguntas do exame?

Na sequência de uma pergunta recente que fiz no Tempdb. Eu queria saber como definir corretamente a propriedade autogrowth ao adicionar e mover os arquivos tempdb?

Pergunto porque gostaria de deixar o SQL Server usar o algoritmo round-robin para distribuir a carga de trabalho em cada um dos arquivos. Entendo que se os arquivos não forem de tamanho igual, o SQL Server usará o arquivo com mais espaço, aumentando assim as chances de causar contenção de trava. Meu entendimento aqui está correto?

Saúde,

Duas coisas que gostaria de saber:

• como você move o tempdb com segurança com o mínimo de tempo de inatividade?
• quantos arquivos tempdb você precisa?

É 1 arquivo por núcleo? Então quad-core = 4 arquivos tempdb, criando três novos?

Eu tenho uma consulta como esta:

SELECT col1
FROM   MyTable
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
       BETWEEN col2 
       AND     col3
;

Isso fornece uma dica de ferramenta sobre o plano de execução semelhante a esta:

A dateaddparte dos predicados de busca é executada para cada linha na consulta? Ou o SQL Server calcula o valor uma vez para toda a consulta?

Praticamente como o título descreve. Acabei de verificar um SQL Server e notei que há muito inchaço no plano de consulta que pode ser corrigido pela parametrização adequada.

Depois de fazer as alterações no código, é recomendável limpar o cache do plano de consulta e permitir que ele se reconstrua? Ou será que vai se resolver com o tempo?

NB: Das 50 maiores consultas, 47 delas eram as mesmas, mas com parâmetros diferentes.

Dadas as seguintes definições:

CREATE TABLE MyTable
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

CREATE FUNCTION fn_MyFunction
(
    @Param1 int,
    @Param2 int
)
RETURNS TABLE AS RETURN
(
    SELECT @Param1 * 2 AS 'res1', @Param2 * 4 AS 'res2', @Param1 AS 'col3'
)

Estou tentando juntar a função à tabela para que eu obtenha um valor calculado (s) por linha. Exemplo: Em uma tabela de reservas, preciso obter o preço com base em uma data inicial e uma data final.

Aqui está um exemplo de consulta:

SELECT      tbl.col1
,           tbl.col2
,           tbl.col3
,           fn.res1
,           fn.res1
FROM        MyTable tbl
CROSS APPLY fn_MyFunction(tbl.col3, tbl.col4) fn
WHERE       fn.col3 = tbl.col3

Embora eu ache que estou obtendo os resultados corretos em minha consulta real, como CROSS APPLYobtenho os resultados neste caso? Isso os obtém linha por linha (efetivamente)? Ou funciona de maneira semelhante a CROSS JOIN(ou seja: fazer um produto cartesiano)?

Estou tentando criar um procedimento que irá gerar um documento xml de uma determinada tabela enquanto atualiza as informações da referida tabela. Meu procedimento não reconhece o uso da tabela temporária, a menos que eu use uma CREATE TABLEinstrução (ainda não tentei table var's). Idealmente, eu não usaria a CREATE TABLEinstrução caso eu/outro desenvolvedor quisesse adicionar outra(s) coluna(s) no futuro.

Aqui está o meu código atualmente:

UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp 
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

E falha com:

Msg 208, Nível 16, Estado 0, Linha 24

Nome de objeto inválido '##temp'.

Não é o fim do mundo se eu tiver que usar uma CREATE TABLEdeclaração, mas prefiro não declará-la explicitamente.

Notas:

1. Sei que não há tratamento de erros no momento, quero fazer esta seção funcionar primeiro.
2. Eu também tentei nomear as colunas em vez de usarInserted.*
3. Estou definindo propositalmente ErrorReported = 0como estou em um ambiente de teste

No momento, estou assistindo a este vídeo no Entity Framework e, cerca de 15 minutos depois, o palestrante diz que as funções de valor da tabela ocasionalmente superam as UDFs.

Minha pergunta é em que cenário os TVFs executam os UDFs?

Tendo feito esta pergunta no Stackoverflow , me perguntei onde o que fiz é a prática correta/melhor.

Basicamente, todo objeto que eu crio vai para um esquema com o nome do esquema refletindo um uso. Por exemplo, tenho os esquemas Audite Admin(entre outros).

Isso, por sua vez, não deixa objetos em dbo. tudo bem? Existe mais alguma coisa que eu preciso fazer?

Ao criar um banco de dados de teste para outra pergunta que fiz anteriormente, lembrei-me de que uma chave primária pode ser declaradaNONCLUSTERED

Quando você usaria uma NONCLUSTEREDchave primária em oposição a uma CLUSTEREDchave primária?

desde já, obrigado

Na maioria dos bancos de dados que crio, preciso conceder permissões para fazer o seguinte:

1. SELECT/INSERT/UPDATE/DELETE em todas as tabelas do banco de dados
2. Execute todos os procedimentos armazenados (do usuário) no banco de dados

Fornecer usuários datareadere datawriterserá suficiente para o sproc ou quais outras funções/permissões eu preciso fornecer?

Se eu precisar fazer mais do que apenas datareadere datawriter, você poderia me indicar a direção certa para criar um script para afetar todos os usuários no banco de dados.

desde já, obrigado

Estou escrevendo um script para verificar rapidamente os índices em um determinado banco de dados. Acho que tenho o script escrito ok, mas para remover linhas duplicadas, fiz várias SUMfunções e não tenho certeza se isso está correto ou não:

SELECT      OBJECT_NAME(i.object_id)                                                                                                                AS 'Table Name'
,           COALESCE(i.name, '')                                                                                                                    AS 'Index Name'
,           i.type_desc                                                                                                                             AS 'Index Type'
,           i.is_primary_key                                                                                                                        AS 'Primary Key'
,           i.is_disabled                                                                                                                           AS 'Index Disabled'
,           ROUND(CONVERT(FLOAT,SUM(user_seeks)) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_scans),0),1))),2)                                          AS 'Seeks Per Scan'
,           ROUND((CONVERT(FLOAT,SUM(user_seeks) +SUM(user_scans) + SUM(user_lookups))) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_updates),0),1))),2) AS 'Reads Per Write'
,           (SUM(user_seeks) + SUM(user_scans) + SUM(user_lookups))                                                                                 AS 'Reads'
,           SUM(user_updates)                                                                                                                       AS 'Writes'
,           SUM(user_seeks)                                                                                                                         AS 'Seeks'
,           SUM(user_scans)                                                                                                                         AS 'Scan'
,           ROUND(AVG(ps.avg_fragmentation_in_percent),2)                                                                                           AS 'Avg Fragmentation %'
,           NULLIF((SUM(leaf_insert_count) + SUM(leaf_update_count) + SUM(leaf_delete_count)),0) / NULLIF(SUM(leaf_allocation_count),0)             AS 'Leaf Page Splits Per Write'
,           NULLIF((SUM(nonleaf_insert_count) + SUM(nonleaf_update_count) + SUM(nonleaf_delete_count)),0) / NULLIF(SUM(nonleaf_allocation_count),0) AS 'Non-Leaf Page Splits Per Write'
,           CAST (100.0 * SUM(row_lock_wait_count) / (1 + SUM(row_lock_count)) AS NUMERIC(15,2))                                                    AS 'Block %'
,           SUM(row_lock_wait_in_ms)                                                                                                                AS 'Row Lock Wait (ms)'
,           CAST(1.0 * SUM(row_lock_wait_in_ms) / (1 + SUM(row_lock_wait_count)) AS NUMERIC(15,2))                                                  AS 'Average Row Lock Wait'
FROM sys.indexes i
LEFT JOIN sys.objects o ON o.object_id = i.object_id 
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) s ON o.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_index_usage_stats u ON s.object_id = u.object_id AND u.index_id = s.index_id
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 
AND NOT OBJECT_NAME(i.object_id) = 'sysdiagrams' -- exclude sysdiagrams
--AND i.is_primary_key = 0
GROUP BY OBJECT_NAME(i.object_id), COALESCE(i.name, ''),i.type_desc,i.is_primary_key,i.is_disabled
ORDER BY OBJECT_NAME(i.object_id), COALESCE(i.name, '')

tudo bem? Ou SUMas linhas estão ruins?

Além disso, há alguma informação que você acha que seria útil para encontrar índices que não são usados/precisam de reindexação, etc.

Enquanto lia meu livro de exames 70-433, pensei em algo que posso ver que não está funcionando, mas acredito que funcione. A passagem dizia algo como:

A coluna também deve ser marcada como PERSISTED , o que significa que o SQL Server armazena fisicamente o resultado da expressão da coluna computada na linha de dados em vez de calculá-la sempre que é referenciada em uma consulta.

Disso entendo duas coisas:

1. Uma coluna computada não persistente é calculada toda vez que é referenciada em uma consulta
2. Como nada é armazenado para a coluna computada, presumo que nenhum índice possa ser criado para a coluna.

Depois de ler, achei um pouco estranho, pois consegui criar um índice em uma coluna não persistente em um projeto anterior.

Como criar um índice para algo que não persiste e isso é prejudicial a longo prazo?

Para provar isso, executei a seguinte instrução SQL:

CREATE TABLE testTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    telephone VARCHAR(14),
    c_areaCode AS (SUBSTRING(telephone,0,5)),
    cp_areaCode AS (SUBSTRING(telephone,0,5)) PERSISTED
)

INSERT INTO testTable VALUES('09823 000000');
INSERT INTO testTable VALUES('09824 000000');
INSERT INTO testTable VALUES('09825 000000');

CREATE NONCLUSTERED INDEX IX_NotPersisted ON testTable(c_areaCode);
CREATE NONCLUSTERED INDEX IX_Persisted ON testTable(cp_areaCode);

E então execute as seguintes consultas:

DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS
GO
SELECT cp_areaCode FROM testTable;
GO
SELECT c_areaCode FROM testTable;

Tendo examinado o plano de consulta para o código acima, posso ver que ambas as consultas selecionadas estão usando o índice não persistente. Mais uma vez, como?

Depois de ler a consulta Slow SQL, não tenho certeza de como otimizar , isso me fez pensar no desempenho geral das consultas. Certamente, precisamos que os resultados da primeira tabela (quando outras tabelas são unidas) sejam tão pequenos quanto possível antes da junção (junções internas para esta questão) para tornar nossas consultas um pouco mais rápidas.

Exemplo, deve isso:

SELECT *
FROM   ( SELECT * FROM table1 WHERE col = @val ) t
INNER JOIN table2 ON col = col2

Seja melhor/mais rápido do que:

SELECT *
FROM table1
INNER JOIN table2 ON col = col2
WHERE table1.col = @val

Minha teoria é a seguinte (essa pode não ser a implementação correta, estou tentando lembrar de um livro interno do SQL Server 2008 que li (MSFT Press)):

1. O processador de consultas primeiro obtém a tabela da esquerda (tabela1)
2. Junta a segunda tabela (tabela2) e forma um produto cartesiano antes de filtrar as linhas necessárias (se aplicável)
3. Em seguida, executa as cláusulas WHERE, ORDER BY, GROUP BY, HAVING com a instrução SEELCT por último.

Portanto, se na instrução nº 1 acima a tabela for menor, o mecanismo SQL terá menos trabalho a fazer ao formar os produtos cartesianos. Então, quando você alcança a instrução where, você tem um conjunto de resultados reduzido para filtrar na memória.

Eu poderia estar tão longe da realidade que é irreal. Como eu disse, é uma teoria.

Seus pensamentos?

Nota : Acabei de pensar nessa questão e ainda não tive a chance de executar nenhum teste.

Nota 2 : Marcado como SQL Server, pois não sei nada sobre a implementação do MySql etc. Sinta-se à vontade para responder/comentar de qualquer maneira

Estou lendo os livros on-line do SQL Server para a primeira equipe enquanto aprendo como usar o agente de serviços e me deparei com esta declaração:

O aplicativo que implementa o serviço lê a mensagem, executa a consulta e retorna os resultados. Todas essas três operações ocorrem na mesma transação. Se ocorrer uma falha antes da confirmação da transação, toda a transação será revertida e a mensagem retornará à fila. Quando o computador se recupera, o aplicativo é reiniciado e processa a mensagem novamente.

Eu estou supondo que isso irá bloquear da mesma forma que as transações normais? ou seja bloqueios de linha/página. Estou correcto? Isso é algo com que devo me preocupar ou apenas deixar até que eu tenha um problema?

Dando continuidade à minha outra pergunta , gostaria de começar a pensar no que devo observar diariamente/semanalmente/mensalmente em termos de alertas. Espero poder ver os problemas chegando antes que eles aconteçam (esse é o plano)...

Até agora, comecei a coletar scripts para o seguinte (sem ordem):

Diário

• Verifique o tempo de atividade do sistema (caso eu precise verificar algo como DBA)
• Verifique o último backup
• Verifique os backups do log de transações
• Verifique o status dos trabalhos SQL
• Verifique o uso médio da CPU nas últimas 24 horas (ou 1140 minutos)

Semanalmente

• Verifique o histórico de backup do MSDB
• Verifique quando foi a última vez que o CheckDB foi executado
• Verifique a fragmentação do índice
• Verifique as estatísticas do índice (leituras x gravações, etc.)
• Verifique se há gargalos de E/S

Por mês

• Verifique os índices ausentes
• Verifique os índices que não são mais usados

Alguma outra sugestão? (Sou novo no DBA, então qualquer ajuda/conselho é sempre bem-vindo)

Tenho alguns scripts SQL que gostaria de executar todos os dias em meu(s) SQL Server(s). Como posso consultar várias vezes e ter a saída semelhante à seguinte:

Health check for <server>

Query Title 1:

<query results 1>


Query Title 2:

<query results 2>

Estou verificando coisas como último backup bom, tempo de atividade, etc., para que não estejam relacionados entre si. Estou planejando usar sp_send_db_mail dentro de um trabalho do SQL Server. Esta é a melhor maneira de realizar esta tarefa?

Idealmente, gostaria apenas de um e-mail.

Atualizar

Depois de lutar por anos!!! A solução que encontrei até agora é colocá-lo em um procedimento armazenado e, em seguida, executar o procedimento armazenado da seguinte forma:

EXEC msdb.dbo.sp_send_dbmail
        @recipients = N'<email>',
        @body = @body, 
        @subject = @subjectt,
        @profile_name = @ProfileName,
        @query ='exec test..sp_checks'

Isso está ok para várias instruções de seleção?

Eu tenho duas colunas de data e hora no SQL Server 2005 que preciso consultar sem a parte de hora da data e hora. Atualmente, minha consulta se parece com isso (apenas um exemplo):

WITH Dates AS ( 
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
) 
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Agora, isso resulta em uma varredura de índice clusterizada completa (surpresa, surpresa). Estou tentando pensar em maneiras de tornar isso mais rápido (a consulta real leva 3 minutos: O). Pensei em fazer o seguinte, mas ainda não tentei, pois fiquei sem tempo antes:

1. Use uma coluna computada apenas com a parte da data
2. Indexar a referida coluna computada (não tenho certeza se isso é possível?)
3. Use uma exibição indexada (novamente, não tenho certeza se isso é possível, funcionará?)

A maneira mais fácil seria atualizar a coluna e remover todas as informações de tempo, mas não posso fazer isso :(

Alguma ideia?

Atualizar

Obrigado por todas as respostas até agora. Acho que o ponto da pergunta foi perdido porque não estava claro sobre o que queria. Foi mal. Eu pretendia apenas otimizar a parte de conversão de data da consulta, pois a quantidade de dados com a qual estou lidando é pequena na realidade (< 500.000 após a aplicação cruzada com um intervalo de datas de um ano). Desculpe pela confusão sobre isso.

Para aqueles que otimizam o restante da consulta para mim, posso ver o que as pessoas estão dizendo usando, < >mas considere o seguinte:

1. Os parâmetros que são passados ​​são um intervalo de datas (por exemplo, 1º ao final deste mês)
2. A data inicial na tabela pode aparecer antes ou durante o intervalo de datas dos parâmetros (por exemplo, apenas a data final está no intervalo de datas)
3. A data final na tabela pode aparecer durante ou após o intervalo de datas dos parâmetros (por exemplo, apenas a data inicial está no intervalo de datas)
4. Por fim, as datas de início e término na tabela estão no intervalo de datas dos parâmetros.

Pessoalmente, diante do exposto, nunca consegui uma < >solução para funcionar. A única maneira de fazê-lo funcionar corretamente e não perder nada é usando um CTE e dizendo where d.[Date] BETWEEN t.StartDate AND t.EndDate.

Eu espero que isso esclareça as coisas. Obrigado novamente.