主页 / user-62128

Rainbolt's questions

Martin Hope
Rainbolt
Asked: 2018-05-16 13:45:56 +0800 CST
  • 33

我有一个要优化的 SQL 查询：

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable有两个索引：

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

当我完全按照上面写的方式执行查询时，SQL Server 会扫描第一个索引，产生 189,703 次逻辑读取和 2-3 秒的持续时间。

当我内联@Id变量并再次执行查询时，SQL Server 寻找第二个索引，结果只有 104 次逻辑读取和 0.001 秒的持续时间（基本上是即时的）。

我需要这个变量，但我希望 SQL 使用好的计划。作为临时解决方案，我在查询上放了一个索引提示，查询基本上是即时的。但是，我尽量远离索引提示。我通常假设如果查询优化器无法完成它的工作，那么我可以做（或停止做）一些事情来帮助它，而无需明确告诉它该做什么。

那么，当我内联变量时，为什么 SQL Server 会提出更好的计划呢？

sql-server performance
Martin Hope
Rainbolt
Asked: 2018-03-07 10:55:51 +0800 CST
  • 1

我在我的服务器上创建了一个出版物。我不得不使用这个特殊选项，以便我可以从备份初始化复制：

exec sp_addpublication 
    ...
    @allow_initialize_from_backup = N'true', 
    ...

然后，我备份了我的数据库：

BACKUP DATABASE [MyDatabase] 
    TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak' 
    WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5;
GO

并在目标服务器上从中恢复：

RESTORE DATABASE [MyDatabase] 
  FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
  WITH CREDENTIAL = 'mycredential'
    ,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf'
    ,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf'
    ,STATS = 5 
GO

现在我已准备好创建订阅者。我遇到的问题是我不知道为sp_addsubscription过程提供什么选项。

我可以使用普通硬盘而不是 URL 完成整个过程。我可以看到数据正在从源复制到目标。我备份到磁盘，将备份复制到目标服务器，还原，然后使用以下命令从备份初始化：

exec sp_addsubscription 
    @publication = N'MyPub', 
    @subscriber = N'MyTargetServer', 
    @destination_db = N'MyDatabase', 
    @subscription_type = N'Push', 
    @sync_type = N'initialize with backup', 
    @article = N'all', 
    @update_mode = N'read only', 
    @subscriber_type = 0,
    @backupdevicetype = 'disk',
    @backupdevicename = 'D:\Backups\MyDatabase.bak'

我如何调整这些选项以便从 URL 初始化复制（在我的例子中是两个服务器都有凭据的 Azure blob 存储容器）？

replication azure-blob-storage
Martin Hope
Rainbolt
Asked: 2018-02-13 07:37:22 +0800 CST
  • 0

在为什么事务日志不断增长或空间不足？，概述了事务日志增长的两个常见原因：

  1. 数据库处于完全恢复模式，不进行日志备份。
  2. 长时间运行的事务占用了大量空间。

据我所知，这些原因都不适用于我。我的数据库处于简单恢复模式，没有长时间运行的事务占用大量空间。

在此处输入图像描述

在此处输入图像描述

DBCC OPENTRAN请注意，当我截取屏幕截图并且没有活动的交易时，我很幸运。大多数时候，我看到并看到几秒钟前开始的交易。

另一个答案表明我的日志可能很大，但为空。要检查这一点，我右键单击数据库，然后导航到任务 -> 收缩 -> 文件，然后选择日志文件类型。我可以看到我的日志文件很大，而且可用空间也很少。

在此处输入图像描述

我使用Aaron Bertrand 的查询来找出导致日志文件增长的会话以及间隔时间。查询告诉我日志文件以半固定的时间间隔增长，大约每 10 分钟一次。SPID 每次都不同，并不总是来自同一个应用程序。我使用了一个查询（由于某种原因我现在找不到）来确定该会话上次执行的 SQL 语句。我研究了这些查询，看看是否有什么突出的，没有发现任何明显的东西。我最终决定，如果DBCC OPENTRAN说没有事务长时间运行，那么问题一定不在于应用程序正在执行的查询。

我们最近尝试使用此数据库作为源来设置事务复制。我们最终不得不放弃，但在删除该出版物时遇到了问题。我们不得不手动删除分发数据库并使用它sp_removedbreplication来清理。我读到复制会导致事务日志未经检查地增长。我的想法是，由于删除发布的过程并不顺利，日志文件可能正在增长，因为复制的一些残余物仍然存在。

那么，除了两个最常见的原因，我还能做些什么来弄清楚为什么事务日志变得如此之大（并修复它）？

sql-server transaction-log
Martin Hope
Rainbolt
Asked: 2017-12-03 21:16:29 +0800 CST
  • -2

是否可以在任何版本的 SQL Server 中禁用特定表的所有锁？

我有一些表在部署时填充了数据。例如，其中一个较小的表[items].[Colour]包含'Blue', 'Green', 'Red', 'White'。部署后，应用程序只会从这些表中进行选择（从不插入、更新或删除）。我开始思考，如果这些表只能从中读取，那么锁有什么意义呢？我认为获得锁定需要一些时间。即使那段时间被证明是微不足道的，在我自己测试差异之前，我的一部分也不会满意。

当我自己搜索答案时，我发现我可以将整个数据库标记为只读，并且可能会有一些好处。我想要这些好处，但要逐桌进行。我还阅读了有关禁用锁升级的信息，但如果我正确理解文档，那只会导致更多（尽管粒度更细）锁，这与我想要实现的相反。

我知道我可以更新每个使用该表的查询以获得NOLOCK提示，但这会引入一个次要问题。将来当我教我的应用程序如何Colour在运行时学习新的 s 时，我是否要更新每个使用该表的查询以删除NOLOCK提示？这听起来很麻烦。

这个问题似乎收到了一些反对票，并且没有提供任何解释，我将假设这是因为这似乎是我要求的过早优化。正如一条现已删除的评论所指出的，过早的优化是万恶之源。我知道，我并没有过早地优化。这就是为什么。

我可以每秒轮询一次此 API 。它没有在链接页面上说明，但我在某处看到了规则（相信我）。无论如何，我的应用程序需要 100 毫秒到 800 毫秒才能发出 HTTP 请求、下载 gzip 压缩的文本文件并解压缩它。大部分时间都花在等待 API 响应上。这让我几乎没有时间将所有数据处理成漂亮、整洁、规范化的数据库表。如果我要有竞争力，我必须在 200 毫秒或更短的时间内处理所有数据。我非常接近实现我的目标，以至于我通过丢弃我可能不需要的数据来偷工减料，将所有数据一次性发送到一个大的丑陋的数据包中，等等。所以是的，我正在优化，但不是过早地（在我看来）。

我也认识到我所要求的可能没有性能优势。事实上，到目前为止提供的一种解决方案似乎根本不会影响性能。不过，在我问这个问题之前，我不可能知道这一点，现在任何想知道这是否对他们有帮助的未来访客都可以看到我执行的测试，并且知道这是不值得的。那不是很有用吗？

如果这不能解决反对者的担忧，请考虑发表评论，说出你认为我可以改进的问题。

sql-server locking
Martin Hope
Rainbolt
Asked: 2017-05-10 13:39:05 +0800 CST
  • 3

我有一个网页，用户可以在其中配置 SQL Server 登录名和密码。我正在尝试对此页面实施验证。

我查找了 SQL Server 登录的要求，并找到了这篇 Microsoft 文章。根据那篇文章，SQL Server 登录名不能是保留的登录名。“sa”和“public”作为示例提供。没有提供完整的保留登录名列表，我无法通过搜索找到一个。

是否有保留的 SQL Server 登录名列表？如果是这样，我在哪里可以找到它？

sql-server logins
Martin Hope
Rainbolt
Asked: 2016-06-28 11:33:48 +0800 CST
  • 3

我在 SQL 中创建了一个表类型：

CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
    [Name] VARCHAR(MAX) DEFAULT '',
    [Index] VARCHAR(MAX) DEFAULT ''
)

我建立一个DataTable并用一个填充它DataRow。我将该表作为参数提供给存储过程：

// Set up connection and command variables (code omitted)
// ...

// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");

// Add one row that is missing an Index    
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);

// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType",
    Value = table
};

command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();

我预计在存储过程内部，索引值将默认为''（空 VARCHAR）。相反，我观察到该值为空。我知道这一点是因为我试图将表类型合并到一个实际的表中，并且我表上的索引列不允许空值。

为什么 SQL Server 不遵守我放置在表类型上的默认值？有没有办法强制 SQL Server 兑现它？

我想我可以通过使用DataTable.DefaultValue属性来解决这个问题，但是该解决方案的缺点是我需要在两个不同的地方声明相同的默认值。如果可能的话，我想避免冗余代码。

sql-server c# default-value
Martin Hope
Rainbolt
Asked: 2016-02-04 09:28:02 +0800 CST
  • 4

我有两个共享多对多关系的表。这些关系存储在第三个表中。

CREATE TABLE Person
(
    Id int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE Filenodes
(
    Id hierarchyid NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE PersonFilenodes
(
    FileId hierarchyid NOT NULL,
    PersonId int NOT NULL,
    PRIMARY KEY (FileId, PersonId)
)

我需要选择属于一个人的所有文件节点，以及作为这些文件祖先的所有文件。

我知道如何选择Filenodes属于给定人的所有：

SELECT Id, Name
FROM PersonFilenodes
JOIN Filenodes on Filenodes.Id = PersonFilenodes.FileId
WHERE Id = @personId

感谢marc_s，我也知道如何选择给定文件节点的所有祖先：

SELECT Id, Name
FROM Filenodes
WHERE @filenodeId.IsDescendantOf(Id) = 1

我只是不知道如何组合这两个查询来获得我需要的结果集。如何选择属于某个人的所有文件节点，以及作为这些文件祖先的所有文件？

sql-server
Martin Hope
Rainbolt
Asked: 2015-10-01 07:38:21 +0800 CST
  • 5

我想将一个表合并到另一个表中。我需要在我的 WHEN MATCHED 子句中应用条件逻辑，理想情况下可以这样完成：

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3

这不是有效的 SQL。根据 MSDN 文档：

如果有两个 WHEN MATCHED 子句，则一个必须指定一个 UPDATE 操作，一个必须指定一个 DELETE 操作。

这导致我进行以下查询：

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
    Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
    ,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
    ,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END

条件逻辑被移动到更新内部，以绕过合并只能有一个WHEN MATCHED THEN UPDATE子句的事实。现在，我不是每行检查一次，而是每行每列检查一次（并且列比示例中的三列多得多）。

我可以避免对需要更新的每一列重复这种情况吗？有没有更好的方法来进行可能不涉及合并的条件更新？

sql-server update
Martin Hope
Rainbolt
Asked: 2015-04-07 09:49:04 +0800 CST
  • 2

是否有基于主键合并行的简写？例如：

MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target
USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source
   -- The following line is pseudocode illustrating what we want to accomplish
   ON (target primary key equals source primary key)
WHEN MATCHED THEN
    UPDATE SET target.MyColumn = source.MyColumn
WHEN NOT MATCHED BY TARGET THEN
    INSERT (MyCol1, MyCol2, MyCol3)
    VALUES (source.MyCol1, source.MyCol2, source.MyCol3);

如果主键包含大量列，则写出条件很繁琐。此外，如果主键发生变化，许多连接和合并可能会突然中断。

您可能会问，“为什么要更改主键？” 真实场景：我们被告知一个对象由其专有名称唯一标识。后来，我们发现如果对象移动到另一个子网，专有名称可能会发生变化。新的要求改变了我们唯一识别对象的方式。这在很多地方破坏了产品。如果我们在主键上而不是在特定列上合并，一些混乱是可以避免的。

sql-server t-sql