Eu tenho um caso típico em que a detecção de parâmetros faz com que um plano de execução "ruim" chegue ao cache do plano, fazendo com que as execuções subsequentes do meu procedimento armazenado sejam muito lentas. Posso "resolver" esse problema com variáveis ​​locais, OPTIMIZE FOR ... UNKNOWN, e OPTION(RECOMPILE). No entanto, também posso mergulhar na consulta e tentar otimizá-la.

Estou tentando determinar se devo : devido ao tempo limitado para corrigir problemas, gostaria de saber o custo de não fazê-lo. A meu ver, se eu continuar com OPTION(RECOMPILE), o efeito líquido é que um plano de consulta é recriado toda vez que a consulta é executada. Então, acho que preciso saber:

Como saber qual é o custo de criar um plano de consultas?

Para responder à minha própria pergunta, pesquisei no Google (por exemplo, com esta consulta ) e examinei a documentação das colunas para o dm_exec_query_statsDMV . Também inspecionei a janela de saída no SSMS para "Plano de consulta real" para encontrar essas informações. Por fim, pesquisei DBA.SE . Nenhum deles levou a uma resposta.

Alguém pode me dizer? É possível encontrar ou medir o tempo necessário para a criação do plano?

A ISNUMERICfunção tem algum comportamento inesperado. A documentação do MSDN diz:

ISNUMERICretorna 1 quando a expressão de entrada é avaliada como um tipo de dados numérico válido; caso contrário, retorna 0. Os tipos de dados numéricos válidos incluem os seguintes: int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, real .

E também tem uma nota de rodapé:

ISNUMERICretorna 1 para alguns caracteres que não são números, como mais (+), menos (-) e símbolos de moeda válidos, como o cifrão ($). Para obter uma lista completa de símbolos de moeda, consulte money e smallmoney (Transact-SQL) .

Ok, então espera-se que +, -e os símbolos de moeda listados sejam considerados numéricos. Até agora tudo bem.

Agora para a parte estranha. Em primeiro lugar, alguns dos símbolos de moeda do artigo vinculado não são numéricos, incluindo:

• Sinal de euro-moeda, hex 20A0:₠
• Sinal de Naira, hex 20A6:₦
• Sinal Rial, hexadecimal FDFC:﷼

Isso é estranho, e eu não consigo descobrir por quê? Esta versão ou ambiente depende?

No entanto, as coisas ficam mais estranhas. Aqui estão alguns outros que não consigo explicar:

• /não é numérico, mas \é ( né?! )
• REPLICATE(N'9', 308)é numérico, mas REPLICATE(N'9', 309)não é

A primeira e mais básica pergunta é: o que explica os casos acima? Mais importante, porém: qual é a lógica por trás doISNUMERIC , para que eu possa explicar / prever todos os casos sozinho?

Aqui está uma boa maneira de reproduzir as coisas:

DECLARE @tbl TABLE(txt NVARCHAR(1000));

INSERT INTO @tbl (txt) 
VALUES (N''), (N' '), (N'€'), (N'$'), (N'$$'), 
       (NCHAR(8356)), (NCHAR(8352)), (NCHAR(8358)), (NCHAR(65020)), 
       (N'+'), (N'-'), (N'/'), (N'\'), (N'_'), (N'e'), (N'1e'), (N'e1'), (N'1e1'), 
       (N'1'), (N'-1'), (N'+1'), (N'1+1'), (N'⒈'), (N'?'), (N'¹'), (N'①'), (N'½'), 
       (N'?'), (REPLICATE(N'9', 307)), (REPLICATE(N'9', 308)), (REPLICATE(N'9', 309)), 
       (REPLICATE(N'9', 310));

SELECT  UNICODE(LEFT(txt, 1)) AS FirstCharAsInt,
        LEN(txt) AS TxtLength,
        txt AS Txt,
        ISNUMERIC(txt) AS [ISNUMERIC]
FROM    @tbl;

Quando executo isso na minha caixa local do SQL Server 2012, obtenho os seguintes resultados:

FirstCharAsInt   TxtLength   Txt        ISNUMERIC
---------------  ----------  ---------  ----------
NULL             0                      0
32               0                      0
8364             1           €          1
36               1           $          1
36               2           $$         0
8356             1           ₤          1
8352             1           ₠          0  --??
8358             1           ₦          0  --??
65020            1           ﷼‎          0  --??
43               1           +          1
45               1           -          1
47               1           /          0
92               1           \          1  --??
95               1           _          0
101              1           e          0
49               2           1e         0
101              2           e1         0
49               3           1e1        1
49               1           1          1
45               2           -1         1
43               2           +1         1
49               3           1+1        0
9352             1           ⒈         0
55356            2           ?          0
185              1           ¹          0
9312             1           ①          0
189              1           ½          0
55356            2           ?         0
57               307        /*...*/     1
57               308        /*...*/     1  --??
57               309        /*...*/     0  --??
57               310        /*...*/     0

Suponha a seguinte função:

CREATE FUNCTION [dbo].[ufnTest]()
RETURNS TABLE
AS RETURN SELECT 1 AS Nr

Minha função real selecionará dados reais de muitas tabelas diferentes. Eu (principalmente) entendo os riscos de usar dicas nolock e decidi que, neste caso, eu realmente as quero. Então eu tento chamar a função acima assim:

SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)

No entanto, isso falha com a seguinte mensagem:

Sintaxe incorreta perto da palavra-chave 'with'. Se esta instrução for uma expressão de tabela comum, uma cláusula xmlnamespaces ou uma cláusula de contexto de controle de alterações, a instrução anterior deverá ser finalizada com um ponto e vírgula.

As dicas para corrigir esse erro não são relevantes. Talvez esta dica de tabela não esteja disponível para funções com valor de tabela?

Existem várias alternativas que eu consideraria. Uma é usar a dica em todas as tabelas em minha consulta de seleção, mas prefiro não fazer isso porque (a) terei que me lembrar disso sempre que mudar a função e (b) é repetitivo. Outra alternativa seria SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, mas uma desvantagem é que não sei bem como defini-lo como era antes (e as declarações após o select do meu TVF devem ter o nível de isolamento original).

Então, eu prefiro fazer WITH (NOLOCK)uma seleção de um trabalho de função com valor de tabela. Isso é possível?

Eu capturei um plano de consulta real para uma consulta específica.

Depois disso, mudei algumas coisas (incluindo a atualização das estatísticas) e executei novamente essa consulta específica. Agora, o plano de consulta real é diferente (o que faz sentido).

A consulta agora é executada muito mais rapidamente. Estou curioso para saber se o novo plano de execução tem algo a ver com isso, porque outras alterações (alterações na configuração do IO, configurações da VM, reinicialização da instância SQL, etc) também podem estar causando a melhoria no desempenho. Para testar isso, gostaria de executar a consulta mais uma vez e tentar forçar o SQL Server a usar o plano de execução antigo.

Pergunta : existe uma maneira de executar novamente uma consulta com um plano de execução fornecido pelo usuário ou até mesmo executar uma consulta diretamente de tal plano?

Aqui está o que eu tentei descobrir isso:

• Pesquisei nos livros que temos disponíveis no escritório ( Professional SQL Server 2012 Internals and Troubleshooting , Querying Microsoft SQL Server 2012 );
• Pesquisas do Google, por exemplo, "executar consulta com base em um plano de consulta específico"
• Pesquisas DBA.SE, por exemplo, "executar plano de consulta" e "reexecutar plano de execução"
• E, finalmente, uma que respondeu às minhas perguntas muitas vezes antes: verifique cuidadosamente "Perguntas que já podem ter sua resposta" antes de clicar em "Poste sua pergunta" :-)

Resumindo: isso é possível? Se sim: como?

Algum contexto:
No início, escrevíamos relatórios apenas "diretos", sem nenhuma dica de bloqueio nas consultas. Com os relatórios maiores, isso às vezes causava problemas de bloqueio. Inicialmente , corrigimos isso usando a WITH (NOLOCK)dica para tabelas na consulta.

Como (a) é bastante intrusivo e (b) é fácil esquecer a dica para uma das tabelas, passamos para uma segunda configuração TRANSACTION ISOLATION LEVELde abordagem READ UNCOMMITTED(o que é bom) na parte superior da consulta de cada conjunto de dados.

Como você pode imaginar, ainda é fácil esquecer a dica para um dos conjuntos de dados. Então isso leva à pergunta:

Descrição: Quais são as opções para envio de NOLOCKdicas junto com as consultas do relatório?

PS. Eu percebo que isso é até certo ponto um problema XY (com muitas das minhas outras opções para X, como otimizar a consulta, não fazer relatórios no banco de dados operacional etc.), mas tentei fazer disso uma pergunta válida em si mesmo .

Opções:
Aqui estão as opções mencionadas acima, com opções adicionais sobre as quais estou curioso para saber se funcionariam:

1. Definir WITH (NOLOCK)dica para cada mesa. (obstrutivo, muito fácil de esquecer)
2. Defina o nível de isolamento READ UNCOMMITTEDpara toda a consulta. (ainda fácil de esquecer)
3. É possível especificar isso no nível do relatório ? Por exemplo, certifique-se de que todas as consultas de conjunto de dados para um relatório sejam executadas sem bloqueio.
4. É possível especificar isso em algum outro nível do SSRS ? Por exemplo, talvez defina isso para uma determinada pasta de relatório ou utilizando uma extensão?
5. É possível especificar isso no nível da fonte de dados/cadeia de conexão ? Por exemplo, todos os relatórios relevantes usam uma determinada "fonte de dados sem bloqueio"?
6. Relacionado à opção anterior: talvez seja possível especificar uma dica de bloqueio padrão para um "no-lock-sql-user" específico (aquele que é usado na conexão)?
7. ???

Quais opções são viáveis? Existem opções que eu perdi?

Pergunta:

Em geral, há alguma armadilha na atualização de dados de um relatório do SSRS?*

Contexto:

Mais específico, estou falando desse contexto:

• SSRS 2008 (com a opção de atualização, se necessário).
• MSSQL 2008 (com a opção de atualização, se necessário).
• Web.forms ReportViewer 2010 em nosso próprio aplicativo ASP.NET.

Eu só preciso fazer uma pequena atualização do banco de dados. O usuário pode marcar um parâmetro de caixa de seleção " Vou imprimir isso ", que fará uma pequena atualização ou inserção para marcar que o relatório foi executado em um ponto específico no tempo. O relatório mostrará apenas que os itens foram adicionados desde a execução de "impressão" anterior, portanto, filtro minha cláusula WHERE com base nisso, fornecendo ao usuário apenas itens "novos".

A razão pela qual estou preocupado é porque não "parece certo" fazer atualizações de uma ferramenta destinada principalmente à visualização de dados. Por outro lado, com algo tão pequeno quanto o acima, parece a solução mais fácil.

Preocupações específicas:

Aqui estão algumas preocupações específicas que tenho sobre as quais estou buscando informações:

• Segurança : a conexão do relatório precisará ser executada com um usuário com permissão para fazer inserções, onde um padrão mais sensato seria executá-lo para um usuário somente de seleção? Existem ataques conhecidos do tipo sql-injection para SSRS?
• Usabilidade : gostaria de fazer a atualização dependendo se um usuário marca uma caixa de seleção. Não tenho certeza de como isso funcionaria com atualizações e se você pode detectar qual é o formato de renderização ao fazer as consultas (o que me permitiria fazer a caixa de seleção acionar atualizações apenas em - por exemplo - uma exportação de PDF).
• Robustez : declarações de atualização parecem mais propensas a exceções (restrições de violação, etc). Não tenho certeza se existe uma maneira elegante de prevenir, registrar, rastrear e depurar esse tipo de problema.

Suponha que temos uma tabela que possui uma restrição de chave estrangeira para si mesma, como:

CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

INSERT INTO Foo (FooId, ParentFooId) 
VALUES (1, NULL), (2, 1), (3, 2)

UPDATE Foo SET ParentFooId = 3 WHERE FooId = 1

Esta tabela terá os seguintes registros:

FooId  ParentFooId
-----  -----------
1      3
2      1
3      2

Há casos em que esse tipo de design pode fazer sentido (por exemplo, o típico relacionamento "empregado-e-chefe-empregado") e, em qualquer caso: Estou em uma situação em que tenho isso em meu esquema.

Infelizmente, esse tipo de design permite a circularidade nos registros de dados, conforme mostrado no exemplo acima.

Minha pergunta então é:

1. É possível escrever uma restrição que verifique isso? e
2. É viável escrever uma restrição que verifique isso? (se necessário apenas até uma certa profundidade)

Para a parte (2) desta questão, pode ser relevante mencionar que espero apenas centenas ou talvez, em alguns casos, milhares de registros em minha tabela, normalmente não aninhados em níveis mais profundos do que 5 a 10.

Depois de limpar minha tabela com DELETE FROM MyTable(e executar DBCC shrinkdatabase('MyDB'), caso isso importe), eu executo a instrução EXEC sp_spaceused MyTable. Os resultados me confundem:

tableName   numberOfRows   reservedSize   dataSize   indexSize   unusedSize
---------   ------------   ------------   --------   ---------   ----------
MyTable     0              21664 KB       20672 KB   736 KB      256 KB

Como você pode ver, não há linhas, mas há quase 21 MB de dados. Minha pergunta é: quais são as possíveis causas para esta situação e/ou como posso investigar mais a fundo?