我们的一位 DBA 向我们的团队抱怨说，他注意到大约有十几个休眠连接实际上是永久性的。它们中的每一个都表示一个非常短的查询（来自单个记录的单个记录由其聚集的 PK 过滤返回）。
我的假设是原因是 ADO.NET 连接池的使用和经常进行相同查询的应用程序。
我试图找出这些休眠连接是否会对 SQL Server 性能产生有意义的影响。默认情况下，ADO.NET 连接池限制为 100 个连接，因此我的假设是十几个休眠连接应该可以忽略不计。
我只能在这个线程中找到信息：
每个睡眠会话的最低成本约为 32kb 的 RAM - 非常非常适中！
CPU 开销可能会有一些微不足道的额外成本，但即使是 10,000 个会话也几乎无法检测到。
在会话/连接开销问题上，SQL Server 表现得非常好！
这些信息准确吗？
我正在处理一个相当老的 .NET 项目，并引入了一些新功能（在顶部），这些功能产生了以下副作用：所有生成的 SELECT（或组）都包含在BEGIN TRAN ... COMMIT语句中。
这听起来很傻，但要摆脱它需要做很多改变，而我负担不起。我的假设是这基本上意味着每组 SELECT 的开销很小（应用程序和 SQL Server 之间的 BEGIN TRAN 和 COMMIT 往返）。
我想知道是否还有更多内容（额外锁定？）。
问题：如果选择语句包含在 BEGIN TRAN ... COMMIT 中，是否有任何副作用？
这是ALTER VIEW drop Index from View问题的后续行动。
接受的响应表明 ALTER VIEW 将自动删除任何已定义的索引。
这与其他类似操作的发生方式有些反直觉：
我想知道为什么首先默认具有这种行为（即在某些情况下是否存在任何客观方面，例如由于视图更改而导致性能降低）。
问题：删除所有索引以更改视图的基本原理是什么？
我有一个在 SQL Server 2017 Express 上运行的测试环境，它只使用最近的数据（一个作业每月删除一次超过三个月的数据）。
该作业执行以下操作：
本文描述了由于索引碎片而收缩数据库是多么可怕，以及为什么重建索引还需要额外的空间。
上次运行从 9GB 数据库开始。删除需要 3-4 分钟，重建索引大约需要 30 秒。数据库减少到大约 5GB。
由于这是一个测试环境，我可以毫无问题地承受这种停机时间。
问题：删除大部分数据后收缩+重建索引不好吗？
这个问题是对这两个问题的跟进：
从第二个开始，我发现一个粗心的 SQL 用户可能会留下一个运行时间很长的事务，并导致为日志文件分配大量磁盘。
第一种表示可以通过job来实现一定时间后kill一个事务，但是不推荐这种方式。
如果我确定针对数据库打开的所有事务都不应该分配超过一定数量的日志，有没有办法找出为 SPID 分配了多少日志，这样我就可以杀死它？
简而言之，我对限制事务的最大日志文件使用量感兴趣。
问题：有没有办法限制 SQL Server 中事务的日志文件使用？
在像这样的某些场景中，SQL Server 会在错误地声明变量时静默截断 (N)VARCHAR 值，从而导致严重的数据丢失。
问题：可以将 SQL Server 配置为不静默截断 VARCHAR 值吗？（并发出错误/引发异常）
我正在使用BrentOzar的SQL Server First Responder Kit，因为我属于“管理 Microsoft SQL Server 的开发人员。如果它们出现故障或运行缓慢，那是你的错。”
运行警告之一sp_blitz如下：
[DB] 数据库文件 DB_log 的最大文件大小设置为 10240MB。如果空间不足，即使可能有可用的驱动器空间，数据库也会停止工作。
日志文件与操作系统位于不同的驱动器上，我确实限制了它们的增长。所有数据库都有简单的恢复模型（大部分数据是通过复制和 ETL 自动获取的，从上次备份恢复对我来说就足够了）。
该数据库有大约 20GB 的数据。
问题：如果恢复模式不强制我备份日志，数据库怎么会停止工作？
我正在尝试对使用的表执行清理操作DELETE，但收到以下错误：
无法为数据库“tempdb”中的对象“dbo.SORT 临时运行存储：140767697436672”分配空间，因为“PRIMARY”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间。
在运行之前，DELETE我有超过 11G 的可用磁盘空间。发出错误时，该分区上几乎没有任何东西。上下文信息如下：
1）有问题的查询：
declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)
2) 涉及表的基数
declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article -- 137181
select count(1) from art.Article where PublishDate < @deleteDate -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate) -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name rows reserved data index_size unused
-- ArticleConcept 14624589 1702000 KB 616488 KB 1084272 KB 1240 KB
3) 索引
-- index_name index_description index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq nonclustered located on PRIMARY ArticleId
CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] ON [art].[ArticleConcept]
(
[ArticleId] ASC
)
INCLUDE ( [LexemId],
[Freq]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
4) 服务器
Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
-- Feb 20 2014 20:04:26
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
5）执行计划（预估）
我知道我正在执行一个大的 DELETE，但我不明白为什么它需要这么多空间来完成它：整个ArticleConcept表只有不到 2GB（保留空间），但要从中删除记录需要超过 11GB。
问题：为什么我的 DELETE 命令需要大量的临时运行存储？
我已经删除了所有二级索引，我可以执行DELETE. 但是，为什么在拥有它们时需要更多的空间来执行DELETE，我觉得很奇怪。
我正在尝试删除 14,624,589 条记录中的 12,153,045 条（相当多）。我没有监控事务日志，但是一旦收到与之相关的错误：
由于“ACTIVE_TRANSACTION”，数据库的事务日志...已满
我有一些叶表（对它们没有 FK），其中包含数十万条记录，这些记录用于使用 Entity Framework ORM 同步一些外部数据。这涉及一些 DELETE，然后是 BULK INSERT。
对于大多数表，一些旧值可能会永远保留，因此我不能将 SEQUENCEs 与 CYCLE 一起使用，正如其中一条评论所建议的那样。
一种影响是身份值会随着时间的推移不断增加，我希望能够降低它们的值。
这个问题及其答案解释了身份值无法更新，即使identity_insert是在表上。
一种快速的方法是将所有数据传输到缓冲区表并通过重命名执行切换。类似于以下内容：
-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready
begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);
-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.
select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0
DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)
insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak
drop table ActiveDirectoryCache_bak
alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);
-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.
-- this should be replaced with commit when not in test mode
rollback
我有能力在夜间进行这种操作，而且我的数据量似乎只需要几秒钟（本例为 4 秒）。
问题： 是否有任何方法可以避免执行完整数据复制以获取标识列的较小值？或者这是在我的上下文中最好的方法之一（合理的数据量并且能够锁定一些表几秒钟）。
这个问题及其答案解释了批量插入将如何呈现FK为不受信任，因为批量插入未完全检查（仅PK和UNIQUEs）。我也知道暂时禁用 aFK可能会导致不受信任的状态。
我当前的项目使用一个相当小的数据库（< 3GB），典型的表数少于 200K 行。但是，完整性非常重要，所以我非常依赖FK约束（完整性比速度重要得多）。
问题：是否有任何其他机制（除了 disable 和BULK INSERT）可以产生不受信任的外键？
我主要是使用实体框架 ORM 的 .NET 开发人员。但是，因为我不想在使用 ORM 时失败，所以我试图了解数据层（数据库）中发生的情况。基本上，在开发过程中，我启动分析器并检查代码的某些部分根据查询生成了什么。
如果我发现一些非常复杂的东西（ORM 甚至可以从相当简单的 LINQ 语句中生成糟糕的查询，如果没有仔细编写的话）和/或繁重的（持续时间、CPU、页面读取），我会将其放入 SSMS 并检查其执行计划。
它适用于我的数据库知识水平。但是， BULK INSERT 似乎是一种特殊的生物，因为它似乎不会产生 SHOWPLAN。
我将尝试说明一个非常简单的示例：
表定义
CREATE TABLE dbo.ImportingSystemFileLoadInfo
(
ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED,
EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment,
ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem,
FileName NVARCHAR(64) NOT NULL,
FileImportTime DATETIME2 NOT NULL,
CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime)
)
注意：表上没有定义其他索引
批量插入 （我在分析器中捕获的内容，仅一批）
insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))
指标
对于我的应用程序，没关系，虽然读取看起来相当大（我对 SQL Server 内部知识知之甚少，所以我比较了 8K 页面大小和我拥有的小记录信息）
问题：如何调查此 BULK INSERT 是否可以优化？或者它没有任何意义，因为它可以说是将大数据从客户端应用程序推送到 SQL Server 的最快方式？
我的一位同事注意到为插入的项目生成了不正确的 ID。最可能的原因：某些脚本发出如下命令：
dbcc checkident('dbo.table', reseed, 1)
我考虑过设置一个 DDL 触发器来捕获此命令，但此DDL 事件列表似乎不包含与重新播种相关的任何内容。
问题：有没有一种方法可以像 DDL 触发器捕获各种数据库模式更改一样捕获此类命令？
我有下表：
CREATE TABLE dbo.Document
(
DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
[Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp DEFAULT (getdate()),
CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy DEFAULT (dbo.getCurrentUser()),
MonthId int NOT NULL,
TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
Key1 bit NOT NULL,
Key2 int NULL,
Key3 varchar(max) NULL, -- sometimes above 8000chars
Key4 varchar(max) NULL, -- sometimes above 8000chars
Key5 varchar(max) NULL, -- sometimes above 8000chars
Key6 varchar(max) NULL, -- sometimes above 8000chars
Key7 varchar(max) NULL, -- sometimes above 8000chars
Key8 int NOT NULL,
CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)
尽管我一直坚持要找到一个更好的自然标识符，但我不得不处理以下自然唯一元组：
MonthId, TimeModeId, Key1, ... , Key8
这对于 UNIQUE 索引来说太大了（在 SQL Server 2014 中最多 900 字节或更少），所以我不得不想出一些办法。我的想法是计算这些列的哈希值，所以我有一个PERSISTED COMPUTED列，如上所示：
FiltersHash AS (hashbytes('SHA2_256',(
(((((((((((((((
(CONVERT(varchar(10),MonthId)+'|')
+ CONVERT(varchar(4),TimeModeId))
+'|')+CONVERT(varchar(4),Key1))
+'|')+isnull(CONVERT(varchar(max),Key2),''))
+'|')+isnull(CONVERT(varchar(max),Key3),''))
+'|')+isnull(CONVERT(varchar(max),Key4),''))
+'|')+isnull(CONVERT(varchar(max),Key5),''))
+'|')+isnull(CONVERT(varchar(max),Key6),''))
+'|')+isnull(CONVERT(varchar(max),Key7),''))
+'|')+isnull(CONVERT(varchar(4),Key8),''))
) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),
它被证明是有用的，因为通过一个复杂的场景，应用程序试图复制一个文档。
问题：这个解决方案是好的解决方案吗？对于大宽度唯一性问题是否有更简单或更有效的解决方案？
注意：在我的应用程序中，我可以放心地忽略碰撞（即使发生碰撞，后果也很小）。感谢您Aaron Bertrand指出。
在我正在处理的一个 Web 应用程序中，所有数据库操作都使用一些在实体框架 ORM 上定义的通用存储库进行抽象。
但是，为了对通用存储库进行简单设计，所有涉及的表都必须定义一个唯一的整数（Int32在 C# 中，int在 SQL 中）。直到现在，这一直是桌游的PK，也是IDENTITY。
外键被大量使用，它们引用这些整数列。它们对于一致性和 ORM 生成导航属性都是必需的。
应用层通常执行以下操作：
SELECT * FROM tableUPDATE table SET Col1 = Val1 WHERE Id = IdValDELETE FROM table WHERE Id = IdValINSERT INTO table (cols) VALUES (...)不太频繁的操作：
BULK INSERT ... into table所有数据加载后跟 (*)（检索生成的标识符）DELETE FROM table where OtherThanIdCol = SomeValueUPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue*所有小表都在应用程序级别缓存，几乎所有SELECTs都不会到达数据库。一个典型的模式是初始加载和大量的INSERTs、UPDATEs 和DELETEs。
根据当前的应用程序使用情况，在任何表中达到 100M 记录的可能性非常小。
问题： 从 DBA 的角度来看，有这个表设计限制我会遇到重大问题吗？
[编辑]
在阅读了答案（感谢您的反馈）和参考文章后，我觉得我必须添加更多细节：
当前应用程序细节- 我没有提及当前的 Web 应用程序，因为我想了解该模型是否也可以用于其他应用程序。但是，我的特殊情况是从 DWH 中提取大量元数据的应用程序。源数据非常混乱（以一种奇怪的方式非规范化，有一些不一致，在许多情况下没有自然标识符等），我的应用程序正在生成清晰分离的实体。此外，还会显示许多生成的标识符 ( IDENTITY)，以便用户可以将它们用作业务密钥。除了大量的代码重构之外，这还排除了 GUID 的使用。
“它们不应该是唯一标识一行的唯一方法”（Aaron Bertrand♦）——这是一个非常好的建议。我所有的表还定义了一个 UNIQUE CONSTRAINT 以确保不允许业务重复。
前端应用驱动设计与数据库驱动设计- 设计选择是由这些因素引起的
实体框架限制- 允许多列 PK，但它们的值不能更新
自定义限制- 具有单个整数键大大简化了数据结构和非 SQL 代码。例如：所有值列表都有一个整数键和一个显示值。更重要的是，它保证任何标记为缓存的表都能够放入Unique int key -> value映射中。
复杂的选择查询——这几乎不会发生，因为所有小（< 20-30K 记录）表数据都在应用程序级别缓存。这使得编写应用程序代码时的生活有点困难（更难编写 LINQ），但数据库受到的影响要好得多：
列表视图- 不会SELECT在加载时生成查询（所有内容都被缓存）或如下所示的查询：
SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)
所有其他必需的值都是通过缓存查找 (O(1)) 获取的，因此不会生成复杂的查询。
编辑视图- 将生成SELECT如下语句：
SELECT allcolumns FROM BigTable WHERE PKId = value1
（所有过滤器和值都是ints）
我被要求为 Web 应用程序创建一个简单的审计系统。由于一些限制，我选择了一种“肮脏而快速”的方式。这是可能的，因为该特定应用程序中的活动很小（每天数百次插入）。
但是，现在我必须在另一个生成更多活动的 Web 应用程序中实施，因此我考虑重构它。
笔记：
所有查询都是由使用的 ORM（实体框架）生成的，看起来非常糟糕。
所有测试都是在一个表中执行的，表中填充了一些 2K 虚拟记录，在一些用户、时间等之间均匀分布。
数据定义
CREATE TABLE dbo.AppEvent
(
AppEventId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AppEvent PRIMARY KEY CLUSTERED,
InsertTimestamp DATETIME2 NOT NULL CONSTRAINT DF_AppEvent DEFAULT(GETDATE()),
UserId INT NOT NULL CONSTRAINT FK_AppEvent_UserId REFERENCES dbo.AppUser,
EventTypeId INT NOT NULL CONSTRAINT FK_AppEvent_EventType REFERENCES dbo.EventType,
RegionId INT NULL CONSTRAINT FK_AppEvent_Region REFERENCES dbo.Region,
CountryId INT NULL CONSTRAINT FK_AppEvent_Country REFERENCES dbo.Country,
InsertDay AS (CAST(InsertTimestamp as DATE)),
InsertMonth AS (CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, InsertTimestamp), 0) AS DATE)),
InsertYear AS (CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, InsertTimestamp), 0) AS DATE)),
Description NVARCHAR(2000) NULL,
ProjectId INT NULL CONSTRAINT FK_AppEvent_Project REFERENCES dbo.Project,
ReminderActionId INT NULL CONSTRAINT FK_AppEvent_ReminderAction REFERENCES dbo.ReminderAction
)
GO
这是唯一相关的表格。所有 FK 引用都指向聚簇主键，并且大多数表包含的记录少于 100 条。
实际记录
该应用程序试图通过避免插入时间太近（相同的用户，相同的事件类型）来聚集日志记录信息。
因此，它需要做一个SELECT：
exec sp_executesql N'SELECT TOP (1)
[Project1].[InsertTimestamp] AS [InsertTimestamp]
FROM ( SELECT
[Extent1].[AppEventId] AS [AppEventId],
[Extent1].[InsertTimestamp] AS [InsertTimestamp]
FROM [dbo].[AppEvent] AS [Extent1]
WHERE ([Extent1].[UserId] = @p__linq__0) AND ([Extent1].[EventTypeId] = @p__linq__1) AND (([Extent1].[ReminderActionId] = @p__linq__2) OR (([Extent1].[ReminderActionId] IS NULL) AND (@p__linq__2 IS NULL)))
) AS [Project1]
ORDER BY [Project1].[AppEventId] DESC',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 int',@p__linq__0=1,@p__linq__1=4,@p__linq__2=27
这产生了关于：CPU = 16, Reads = 34, Writes = 0, Duration = 0
查看执行计划，我认为索引可能会有所改善：
CREATE INDEX IDX_AppEvent_User_EventType ON dbo.AppEvent (UserId, EventTypeId, ReminderActionId) INCLUDE (AppEventId, InsertTimestamp)
这给CPU = 0, Reads = 20, Writes = 0, Duration = 0
实际的 INSERT 语句如下所示：
exec sp_executesql N'INSERT [dbo].[AppEvent]([InsertTimestamp], [UserId], [EventTypeId], [RegionId], [CountryId], [Description], [ProjectId], [ReminderActionId])
VALUES (@0, @1, @2, @3, @4, @5, @6, NULL)
SELECT [AppEventId], [InsertDay], [InsertMonth], [InsertYear]
FROM [dbo].[AppEvent]
WHERE @@ROWCOUNT > 0 AND [AppEventId] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int,@3 int,@4 int,@5 nvarchar(2000),@6 int',
@0='2017-01-30 14:54:02.6469319',@1=1,@2=7,@3=5,@4=305,@5=N'Custom message',@6=1533
SELECT声明是由 ORM 引起的，它需要知道刚刚创建的标识符（我将不得不看看我是否可以摆脱SELECT不需要的额外内容）。
这大约需要CPU = 16, Reads = 32, Writes = 0, Duration = 9
执行计划生成以下内容：
和这个
报告
审计报告非常简单，很少运行（每天最多几次）。典型的查询如下所示：
SELECT
1 AS [C1],
[GroupBy1].[K2] AS [InsertDay],
[GroupBy1].[K1] AS [CountryId],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
[Extent1].[CountryId] AS [K1],
[Extent1].[InsertDay] AS [K2],
COUNT(1) AS [A1]
FROM [dbo].[AppEvent] AS [Extent1]
WHERE ([Extent1].[EventTypeId] IN (1, 6, 7, 9)) AND ([Extent1].[CountryId] IS NOT NULL)
GROUP BY [Extent1].[CountryId], [Extent1].[InsertDay]
) AS [GroupBy1]
`CPU = 16, Reads = 25, Writes = 0, Duration = 11`
SELECT
1 AS [C1],
[GroupBy1].[K2] AS [InsertMonth],
[GroupBy1].[K1] AS [CountryId],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
[Extent1].[CountryId] AS [K1],
[Extent1].[InsertMonth] AS [K2],
COUNT(1) AS [A1]
FROM [dbo].[AppEvent] AS [Extent1]
WHERE ([Extent1].[EventTypeId] IN (1, 6, 7)) AND ([Extent1].[CountryId] IS NOT NULL)
GROUP BY [Extent1].[CountryId], [Extent1].[InsertMonth]
) AS [GroupBy1]
`CPU = 16, Reads = 25, Writes = 0, Duration = 12`
问题： 考虑到每天最多生成 100K 个事件，我是否应该考虑重写整个审计机制（从数据库的角度来看）？
在应用层，我可以做几项改进，例如：确保审计不在与操作更改相同的事务中执行，并使用其他线程执行查询。
我正在处理的 Web 应用程序之一使用 NLog 在数据库中记录调试和错误上下文信息。基本上，它使用以下模式执行中等数量的插入（我很欣赏每天数万次）：
<commandText>
insert into dbo.nlog
(log_date, log_level_id, log_level, logger, log_message, machine_name, log_user_name, call_site, thread, exception, stack_trace, full_exception_info)
values(@timestamp, dbo.func_get_nlog_level_id(@level), @level, @logger, @message, @machinename, @username, @call_site, @threadid, @log_exception, @stacktrace, @FullExceptionInfo);
</commandText>
<parameter name="@timestamp" layout="${longdate}"/>
<parameter name="@level" layout="${level}"/>
<parameter name="@logger" layout="${logger}"/>
<parameter name="@message" layout="${message}"/>
<parameter name="@machinename" layout="${machinename}"/>
<parameter name="@username" layout="${windows-identity:domain=true}"/>
<parameter name="@call_site" layout="${callsite:filename=true}"/>
<parameter name="@threadid" layout="${threadid}"/>
<parameter name="@log_exception" layout="${exception}"/>
<parameter name="@stacktrace" layout="${stacktrace}"/>
<parameter name="@FullExceptionInfo" layout="${gdc:FullExceptionInfo}"/>
为了尽量减少日志记录的影响，数据库查询是异步发出的（在不同的线程上）。但是，我必须小心不要用完线程池线程。
为了在查询日志时获得更好的性能，我为最常用的列放置了两个索引，log_data和log_user_name。但是，我知道这会对插入性能产生负面影响。entered_date上还有聚集索引，如下所示sp_help：
IX_nlog_entered_date clustered located on PRIMARY entered_date
Q1：有这些索引是可以的还是没有它们更好，并且在很少查询表时会受到惩罚？或者也许有更好的方法。
使用如下简单查询完成查询：
-- just see the latest logged activity
SELECT TOP 1000 *
FROM nlog
ORDER BY nlog_id DESC
或者像这样：
SELECT TOP 200*
FROM nlog
WHERE log_user_name = 'domain\username'
ORDER BY nlog_id DESC
显然，这可能会在执行时锁定表，从而延迟一些插入。我认为 usingWITH(NOLOCK)应该是一个不错的选择，但人们常常忘记它。
Q2：如何尽量减少阅读对桌子的影响？我正在考虑拒绝对表的读取访问，而是创建一个存储过程来执行读取NOLOCK，但这会导致更复杂。
一段时间后，应该删除旧记录。据我所知，从大表中删除许多行是一个繁重的查询。Web 应用程序有指定的时间段（晚上）执行维护工作，但我想改进这一步。那么，第三个问题：
Q3：如何将大删除的影响降到最低？. 我正在考虑表分区entered_date（默认为GETDATE()），但我不知道这是否是个好主意。
CREATE TABLE [dbo].[nlog](
[nlog_id] [int] IDENTITY(1,1) NOT NULL,
[entered_date] [datetime2](7) NOT NULL CONSTRAINT [DF_nlog_log_time] DEFAULT (getdate()),
[log_app_name] [nvarchar](255) NULL,
[log_date] [nvarchar](64) NULL,
[log_level_id] [tinyint] NOT NULL,
[log_level] [nvarchar](64) NULL,
[logger] [nvarchar](1024) NULL,
[log_message] [nvarchar](max) NULL,
[machine_name] [nvarchar](255) NULL,
[log_user_name] [nvarchar](255) NULL,
[call_site] [nvarchar](4000) NULL,
[thread] [nvarchar](255) NULL,
[exception] [nvarchar](max) NULL,
[stack_trace] [nvarchar](max) NULL,
[full_exception_info] [nvarchar](max) NULL,
CONSTRAINT [PK_nlog] PRIMARY KEY NONCLUSTERED
(
[nlog_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_nlog_entered_date] ON [dbo].[nlog]
(
[entered_date] 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, FILLFACTOR = 95) ON [PRIMARY]
GO
我正在开发一个应用程序，该应用程序具有几个严重依赖存储过程的遗留模块（没有 ORM，因此所有获取和数据持久性都是通过存储过程完成的）。
遗留模块的安全性依赖于SUSER_NAME()获取当前用户并应用安全规则。
我正在迁移它以使用 ORM（实体框架），SQL 连接器将使用通用用户连接到数据库（SQL Server），因此我必须为许多程序提供当前用户名。
为了避免更改 .NET 代码，我想到了在建立新连接时以某种方式在上下文中“注入”当前用户：
CREATE TABLE dbo.ConnectionContextInfo
(
ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
SPID INT NOT NULL,
AttributeName VARCHAR(32) NOT NULL,
AttributeValue VARCHAR(250) NULL,
CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO
当打开连接（或重用，因为使用了连接池）时，使用以下命令：
exec sp_executesql N'
DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go
（0 CPU，约 15 次读取，<6 毫秒）
标量函数允许轻松获取当前用户：
alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
-- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
RETURN ISNULL(@ret, SUSER_NAME())
END
GO
从数据层的角度来看，这种方法是否有任何警告（稳健性、性能等）？
谢谢。
我团队中的一些任务与存储过程优化有关。我主要是一名 .NET 开发人员，但我想在更改过程时编写一个用于数据比较的通用过程。主要目标是：
编码：
--
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
-- must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are
-- empty, results are equivalent (order does not matter)
--
-- PARAMS:
-- @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
-- @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
-- @Procedure2FullName: procedure 2 full name
-- @Params2Str: procedure 2 params as string
-- @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012+)
-- @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
-- @Debug: outputs debug info
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
@Procedure1FullName VARCHAR(255),
@Params1Str VARCHAR(MAX),
@Procedure2FullName VARCHAR(255),
@Params2Str VARCHAR(MAX),
@ResultSetStr VARCHAR(MAX),
@LoopBackServerName VARCHAR(255) = 'loopback',
@ForceShowDetails BIT = 0,
@Debug BIT = 0
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @InputStr NVARCHAR(MAX)
-- escaping string parameters
SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
SET @Params2Str = REPLACE(@Params2Str, '''', '''''')
SET @InputStr = @Procedure1FullName + '(' + @Params1Str + ')'
SET @SQL = '
DECLARE @StartTime datetime;
DECLARE @Diff1 BIGINT;
DECLARE @Diff2 BIGINT;
-- executing and measuring time for the first procedure
SET @StartTime = GETDATE();
SELECT * INTO #R1
FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure1FullName + ' ' + @Params1Str + ' WITH RESULT SETS (( ' + @ResultSetStr + '))'');
SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());
-- executing and measuring time for the second procedure
SET @StartTime = GETDATE();
SELECT * INTO #R2
FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure2FullName + ' ' + @Params2Str + ' WITH RESULT SETS (( ' + @ResultSetStr + '))'');
SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());
-- changing all float columns to decimal to ensure correct comparison
DECLARE @InnerSQL NVARCHAR(MAX) = N''''
select @InnerSQL += ''alter table #R1 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';
EXEC (@InnerSQL);
SET @InnerSQL = N'''';
select @InnerSQL += ''alter table #R2 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';
EXEC (@InnerSQL);
-- creating temporary tables to hold result sets differences
SELECT ''R1 \ R2'' AS [R1 \ R2], * INTO #R12
FROM #R1
WHERE 1 = 0
SELECT ''R2 \ R1'' AS [R2 \ R1], * INTO #R21
FROM #R1
WHERE 1 = 0
-- inserting data
INSERT INTO #R12
SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R1
EXCEPT
SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R2;
INSERT INTO #R21
SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R2
EXCEPT
SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R1;
-- difference flag
DECLARE @IsDiff BIT = 0
IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
SET @IsDiff = 1
SELECT ''' + @InputStr + ''' AS ''' + LEFT(@InputStr, 128) + ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';
-- showing details if a difference exists or details must be output
if (@IsDiff = 1 OR ' + CAST(@ForceShowDetails AS VARCHAR) + ' = 1)
BEGIN
SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
SELECT * FROM #R12
SELECT * FROM #R21
END
'
if (@Debug = 1)
BEGIN
PRINT '@SQL = ' + @SQL
PRINT 'SQL len = ' + CAST(LEN(@SQL) AS VARCHAR(MAX))
END
EXEC (@SQL)
END
调用示例：
declare @paramsStr VARCHAR(max) = '@year=2014,@month=6'
declare @resultSetStr VARCHAR(MAX) = 'kpi_id INT, kpi_value NUMERIC(18, 2)'
exec uspCompareProcedureResults
@Procedure1FullName = '[loopback].[DB].[usr].[get_data]', @Params1Str = @paramsStr,
@Procedure2FullName = '[loopback].[DB].[usr].[get_data_next_gen]', @Params2Str = @paramsStr,
@ResultSetStr = @resultSetStr, @ForceShowDetails = 0, @Debug = 1
GO
限制/注意事项/已知问题：
openquery）该程序可以正常工作，但我想知道：是否有更简单/更好的选择来实现比较。