irimias's questions

Tenho uma tabela descrevendo os usuários do meu aplicativo com alguns detalhes como nome, sobrenome, data de nascimento, nacionalidade, email, etc...

Gostaria de saber para cada propriedade, e para cada categoria de usuário, o valor mais frequente e a porcentagem de ocorrência.

Por exemplo :

create table test ( userId int identity(1,1), 
                    categoryId int, 
                    name varchar(50), 
                    surname varchar(50))

insert into test(categoryId, name, surname)
values  (1, 'John', 'Locke'),
        (1, 'John', 'Millar'),
        (1, 'James', 'Mill'),
        (1, 'John Stuart', 'Mill'),
        (2, 'Thomas', 'Bayes'),
        (2, 'Laurent', 'Schwartz'),
        (2, 'Herrmann Amandus', 'Schwartz'),
        (2, 'Thomas', 'Simpson'),
        (2, 'Leonhard', 'Euler')

Resultado deve ser:

+------------+-------+--------+---------+----------+------------+
| categoryId | total |  name  | namePct | surname  | surnamePct |
+------------+-------+--------+---------+----------+------------+
|          1 |     4 | John   |    0.50 | Mill     |       0.50 |
|          2 |     5 | Thomas |    0.40 | Schwartz |       0.40 |
+------------+-------+--------+---------+----------+------------+

Para este exemplo simples, posso descobrir como posso conseguir isso, com uma consulta como:

select  t.categoryId, 
        t.total, 
        n.name, 
        1. * n.total / t.total as namePct,
        sn.surname,
        1. * sn.total / t.total as surnamePct
from (
    select categoryId, count(*) as total
    from test
    group by categoryId
    ) t
join (
        select categoryId, name, total
        from (
            select categoryId, name, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, name, count(*) as total
                from test
                group by categoryId, name
                ) t
            ) t
        where rn = 1
        ) n on t.categoryId = n.categoryId
join (
        select categoryId, surname, total
        from (
            select categoryId, surname, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, surname, count(*) as total
                from test
                group by categoryId, surname
                ) t
            ) t
        where rn = 1
        ) sn on t.categoryId = sn.categoryId

Mas, no meu caso de uso real, minha tabela tem milhões de linhas, centenas de categorias e uma dúzia de atributos.

Existe uma maneira de tornar a consulta mais simples e eficiente (ou seja, sem um monte de sub-select para cada atributo)?

Atualmente estou usando o SQL Server 2008, mas respostas usando versões mais recentes são bem-vindas.

Estou testando um processo de replicação de instantâneo (SQLServer 2008 (sim, infelizmente...)).

Está tudo bem, mas não consigo encontrar na documentação do MS como as tabelas são atualizadas. Vi que o processo de publicação descarta as tabelas, depois as recria e as preenche com dados.

OK, fixe. Mas existe algum tipo de segurança (bloqueios, transações...) que impeça que seja feita uma consulta durante esta sincronização (em particular apenas entre a drop table e a create)?

Vamos imaginar um banco de dados A( publisher ) tendo uma tabela Treplicada em outro banco de dados B( subscriber ). Quando o agente de distribuição está em execução e a tabela Té atualizada no servidor B ( drop table T + create table T + bcp-in data ?), durante alguns milissegundos a tabela não estará lá. Se um aplicativo tentar ler table T, ele receberá um erro.

Quando tento iniciar um rastreamento no servidor Bquando o agente de distribuição está em execução, não consigo ver nenhuma transação feita para esta etapa. E, de fato, posso fazer uma seleção em uma tabela replicada Tno servidor B(e obter um erro).

É realmente assim que deve funcionar?

Existem configurações em algum lugar para evitar tal comportamento?

No SQL Server 2008 (mas também em 2014). Vamos considerar um procedimento que tenha um parâmetro de saída. Este procedimento pode produzir um erro (e o fará no exemplo a seguir). Observo que o comportamento do parâmetro de saída não é o mesmo se chamarmos o procedimento dentro de um bloco TRY/ .CATCH

Exemplo:

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

Se iniciarmos o procedimento da maneira simples:

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

obtemos (e estou bem com isso):

Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
Este é um erro O
resultado é: erro

Se o procedimento estiver agora em um bloco try/catch:

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

temos (e estou chateado):

Msg 50000, Level 16, State 1, Line 28
Este é um erro O
resultado é: vazio

A mensagem de erro está OK, mas o parâmetro de saída agora é NULL . Se, em um TRY...CATCHcontexto, a execução for interrompida imediatamente após o RAISERROR, eu esperaria que o valor de saída fosse definido como hello .

Por que é tão?

Eu tento executar o SQL Server Upgrade Advisor 2014 em um servidor SQL Server 2008.

Tudo parece bem ao conectar ao servidor 2008 e posso selecionar qual banco de dados gostaria de analisar.

Mas uma vez lançado, ele roda para sempre, sem fazer nada (mantendo-se na etapa Analisando Regras: 0/112 ).

O que está acontecendo e como posso corrigir isso?

Tanto o SQL Server 2008 quanto o 2014 são versões Express e estão instalados no meu laptop. Infelizmente, esta é a única maneira que encontrei para executar o Upgrade Advisor (há muitas restrições para executá-lo em bancos de dados reais de produção / desenvolvimento).

A instância do SQL 2008 está com SP3 conforme necessário .

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) 

Quando o Advisor está congelado, podemos ver 4 consultas adormecidas (não ativas):

SELECT name,
       cmptlevel
FROM
  (SELECT name,
          dbid,
          cmptlevel,
          DATABASEPROPERTYEX(name, N'UserAccess') AS 'UserAccess',
          DATABASEPROPERTYEX(name, N'Status') AS 'Status',
          DATABASEPROPERTYEX(name, N'IsInStandBy') AS 'IsInStandBy'
   FROM master.dbo.sysdatabases) t
WHERE LOWER(name) NOT IN ('tempdb',
                          'master',
                          'model',
                          'msdb')
  AND HAS_DBACCESS(name) = 1
  AND dbid NOT IN (32767)
  AND UserAccess != 'SINGLE_USER'
  AND Status = 'ONLINE'
  AND IsInStandBy = 0
ORDER BY name;

e

(@dbname nvarchar(256))
SELECT COUNT(*)
FROM master.dbo.sysdatabases
WHERE name=@dbname

e

SELECT 'Edition'=SUBSTRING(@@VERSION,PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2,PATINDEX(N'% on %',@@VERSION)-(PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2))

e

SELECT N'Job.Step',
        j.name + N'.' + js.step_name
FROM msdb.dbo.syssubsystems ss
JOIN msdb.dbo.sysjobsteps js ON ss.subsystem = js.subsystem
JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE ss.description_id=14555
UNION
SELECT N'Proxy',
        p.name
FROM msdb.dbo.sysproxysubsystem ps
JOIN msdb.dbo.sysproxies p ON ps.proxy_id = p.proxy_id
JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
WHERE ss.description_id=14555

Claro, quando os iniciamos manualmente, eles funcionam.

E não há nada suspeito quando lanço um rastreamento SQL no servidor...

Eu tenho uma tabela como (SQLServer 2008):

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED 
(
    [productId] ASC,
    [purchaseId] ASC
))

tendo cerca de 10 milhões de linhas.

Desejo uma consulta que retorne o número total de linhas de um produto ou, se o produto não estiver definido, o número total de linhas de todos os produtos. Algo como:

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

O problema é que a consulta leva muito mais tempo do que a consulta equivalente:

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

Como podemos explicar esse comportamento?

Aqui estão os planos de execução:

Vamos imaginar um procedimento armazenado que recupera dados e faz algum tipo de paginação. Este procedimento tem algumas entradas que descrevem qual conjunto de dados queremos e como o classificamos.

Aqui está uma consulta muito simples, mas vamos tomá-la como exemplo.

create table Persons(id int, firstName varchar(50), lastName varchar(50))
go
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

Deve ser usado assim:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc'

Mas um cara esperto poderia lançar:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = ''

... e soltar dados

Isso obviamente não é uma situação segura. E como poderíamos evitar?

Nota : esta pergunta não é sobre "é uma boa maneira de fazer paginação?" nem "é uma coisa boa fazer sql dinâmico?". A questão é sobre evitar a injeção de código ao construir consultas sql dinamicamente para ter algumas diretrizes para tornar o código um pouco mais limpo se tivermos que fazer procedimentos armazenados semelhantes novamente no futuro.

Algumas ideias básicas:

Validar entradas

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

Passe ids em vez de strings como entradas

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0
as

declare @orderByName varchar(50)
set @orderByName =  case @orderBy when 1 then 'id'
                        when 2 then 'firstName'
                        when 3 then 'lastName'
                    end 
                +' '+case @orderDir 
                        when 0 then 'desc' 
                        else 'asc' 
                    end

if @orderByName is null
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderByName

exec(@sql)

Alguma outra sugestão?

Eu tenho um problema estranho com um banco de dados SQLServer 2014 (12.0.4439.1).

Criei há alguns meses um arquivo de dados e uma tabela para brincar com os recursos básicos do In-Memory e ver se poderia melhorar alguns de nossos processos. Quando terminei, queria limpar tudo, mas não sabia que " Depois de criar um grupo de arquivos com otimização de memória, você só pode removê-lo descartando o banco de dados ".

Bem, eu não queria deletar meu banco de dados (já que é usado na vida real por pessoas reais) nem recriá-lo / preenchê-lo novamente (muito trabalho).

Então tudo ficou assim: um banco de dados normal com um arquivo vazio para coisas na memória. E tudo está indo bem.

Mas, uma vez, o arquivo de log desse banco de dados está enlouquecendo: seu tamanho aumenta e nada pode detê-lo. E quando tento reduzi-lo, geralmente (nem sempre) recebo um " redução falhou no arquivo de log devido a XTP_CHECKPOINT " que parece relacionado a recursos na memória. E depois de tentar o backup / reinício do serviço / ponto de verificação e outros comandos esotéricos, o arquivo de log pode ser manipulado e reduzido corretamente.

Resumindo: não entendo o que aconteceu.

Alguém já experimentou isso? e/ou conhece uma maneira limpa de evitar/consertar esse comportamento estranho?

[EDIT] : o problema é muito semelhante a https://support.microsoft.com/en-us/kb/3090141 , mas no meu caso, o hotfix já está instalado e a solução alternativa sugerida (offline / online) não funciona

Normalmente, nos logs do SQLServer, há uma mensagem HkHostLogCheckpointRecord() a cada hora. O aumento do arquivo de log começou a acontecer quando essas mensagens pararam de aparecer. É a causa ou a consequência? essa é a questão....