Martin Riddar's questions

Estou tentando criar um procedimento armazenado, mas ele me dá a mensagem de erro
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

O fato é que tanto o banco de dados quanto o agrupamento do servidor são SQL_Latin1_General_CP1_CI_ASe não tenho ideia de onde Latin1_General_BIN2vem o agrupamento.

create procedure [ETL_1.4.0].update_valve_event_type
(
    @data nvarchar(max)
)
as
    declare @mismatch_table table(id int, [name] varchar(50))

    if isjson(@data) = 0
    begin
        ;throw 50000,'Input argument @data is invalid JSON.', 1
    end

    insert @mismatch_table (id, [name])
    select * from
    (
        select
            value as id,
            [key] as [name]
        from openjson(@data)
        except
        select
            id,
            [name]
        from enum.valve_event
    )data

    --clear mismatches that are deemed ok, e.g. spelling corrections
    delete from @mismatch_table
    where id = 10 and [name] = 'FEEDBACK_FROM_USER'

    if exists(select * from @mismatch_table)
    begin
        ;throw 50000,'Terminal version of enum for valve events does not match the EDW version', 1
    end
go

Então, alguém pode explicar o que estou perdendo?
Se for importante, esse banco de dados (e servidor) ainda não está ativo, portanto, tecnicamente, posso alterar os agrupamentos onde quiser.

O título diz "não relacionado" porque é isso que eles deveriam ser, mas aparentemente não...

Eu tenho dois procedimentos armazenados ( log_sequence_evente log_se_event) que causam um impasse e não tenho idéia do porquê. Eles são chamados pelo mesmo aplicativo Python em diferentes threads por meio de PYODBC com autocommit desativado. Os encadeamentos fazem um máximo de 2.000 chamadas antes de confirmar.
sequencee senão devem ser confundidos entre si, eles são completamente independentes.

Ambos os procedimentos fazem uma inserção em suas respectivas tabelas ( fact.sequencee fact.se) e cada tabela possui uma visualização indexada separada nela ( v_daily_fact_sequencee v_daily_fact_se).
O procedimento log_se_eventfaz um select (com join) das tabelas dim.see dim.network(que não tem conexão com as sequências) antes do insert para fact.se. O log_sequence_eventnão está fazendo nada antes da inserção.

Se eu entendi o relatório de deadlock corretamente, o deadlock acontece quando log_sequence_eventmantém um RangeS-Ubloqueio no índice para exibição v_daily_fact_see log_se_eventmantém um RangeS-Ubloqueio no índice para exibição v_daily_fact_sequencee eles querem um bloqueio para o outro índice. Esta é (para mim) a parte wtf, por que está log_sequence_eventsegurando um bloqueio no índice v_daily_fact_see vice-versa?
Isso está no meu laptop executando o SQL Server 2019 Developers Edition.

As partes envolvidas:

• Os procs:

create procedure [ETL_1.7.1].log_sequence_event
(
    @terminal_id int,
    @terminal_row_id bigint,
    @timestamp datetime2(3),
    @id int,
    @event int,
    @energy int,
    @duration int,
    @weight int = null,
    @no_of_dvs int,
    @average_fill_degree int --not used
)
as
    if (@event < 100)
        insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            @energy,--pred_kwh,
            @no_of_dvs,--pred_dv_count,
            @duration,--pred_duration,
            @weight,--pred_collected_weight,
            0,--actual_kwh,
            0,--actual_dv_count,
            0,--actual_duration,
            0--actual_collected_weight
        )
    else
        insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            0,--pred_kwh,
            0,--pred_dv_count,
            0,--pred_duration,
            0,--pred_collected_weight,
            @energy,--actual_kwh,
            @no_of_dvs,--actual_dv_count,
            @duration,--actual_duration,
            @weight--actual_collected_weight
        )
go


create procedure [ETL_1.7.1].log_se_event
(
    @terminal_id int,
    @terminal_row_id bigint,
    @timestamp datetime2(3),
    @id int,
    @event int
)
as
    declare @vcm_id int = 0

    select
        @vcm_id = [address]
    from dim.se se
    join dim.network net
        on se.terminal_id = net.terminal_id and se.network = net.id
    where se.terminal_id = @terminal_id
        and se.se = @id
        and network_type = 6

    insert into fact.se
    (
        terminal_id,
        terminal_row_id,
        [timestamp],
        se,
        event_id,
        repeat_count,
        error_code,
        network,
        vcm_id
    )
    values
    (
        @terminal_id,
        @terminal_row_id,
        @timestamp,
        @id,
        @event,--event_id,
        0,--repeat_count,
        0,--error_code,
        (select d.network from dim.se d where d.terminal_id = @terminal_id and d.se = @id),--network,
        @vcm_id
    )
go

• As tabelas e as definições de exibição indexada relacionadas:

create table fact.sequence
(
    terminal_id int not null,
    terminal_row_id bigint not null,
    [timestamp] datetime2(3) not null,

    sequence_id int not null,
    event_type_id int not null,
    pred_kwh int not null,
    pred_dv_count int not null,
    pred_duration int not null, --seconds
    pred_collected_weight int not null, --kg
    actual_kwh int not null,
    actual_dv_count int not null,
    actual_duration int not null, --seconds
    actual_collected_weight int not null, --kg

    constraint pk__fact_sequence primary key (terminal_id, terminal_row_id),
    constraint fk__fact_sequence__dim_sequence foreign key (terminal_id, sequence_id) references dim.sequence(terminal_id, id),
    constraint fk__fact_sequence__enum_sequence_event foreign key (event_type_id) references enum.sequence_event (id)
)

go
create view staging.v_daily_fact_sequence
with schemabinding
as
    SELECT
        cast(fs.[timestamp] as date) [date],
        fs.terminal_id,
        fs.sequence_id,
        fs.event_type_id,
        count_big(*) as [count],
        sum(fs.actual_kwh) sum_kwh,
        sum(fs.actual_dv_count) sum_dv_count,
        sum(fs.actual_duration) sum_duration,
        sum(fs.actual_collected_weight) sum_kg
    FROM [fact].[sequence] fs
    group by
        cast([timestamp] as date),
        fs.terminal_id,
        sequence_id,
        event_type_id
go
create unique clustered index uci__staging__fact_sequence on staging.v_daily_fact_sequence([date], terminal_id, sequence_id, event_type_id)
go



create table fact.se
(
    terminal_id int not null,
    terminal_row_id bigint not null,
    [timestamp] datetime2(3) not null,

    se int not null,
    event_id int not null,
    repeat_count int not null,
    error_code int not null,
    network int not null,
    vcm_id int not null,

    constraint pk__fact_se primary key (terminal_id, terminal_row_id),
    constraint fk__fact_se__dim_se foreign key (terminal_id, se) references dim.se(terminal_id, se),
    constraint fk__fact_se__enum_valve_event foreign key (event_id) references enum.valve_event (id)
)

go
create view staging.v_daily_fact_se
with schemabinding
as
    select
        cast([timestamp] as date) [date],
        terminal_id,
        se,
        event_id,
        count_big(*) as [count]
    from fact.se
    group by
        cast([timestamp] as date),
        terminal_id,
        se,
        event_id
go
create unique clustered index uci__staging__fact_se on staging.v_daily_fact_se([date], terminal_id, se, event_id)
go

<deadlock>
  <victim-list>
    <victimProcess id="process2742b6a5848" />
  </victim-list>
  <process-list>
    <process id="process2742b6a5848" taskpriority="0" logused="351972" waitresource="KEY: 5:72057594049855488 (1c280247b0f1)" waittime="2388" ownerId="8532120" transactionname="user_transaction" lasttranstarted="2022-11-02T11:48:16.683" XDES="0x273c207c428" lockMode="RangeS-U" schedulerid="11" kpid="30304" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-02T11:48:17.697" lastbatchcompleted="2022-11-02T11:48:17.697" lastattention="1900-01-01T00:00:00.697" clientapp="Python" hostname="ENE-5CD9245PJR" hostpid="30564" loginname="etl" isolationlevel="read committed (2)" xactid="8532120" currentdb="5" currentdbname="EDW" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="EDW.ETL_1.7.1.log_sequence_event" line="50" stmtstart="2510" stmtend="4352" sqlhandle="0x03000500e1ab652609eeba0041af000001000000000000000000000000000000000000000000000000000000">
insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            0,--pred_kwh,
            0,--pred_dv_count,
            0,--pred_duration,
            0,--pred_collected_weight,
            @energy,--actual_kwh,
            @no_of_dvs,--actual_dv_count,
            @duration,--actual_duration,
            @weight--actual_collected_weight    </frame>
        <frame procname="adhoc" line="1" stmtstart="218" stmtend="588" sqlhandle="0x0100050095e97700e09e97017402000000000000000000000000000000000000000000000000000000000000">
EXEC [ETL_1.7.1].log_sequence_event @timestamp=@P1,@id=@P2,@event=@P3,@energy=@P4,@duration=@P5,@weight=@P6,@no_of_dvs=@P7,@average_fill_degree=@P8,@terminal_id=@P9,@terminal_row_id=@P1    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P1 nvarchar(46),@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 bigint)set nocount on;EXEC [ETL_1.7.1].log_sequence_event @timestamp=@P1,@id=@P2,@event=@P3,@energy=@P4,@duration=@P5,@weight=@P6,@no_of_dvs=@P7,@average_fill_degree=@P8,@terminal_id=@P9,@terminal_row_id=@P10   </inputbuf>
    </process>
    <process id="process2742b06d468" taskpriority="0" logused="770436" waitresource="KEY: 5:72057594049658880 (ffffffffffff)" waittime="2426" ownerId="8529120" transactionname="user_transaction" lasttranstarted="2022-11-02T11:48:15.520" XDES="0x273c2164428" lockMode="RangeS-U" schedulerid="4" kpid="36140" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-02T11:48:17.657" lastbatchcompleted="2022-11-02T11:48:17.657" lastattention="1900-01-01T00:00:00.657" clientapp="Python" hostname="ENE-5CD9245PJR" hostpid="30564" loginname="etl" isolationlevel="read committed (2)" xactid="8529120" currentdb="5" currentdbname="EDW" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="EDW.ETL_1.7.1.log_se_event" line="22" stmtstart="872" stmtend="1872" sqlhandle="0x03000500c4f6a02105eeba0041af000001000000000000000000000000000000000000000000000000000000">
insert into fact.se
    (
        terminal_id,
        terminal_row_id,
        [timestamp],
        se,
        event_id,
        repeat_count,
        error_code,
        network,
        vcm_id
    )
    values
    (
        @terminal_id,
        @terminal_row_id,
        @timestamp,
        @id,
        @event,--event_id,
        0,--repeat_count,
        0,--error_code,
        (select d.network from dim.se d where d.terminal_id = @terminal_id and d.se = @id),--network,
        @vcm_id    </frame>
        <frame procname="adhoc" line="1" stmtstart="136" stmtend="336" sqlhandle="0x01000500b1ea553470ff35ee7302000000000000000000000000000000000000000000000000000000000000">
EXEC [ETL_1.7.1].log_se_event @timestamp=@P1,@id=@P2,@event=@P3,@terminal_id=@P4,@terminal_row_id=@P    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P1 nvarchar(46),@P2 int,@P3 int,@P4 int,@P5 bigint)set nocount on;EXEC [ETL_1.7.1].log_se_event @timestamp=@P1,@id=@P2,@event=@P3,@terminal_id=@P4,@terminal_row_id=@P5   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594049855488" dbid="5" objectname="EDW.staging.v_daily_fact_sequence" indexname="uci__staging__fact_sequence" id="lock2740ecc4e00" mode="X" associatedObjectId="72057594049855488">
      <owner-list>
        <owner id="process2742b06d468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2742b6a5848" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594049658880" dbid="5" objectname="EDW.staging.v_daily_fact_se" indexname="uci__staging__fact_se" id="lock27317245700" mode="RangeS-U" associatedObjectId="72057594049658880">
      <owner-list>
        <owner id="process2742b6a5848" mode="RangeS-U" />
      </owner-list>
      <waiter-list>
        <waiter id="process2742b06d468" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Posso obter a memória disponível consultando a [master].[sys].[dm_os_sys_memory]exibição, mas não encontrei nada semelhante para obter informações sobre a carga da CPU.

Só encontro informações sobre como consultar a carga da CPU pelo próprio servidor SQL, o que não é minha principal preocupação, preciso da carga total no sistema. Procurei nas visualizações do sistema, mas não consigo encontrar nada ou pelo menos não entendo se encontrei ...

A razão pela qual quero obter isso via T-SQL é que já temos um aplicativo de monitoramento que está consultando todos os nossos sistemas em busca de outras informações e seria bom poder expandir isso em vez de configurar algo completamente novo.

Atualmente, os sistemas de interesse estão executando o expresso 2014 e 2017, mas provavelmente incluirão o expresso 2019/2022 em breve.

Estou tentando encontrar uma maneira de permitir que um aplicativo crie tabelas e insira dados nelas em um SQL Server 2019 enquanto protege contra ataques de injeção caso as credenciais do aplicativo vazem. Minha experiência é limitada quando se trata de escrever código que pode ser executado em paralelo e escrever sql dinâmico protegido contra ataques de injeção de sql.
O nome da tabela é baseado na entrada do aplicativo, ou seja, se a entrada for 'nds', o nome da tabela deve ser lake.nds_raw_log.
É meu entendimento que não há como fazer isso por meio da concessão direta de permissões à função para este aplicativo, pois a criação de tabelas não é separada da exclusão ou alteração delas.

O que eu criei é executar um procedimento armazenado como dbo. Claro que não é longo, mas eu tenho dois problemas com ele:

• parece artificial, o que, pela minha experiência, diz que existe uma maneira mais fácil.
• Acredito que preciso executá-lo como serializável para evitar tabelas órfãs se eu recuperar a tabela errada quando consultar minha tabela recém-criada. Isso não deve ser um problema tão grande, já que não acontecerá com tanta frequência após o primeiro início da produção, então talvez eu não deva me importar com isso.

    create procedure [lake].[create_terminal_raw_log_table]
    (
        @terminal_name nvarchar(100)
    )
    with execute as 'dbo'
    as
        begin try
            set transaction isolation level serializable
            begin transaction
                --create table
                declare @dynamic_sql nvarchar(1000) =
                    'create table [lake].' + quotename(@terminal_name) + '
                    (
                        id bigint not null,
                        [timestamp] datetime2(3) not null,
                        cmd varbinary(max) not null
                    );'
                exec sp_executesql @dynamic_sql
    
                /*get name of new table, this is why I believe that I need serializable isolation
                since other tables can be created in parallel*/
                declare @table_name nvarchar(100) =
                (
                    select top 1
                        [name] as table_name
                    from sys.tables
                    order by create_date desc
                )
        
                --rename table
                declare
                    @old_name nvarchar(100) = '[lake].' + @table_name,
                    @new_name nvarchar(100) = @table_name + '_raw_log'
                begin try
                    exec sp_rename
                        @objname = @old_name,
                        @newname = @new_name
                end try
                begin catch
                    set @dynamic_sql = 'drop table ' + @old_name
                    exec sp_executesql @dynamic_sql
                    ;throw
                end catch
    
                --create primary key
                set @dynamic_sql = 'alter table [lake].' + @new_name + ' add constraint pk__' + @new_name + ' primary key(id)'
                exec sp_executesql @dynamic_sql
    
            commit transaction
        end try
        begin catch
            rollback --I thought a rollback would occur when I throw after dropping the table but that doesn't seem to be the case
            ;throw
        end catch

Então eu acho que isso se resume a 3 perguntas:

• Este procedimento armazenado é realmente seguro contra ataques de injeção de SQL?
• Existe uma maneira mais fácil de fazer isso?
• É correto que definir o nível de transação como serializável protegerá o código de selecionar a tabela errada ao selecionar sys.tables?

Eu postei isso pela primeira vez no SO, mas não obtive respostas, esta é a razão pela qual estou tentando aqui agora e espero não estar quebrando nenhuma regra ao fazê-lo (dedos cruzados).

Estou tentando entender como usar o particionamento de tabelas e a eliminação de partições corretamente para poder decidir se quero usá-lo ou não em um novo banco de dados (sql server 17 express). Estou inclinado a não usá-lo, mas essa é uma história diferente, então essa pergunta é mais que eu não quero saber como funciona agora. No script a seguir eu crio uma função e esquema de particionamento, uma tabela usando o esquema, insiro alguns dados e depois consulto de 3 maneiras diferentes.

• A primeira consulta está acessando corretamente apenas a primeira partição (contagem real de partições = 1, partições reais acessadas = 1).

• Espero que a segunda consulta acesse apenas a primeira partição, já que criei a função de partição como intervalo no valor 20200101, mas ela está acessando as partições 1 e 2 (contagem real de partições = 2, partições reais acessadas = 1. .2).

• Espero que a terceira consulta acesse apenas a partição 2, mas está acessando as partições 2 e 3 (contagem real de partições = 2, partições reais acessadas = 2..3).

Estou ciente de que preciso ter uma correspondência de tipo exata para que a eliminação de partição ocorra, mas acho que tenho isso aqui? o que estou perdendo?

create partition function partfun (datetime2(0))
as range right for values ('20200101', '20210101')

create partition scheme ps
as partition partfun all to ([primary])

create table t
(
    id int not null identity(1,1),
    logdate datetime2(0)
) on ps(logdate)
insert into t values ('20190101'),('20200102')

select * from t where logdate < cast('20191231' as datetime2(0))
select * from t where logdate < cast('20200101' as datetime2(0))
select * from t where logdate >= cast('20200101' as datetime2(0)) and t.logdate < cast('20210101' as datetime2(0))