Vladimir Baranov's questions

Temos um SQL Server 2016 que possui 20 bancos de dados diferentes que são usados ​​por diferentes aplicativos e diferentes equipes da empresa. Existem grupos de usuários no Active Directory que reúnem funcionários que trabalham em determinados projetos. O problema é que um funcionário pode fazer parte de diversas equipes ao mesmo tempo. A mesma pessoa pode pertencer a vários grupos AD diferentes.

Um usuário reclamou que não conseguia acessar um determinado banco de dados. Quando ele estava tentando atualizar o relatório do Power BI que consulta esse banco de dados, ele recebeu uma mensagem de erro "Não foi possível autenticar com as credenciais fornecidas".

Aqui está uma imagem simplificada. Um servidor possui dois bancos de dados: DB1e DB2. Existem dois grupos AD App_Excel_Reportere DB2_Userdois logins correspondentes no SQL Server:

CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Não tenho certeza se isso é importante, mas este servidor é um servidor legado que fazia parte de outra empresa adquirida pela empresa principal. Portanto, este computador SQL Server está no domínio LegacyDomain.

Dois grupos AD LegacyDomain\App_Excel_Reportertêm Domain\DB2_Userseus próprios conjuntos de usuários, mas um usuário Jack.Universalpertence a ambos os grupos AD.

Os usuários que fazem parte App_Excel_Reporterdevem ter acesso ao DB1. Os usuários que fazem parte DB2_Userdevem ter acesso ao DB2.

Nele DB1há um usuário mapeado para o login correspondente:

USE [DB1]
GO
CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo]
GO

Nele DB2há um usuário mapeado para o login correspondente:

USE [DB2]
GO
CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo]
GO

Quando um usuário Jack.Universaltenta atualizar o Power BI, ele está conectado ao Windows em seu laptop Domain\Jack.Universale vejo estas mensagens no log do SQL Server com o mesmo carimbo de data/hora:

Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: <ip address>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: <ip address>]

Quando eu corro

xp_logininfo 'Domain\Jack.Universal', @option = 'all'

isto mostra

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

(sim, existem mais de dois bancos de dados e este usuário pertence a três grupos AD)

Se eu correr

xp_logininfo 'Domain\Jack.Universal'

sem a opção "todos", apenas a primeira linha será retornada:

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------+------+-----------+-----------------------+---------------------------------+

Portanto, parece que quando um usuário do Windows Domain\Jack.Universalefetua login no SQL Server, o SQL Server escolhe o login LegacyDomain\App_Excel_Reporterpara permitir a entrada do usuário, mas quando tenta acessar um banco de dados, DB2essa tentativa falha porque o login LegacyDomain\App_Excel_Reporteré mapeado apenas para o usuário em DB1.

"o banco de dados especificado explicitamente" deve ser devido à cadeia de conexão do Power BI que especifica explicitamente DB2.

Como configuramos isso para que um usuário pertencente a ambos os grupos AD tenha acesso a ambos os bancos de dados? Os usuários que pertencem a apenas um grupo AD deverão ter acesso apenas ao banco de dados correspondente.

Não sou administrador de domínio, mas posso perguntar o que for necessário. A empresa não tem DBA, sou apenas um programador que sabe uma ou duas coisas sobre SQL Server.

Cavei um pouco mais fundo. Quando eu corro

EXEC xp_logininfo 'Domain\DB2_User', @option = 'members'

Recebo uma lista de usuários nesse grupo:

+-----------------------+------+-----------+-----------------------+-----------------+
|     account name      | type | privilege |   mapped login name   | permission path |
+-----------------------+------+-----------+-----------------------+-----------------+
| Domain\user1          | user | user      | Domain\user1          | Domain\DB2_User |
| Domain\user2          | user | user      | Domain\user2          | Domain\DB2_User |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User |
+-----------------------+------+-----------+-----------------------+-----------------+

E os usuários estão dentro Domaindo esperado.

Quando eu corro

EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members'

Eu entendi isso:

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\user3          | user | user      | LegacyDomain\user3          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\user4          | user | user      | LegacyDomain\user4          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

Aqui o caminho da permissão está dentro LegacyDomaine o nome da conta também está dentro LegacyDomain, mas acima nos resultados do xp_logininfo 'Domain\Jack.Universal'nome da conta está dentro Domain, mas o caminho da permissão está dentro LegacyDomain.

Outra vez:

EXEC xp_logininfo 'Domain\Jack.Universal', @option = 'all'

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

EXEC xp_logininfo 'LegacyDomain\Jack.Universal', @option = 'all'

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

Deve haver alguma mágica no Active Directory que mapeie os usuários do domínio legado para o domínio principal.

Nossa empresa possui uma instância RDS de um banco de dados Postgres na AWS. Não tenho muita experiência com AWS.

Gostaria de executar uma consulta muito longa no banco de dados (VACUUM FULL) que pode levar muitas horas ou até dias. Posso me conectar ao banco de dados do meu laptop criando um túnel para a AWS usando uma ferramenta chamada "passaporte".

O problema é que se eu iniciar uma consulta no meu laptop e minha conexão com a Internet cair, ou o laptop reiniciar, ou qualquer outra coisa acontecer na rede entre meu laptop e o computador da Amazon, a consulta será interrompida e perderei qualquer saída do consulta pode gerar.

Nos velhos tempos, quando eu tinha um servidor rodando um sistema operacional com um banco de dados, eu podia me conectar a esse sistema operacional (área de trabalho remota ou ssh ou qualquer outro) e então executar um programa nesse servidor (pgAdmin, psql, etc) que se conectaria ao banco de dados localmente, ao "localhost". Então eu poderia deixá-lo rodando, desconectar do servidor e voltar no dia seguinte, conectar-me novamente e ver os resultados naquela instância do pgAdmin ainda em execução naquele servidor. Nesse caso não havia rede entre a aplicação que estava executando a consulta e o banco de dados.

É possível ter algo assim com o RDS? Como?

Usamos uma instância do Amazon RDS com

PostgreSQL 11.13 em x86_64-pc-linux-gnu, compilado por gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64 bits

Eu tenho uma consulta clássica simples de 1 por grupo. Eu preciso obter o último item no histórico para cada arquivo creativeScheduleId.

Aqui está uma tabela e definições de índice:

CREATE TABLE IF NOT EXISTS public.creative_schedule_status_histories (
  id serial PRIMARY KEY,
  "creativeScheduleId" text NOT NULL,
  -- other columns
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_creativescheduleid_id
  ON public.creative_schedule_status_histories ("creativeScheduleId" ASC, id ASC);

Quando uma consulta ordena pelo id ASCmecanismo lê apenas o índice e não faz nenhuma classificação extra:

EXPLAIN (ANALYZE) 
SELECT history.id, history."creativeScheduleId"
FROM  (
    SELECT cssh.id, cssh."creativeScheduleId"
         , ROW_NUMBER() OVER (PARTITION BY cssh."creativeScheduleId"
                              ORDER BY cssh.id ASC) AS rn  -- !
    FROM creative_schedule_status_histories as cssh
    ) AS history
WHERE history.rn = 1;

"Subquery Scan on history  (cost=0.56..511808.63 rows=26377 width=41) (actual time=0.047..4539.058 rows=709030 loops=1)"
"  Filter: (history.rn = 1)"
"  Rows Removed by Filter: 4579766"
"  ->  WindowAgg  (cost=0.56..445866.24 rows=5275391 width=49) (actual time=0.046..4165.835 rows=5288796 loops=1)"
"        ->  Index Only Scan using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..353546.90 rows=5275391 width=41) (actual time=0.037..1447.490 rows=5288796 loops=1)"
"              Heap Fetches: 2372"
"Planning Time: 0.072 ms"
"Execution Time: 4568.235 ms"

Eu esperava ver exatamente o mesmo plano para uma consulta ao fazer o pedido por id DESC, mas há uma classificação explícita no plano que se espalha para o disco e, obviamente, tudo é apenas mais lento.

EXPLAIN (ANALYZE) 
SELECT history.id, history."creativeScheduleId"
FROM  (
    SELECT cssh.id, cssh."creativeScheduleId"
         , ROW_NUMBER() OVER (PARTITION BY cssh."creativeScheduleId"
                              ORDER BY cssh.id DESC) AS rn  -- !
    FROM creative_schedule_status_histories as cssh
    ) AS history
WHERE history.rn = 1;

"Subquery Scan on history  (cost=1267132.63..1438582.84 rows=26377 width=41) (actual time=11974.827..15840.338 rows=709046 loops=1)"
"  Filter: (history.rn = 1)"
"  Rows Removed by Filter: 4579802"
"  ->  WindowAgg  (cost=1267132.63..1372640.45 rows=5275391 width=49) (actual time=11974.825..15529.679 rows=5288848 loops=1)"
"        ->  Sort  (cost=1267132.63..1280321.11 rows=5275391 width=41) (actual time=11974.814..13547.038 rows=5288848 loops=1)"
"              Sort Key: cssh.""creativeScheduleId"", cssh.id DESC"
"              Sort Method: external merge  Disk: 263992kB"
"              ->  Index Only Scan using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..353550.90 rows=5275391 width=41) (actual time=0.015..1386.310 rows=5288848 loops=1)"
"                    Heap Fetches: 2508"
"Planning Time: 0.078 ms"
"Execution Time: 15949.877 ms"

Eu esperava que o índice fornecido fosse igualmente útil em ambas as variantes da consulta.
O Postgres não pode digitalizar um índice para trás aqui?
O que estou perdendo aqui?

Quando faço uma consulta para um dado específico creativeScheduleId, o Postgres usa o índice igualmente bem para ambos ASCe a DESCordem de classificação. Não há classificação explícita em nenhuma variante:

EXPLAIN (ANALYZE) 
SELECT id, "creativeScheduleId"
FROM   creative_schedule_status_histories AS cssh
WHERE  "creativeScheduleId" = '24238370-a64c-4b30-ac8e-27eb2b693aca'
ORDER  BY id DESC  -- or ASC, no sort
LIMIT  1

"Limit  (cost=0.56..0.71 rows=1 width=41) (actual time=0.022..0.022 rows=1 loops=1)"
"  ->  Index Only Scan Backward using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..14.06 rows=86 width=41) (actual time=0.021..0.021 rows=1 loops=1)"
"        Index Cond: (""creativeScheduleId"" = '24238370-a64c-4b30-ac8e-27eb2b693aca'::text)"
"        Heap Fetches: 0"
"Planning Time: 0.064 ms"
"Execution Time: 0.033 ms"

Aqui nós realmente vemos Index Only Scan Backward, então o Postgres é capaz disso. Mas não para a mesa inteira.

Alguma idéia de como incentivar o mecanismo a varrer todo o índice para trás para a primeira consulta que lê a tabela inteira?

Preciso ler dados de um servidor vinculado e inserir em uma tabela local. Preciso remover duplicatas nos dados e preciso fazer isso em um servidor local, pois o servidor remoto está sobrecarregado. Então, eu adicionei uma DISTINCTcláusula que faz Distinct Sort como eu quero.

O problema é que o operador Remote Scan sempre estima o número de linhas como 10.000, enquanto o número real de linhas é em torno de 3M. Assim, a classificação se espalha para o disco e se torna lenta.

Se existe uma maneira de sugerir ao otimizador que o número real de linhas é muito maior que 10K?

Devo carregar dados brutos em uma tabela de preparo local e, em seguida, executar DISTINCT na tabela local? Eu não queria gravar no disco duas vezes.

O número de linhas duplicadas é pequeno - algumas centenas de 3M. Quero dizer com isso que antes que as duplicatas sejam removidas, existem ~ 3.000.000 linhas; depois que os duplicados são removidos, existem cerca de 2.999.800 linhas. Portanto, remover as duplicatas no servidor remoto não reduziria visivelmente a quantidade de dados transferidos pela rede.

A tabela de destino é truncada antes da inserção, então estou sempre inserindo em uma tabela vazia. Além disso, a tabela de destino não possui índices, gatilhos ou restrições. Há muitas colunas na tabela. Cerca de 110 colunas. Na consulta abaixo, escrevi ManyManyColumnsem vez disso.

A pergunta:

WITH
CTE_Raw
AS
(
SELECT
    [ManyManyColumns]
FROM OpenQuery([remote_server],'
SELECT
    [ManyManyColumns]
FROM
    [DB].[dbo].[remote_view]
')
)
,CTE_Converted
AS
(
    SELECT DISTINCT
        [ManyManyColumns]
    FROM
        CTE_Raw
)
INSERT INTO [dbo].[TestVBFast2]
    ([ManyManyColumns]
    )
SELECT
    [ManyManyColumns]
FROM
    CTE_Converted
;

Versão do SQL Server:

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
        15 de agosto de 2017 10:23:29
        Direitos autorais (c) Microsoft Corporation
        Standard Edition (64 bits) no Windows NT 6.3 (Build 9600: ) (Hypervisor)

Quero realizar o DISTINCTlocalmente, pois o servidor remoto está sobrecarregado e quero reduzir sua carga. DISTINCTremoverá apenas algumas centenas de linhas da 3M, portanto, a quantidade de dados transferidos pela rede não mudará muito.

Quando tento executar uma consulta simples envolvendo um servidor vinculado, ela falha:

SELECT * FROM [server2].[DWH].[dbo].[SomeTable]

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Mas, há outro usuário que pode executar essa consulta sem problemas.

Nossa empresa não possui um SQL Server DBA e herdamos alguns SQL Servers de outra empresa após a aquisição.

Sou um desenvolvedor tentando executar algumas consultas e estou com dificuldades para descobrir como configurar o acesso corretamente. Eu realmente não sei onde procurar, então tentarei explicar a configuração atual da melhor maneira possível.

Existe o Servidor1:

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) 
    Aug 15 2017 10:23:29 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Existe o Servidor2:

Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 
    Jun 15 2019 07:56:34 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Nossa empresa tem um domínio MAIN_DOMAINe eu entro no meu laptop Windows como MAIN_DOMAIN\my.name. Quando executo o SSMS no meu laptop, posso me conectar a ambos Server1e Server2usando a autenticação do Windows.

Pelo que entendi, meu login nos dois SQL Servers tem praticamente todas as permissões:

Servidor1:

CREATE LOGIN [MAIN_DOMAIN\my.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [setupadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [processadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [diskadmin] ADD MEMBER [MAIN_DOMAIN\my.name]

Servidor2:

CREATE LOGIN [MAIN_DOMAIN\my.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [setupadmin] ADD MEMBER [MAIN_DOMAIN\my.name]

O próprio Server1 e Server2 não estão em MAIN_DOMAIN, eles estão emother_domain.com

Eu posso Remote Desktop para ambos usando other_domain\my.nameum usuário com uma senha diferente do meu usuário de domínio principal.

É assim que o servidor vinculado é configurado em Server1:

EXEC master.dbo.sp_addlinkedserver @server = N'server2', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'remote proc transaction promotion', @optvalue=N'true'

No Server1serviço MSSQLSERVER (sqlservr.exe) é executado no usuário chamado NT Service\MSSQLSERVER.

No Server2serviço MSSQLSERVER (sqlservr.exe) é executado no usuário chamado NT Service\MSSQLSERVER.

Aqui está a saída de setspn -l. Eu recebo a mesma saída quando executo server1ou server2:

C:\Windows\system32>setspn -l SERVER2
Registered ServicePrincipalNames for CN=SERVER2,OU=Azure Resources,OU=Corporate,DC=other_domain,DC=com,DC=au:
        MSSQLSvc/SERVER2.other_domain.com.au:1433
        MSSQLSvc/SERVER2.other_domain.com.au
        WSMAN/SERVER2
        WSMAN/SERVER2.other_domain.com.au
        TERMSRV/SERVER2
        TERMSRV/SERVER2.other_domain.com.au
        RestrictedKrbHost/SERVER2
        HOST/SERVER2
        RestrictedKrbHost/SERVER2.other_domain.com.au
        HOST/SERVER2.other_domain.com.au

C:\Windows\system32>setspn -l SERVER1
Registered ServicePrincipalNames for CN=SERVER1,OU=Azure Resources,OU=Corporate,DC=other_domain,DC=com,DC=au:
        MSSQLSvc/SERVER1.other_domain.com.au:1433
        MSSQLSvc/SERVER1.other_domain.com.au
        Microsoft Virtual Console Service/SERVER1.other_domain.com.au
        Microsoft Virtual Console Service/SERVER1
        Microsoft Virtual System Migration Service/SERVER1.other_domain.com.au
        Microsoft Virtual System Migration Service/SERVER1
        Hyper-V Replica Service/SERVER1.other_domain.com.au
        Hyper-V Replica Service/SERVER1
        WSMAN/SERVER1
        WSMAN/SERVER1.other_domain.com.au
        TERMSRV/SERVER1.other_domain.com.au
        TERMSRV/SERVER1
        RestrictedKrbHost/SERVER1
        HOST/SERVER1
        RestrictedKrbHost/SERVER1.other_domain.com.au
        HOST/SERVER1.other_domain.com.au

Infelizmente não entendo o que significa. A única coisa que eu entendo aqui é que MAIN_DOMAINnão é mencionado em nenhum lugar.

Um usuário diferente pode executar a consulta envolvendo o servidor vinculado sem problemas.

Ele faz login Server1usando a Área de Trabalho Remota e usando other_domain\his.name. Ele executa o SSMS nele Server1e se conecta ao SQL Server usando a autenticação do Windows.

Seu login Server1também tem todas as permissões:

CREATE LOGIN [other_domain\his.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [other_domain\his.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [other_domain\his.name]

O que preciso configurar para poder executar uma consulta envolvendo o servidor vinculado enquanto estou conectado ao SQL Server do meu laptop usando MAIN_DOMAIN\my.name?

Eu sou um DBA acidental. Sou a única pessoa em uma empresa que tem alguma experiência com SQL Server e tenho que lidar com um sistema herdado de outra empresa depois que ele foi adquirido. As pessoas que originalmente configuraram as coisas não estão mais disponíveis.

Versão do servidor: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 15 de junho de 2019 07:56:34 Copyright (c) Microsoft Corporation Enterprise Edition: licenciamento baseado em núcleo (64 bits) no Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Uma das primeiras coisas que me chamaram a atenção quando olhei para o servidor foi um trabalho do SQL Server Agent que faz um backup do banco de dados principal. O banco de dados está no modo de recuperação Simples. O trabalho tem três etapas:

-- step 1
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

-- step 2
declare @disk varchar(100) = replace(N'G:\DB_Backup\DWH_Main_&d&.bak','&d&', convert(varchar, getdate(), 112))
--print @disk
;
BACKUP DATABASE [DWH_Main] 
    TO  DISK = @disk
    WITH  RETAINDAYS = 2
        , NOFORMAT
        , NOINIT
        ,  NAME = N'DWH_Main-Full Database Backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        ,  STATS = 10
GO

-- step 3
WAITFOR DELAY '00:05'
;
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

O tamanho do arquivo de dados é de cerca de 400 GB, o tamanho do arquivo de log agora é de cerca de 50 GB.

CREATE DATABASE [DWH_Main]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DWH_Main', FILENAME = N'E:\Data\DWH_Main.mdf' , SIZE = 411114496KB , 
  MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DWH_Main_log', FILENAME = N'F:\Log\DWH_Main_log.ldf' , SIZE = 53941184KB , 
  MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Eu posso ver MUITOS eventos de crescimento automático do arquivo de log, o que não é surpresa. DBCC LOGINFOretorna 858 linhas, ou seja, 858 fragmentos, quase cada um com 64 MB.

O que devo dizer ao meu chefe sobre esta situação? Não entendo por que alguém gostaria de reduzir o arquivo de log de transações todos os dias após um backup completo, especialmente para um banco de dados em um modo de recuperação simples.

Minha primeira reação foi simplesmente remover os DBCC SHRINKFILEcomandos do script e reconfigurar o tamanho inicial do arquivo de log de transações. Aumente manualmente em pedaços de 8 GB para ~ 64 GB para reduzir a fragmentação, mas talvez esteja faltando alguma coisa?

Eu tenho dois SQL Servers em duas máquinas diferentes em algum lugar na nuvem (talvez Azure).

Um deles é o Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) 8 de julho de 2017 16:43:40 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows NT 6.3 (Build 9600: ) (Hipervisor)

Neste servidor existe um link para o segundo servidor.

O segundo servidor ( aae-sqldw-02) é o Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 15 de junho de 2019 07:56:34 Copyright (c) Microsoft Corporation Enterprise Edition: licenciamento baseado em núcleo (64 -bit) no Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

No primeiro servidor estamos executando uma consulta "simples":

TRUNCATE TABLE [dbo].[LocalTable]

INSERT INTO [dbo].[LocalTable]
    ([DatabaseName]
    ,[SalesContractNumber]
    ,... 60 columns
    )
SELECT
    convert(varchar(128), DatabaseName) collate Latin1_General_CI_AS
    ,convert(varchar(60), SalesContractNumber) collate Latin1_General_CI_AS
    ,... 60 columns
FROM [aae-sqldw-02].[Fin_DWH].[dbo].[RemoteView]
WHERE DatabaseName = 'somename'

Esta consulta às vezes falha com um erro:

Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server "aae-sqldw-02".

ou com este erro:

Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "aae-sqldw-02". .

Eu sei que o segundo servidor está sob uma carga muito pesada a maior parte do dia. Ele literalmente maximiza sua E/S de disco (255 MB/s). A solução de força bruta é simplesmente movê-lo para um plano mais caro com mais IO. Essa mudança precisa de muita burocracia e levará muito tempo. Além disso, não há garantia de que o próximo nível será suficiente.

Existe alguma coisa que eu possa fazer com os recursos fornecidos agora?

Quando uma consulta é concluída com êxito, pode levar de 1 a 3 horas. A consulta retorna cerca de 3 milhões de linhas, cerca de 4 GB de dados, portanto, não muito.

Quando uma consulta falha com Cannot fetch a row, nas últimas vezes ela falhou após 9.294 segundos (2,5 horas), 12.326 segundos (3,5 horas).

Quando uma consulta falha com Cannot fetch the rowset, ela falhou após 606 segundos, 611 segundos.

Portanto, 600 segundos sugerem algum tempo limite padrão de 10 minutos (para conexão?) Nos casos em que a conexão foi bem-sucedida, ela começou a buscar os dados, mas falhou no processo. Talvez o servidor vinculado não tenha conseguido enviar a próxima linha com rapidez suficiente e algum outro tempo limite foi ativado.

Quando uma consulta foi bem-sucedida, levou 3841 segundos da última vez.

Aqui estão as configurações para o servidor vinculado:

EXEC master.dbo.sp_addlinkedserver @server = N'aae-sqldw-02', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'aae-sqldw-02',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'aae-sqldw-02', @optname=N'remote proc transaction promotion', @optvalue=N'true'

O que você acha, faria alguma diferença se eu definir explicitamente a query timeoutopção para algo como 5 horas? Pode piorar as coisas?

Obviamente, a correção adequada seria observar o que está acontecendo no servidor e otimizar as consultas para reduzir a carga geral, mas há algo que eu possa fazer em um nível de servidor / banco de dados mais alto, para que a consulta seja concluída, mesmo que leva muito tempo?

Precisamos executar essa consulta uma vez por semana e agora temos que tentar novamente várias vezes até que ela seja concluída com sucesso.

Estou usando o SQL Server 2008 Standard, que não possui um SEQUENCErecurso.

Um sistema externo lê dados de várias tabelas dedicadas do banco de dados principal. O sistema externo mantém uma cópia dos dados e verifica periodicamente se há alterações nos dados e atualiza sua cópia.

Para tornar a sincronização eficiente, desejo transferir apenas as linhas que foram atualizadas ou inseridas desde a sincronização anterior. (As linhas nunca são excluídas). Para saber quais linhas foram atualizadas ou inseridas desde a última sincronização, há uma bigintcoluna RowUpdateCounterem cada tabela.

A ideia é que sempre que uma linha for inserida ou atualizada, o número em sua RowUpdateCountercoluna mudará. Os valores que vão para a RowUpdateCountercoluna devem ser retirados de uma sequência crescente de números. Os valores na RowUpdateCountercoluna devem ser exclusivos e cada novo valor armazenado em uma tabela deve ser maior que qualquer valor anterior.

Consulte os scripts que mostram o comportamento desejado.

Esquema

CREATE TABLE [dbo].[Test](
    [ID] [int] NOT NULL,
    [Value] [varchar](50) NOT NULL,
    [RowUpdateCounter] [bigint] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [ID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RowUpdateCounter] ON [dbo].[Test]
(
    [RowUpdateCounter] ASC
)
GO

INSERIR algumas linhas

INSERT INTO [dbo].[Test]
    ([ID]
    ,[Value]
    ,[RowUpdateCounter])
VALUES
(1, 'A', ???),
(2, 'B', ???),
(3, 'C', ???),
(4, 'D', ???);

Resultado esperado

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | C     |                3 |
|  4 | D     |                4 |
+----+-------+------------------+

Os valores gerados em RowUpdateCounterpodem ser diferentes, digamos, 5, 3, 7, 9. Eles devem ser únicos e devem ser maiores que 0, pois partimos de uma tabela vazia.

INSERT e UPDATE algumas linhas

DECLARE @NewValues TABLE (ID int NOT NULL, Value varchar(50));
INSERT INTO @NewValues (ID, Value) VALUES
(3, 'E'),
(4, 'F'),
(5, 'G'),
(6, 'H');

MERGE INTO dbo.Test WITH (HOLDLOCK) AS Dst
USING
(
    SELECT ID, Value
    FROM @NewValues
)
AS Src ON Dst.ID = Src.ID
WHEN MATCHED THEN
UPDATE SET
     Dst.Value            = Src.Value
    ,Dst.RowUpdateCounter = ???
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (ID
    ,Value
    ,RowUpdateCounter)
VALUES
    (Src.ID
    ,Src.Value
    ,???)
;

Resultado esperado

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | E     |                5 |
|  4 | F     |                6 |
|  5 | G     |                7 |
|  6 | H     |                8 |
+----+-------+------------------+

• RowUpdateCounterpara linhas com ID 1,2devem permanecer como estão, porque essas linhas não foram alteradas.
• RowUpdateCounterpara linhas com ID 3,4devem mudar, porque foram atualizados.
• RowUpdateCounterpara linhas com ID 5,6devem mudar, porque foram inseridas.
• RowUpdateCounterpara todas as linhas alteradas deve ser maior que 4 (o último RowUpdateCounterda sequência).

A ordem na qual os novos valores ( 5,6,7,8) são atribuídos às linhas alteradas realmente não importa. Os novos valores podem ter lacunas, por exemplo 15,26,47,58, mas nunca devem diminuir.

Existem várias tabelas com esses contadores no banco de dados. Não importa se todos eles usam uma única sequência global para seus números, ou se cada tabela tem sua própria sequência individual.

Não quero usar uma coluna com carimbo de data e hora em vez de um contador inteiro, porque:

• O relógio no servidor pode avançar e retroceder. Principalmente quando está em uma máquina virtual.

• Os valores retornados pelas funções do sistema como SYSDATETIMEsão os mesmos para todas as linhas afetadas. O processo de sincronização deve ser capaz de ler as alterações em lotes. Por exemplo, se o tamanho do lote for de 3 linhas, após a MERGEetapa acima, o processo de sincronização lerá apenas linhas E,F,G. Quando o processo de sincronização for executado na próxima vez, ele continuará da linha H.

A maneira como estou fazendo isso agora é bastante feia.

Como não há SEQUENCEno SQL Server 2008, emulo o SEQUENCEpor uma tabela dedicada com IDENTITYconforme mostrado nesta resposta . Isso em si é muito feio e exacerbado pelo fato de que preciso gerar não um único, mas um lote de números de uma vez.

Em seguida, tenho um INSTEAD OF UPDATE, INSERTgatilho em cada tabela com os RowUpdateCounterconjuntos de números necessários e gero lá.

Nas consultas INSERT, UPDATEe MERGEdefino RowUpdateCountercomo 0, que é substituído pelos valores corretos no gatilho. As ???nas consultas acima são 0.

Funciona, mas existe uma solução mais fácil?

Considere estas duas funções:

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

Tanto quanto eu entendo, eles produzem exatamente o mesmo resultado. Em outras palavras, a ordem em que você lista as colunas na PARTITION BYcláusula não importa.

Se houver um índice, (A,B,C)eu esperava que o otimizador usasse esse índice em ambas as variantes.

Mas, surpreendentemente, o otimizador decidiu fazer uma classificação extra explícita na segunda variante.

Eu vi isso no SQL Server 2008 Standard e no SQL Server 2014 Express.

Aqui está um script completo que usei para reproduzi-lo.

Tentei no Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 de fevereiro de 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)

e Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) 27 de maio de 2016 15:33:17 Copyright (c) Microsoft Corporation Express Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pacote 1)

com o estimador de cardinalidade antigo e novo usando OPTION (QUERYTRACEON 9481)e OPTION (QUERYTRACEON 2312).

Configurar tabela, índice, dados de amostra

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

Consultas

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

Planos de execução

PARTIÇÃO POR A,B

PARTIÇÃO POR B,A

Ambos

Como você pode ver, o segundo plano tem um Sort extra. Ordena por B,A,C. O otimizador, aparentemente, não é inteligente o suficiente para perceber que PARTITION BY B,Aé o mesmo PARTITION BY A,Be reclassificar os dados.

Curiosamente, a terceira consulta contém ambas as variantes ROW_NUMBERe não há classificação extra! O plano é o mesmo da primeira consulta. (O Projeto de Sequência tem expressão extra na Lista de Saída para a coluna extra, mas não Ordenação extra). Portanto, neste caso mais complicado, o otimizador parecia ser inteligente o suficiente para perceber que PARTITION BY B,Aé o mesmo que PARTITION BY A,B.

Na primeira e terceira consultas o operador Index Scan tem a propriedade Ordered:True, na segunda consulta é False.

Ainda mais interessante, se eu reescrever a terceira consulta assim (troque duas colunas):

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

então o Sort extra aparece novamente!

Alguém poderia dar uma luz? O que está acontecendo no otimizador aqui?

Eu preciso converter dados entre dois sistemas.

O primeiro sistema armazena horários como uma lista simples de datas. Cada data incluída na agenda é uma linha. Podem existir várias lacunas na sequência de datas (fins de semana, feriados e pausas mais longas, alguns dias da semana podem ser excluídos da programação). Não pode haver nenhuma lacuna, mesmo fins de semana podem ser incluídos. O cronograma pode durar até 2 anos. Geralmente dura algumas semanas.

Aqui está um exemplo simples de uma programação que abrange duas semanas excluindo fins de semana (existem exemplos mais complicados no script abaixo):

+----+------------+------------+---------+--------+
| ID | ContractID |     dt     | dowChar | dowInt |
+----+------------+------------+---------+--------+
| 10 |          1 | 2016-05-02 | Mon     |      2 |
| 11 |          1 | 2016-05-03 | Tue     |      3 |
| 12 |          1 | 2016-05-04 | Wed     |      4 |
| 13 |          1 | 2016-05-05 | Thu     |      5 |
| 14 |          1 | 2016-05-06 | Fri     |      6 |
| 15 |          1 | 2016-05-09 | Mon     |      2 |
| 16 |          1 | 2016-05-10 | Tue     |      3 |
| 17 |          1 | 2016-05-11 | Wed     |      4 |
| 18 |          1 | 2016-05-12 | Thu     |      5 |
| 19 |          1 | 2016-05-13 | Fri     |      6 |
+----+------------+------------+---------+--------+

IDé único, mas não é necessariamente sequencial (é a chave primária). As datas são únicas dentro de cada Contrato (há um índice único em (ContractID, dt)).

O segundo sistema armazena as programações como intervalos com a lista de dias da semana que fazem parte da programação. Cada intervalo é definido por suas datas de início e término (inclusive) e uma lista de dias da semana incluídos na programação. Nesse formato, você pode definir com eficiência padrões semanais repetitivos, como de segunda a quarta, mas torna-se um problema quando um padrão é interrompido, por exemplo, em um feriado.

Veja como ficará o exemplo simples acima:

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          1 | 2016-05-02 | 2016-05-13 |       10 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+

[StartDT;EndDT]intervalos que pertençam ao mesmo Contrato não devem se sobrepor.

Preciso converter dados do primeiro sistema para o formato usado pelo segundo sistema. No momento, estou resolvendo isso no lado do cliente em C # para o único contrato fornecido, mas gostaria de fazer isso em T-SQL no lado do servidor para processamento em massa e exportação/importação entre servidores. Provavelmente, isso poderia ser feito usando CLR UDF, mas neste estágio não posso usar SQLCLR.

O desafio aqui é tornar a lista de intervalos o mais curta e amigável possível.

Por exemplo, este cronograma:

+-----+------------+------------+---------+--------+
| ID  | ContractID |     dt     | dowChar | dowInt |
+-----+------------+------------+---------+--------+
| 223 |          2 | 2016-05-05 | Thu     |      5 |
| 224 |          2 | 2016-05-06 | Fri     |      6 |
| 225 |          2 | 2016-05-09 | Mon     |      2 |
| 226 |          2 | 2016-05-10 | Tue     |      3 |
| 227 |          2 | 2016-05-11 | Wed     |      4 |
| 228 |          2 | 2016-05-12 | Thu     |      5 |
| 229 |          2 | 2016-05-13 | Fri     |      6 |
| 230 |          2 | 2016-05-16 | Mon     |      2 |
| 231 |          2 | 2016-05-17 | Tue     |      3 |
+-----+------------+------------+---------+--------+

deve se tornar isso:

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          2 | 2016-05-05 | 2016-05-17 |        9 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+

,isso não:

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          2 | 2016-05-05 | 2016-05-06 |        2 | Thu,Fri,             |
|          2 | 2016-05-09 | 2016-05-13 |        5 | Mon,Tue,Wed,Thu,Fri, |
|          2 | 2016-05-16 | 2016-05-17 |        2 | Mon,Tue,             |
+------------+------------+------------+----------+----------------------+

Eu tentei aplicar uma gaps-and-islandsabordagem para este problema. Eu tentei fazer isso em duas passagens. Na primeira passagem encontro ilhas de dias consecutivos simples, ou seja, o fim da ilha é qualquer intervalo na sequência de dias, seja final de semana, feriado ou qualquer outra coisa. Para cada ilha encontrada, construo uma lista separada por vírgulas de arquivos WeekDays. Na segunda passagem, o grupo I encontrou ilhas mais adiante, observando a lacuna na sequência dos números da semana ou uma mudança no WeekDays.

Com essa abordagem, cada semana parcial acaba sendo um intervalo extra, conforme mostrado acima, porque, embora os números das semanas sejam consecutivos, a WeekDaysmudança. Além disso, pode haver intervalos regulares dentro de uma semana (veja ContractID=3nos dados de amostra, que contém dados apenas para Mon,Wed,Fri,) e essa abordagem geraria intervalos separados para cada dia nessa programação. Pelo lado bom, gera um intervalo se o cronograma não tiver nenhuma lacuna (veja ContractID=7nos dados de amostra que incluem finais de semana) e, nesse caso, não importa se a semana de início ou fim é parcial.

Por favor, veja outros exemplos no script abaixo para ter uma ideia melhor do que estou procurando. Você pode ver que muitas vezes os fins de semana são excluídos, mas quaisquer outros dias da semana também podem ser excluídos. No exemplo 3 apenas Mon, Wede Frifazem parte do cronograma. Além disso, finais de semana podem ser incluídos, como no exemplo 7. A solução deve tratar todos os dias da semana igualmente. Qualquer dia da semana pode ser incluído ou excluído da programação.

Para verificar se a lista de intervalos gerada descreve o cronograma fornecido corretamente, você pode usar o seguinte pseudocódigo:

• percorrer todos os intervalos
• para cada intervalo de loop por todas as datas do calendário entre as datas de início e término (inclusive).
• para cada data, verifique se o dia da semana está listado no arquivo WeekDays. Se sim, então esta data está incluída no cronograma.

Esperançosamente, isso esclarece em quais casos um novo intervalo deve ser criado. Nos exemplos 4 e 5 uma segunda-feira ( 2016-05-09) é retirada do meio do horário e tal horário não pode ser representado por um único intervalo. No exemplo 6, há um longo intervalo no cronograma, portanto, são necessários dois intervalos.

Os intervalos representam padrões semanais na programação e quando um padrão é interrompido/alterado, um novo intervalo deve ser adicionado. No exemplo 11, as três primeiras semanas têm um padrão Tue, então esse padrão muda para Thu. Como resultado, precisamos de dois intervalos para descrever tal cronograma.

Estou usando o SQL Server 2008 no momento, então a solução deve funcionar nesta versão. Se uma solução para SQL Server 2008 puder ser simplificada/melhorada usando recursos de versões posteriores, isso é um bônus, mostre-o também.

Eu tenho uma Calendartabela (lista de datas) e uma Numberstabela (lista de números inteiros começando em 1), então não há problema em usá-los, se necessário. Também não há problema em criar tabelas temporárias e ter várias consultas que processam dados em vários estágios. O número de estágios em um algoritmo deve ser corrigido, porém, cursores e WHILEloops explícitos não são aceitáveis.

Script para dados de amostra e resultados esperados

-- @Src is sample data
-- @Dst is expected result

DECLARE @Src TABLE (ID int PRIMARY KEY, ContractID int, dt date, dowChar char(3), dowInt int);
INSERT INTO @Src (ID, ContractID, dt, dowChar, dowInt) VALUES

-- simple two weeks (without weekend)
(110, 1, '2016-05-02', 'Mon', 2),
(111, 1, '2016-05-03', 'Tue', 3),
(112, 1, '2016-05-04', 'Wed', 4),
(113, 1, '2016-05-05', 'Thu', 5),
(114, 1, '2016-05-06', 'Fri', 6),
(115, 1, '2016-05-09', 'Mon', 2),
(116, 1, '2016-05-10', 'Tue', 3),
(117, 1, '2016-05-11', 'Wed', 4),
(118, 1, '2016-05-12', 'Thu', 5),
(119, 1, '2016-05-13', 'Fri', 6),

-- a partial end of the week, the whole week, partial start of the week (without weekends)
(223, 2, '2016-05-05', 'Thu', 5),
(224, 2, '2016-05-06', 'Fri', 6),
(225, 2, '2016-05-09', 'Mon', 2),
(226, 2, '2016-05-10', 'Tue', 3),
(227, 2, '2016-05-11', 'Wed', 4),
(228, 2, '2016-05-12', 'Thu', 5),
(229, 2, '2016-05-13', 'Fri', 6),
(230, 2, '2016-05-16', 'Mon', 2),
(231, 2, '2016-05-17', 'Tue', 3),

-- only Mon, Wed, Fri are included across two weeks plus partial third week
(310, 3, '2016-05-02', 'Mon', 2),
(311, 3, '2016-05-04', 'Wed', 4),
(314, 3, '2016-05-06', 'Fri', 6),
(315, 3, '2016-05-09', 'Mon', 2),
(317, 3, '2016-05-11', 'Wed', 4),
(319, 3, '2016-05-13', 'Fri', 6),
(330, 3, '2016-05-16', 'Mon', 2),

-- a whole week (without weekend), in the second week Mon is not included
(410, 4, '2016-05-02', 'Mon', 2),
(411, 4, '2016-05-03', 'Tue', 3),
(412, 4, '2016-05-04', 'Wed', 4),
(413, 4, '2016-05-05', 'Thu', 5),
(414, 4, '2016-05-06', 'Fri', 6),
(416, 4, '2016-05-10', 'Tue', 3),
(417, 4, '2016-05-11', 'Wed', 4),
(418, 4, '2016-05-12', 'Thu', 5),
(419, 4, '2016-05-13', 'Fri', 6),

-- three weeks, but without Mon in the second week (no weekends)
(510, 5, '2016-05-02', 'Mon', 2),
(511, 5, '2016-05-03', 'Tue', 3),
(512, 5, '2016-05-04', 'Wed', 4),
(513, 5, '2016-05-05', 'Thu', 5),
(514, 5, '2016-05-06', 'Fri', 6),
(516, 5, '2016-05-10', 'Tue', 3),
(517, 5, '2016-05-11', 'Wed', 4),
(518, 5, '2016-05-12', 'Thu', 5),
(519, 5, '2016-05-13', 'Fri', 6),
(520, 5, '2016-05-16', 'Mon', 2),
(521, 5, '2016-05-17', 'Tue', 3),
(522, 5, '2016-05-18', 'Wed', 4),
(523, 5, '2016-05-19', 'Thu', 5),
(524, 5, '2016-05-20', 'Fri', 6),

-- long gap between two intervals
(623, 6, '2016-05-05', 'Thu', 5),
(624, 6, '2016-05-06', 'Fri', 6),
(625, 6, '2016-05-09', 'Mon', 2),
(626, 6, '2016-05-10', 'Tue', 3),
(627, 6, '2016-05-11', 'Wed', 4),
(628, 6, '2016-05-12', 'Thu', 5),
(629, 6, '2016-05-13', 'Fri', 6),
(630, 6, '2016-05-16', 'Mon', 2),
(631, 6, '2016-05-17', 'Tue', 3),
(645, 6, '2016-06-06', 'Mon', 2),
(646, 6, '2016-06-07', 'Tue', 3),
(647, 6, '2016-06-08', 'Wed', 4),
(648, 6, '2016-06-09', 'Thu', 5),
(649, 6, '2016-06-10', 'Fri', 6),
(655, 6, '2016-06-13', 'Mon', 2),
(656, 6, '2016-06-14', 'Tue', 3),
(657, 6, '2016-06-15', 'Wed', 4),
(658, 6, '2016-06-16', 'Thu', 5),
(659, 6, '2016-06-17', 'Fri', 6),

-- two weeks, no gaps between days at all, even weekends are included
(710, 7, '2016-05-02', 'Mon', 2),
(711, 7, '2016-05-03', 'Tue', 3),
(712, 7, '2016-05-04', 'Wed', 4),
(713, 7, '2016-05-05', 'Thu', 5),
(714, 7, '2016-05-06', 'Fri', 6),
(715, 7, '2016-05-07', 'Sat', 7),
(716, 7, '2016-05-08', 'Sun', 1),
(725, 7, '2016-05-09', 'Mon', 2),
(726, 7, '2016-05-10', 'Tue', 3),
(727, 7, '2016-05-11', 'Wed', 4),
(728, 7, '2016-05-12', 'Thu', 5),
(729, 7, '2016-05-13', 'Fri', 6),

-- no gaps between days at all, even weekends are included, with partial weeks
(805, 8, '2016-04-30', 'Sat', 7),
(806, 8, '2016-05-01', 'Sun', 1),
(810, 8, '2016-05-02', 'Mon', 2),
(811, 8, '2016-05-03', 'Tue', 3),
(812, 8, '2016-05-04', 'Wed', 4),
(813, 8, '2016-05-05', 'Thu', 5),
(814, 8, '2016-05-06', 'Fri', 6),
(815, 8, '2016-05-07', 'Sat', 7),
(816, 8, '2016-05-08', 'Sun', 1),
(825, 8, '2016-05-09', 'Mon', 2),
(826, 8, '2016-05-10', 'Tue', 3),
(827, 8, '2016-05-11', 'Wed', 4),
(828, 8, '2016-05-12', 'Thu', 5),
(829, 8, '2016-05-13', 'Fri', 6),
(830, 8, '2016-05-14', 'Sat', 7),

-- only Mon-Wed included, two weeks plus partial third week
(910, 9, '2016-05-02', 'Mon', 2),
(911, 9, '2016-05-03', 'Tue', 3),
(912, 9, '2016-05-04', 'Wed', 4),
(915, 9, '2016-05-09', 'Mon', 2),
(916, 9, '2016-05-10', 'Tue', 3),
(917, 9, '2016-05-11', 'Wed', 4),
(930, 9, '2016-05-16', 'Mon', 2),
(931, 9, '2016-05-17', 'Tue', 3),

-- only Thu-Sun included, three weeks
(1013,10,'2016-05-05', 'Thu', 5),
(1014,10,'2016-05-06', 'Fri', 6),
(1015,10,'2016-05-07', 'Sat', 7),
(1016,10,'2016-05-08', 'Sun', 1),
(1018,10,'2016-05-12', 'Thu', 5),
(1019,10,'2016-05-13', 'Fri', 6),
(1020,10,'2016-05-14', 'Sat', 7),
(1021,10,'2016-05-15', 'Sun', 1),
(1023,10,'2016-05-19', 'Thu', 5),
(1024,10,'2016-05-20', 'Fri', 6),
(1025,10,'2016-05-21', 'Sat', 7),
(1026,10,'2016-05-22', 'Sun', 1),

-- only Tue for first three weeks, then only Thu for the next three weeks
(1111,11,'2016-05-03', 'Tue', 3),
(1116,11,'2016-05-10', 'Tue', 3),
(1131,11,'2016-05-17', 'Tue', 3),
(1123,11,'2016-05-19', 'Thu', 5),
(1124,11,'2016-05-26', 'Thu', 5),
(1125,11,'2016-06-02', 'Thu', 5),

-- one week, then one week gap, then one week
(1210,12,'2016-05-02', 'Mon', 2),
(1211,12,'2016-05-03', 'Tue', 3),
(1212,12,'2016-05-04', 'Wed', 4),
(1213,12,'2016-05-05', 'Thu', 5),
(1214,12,'2016-05-06', 'Fri', 6),
(1215,12,'2016-05-16', 'Mon', 2),
(1216,12,'2016-05-17', 'Tue', 3),
(1217,12,'2016-05-18', 'Wed', 4),
(1218,12,'2016-05-19', 'Thu', 5),
(1219,12,'2016-05-20', 'Fri', 6);

SELECT ID, ContractID, dt, dowChar, dowInt
FROM @Src
ORDER BY ContractID, dt;


DECLARE @Dst TABLE (ContractID int, StartDT date, EndDT date, DayCount int, WeekDays varchar(255));
INSERT INTO @Dst (ContractID, StartDT, EndDT, DayCount, WeekDays) VALUES
(1, '2016-05-02', '2016-05-13', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(2, '2016-05-05', '2016-05-17',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(3, '2016-05-02', '2016-05-16',  7, 'Mon,Wed,Fri,'),
(4, '2016-05-02', '2016-05-06',  5, 'Mon,Tue,Wed,Thu,Fri,'),
(4, '2016-05-10', '2016-05-13',  4, 'Tue,Wed,Thu,Fri,'),
(5, '2016-05-02', '2016-05-06',  5, 'Mon,Tue,Wed,Thu,Fri,'),
(5, '2016-05-10', '2016-05-20',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-05-05', '2016-05-17',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-06-06', '2016-06-17', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(7, '2016-05-02', '2016-05-13', 12, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(8, '2016-04-30', '2016-05-14', 15, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(9, '2016-05-02', '2016-05-17',  8, 'Mon,Tue,Wed,'),
(10,'2016-05-05', '2016-05-22', 12, 'Sun,Thu,Fri,Sat,'),
(11,'2016-05-03', '2016-05-17',  3, 'Tue,'),
(11,'2016-05-19', '2016-06-02',  3, 'Thu,'),
(12,'2016-05-02', '2016-05-06',  5, 'Mon,Tue,Wed,Thu,Fri,'),
(12,'2016-05-16', '2016-05-20',  5, 'Mon,Tue,Wed,Thu,Fri,');

SELECT ContractID, StartDT, EndDT, DayCount, WeekDays
FROM @Dst
ORDER BY ContractID, StartDT;

Comparação de respostas

A tabela real @Srcpossui 403,555linhas 15,857com ContractIDs. Todas as respostas produzem resultados corretos (pelo menos para meus dados) e todas elas são razoavelmente rápidas, mas diferem em otimização. Quanto menos intervalos gerados, melhor. Incluí tempos de execução apenas por curiosidade. O foco principal é o resultado correto e ideal, não a velocidade (a menos que demore muito - interrompi a consulta não recursiva de Ziggy Crueltyfree Zeitgeister após 10 minutos).

+--------------------------------------------------------+-----------+---------+
|                         Answer                         | Intervals | Seconds |
+--------------------------------------------------------+-----------+---------+
| Ziggy Crueltyfree Zeitgeister                          |     25751 |    7.88 |
| While loop                                             |           |         |
|                                                        |           |         |
| Ziggy Crueltyfree Zeitgeister                          |     25751 |    8.27 |
| Recursive                                              |           |         |
|                                                        |           |         |
| Michael Green                                          |     25751 |   22.63 |
| Recursive                                              |           |         |
|                                                        |           |         |
| Geoff Patterson                                        |     26670 |    4.79 |
| Weekly gaps-and-islands with merging of partial weeks  |           |         |
|                                                        |           |         |
| Vladimir Baranov                                       |     34560 |    4.03 |
| Daily, then weekly gaps-and-islands                    |           |         |
|                                                        |           |         |
| Mikael Eriksson                                        |     35840 |    0.65 |
| Weekly gaps-and-islands                                |           |         |
+--------------------------------------------------------+-----------+---------+
| Vladimir Baranov                                       |     25751 |  121.51 |
| Cursor                                                 |           |         |
+--------------------------------------------------------+-----------+---------+

Sou um DBA acidental em uma pequena empresa que não possui um DBA dedicado.

Preciso configurar um login/usuário somente leitura para o banco de dados do SQL Server 2008 usando a autenticação do SQL Server que pode ser usada por usuários avançados para executar algumas consultas/relatórios ad hoc.

Encontrei muitas postagens e artigos descrevendo a configuração de um usuário somente leitura, por exemplo:

Como conceder aos usuários acesso somente leitura a todos os bancos de dados

https://www.itsupportguides.com/server-side-tips/sql-management-studio-how-to-create-read-only-users/

http://www.joellipman.com/articles/microsoft/sql-server/create-read-only-database-user-in-sql-server.html

Tentei seguir esses guias e me certificar de que o login em questão é um membro apenas de db_datareaderfunções public, mas ainda posso fazer alterações nos dados quando estou logado como este usuário "somente leitura".

Por exemplo, posso executar esta consulta no SSMS

UPDATE [dbo].[SoftwareRunLogs]
SET [RunDateTime] = '2010-10-29 13:31:06.133'
WHERE ID = 1

e atualiza a linha em uma tabela:

(1 row(s) affected)

Espero que essa consulta falhe.

Quando tento executar uma CREATE TABLEinstrução, ela falha com a mensagem CREATE TABLE permission denied in database ...que eu esperava UPDATEe INSERTas DELETEinstruções falham de maneira semelhante, mas isso não acontece.

Talvez eu tenha dado permissões de gravação inadvertidamente a esse usuário, mas não sei como verificar e onde procurar. Devo ter perdido algo trivial.

Alguma ideia?

Há um login de servidor chamado ReadOnlyUsere aqui estão suas propriedades:

No banco de dados existe um usuário chamado ReadOnlyUsere aqui estão suas propriedades:

Eu tenho lido MSDN sobre TRY...CATCHe XACT_STATE.

Tem o seguinte exemplo que usa XACT_STATEno CATCHbloco de uma TRY…CATCHconstrução para determinar se deve confirmar ou reverter uma transação:

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should 
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' + 
              ' Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

O que não entendo é: por que devo me importar e verificar o que XACT_STATEretorna?

Observe que o sinalizador XACT_ABORTestá definido como ONno exemplo.

Se houver um erro grave o suficiente dentro do TRYbloco, o controle passará para CATCH. Então, se estou dentro do CATCH, sei que aquela transação teve um problema e realmente a única coisa sensata a fazer nesse caso é revertê-la, não é?

Mas, este exemplo do MSDN implica que pode haver casos em que o controle é passado CATCHe ainda faz sentido confirmar a transação. Alguém poderia dar algum exemplo prático de quando isso pode acontecer, quando faz sentido?

Não vejo em que casos o controle pode ser passado para dentro CATCHcom uma transação que pode ser confirmada quando XACT_ABORTestá definida comoON .

O artigo do MSDN sobre SET XACT_ABORTtem um exemplo quando algumas instruções dentro de uma transação são executadas com êxito e algumas falham quando XACT_ABORTé definido como OFF, eu entendo isso. Mas, SET XACT_ABORT ONcomo pode acontecer que XACT_STATE()retorne 1 dentro do CATCHbloco?

Inicialmente, eu teria escrito este código assim:

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Some severe problem with the transaction
    PRINT 'Rolling back transaction.';
    ROLLBACK TRANSACTION;
END CATCH;
GO

Levando em consideração uma resposta de Max Vernon, eu escreveria o código assim. Ele mostrou que faz sentido verificar se há uma transação ativa antes de tentar ROLLBACK. Ainda assim, com SET XACT_ABORT ONo CATCHbloco pode haver uma transação condenada ou nenhuma transação. Portanto, em qualquer caso, não há nada a fazer COMMIT. Estou errado?

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Some severe problem with the transaction
    IF (XACT_STATE()) <> 0
    BEGIN
        -- There is still an active transaction that should be rolled back
        PRINT 'Rolling back transaction.';
        ROLLBACK TRANSACTION;
    END;

END CATCH;
GO

A nova versão do SQL Server 2016 suportará as chamadas Tabelas Temporais .

Não consigo encontrar nenhuma documentação que diga se esse recurso seria somente Enterprise ou também disponível na edição Standard.

Por um lado, as Considerações e Limitações da Tabela Temporal sugerem que será somente Enterprise:

• Por padrão, a tabela de histórico é PAGE compactada.
• Uma estratégia de indexação ideal incluirá um índice columnstore clusterizado [...] A tabela de histórico padrão tem um índice rowstore clusterizado

Até onde eu sei, a compactação de dados é um recurso exclusivo do Enterprise, mas tecnicamente a edição Standard pode criar uma tabela de histórico não compactada.

O índice Columnstore é um recurso somente para empresas, mas, por padrão, ele criará um índice rowstore, então ainda há esperança.

Por outro lado, a documentação para sys.dm_db_persisted_sku_features é a mesma para 2014 e 2016 e a lista de recursos Enterprise não possui Tabelas Temporais (talvez a documentação ainda não esteja atualizada?).

• Alguém sabe, talvez não oficialmente, qual é a variante provável?
• Se alguém tiver o RC 2016 mais recente instalado, poderia nos dizer o que sys.dm_db_persisted_sku_features retorna se o banco de dados tiver tabelas temporais?

Se houver uma boa chance de que esse recurso seja incluído na Standard Edition, posso esperar meio ano até o lançamento de 2016 antes de migrar meu banco de dados de 2008 para a versão mais recente. Se não houver esperança, eu o migraria para 2014. Se eu migrar para 2014, provavelmente permanecerá nesta versão pelos próximos 10 anos.

Sobre colocar esta pergunta em espera:

Não quero uma resposta "baseada em opinião" para essa pergunta, gostaria de saber os fatos. Eu esperava que esse tipo de informação fosse publicada em algum lugar e simplesmente não sabia onde procurar. Por exemplo, até hoje eu não sabia sobre sys.dm_db_persisted_sku_features .

As perguntas podem ser ajustadas para:

• Como e quando as decisões de SKU são tomadas?
• Como alguém saberia que a decisão foi tomada?
• Se não para esta versão, como foi feito para as versões anteriores?
• Esse tipo de informação é publicada quando a versão RTM é lançada ou antes?

Se um fato concreto não for conhecido (a decisão ainda não foi tomada e nem mesmo os desenvolvedores do SQL Server sabem), um palpite também seria ótimo, por exemplo, com base em como essa decisão foi tomada em versões anteriores.

Estive lendo uma série de posts de Paul White sobre SQL Server Isolation Levels e me deparei com uma frase :

Para enfatizar o ponto, as pseudo-restrições escritas em T-SQL devem ser executadas corretamente, independentemente das modificações simultâneas que possam estar ocorrendo. Um desenvolvedor de aplicativo pode proteger uma operação confidencial como essa com uma instrução de bloqueio. A coisa mais próxima que os programadores T-SQL têm dessa facilidade para procedimento armazenado em risco e código de gatilho é o sp_getapplockprocedimento armazenado do sistema comparativamente raramente usado. Isso não quer dizer que seja a única opção, ou mesmo preferida, apenas que existe e pode ser a escolha certa em algumas circunstâncias.

Estou usando sp_getapplocke isso me fez pensar se estou usando corretamente ou se existe uma maneira melhor de obter o efeito desejado.

Eu tenho um aplicativo C++ que processa os chamados "servidores de construção" em um loop 24 horas por dia, 7 dias por semana. Existe uma tabela com a lista desses Servidores do Edifício (cerca de 200 linhas). Novas linhas podem ser adicionadas a qualquer momento, mas isso não acontece com frequência. As linhas nunca são excluídas, mas podem ser marcadas como inativas. O processamento de um servidor pode levar de alguns segundos a dezenas de minutos, cada servidor é diferente, alguns são "pequenos", alguns são "grandes". Depois que um servidor é processado, o aplicativo precisa esperar pelo menos 20 minutos antes de processá-lo novamente (os servidores não devem ser pesquisados ​​com muita frequência). O aplicativo inicia 10 threads que executam o processamento em paralelo, mas devo garantir que dois threads não tentem processar o mesmo servidor ao mesmo tempo. Dois servidores diferentes podem e devem ser processados ​​simultaneamente, mas cada servidor pode ser processado não mais do que uma vez a cada 20 minutos.

Aqui está a definição de uma tabela:

CREATE TABLE [dbo].[PortalBuildingServers](
    [InternalIP] [varchar](64) NOT NULL,
    [LastCheckStarted] [datetime] NOT NULL,
    [LastCheckCompleted] [datetime] NOT NULL,
    [IsActiveAndNotDisabled] [bit] NOT NULL,
    [MaxBSMonitoringEventLogItemID] [bigint] NOT NULL,
CONSTRAINT [PK_PortalBuildingServers] PRIMARY KEY CLUSTERED 
(
    [InternalIP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_LastCheckCompleted] ON [dbo].[PortalBuildingServers]
(
    [LastCheckCompleted] ASC
)
INCLUDE 
(
    [LastCheckStarted],
    [IsActiveAndNotDisabled],
    [MaxBSMonitoringEventLogItemID]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

O loop principal de um thread de trabalho em um aplicativo se parece com isso:

for(;;)
{
    // Choose building server for checking
    std::vector<SBuildingServer> vecBS = GetNextBSToCheck();
    if (vecBS.size() == 1)
    {
        // do the check and don't go to sleep afterwards
        SBuildingServer & bs = vecBS[0];
        DoCheck(bs);
        SetCheckComplete(bs);
    }
    else
    {
        // Sleep for a while
        ...
    }
}

Duas funções aqui GetNextBSToChecke SetCheckCompleteestão chamando procedimentos armazenados correspondentes.

GetNextBSToCheckretorna 0 ou 1 linha com detalhes do servidor que deve ser processado a seguir. É um servidor que não é processado há muito tempo. Se este servidor "mais antigo" tiver sido processado há menos de 20 minutos, nenhuma linha será retornada e o encadeamento aguardará um minuto.

SetCheckCompletedefine a hora em que o processamento foi concluído, tornando possível escolher este servidor para processamento novamente após 20 minutos.

Finalmente, o código de stored procedures:

GetNextToCheck:

CREATE PROCEDURE [dbo].[GetNextToCheck]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @VarInternalIP varchar(64) = NULL;
        DECLARE @VarMaxBSMonitoringEventLogItemID bigint = NULL;

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'PortalBSChecking_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock
            -- Find BS that wasn't checked for the longest period
            SELECT TOP 1
                @VarInternalIP = InternalIP
                ,@VarMaxBSMonitoringEventLogItemID = MaxBSMonitoringEventLogItemID
            FROM
                dbo.PortalBuildingServers
            WHERE
                LastCheckStarted <= LastCheckCompleted
                -- this BS is not being checked right now
                AND LastCheckCompleted < DATEADD(minute, -20, GETDATE())
                -- last check was done more than 20 minutes ago
                AND IsActiveAndNotDisabled = 1
            ORDER BY LastCheckCompleted
            ;

            -- Start checking the found BS
            UPDATE dbo.PortalBuildingServers
            SET LastCheckStarted = GETDATE()
            WHERE InternalIP = @VarInternalIP;
            -- There is no need to explicitly verify if we found anything.
            -- If @VarInternalIP is null, no rows will be updated
        END;

        -- Return found BS, 
        -- or no rows if nothing was found, or failed to acquire the lock
        SELECT
            @VarInternalIP AS InternalIP
            ,@VarMaxBSMonitoringEventLogItemID AS MaxBSMonitoringEventLogItemID
        WHERE
            @VarInternalIP IS NOT NULL
            AND @VarMaxBSMonitoringEventLogItemID IS NOT NULL
        ;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH;

END

SetCheckComplete:

CREATE PROCEDURE [dbo].[SetCheckComplete]
    @ParamInternalIP varchar(64)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;
    BEGIN TRY

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'PortalBSChecking_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock
            -- Completed checking the given BS
            UPDATE dbo.PortalBuildingServers
            SET LastCheckCompleted = GETDATE()
            WHERE InternalIP = @ParamInternalIP;
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH;

END

Como você pode ver, eu uso sp_getapplockpara garantir que apenas uma instância de ambos os procedimentos armazenados esteja em execução a qualquer momento. Acho que preciso usar sp_getapplocknas duas procedures, pois a consulta que escolhe o servidor "mais antigo" usa o LastCheckCompletedhorário, que é atualizado por SetCheckComplete.

Acho que esse código garante que dois threads não tentem processar o mesmo servidor ao mesmo tempo, mas ficaria grato se você pudesse apontar algum problema com esse código e a abordagem geral. Então, a primeira pergunta: esta abordagem está correta?

Além disso, gostaria de saber se o mesmo efeito poderia ser alcançado sem usar sp_getapplock. A segunda pergunta: Existe uma maneira melhor?