Aleksey Vitsko's questions

我在 Azure SQL DB 中运行以下查询：

SELECT 
    [TransactionID],
    [TrackingNumber],
    TRY_CAST([EventCode] as int) as EventCode,      
    [EventDesc] ,
    [EventTime],
    [EventCountry],  
    [EventState],
    [EventCity],
    [EventPostCode],
    [SpecialOperDesc],   
    [Reference]  
FROM [dbo].[MyTable]  
with (index = IX_EventTime_TrackNum_Reference)
WHERE   EventTime>= dateadd(d,-15,getdate()) 
        AND LEN(TrackingNumber) >= 22          
        AND Reference IS NOT NULL

强制它使用IX_EventTime_TrackNum_Reference带有 Index Hint 的非聚集索引，直到最近它才起作用。执行计划是非聚集索引查找加上聚集索引上的查找。

现在它尝试对现有的非聚集列存储索引使用列存储索引扫描。我可以使用以下代码实时查看：

 exec sp_WhoIsActive @get_plans = 2

性能变差，因为非聚集列存储索引的大小比 IX_EventTime_TrackNum_Reference 大得多，并且它在增量存储中有一些数据。SQL DB 相对较小，存储速度很慢。

是什么导致查询忽略索引提示？以及如何强制它IX_EventTime_TrackNum_Reference再次使用？

我们有一张包含 54 列的表，它被许多应用程序、报告和用户使用。该表来自旧系统，某些列可能已过时。

我们想要找出查询实际使用了哪些列SELECT。UPDATE如何找出答案？

如果可能的话，我们希望获得以下格式：

Column1 - SELECT - 1,890,123 times 
Column1 - UPDATE - 2,394 times 
Column2 - SELECT - 5,222,456 times 
Column2 - UPDATE - 567 times 
Column3...

一个想法是扫描所有 GIT 存储库（应用程序源代码）以查找这些列，但有些应用程序实际上可能不在 GIT 中。而且报告也不在 GIT 中。

以上研究是为了：

1. 找出根本没有查询的列，并可能将其删除。
2. 要找出经常查询但未更新的列，请查看创建非聚集列存储索引的可能性。

我们创建了一个扩展事件会话来捕获严重级别为 16 或更高的错误：

CREATE EVENT SESSION [Error_Reporting] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([severity]>(15)))
ADD TARGET package0.event_file(SET filename=N'https://storageaccount.blob.core.windows.net/logs/Errors.xel',max_file_size=(10240), max_rollover_files=1)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

现在，我们可以通过运行以下命令来查询它：

select cast(event_data as xml), timestamp_utc
from sys.fn_xe_file_target_read_file('https://storageaccount.blob.core.windows.net/logs/Errors_0_133667581914690000.xel', NULL, NULL, NULL)

列event_data是 nvarchar 类型但包含 XML 值，代表一个表。

问题：

在 SSMS 中的文本编辑器中查询时，如何将event_data列转换（分解）为多个列（例如用户名、错误号、类别、目标、消息等）？

XML 数据示例：https://pastecode.io/s/tjg7jo40

我正在查看托管数十个应用程序的 IIS 服务器。其中一些已经很老了。它们都使用存储在 C:\Windows\Microsoft.NET\Framework64 中的 machine.config 中的连接字符串。

所有连接字符串都指向 SQL Server 2016，并使用 System.Data.SqlClient 进行连接：

providerName="System.Data.SqlClient" 

目标是将数据迁移到新的 Azure SQL 托管实例并替换连接字符串，以便它们指向新服务器。

在 Azure SQL 托管实例中，连接加密是强制性的，无法将其关闭。您只能选择最低 TLS 版本（1.0、1.1 或 1.2）。

MI 连接字符串包含Encrypt=True;TrustServerCertificate=False. 我尝试Encrypt=False在 SSMS 中进行设置进行测试，但随后查询sys.dm_exec_connectionsencrypt_option 仍然显示TRUE.

System.Data.SqlClient 的所有版本都支持连接加密吗？还是有一些旧版本不支持？

之所以问这个问题，是因为我想防止迁移后某些应用程序可能因为其客户端驱动程序不支持加密而停止工作的情况。

我们有一台 256 GB RAM 的机器。
SQL Server 的最大服务器内存设置为 180 GB。

在 180 GB 中，SQL Server 通常使用：

• 数据库缓存内存 - ~ 140-150 GB
• 计划缓存 - ~ 10 GB
• 被盗服务器内存：~ 30 GB
• 可用内存：~ 9 GB
• 授予的工作区内存：这通常很低，峰值可以是 0.5-1 GB

缓冲区缓存命中率 - 一直徘徊在 99.9% 以上。
页面预期寿命是相当高的数字。

数据库数据文件的总大小 - 650 GB。
数据增长率约为每天 500-1500 MB（但是！旧数据每 6-8 个月被删除一次，所以基本上数据文件的增长速度要慢得多）。

问题

需要将 SQL Server 迁移到另一台计算机。面向 SQL Server 2022 推出时。

它是混合 OLTP 和 OLAP 类型的工作负载，许多应用程序使用相同的数据库；大部分 RAM 被缓存的数据库页面使用，这意味着 SQL Server 不必一直从磁盘读取它。

感觉新机器 128 GB 就足够了，最大服务器内存设置为 ~ 110 GB，为操作系统留下 18-13 GB。

目标服务器将位于 Azure VM 中，您无法在其中独立扩展 vCPU 和 RAM。与 128 GB 机器相比，具有 256 GB RAM 的机器具有两倍的内核数，从而导致相当大的成本差异。我认为与最初预计的相比降低成本，从长远来看可能对我有好处，所以我认为值得探索。在 Azure 中，您可以根据需要随时进行扩展。

如果你是我，你将如何科学地向你的经理证明，将内存减半不会降低 SQL Server 的性能，不会破坏缓冲区缓存命中率或类似的东西？

我知道对于 DBA，考虑到工作负载不会改变，将这台机器缩小到 128 GB 看起来很安全。但是，根据您的经验，您将如何说服经理呢？

根据以下链接，NULL值会占用一些存储空间：

https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server
https://www.sqlservercentral.com/forums/topic/null-storage-2

如果列具有固定宽度数据类型 -NULL占用列的长度

char(10) NULL takes 10 bytes
INT NULL takes 4 bytes

如果列具有可变宽度 -NULL占用 0 个字节

varchar(1000) NULL takes 0 bytes (+ 2 bytes of varchar column overhead ?)

此外，还有一个可以为空的列的开销。链接中的信息有点矛盾。
任何人都可以对此有所了解吗？

以下是我的问题：

1. 有人说具有可空列的开销是每行 1 位，有些人说它是每行 1 字节
   哪一个是真的？我假设每行 1 位（所以 8 行占 1 个字节），对吗？

2. 对于 varchar 中的 NULL 值，每行仍有 2 个字节的开销 - 真的是这样吗？

3. 最重要的问题：
   我有一个包含约 2.5 亿行数据的表，总大小约为 115 GB
   我使用下面的代码添加了 7 列，在每个命令后使用 sp_spaceused 检查表的大小

添加的列：

alter table MyTable add TestVarchar10 varchar(10) NULL      
alter table MyTable add TestVarchar1000 varchar(1000) NULL  
alter table MyTable add TestVarchar10DefaultTest varchar(10) NULL default 'test'        
alter table MyTable add TestVarchar10DefaultTestWithValues varchar(10) NULL default 'test' with values  

alter table MyTable add TestINT int NULL
alter table MyTable add TestINTDefault99 int NULL default 99
alter table MyTable add TestINTDefault99WithValues int NULL default 99 with values

并且任何其他列，根本没有增加表的大小这
怎么可能？我希望表大小会随着NULL存储占用空间而改变

在频繁执行的存储过程中（有时高达每秒 10-15 次），有两条特定语句显示在按累积 CPU 影响排名前 10 的最重查询中

这些是用于创建 #Temp 表的 DDL 语句：

1.

SELECT cast(0 as int) as rowId
     , Column1 as tColumn1
     , Column2 as tColumn2
     , ...
     , Column14 as tColumn14
     , cast(0 as datetime) as tUTC
     , -1 as tRefId
INTO #TempTable1
FROM Table1 WITH(NOLOCK)
WHERE 0=1

SELECT    tColumn1, ..., tColumn14, tUtc, tRefId
INTO    #TempTable2
FROM    #TempTable1
WHERE    0=1

上述任何 DDL，平均占用 10-15 毫秒 CPU 时间，仅用于创建 #temp 表
更改存储过程的逻辑，使其不创建临时表，不是一种选择

问题：就 CPU 时间而言，如何加快临时表的创建时间？

我有一个简单的分组查询，效果很好，直到我再添加一个联接：

select 
   [ca].Value,
   [c].cID, 
   [c].Name

from ReportingDB..Table1 [t1]

join MainDB..Companies [c] on
    [t1].CompanyID = [c].cID
    and [c].cID not in (1)

join MainDB..CompanyAttributes [ca] on -- this is the join that causes trouble
    [t1].CompanyID = caCID
    and caAttr = 26

group by [ca].Value, [c].cID, [c].Name

信息：

Companies 表是一个“查找”表，有 2254 行，cID是 PK
CompanyAttributes与多对一关系Companies并有 4055 行
Table1与表有多对一关系Companies，有 3,485,150 行和

估计执行计划看起来并不异常。

• 当我尝试运行查询时，它没有完成，1 小时后我停止它，所以看不到实际执行计划发生了什么
• 实时查询统计使我的 SSMS 挂起
• 如果删除“group by”子句，它会非常快地开始毫无问题地获取行。或者当最后一个连接被删除时，它也可以正常工作 - 使用分组
• 服务器不忙，有足够的资源，启动查询时我没有看到明显的 CPU 上升
• 看sys.dm_exec_requests，wait_type 为 NULL，cpu_time并且logical_reads继续增长，对于运行查询的会话

原始查询运行超过 1 小时而未完成的根本原因是什么？

我自己解决了性能瓶颈（请参阅我的答案），但不明白究竟是什么导致原始查询运行 1 小时并且没有在体面的服务器上完成，查询的表并不大。预计原始查询将在不到 1 分钟的时间内完成。

我对表锁升级有几个问题，请确认或反驳：

1. 查询中的索引扫描运算符导致表锁升级？总是与否（小桌子）？

2. 当共享锁升级到表级别时，它会阻止小的更新和删除？也插入？

3. 是否可以获得表lock_escalation事件的计数，按登录名然后按表分组？例如：

login1 - table1 - 11
login1 - table2 - 5
login2 - table1 - 29
等等...

如果我没记错的话，扩展事件似乎只允许一个级别的分组

可更新的聚集列存储索引 (SQL Server 2016+) - 它是否有最大行大小 (8060) 限制，或者您可以在其中放置您想要的任何行大小？

换句话说，是否有可能通过可更新的 CCI 得到以下错误？

无法创建大于允许的最大行大小 8060 的大小为 8061 的行。

我很少有索引高度碎片化（超过 90% 的碎片化）且大小足够大（数十 GB）的表。对这些进行碎片整理的目标是减少上述表格的空间消耗。

我想避免执行 REBUILD 操作，因为它太耗费资源。还试图避免记录到事务日志和日志增长，以及因此导致大的事务日志备份大小。

这就是为什么选择 REORGANIZE 操作而不是 REBUILD 来处理高碎片的原因。

关于 REORGANIZE，有以下问题：

1. 如果同时在单独的索引上运行多个 REORGANIZE 操作（比如 10+，在单独的 SSMS 窗口/会话中） - 它会导致数据损坏或任何其他已知问题吗？

2. REORGANIZE 操作是否以任何方式/根本记录到事务日志？

3. REORGANIZE 持有什么样的锁？哪些查询会因此受到影响？

在我的案例中，进行碎片整理的唯一原因是减少存储空间消耗，它确实有帮助（例如索引之前是 18 GB，然后变成了 8 GB，等等）。就我而言，这比订购额外的存储空间更容易。

我团队中的一些开发人员知道具有扩展权限的 SQL 帐户的密码
我们希望在任何开发人员使用任何这些 SQL 帐户进行连接时进行跟踪并收到警报

我们即将logon trigger为每次登录尝试实施该功能，评估登录的属性，并在它们符合特定条件时发送电子邮件报告。逻辑如下：

如果 original_login() in (...SQL accounts list here...) 并且：
a) 客户端 IP 地址是 192.168.xx VPN 子网（没有生产应用程序从该子网连接，只有开发人员可以）或
b) 客户端主机名（... Dev 的主机名称列表...）或
c）（SSMS、az-Data 等）中的客户端应用程序名称
exec sp_send_dbmail（通过电子邮件向 DBA 发送报告）

触发器将具有“执行为”子句并代表 SQL 登录名运行，该登录名的唯一权限是发送 db 邮件电子邮件

在 Production 上启用这种登录触发器会产生什么不良副作用？
它会减慢登录过程或导致任何其他问题吗？

ps 我知道DAC以及如何使用它。
测试使用连接DAC并依靠它来帮助我在出现任何问题时禁用触发器

我在 SSMS 对象资源管理器中连接了几个 Azure SQL Server（它们在上面托管了几个 Azure SQL 数据库），并看到该版本是 12.0.2000.8

select @@Version如下图所示：

Microsoft SQL Azure (RTM) - 2021 年 2 月 20 日 12.0.2000.8 17:51:58 版权所有 (C) 2019 Microsoft Corporation

为了比较，我的另一个盒子是 SQL Server 2017，它显示的版本是 14.0.3370.1

我希望将 Azure SQL Servers 视为 15.0.something（对应于 SQL Server 2019），但它们似乎对应于 SQL Server 2014（版本 12）

问题：

Azure SQL Server 不应该自动升级到最新版本吗？
如何手动将它们升级到版本 15？

我想在生产 SQL Server 上向 Developers 组授予以下权限：

VIEW SERVER STATE
VIEW DEFINITION (server level)

这样做是为了让他们能够查询一些动态管理视图和函数，查看性能数据，以及查看所有存储过程和函数的代码（定义）

是否有任何缺点或任何原因在某些情况下不好？
授予上述权限的任何可能的不良副作用？

第二个问题 - 如果我VIEW DEFINITION在 master 数据库中授予，这使得它成为服务器级别并且不必在任何用户数据库中授予？与SHOWPLAN?

上周在其中一个 SQL Server 上出现问题，CPU 开始燃烧超过 80%（正常为 10-30%）
这持续了大约 2 小时，直到我手动故障转移到 AG 中的辅助副本（这已经解决了问题）

问题开始：12:15
问题结束：14:15（手动 AG 故障转移后）

服务器信息：

SQL Server 2017  
32 logical processors (max DOP = 8)  
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

问题开始前与问题开始后相比，以下指标没有明显变化

• 用户连接数/秒（平均 200-300）
• 批处理请求/秒（平均 200 次及以下）
• 数据库缓存内存（~150 GB）

低于指标峰值显着，这对于该服务器来说并不典型（通常这些指标很低）：

• 中央处理器 ( 超过 80 % )
• 内存授予待定
• 锁定等待/秒，平均。锁定等待时间，死锁
• 锁存等待时间
• 授予的工作区内存和保留的服务器内存

查询：

当问题开始时，我没有注意到此服务器的工作负载发生变化
开发人员还确认应用程序完成了他们通常的工作并且正在运行通常的查询，应用程序负载没有峰值

在这个“高 CPU 使用率”问题期间，CPU 的前 10 个查询看起来并不异常
即使 CPU 正常，我们通常看到的前 10 个查询都是相同的（10-30 %）

问题：

问题似乎出在几个相关的存储过程中，该应用程序通常运行 1-4 次/秒，并且通常在 50 毫秒内完成，但是在问题期间，任何时候我检查过 sys.dm_exec_requests（也使用了exec ViewSessionsConnections 'running' https ://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql），有来自 1 个应用程序的 50-70 个会话，所有这些都试图完成上述程序，而且速度很慢

在监控工具中按持续时间查看前 10 个查询时，前 1 和 2 是上述过程中的两条语句 - 它们没有消耗大量 CPU，但有过多的等待（RESOURCE_SEMAPHORE、LCK_M_IS）

通常这些在 10 毫秒或更短的时间内完成，每秒执行 1-4 次并且不会引起任何问题，现在这些开始每 1 次执行的持续时间为 4000-8000 毫秒，这就是问题所在

RESOURCE_SEMAPHORE 绝对不是此服务器的典型情况，但在问题期间，它处于最高等待状态（RESOURCE_SEMAPHORE - 等待授予内存的查询；2 小时内总计 135400234 毫秒；平均 4174 毫秒）

Granted Workspace Memory在 SQL Server 中，Reserved System Memory在问题期间从 0 GB 飙升至 ~110 GB

问题：

1. 你对上面有什么想法和经验？

2. 常量 RESOURCE_SEMAPHORE 等待和 Memory Grants Pending 是否会导致 CPU 压力仅仅是为查询分配工作空间内存？因为在问题期间查看 CPU 的前 10 个查询时，CPU 数量看起来正常/正常

3. 鉴于一直是 180 GB 和~ 150 GB ，问题开始时怎么会这样Granted Workspace Memory并开始消耗 ~ 112 和 110 GB？它是否过度使用内存或类似的东西？Reserved Server MemoryMax Server MemoryDatabase Cache Memory remained

4. 为什么通常在几个月内 10 毫秒内完成的 SP 中的语句会开始经历 RESOURCE_SEMAPHORE 等待并在 4000-8000 毫秒内完成？

5. 如何在不手动故障转移到辅助副本的情况下以更外科手术的方式解决问题？如何让查询平静下来并将其恢复到 10 毫秒？需要删除计划，或者重新编译查询等？最好的方法是什么？

6. Brent Ozar First Responder Kit 或其他诊断程序 - 在性能问题期间应按什么顺序执行，以便更好地了解发生了什么？

我对生产有以下两个疑问：

查询 #1：根据“日期”列从核心表“Table1”中删除旧历史数据的脚本

有约 20 个表通过外键通过级联规则指向“Table1”，因此当您从“Table1”中删除时，数据也会从约 20 个其他表中删除

脚本一次删除 50 行数据，批处理间隔为 1 秒
当批处理只有 50 行时，脚本不会导致排他锁升级为表级锁；它只放置行/页级别 (X) 锁

查询 #2：从“Table1”、“Table2”、“Table3”和其他一些表中提取数据的生产应用程序

这是一个重读查询，它在表上放置共享锁。它不做任何插入/更新/删除

问题：

有时，当Query # 2（Prod 应用程序）读取数据时会发生死锁，而我同时Query # 1在 SSMS 中运行（脚本删除旧数据）。Query # 2始终是受害者，据我了解，这是因为它没有对事务日志进行任何更改，而Query # 1确实

为了解决这个问题，我添加set deadlock_priority -10了Query # 1，希望它会使Query # 1任何可能的死锁成为受害者

首先它似乎有效，Query # 1成为死锁情况的受害者，在删除一些批次时抛出错误 1205。

但是-最近我发现有时Query # 2仍然会成为受害者

问题：

这怎么可能Query # 2有时甚至在我已经成为受害者set deadlock_priority -10之后Query # 1？

有没有办法解决它并确保Query # 1在 100% 的情况下成为受害者？

更新：在下面添加图表

我在表上创建了一个 DML 触发器（AFTER INSERT、UPDATE、DELETE）
触发器的逻辑大约需要 30 秒才能执行

因此，即使您更改 1 行，由于触发器执行，也需要 30 秒左右
开发人员问我“触发器是否有可能是触发并忘记操作？”

我说不是，但真的是这样吗？

问题：

可以在“异步”模式下执行触发器吗？
应用程序在几毫秒内更新了几行，并认为事务已完成，然后触发器在后台静默执行？

我知道从一致性的角度来看，这实际上看起来并不好，但是，这可能吗？

我有一个设置，其中 3 台服务器组合成可用性组

所有 3 台服务器都有本地直接连接的 SSD 驱动器，用户数据库文件托管在这些驱动器上

但是 AG 中每台服务器的系统数据库（master 和 msdb）都托管在通过网络访问的 SAN 设备上

尚未将它们移动到本地 SSD 驱动器

问题：

在任何服务器和 SAN 设备之间的网络连接丢失（电缆损坏、NIC 损坏、一些临时网络故障等）的假设情况下，

• 该服务器上的 SQL Server 服务会立即脱机或停止正常工作吗？

• 或者如果 master 和 msdb 在网络中断之前缓存在 RAM 中，它会继续工作一段时间？

我们有一个配置文件E:\SQL_Server_2016_Install\MyConfigFile.ini，如下所示：

[OPTIONS]
ACTION = "Install"
FEATURES = SQLENGINE
INSTANCENAME = MSSQLSERVER
AGTSVCACCOUNT = "MyDomain\MyAccount"
SQLSVCACCOUNT = "MyDomain\MyAccount"
QS
IACCEPTSQLSERVERLICENSETERMS

然后使用以下批处理文件安装 SQL Server：
E:\SQL_Server_2016_Install\Setup.exe /ConfigurationFile=MyConfigFile.ini /InstanceName="TestSQLServer"

问题：

参数是否会/InstanceName="TestSQLServer"覆盖我们在配置文件中的内容（/InstanceName = MSSQLSERVER（默认实例）），并将 SQL Server 安装为命名实例TestSQLServer？

虚拟机大小DS3（在老一代大小下）
Azure 门户（部署 VM 时）显示DS3最多支持 16 个数据磁盘/最多12800 IOPS- 很好，

但我感兴趣的是它的本地/临时存储 28 GB D：驱动器
文档显示此本地/临时驱动器是 SSD，但我似乎找不到有关此驱动器可以处理多少 IOPS 的信息？

更具体地说，如果我的 TempDB 对 IOPS 的要求最高1900，那么 VM 上的 D: 驱动器可以DS3处理该要求吗？
从这个来源https://learn.microsoft.com/en-us/azure/virtual-machines/sizes-previous-gen我可以猜到 D: 是3200IOPS 但不确定我是否理解正确......