Mitch's questions

Se eu tiver uma tabela com uma coluna contendo um documento JSON válido arbitrário, posso incorporar esse documento em uma consulta retornando JSON diferente de uma string?

Por exemplo:

CREATE TABLE #Example (Name nvarchar(50) not null, Document nvarchar(max) not null);

INSERT INTO #Example VALUES 
    ('Document 1', '{ "a": "a" }'),
    ('Document 2', '{ "b": "b" }');

SELECT *
FROM #Example
FOR JSON AUTO;

Saída real:

[
    {
        "Name":"Document 1",
        "Document":"{ \"a\": \"a\" }"
    },
    {
        "Name":"Document 2",
        "Document":"{ \"b\": \"b\" }"
    }
]

Saída desejada (observe que o valor analisado de Documentfoi incorporado):

[
    {
        "Name":"Document 1",
        "Document": { "a": "a" }
    },
    {
        "Name":"Document 2",
        "Document": { "b": "b" }
    }
]

Temos um SQL Server específico que está expirando intermitentemente ao aceitar conexões. O problema é consistente ao longo do dia, mas ocorre em uma incidência muito baixa. Como posso continuar a solucionar problemas?

Tempo limite de conexão expirado. O período de tempo limite decorrido ao tentar consumir a confirmação de handshake de pré-login. Isso pode ocorrer porque o handshake de pré-login falhou ou o servidor não conseguiu responder no tempo. O tempo gasto na tentativa de conexão com este servidor foi - [Pré-Login] inicialização=0; aperto de mão=15002; (Microsoft SQL Server, Erro: -2)

Configuração do servidor:

• SQL Server 2016 SP1 CU5 Enterprise (o problema também ocorreu antes do SP1)
• Windows Server 2012 R2 no servidor e no cliente
• VMware ESXi, 6.5.0 no HP ProLiant DL360 Gen9
• VM tem 8 vCPU, 64 GiB de memória (totalmente reservada)

Script de teste (executado uma vez por segundo):

$failed = $false;
$loginDuration = (Measure-Command {
    $ncon = New-Object System.Data.SqlClient.SqlConnection `
        @( 'Data Source=1.2.3.4,16143;Database=Test;User=Test;Password=****;Pooling=false;' );
    try 
    {
        $ncon.Open();

        $cmd = New-Object System.Data.SqlClient.SqlCommand `
            @( 'SELECT @@VERSION', $ncon );
        $cmd.ExecuteNonQuery();

        $ncon.Dispose();
    }
    catch
    {
        $failed = $true;
    }
}).TotalMilliseconds;
Write-Metric -metric 'itp.dbserver.logintime' -unit 'milliseconds' `
    -value (&{if ($failed) { 120000 } else { $loginDuration }});

Observações:

• O problema começou a ocorrer após atualizações do sistema operacional, atualizações do SQL Server, movimentação de San e movimentação do Hyper-V para o VMWare
• A maioria das conexões é bem-sucedida (4 falhas em 1.440 tentativas)
• As falhas são sempre listadas com um número baixo em "[Pré-Login] inicialização=0;" e um número alto em "handshake=15002". Não recebemos erros como "Não encontrado" ou "Nenhum host é conhecido", apenas "Tempo limite de conexão"
• Nenhuma criptografia está habilitada para o ouvinte
• Pings não mostram perda durante um período prolongado (0 perdido de 96.045 enviados)
• Todos os firewalls estão desativados
• As conexões tentadas usando endereços IPv6 e IPv4 falham na mesma taxa
• CPU é baixa (<40%)
• As sessões ativas são persistentemente em torno de 400
• O driver do balão está desativado
• As conexões, uma vez feitas, são estáveis, sem erros inesperados ao executar consultas, sem desconexões estranhas.
• Vários clientes estão tendo problemas para se conectar - ODBC e ADO de vários computadores

Atualização: finalmente obtive um rastreamento do Wireshark do lado do cliente de uma conexão com falha. Nenhuma perda de pacote é aparente, o cliente recebe TCP ACKs em tempo real (<10ms). O cliente estava usando o nome DNS no momento da falha, mas a falha ocorre usando o endereço IPv4 na cadeia de conexão.

Estou correto ao pensar que o fato de eu obter ACKs TCP imediatos para os pacotes de solicitação de pré-login enviados localizaria o problema no sistema operacional ou no SQL Server?

Estou dando suporte a um aplicativo de terceiros que usa um back-end do SQL Server 2016. Não temos acesso ao código-fonte e o suporte de terceiros demora a responder. Na maioria das vezes, posso indexar e fazer mágica para sair das consultas às vezes terríveis que ele lança no banco de dados, mas houve várias ocasiões em que não há literalmente nada que eu possa fazer (dentro de restrições de tempo, dinheiro e armazenamento ). A consulta está simplesmente errada .

Existe alguma forma de substituir administrativamente as consultas apresentadas pelo aplicativo por outras? Estou imaginando algum tipo de correspondência de regex executada no lote se o nome do aplicativo corresponder.

Exemplo:

-- Submitted by application
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}'

-- What I want the query to be (to avoid spurious updates to Table)
-- (Table is large, and the application is submitting this query on every page view)
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}' and IsBilled = 1

-- Proposed regex
-- Match
update Table set IsBilled = 0 where ID = '(\{[A-F0-9\-]+\})'
-- Replace
update Table set IsBilled = 0 where ID = '$1' and IsBilled = 1

Alguns dos meus usos concebidos seriam para evitar atualizações inúteis, outros seriam para reformular cláusulas where para manter a sargabilidade. Tenho certeza de que existem outros cenários que me vêm à mente.

Imagine que eu tenha um esquema que inclua Products, Orderse OrderLineItems, Productssendo uma tabela temporal com versão do sistema.

Esquema:

CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(255) not null,
    SysStart DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEnd DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.Products_History, DATA_CONSISTENCY_CHECK = ON));
GO

CREATE TABLE dbo.Orders
(
    OrderID int not null identity primary key,
    OrderDate datetime2 (7) not null
);

CREATE TABLE dbo.OrderLineItems
(
    OrderID int not null,
    ProductID int not null,
    CONSTRAINT FK_OrderLineItems_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID),
    CONSTRAINT FK_OrderLineItems_Products FOREIGN KEY (ProductID) REFERENCES dbo.Products (ProductID),
    CONSTRAINT PK_OrderLineItems PRIMARY KEY (OrderID, ProductID)
);
GO

-- Load Sample data
insert into Products (Name) values ('a'), ('b');
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
waitfor delay '00:00:02';
update products set name= 'c' where name = 'a'
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
insert into OrderLineItems (OrderID, ProductID) values (1, 1), (1, 2), (2, 1);

Como eu escreveria uma consulta que se une Productsao OrderLineItemsuso da data de Orders?

Por exemplo, para consultar "Pedidos que contêm produtos cujos nomes foram alterados":

-- Fake syntax:
SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products as p_then
    for system time o.OrderDate
    on oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
--  for system time now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name

Eu tenho quatro tabelas idênticas em quatro servidores que preciso consultar em um quinto. O quinto servidor está vinculado aos outros quatro e possui uma visão criada para unificá-los.

-- Servers 1-4
CREATE TABLE Transactions
(
    TxID int not null primary key,
    Description nvarchar(50) not null
);

-- Server 5
CREATE VIEW vwTransactions
AS
SELECT 1 as ServerID, * FROM Server1.DB.dbo.Transactions
UNION ALL
SELECT 2 as ServerID, * FROM Server2.DB.dbo.Transactions
UNION ALL
SELECT 3 as ServerID, * FROM Server3.DB.dbo.Transactions
UNION ALL
SELECT 4 as ServerID, * FROM Server4.DB.dbo.Transactions;

Quando executo uma consulta com uma cláusula where constante na ServerIDcoluna, recebo um bom plano de consulta feliz que elimina três das partições.

SELECT * FROM vwTransactions WHERE ServerID = 1;

Mas quando uso um parâmetro, ou pior – uma junção, recebo uma varredura de todas as quatro tabelas.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID;

Eu também tentei usar funções com valor de tabela inline sem sucesso.

Como faço uma visualização dessas tabelas que podem ser consultadas facilmente?

Um dos meus desenvolvedores está argumentando que COALESCE(column, default value) = default valueagora é sargável. Isso está certo?

Eu executei o seguinte teste e acho que isso implica que COALESCEnão é sargável.

USE tempdb;

SELECT @@VERSION;
-- Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64)   Oct 31 2016 18:27:32   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor) 

CREATE TABLE Test 
(
    ID int primary key clustered, 
    Mod6 int null, 
    INDEX IX_Mod6 NONCLUSTERED (Mod6)
);

INSERT INTO Test (ID, Mod6)
SELECT object_id as ID, case when name like '%k%' then null else object_id % 6 end as Mod6
FROM sys.objects;

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
where Mod6 is null or Mod6 = 0;
-- Plan shows expected seek

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;
-- Error:
-- Msg 8622, Level 16, State 1, Line 20
-- Query processor could not produce a query plan because of the hints 
-- defined in this query. Resubmit the query without specifying any hints 
-- and without using SET FORCEPLAN.

Supondo por um momento que as referências FK não estejam disponíveis, quais dicas de bloqueio devem ser colocadas nas pré-condições do exemplo a seguir para evitar impasses e dados inconsistentes?

(Este é um exemplo artificial, é claro, para ilustrar um cenário em que uma exclusão/atualização só pode ser executada dependendo do estado de alguma outra tabela)

-- Schema
CREATE TABLE EntityTypes (
    EntityTypeID int NOT NULL, 
    Name nvarchar(50) NOT NULL, 
    CONSTRAINT PK_EntityTypes PRIMARY KEY CLUSTERED (EntityTypeID)
);

CREATE TABLE Entities (
    EntityID int IDENTITY(1,1) NOT NULL,
    EntityTypeID int NOT NULL,
    EntityName nvarchar(50) NOT NULL,
    CONSTRAINT PK_Entities PRIMARY KEY CLUSTERED (EntityID)
);

-- Test data
INSERT INTO EntityTypes (EntityTypeID, Name) VALUES (1, 'Test 1');

-- Updater 1
SET XACT_ABORT ON;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN 
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

-- Updater 2
INSERT INTO Entities (EntityTypeID, EntityName) VALUES (1, 'Test Entity')

Ambiente: SQL Server 2012

Eu tenho um banco de dados acessado por cerca de 50 clientes via TDS sobre TCP, que parece não estar liberando espaço de log. O número de processos fica em torno dos 50 esperados, sendo alguns deles bastante longos (>120 dias).

O banco de dados agora tem 40 gb de espaço de log (tem apenas 14 gb de dados), 39 gb livres. Devido a limitações de espaço na unidade, gostaria de reduzir para algo mais razoável (10 GB). Quando eu executo DBCC SHRINKFILE('db_log', 10000), retorna um erro que o final do log está em uso.

Para liberar o acesso ao final do log, tentei colocar o banco de dados em modo monousuário com o seguinte:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

mas o script está retornando a seguinte mensagem repetida centenas de vezes:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

O que me leva a acreditar que em algum lugar, estou deixando algumas transações não confirmadas. Não tenho conhecimento de nenhum processo que abrisse intencionalmente tantas transações de uma só vez, então acho que elas devem se acumular ao longo do tempo, nunca sendo fechadas.

Pergunta: Como localizo o processo ou script incorreto ou por que o log não está sendo liberado?

sys.dm_tran_active_transactionsestá mostrando um número razoável de 18 transações com propósitos compreensíveis. sp_whomostra apenas os processos que conheço.

Versão do SQL Server:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Versão do servidor:

Windows Server 2008 R2 x64 - Datacenter 4 vCPUs, 16 GB de memória, disco de passagem para dados e log, disco do sistema operacional é VHD

no Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter) Dual Intel X5650 (6 núcleos, 12 threads a 2,67 GHz) 72 GB de memória

O hipervisor possui apenas três VMs e não apresenta alto uso de recursos. A VM do SQL Server mostra cerca de 40% da CPU sob carga e 99% de acessos ao cache.