Mitch's questions

如果我有一个包含任意有效 JSON 文档的列的表，我可以将该文档嵌入到返回 JSON 的查询中，而不是作为字符串？

例如：

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;

实际输出：

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

期望的输出（注意解析后的值Document已经嵌入）：

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

我们有一个特定的 SQL Server，它在接受连接时会间歇性地超时。这个问题在一天中是一致的，但发生率非常低。如何继续排除故障？

连接超时已过期。尝试使用登录前握手确认时超时时间已过。这可能是因为登录前握手失败或服务器无法及时响应。尝试连接到此服务器所花费的持续时间是 - [Pre-Login] 初始化 = 0；握手=15002；（Microsoft SQL Server，错误：-2）

服务器配置：

• SQL Server 2016 SP1 CU5 Enterprise（在 SP1 之前也出现过问题）
• 服务器和客户端上的 Windows Server 2012 R2
• HP ProLiant DL360 Gen9 上的 VMware ESXi，6.5.0
• VM 有 8 个 vCPU，64 GiB 内存（完全保留）

测试脚本（每秒执行一次）：

$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 }});

观察：

• 在操作系统更新、SQL Server 更新、San 移动以及从 Hyper-V 移动到 VMWare 之后开始出现问题
• 大多数连接成功（1,440 次尝试中有 4 次失败）
• 在“[Pre-Login] 初始化 = 0;”中，失败总是以小数字列出 并且“握手= 15002”中的数字很大。我们没有收到诸如“未找到”或“不知道这样的主机”之类的错误，只有“连接超时”
• 没有为监听器启用加密
• Ping 显示在较长时间内没有丢失（发送的 96,045 次中有 0 次丢失）
• 所有防火墙都被禁用
• 尝试使用 IPv6 和 IPv4 地址的连接失败率相同
• CPU 偏低 (<40%)
• 活跃会话持续在 400 左右
• 气球驱动程序已禁用
• 一旦建立的连接是稳定的，执行查询时没有意外错误，没有奇怪的断开连接。
• 多个客户端在连接时遇到问题 - 来自多台计算机的 ODBC 和 ADO

更新：我终于得到了一个失败连接的客户端 Wireshark 跟踪。没有明显的数据包丢失，客户端实时接收 TCP ACK（<10ms）。发生故障时客户端使用 DNS 名称，但使用连接字符串中的 IPv4 地址确实会发生故障。

我是否正确地认为，我收到对发送的登录前请求数据包的即时 TCP ACK 会将问题本地化到操作系统或 SQL Server 的事实？

我支持使用 SQL Server 2016 后端的第三方应用程序。我们没有源代码访问权限，第三方支持响应缓慢。大多数时候，我可以通过索引和变魔术摆脱它向数据库抛出的有时可怕的查询，但在很多情况下我几乎无能为力（在时间、金钱和存储的限制下） ). 查询是错误的。

有没有办法在管理上用应用程序提交的查询替换其他查询？如果应用程序名称匹配，我正在想象对批处理执行某种正则表达式匹配。

例子：

-- 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

我设想的一些用途是避免无用的更新，其他用途是改写 where 子句以保持可搜索性。我相信还有其他场景会浮现在脑海中。

想象一下，我有一个包含Products、Orders和的模式OrderLineItems，它Products是一个系统版本化的时态表。

架构：

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);

我将如何编写一个查询Products来OrderLineItems使用日期从Orders？

例如，要查询“包含名称已更改的产品的订单”：

-- 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

我在四台服务器上有四个相同的表，我需要从第五台服务器上查询。第五个服务器链接到其他四个，并创建了一个视图来统一它们。

-- 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;

当我在列上运行一个带有常量 where 子句的查询时ServerID，我得到了一个非常愉快的查询计划，它消除了三个分区。

SELECT * FROM vwTransactions WHERE ServerID = 1;

但是当我使用一个参数，或者更糟的是——一个连接时，我得到了所有四个表的扫描。

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID;

我也尝试过使用内联表值函数无济于事。

如何查看这些可以轻松查询的表？

我的一位开发人员争辩说，这COALESCE(column, default value) = default value现在是可悲的。那正确吗？

我进行了以下测试，并认为这意味着它COALESCE是不可预测的。

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.

假设暂时FK引用不可用，下面例子的前置条件应该放置什么锁提示来防止死锁和数据不一致？

（当然，这是一个人为的例子，用于说明只能根据其他表的状态执行删除/更新的场景）

-- 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')

环境：SQL Server 2012

我有一个由大约 50 个客户端通过 TCP 上的 TDS 访问的数据库，这似乎没有释放日志空间。进程数保持在预期的 50 个左右，其中一些进程的寿命很长（> 120 天）。

数据库现在有 40 GB 的日志空间（它只有 14 GB 数据），39 GB 空闲空间。由于驱动器的空间限制，我想缩小到更合理的东西（10gb-ish）。当我执行DBCC SHRINKFILE('db_log', 10000)时，它返回一个日志末尾正在使用的错误。

为了自由访问日志末尾，我尝试使用以下命令将数据库置于单用户模式：

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

但脚本返回重复数百次的以下消息：

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

这让我相信在某个地方，我留下了一些未提交的交易。我不知道有任何过程会故意一次打开这么多交易，所以我认为它们必须随着时间的推移而累积，永远不会被关闭。

问题：我如何找到有问题的进程或脚本，或者为什么没有发布日志？

sys.dm_tran_active_transactions显示合理的 18 笔交易，目的可理解。 sp_who仅显示我知道的过程。

SQL 服务器版本：

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)

服务器版本：

Windows Server 2008 R2 x64 - 数据中心 4 个 vCPU，16GB 内存，数据和日志直通磁盘，操作系统磁盘是 VHD

Hyper-V（Windows Server 2008 R2 SP1 x64 Datacenter）双 Intel X5650（6 核，12 线程，2.67GHz）72 GB 内存

Hypervisor 只有三个虚拟机，并没有显示出很高的资源使用率。SQL Server VM 显示约 40% 的 CPU 负载不足和 99% 的缓存命中。