J Weezy's questions

在 SQL Server 2014 上工作。

我注意到过去一天内数据库上的一个日志文件 (60GB) 已填满。据我所知，我是唯一使用此数据库的开发人员，并且我已经有一个多星期没有在数据库上执行任何事务了。我问 DBA 是否有其他人在使用该数据库，但他们不这么认为。DBA 提到自动索引重建作业可能会填满日志。

我的问题是，每小时运行的自动索引重建作业能否填满日志文件？我在这里的假设是索引不会碎片化，因此重建索引不会有任何工作。因此，日志文件不应填满。这个假设是错误的吗？我是否遗漏了 SQL Server 索引重建的工作方式以及这可能如何影响日志文件？

更新

我们正在使用 Ola 的解决方案：点击这里

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\DirectoryPath\',
@MirrorDirectory = N'\\DirectoryPath\',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 48,
@MirrorCleanupTime=24,
@CheckSum = 'Y',
@LogToTable = 'Y',
@Compress = 'Y'

我之前在 Stack Overflow 上问过这个问题，但我不确定是否应该将它迁移到 DBA Stack Exchange。

https://stackoverflow.com/questions/55186094/error-when-running-sql-server-2014-64-bit-import-export-wizard

我尝试重新启动计算机但没有成功。此外，DBA 能够使用相同的方法从他的计算机迁移该表。我怀疑我的电脑可能有问题，但我不知道应该采取的故障排除步骤。唯一能想到的就是卸载重装SSMS，好像有点偏激。

话虽如此，我昨天确实执行了 Windows 更新。此外，本周早些时候我对 VS2017 进行了更新，现在 SSIS 脚本任务/组件的 VSTA 编辑器将无法打开。我知道在重新安装 MS 产品时，我需要让安全管理员禁用病毒扫描程序，因为这会阻止我重新安装的能力。所以，我想知道病毒扫描程序是否会干扰 Windows 更新。

问题：是否有更好的索引策略或查询 SELECT 可用于查找一个大数据集与另一个大数据集？或者，我应该看看将查找维度表放在内存中（全部 125 GB）吗？

服务器配置：

• 该服务器是运行在 VMWare 之上的虚拟服务器，因此可以在后台添加额外的硬件而无需重新安装操作系统
• Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 版权所有 (C) 2017 Microsoft Corporation Standard Edition（64 位）在 Windows Server 2016 Standard 10.0（Build 14393：）（Hypervisor）
• 注意：我之前使用的是 2014 Enterprise - 我询问过为什么我被安排在 Standard 上。
• 只有一个实例运行 2 个数据库：我的和 DBA
• 2 个文件组，每个有 1 个文件：PRIMARY（系统表：非默认）和 SECONDARY（非系统表：默认）。SECONDARY 旨在可扩展以在添加更多 CPU 后容纳更多文件。最初创建文件组时，服务器只有 2 个 CPU
• 8 GB 内存
• 500 GB 磁盘存储 (ISCSI SAN)
• 4 个 CPU（我假设是英特尔）

IIS Exchange Server 日志表架构：

CREATE TABLE [FWY].[ExchangeServerLogTest](
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceFileName] [varchar](50) NOT NULL,
    [SourceServer] [varchar](9) NOT NULL,
    [SourceService] [varchar](6) NOT NULL,
    [EventOccuranceTs] [datetime] NOT NULL,
    [ServiceType] [varchar](50) NOT NULL,
    [UserNameType] [varchar](25) NOT NULL,
    [DomainId] [varchar](50) NULL,
    [DomainName] [varchar](255) NULL,
    [UserNameToLookup] [varchar](255) NOT NULL,
    [UserAgent] [varchar](255) NULL,
    [OutsideProtocolId] [varchar](10) NOT NULL,
    [OutsideIp] [varchar](39) NULL,
    [OutsideIpHex] [varbinary](16) NULL,
    [InsideProtocolId] [varchar](10) NOT NULL,
    [InsideIp] [varchar](39) NULL,
    [InsideIpHex] [varbinary](16) NULL,
    [DeviceId] [varchar](32) NULL,
    [DeviceType] [varchar](25) NULL,
    [DeviceModel] [varchar](75) NULL,
    [AsOfDt] [date] NULL,
    [OutsideProtocolKey] [int] NULL,
    [InsideProtocolKey] [int] NULL,
 CONSTRAINT [PK_ExchangeServerLogTest] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

非聚集索引：

CREATE NONCLUSTERED INDEX [NCIDX_ExchangeServerLogTest_InsideOutsideProtocolKeyIpHexInclRowKey] ON [FWY].[ExchangeServerLogTest]
(
    [InsideProtocolKey] ASC,
    [OutsideProtocolKey] ASC,
    [InsideIpHex] ASC,
    [OutsideIpHex] ASC
)
INCLUDE (   [RowKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

IP GeoLocation 数据供应商表架构

CREATE TABLE [DE].[IpGeoLocation](
    [CreateTs] [datetime] NOT NULL,
    [CreateBy] [varchar](50) NOT NULL,
    [CreateSequenceKey] [int] NULL,
    [UpdateTs] [datetime] NULL,
    [UpdateBy] [varchar](50) NULL,
    [UpdateSequenceKey] [int] NULL,
    [ActiveInd] [int] NOT NULL,
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [VendorKey] [int] NULL,
    [VendorTypeKey] [int] NULL,
    [DimensionTypeKey] [int] NULL,
    [ProtocolKey] [int] NULL,
    [ProtocolId] [varchar](10) NOT NULL,
    [EffectiveStartDate] [date] NULL,
    [EffectiveEndDate] [date] NULL,
    [NetworkStartIp] [varchar](39) NOT NULL,
    [NetworkStartIpHex] [varbinary](16) NULL,
    [NetworkEndIp] [varchar](39) NOT NULL,
    [NetworkEndIpHex] [varbinary](16) NULL,
    [Country] [varchar](255) NOT NULL,
    [Region] [varchar](255) NOT NULL,
    [City] [varchar](255) NOT NULL,
    [ConnectionSpeed] [varchar](255) NOT NULL,
    [ConnectionType] [varchar](255) NOT NULL,
    [MetroCode] [int] NOT NULL,
    [Latitude] [numeric](6, 3) NULL,
    [Longitude] [numeric](6, 3) NULL,
    [PostalCode] [varchar](255) NOT NULL,
    [PostalExtension] [varchar](255) NOT NULL,
    [CountryCode] [int] NOT NULL,
    [RegionCode] [int] NOT NULL,
    [CityCode] [int] NOT NULL,
    [ContinentCode] [int] NOT NULL,
    [TwoLetterCountry] [varchar](2) NOT NULL,
    [InternalCode] [int] NOT NULL,
    [AreaCodes] [varchar](255) NOT NULL,
    [CountryConfidenceCode] [int] NOT NULL,
    [RegionConfidenceCode] [int] NOT NULL,
    [CityConfidenceCode] [int] NOT NULL,
    [PostalConfidenceCode] [int] NOT NULL,
    [GmtOffset] [varchar](255) NOT NULL,
    [InDistance] [varchar](255) NOT NULL,
    [TimeZoneName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_IpGeoLocation] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

非聚集索引：

CREATE NONCLUSTERED INDEX [NCIDX_IpGeoLocation_ProtocolKeyNetworkStartEndIpHexIncRowKey] ON [DE].[IpGeoLocation]
(
    [ProtocolKey] ASC,
    [NetworkStartIpHex] ASC,
    [NetworkEndIpHex] ASC
)
INCLUDE (   [RowKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

使用 .NET 的 System.Net 类将 IP 地址转换为其十六进制值：Ipaddress.Parse( IpAddress ).GetAddressBytes()。我用 SSIS 加载数据文件，我有一个脚本组件，它返回 ProtocolId 和 IP 地址作为字节数组，它作为 DT_BYTE 进入 SSIS 并映射到 SQL Server VARBINARY(16) 字段（字节数组隐式转换为十六进制值）。

查找 IP 地址范围

我有两个数据集：IIS Exchange Server IP 日志记录和第三方供应商提供的 IP GeoLocation 数据；其中地理位置涵盖了一系列 IP 地址。我需要从日志文件中查找 IP 地址并获取其 GeoLocation。两个数据集都适用于 IPv4 和 IPv6，并且 IP 地址以字符串格式接收。当我加载数据时，我将 IP 地址转换为十六进制值 [VARBINARY(16)]，以便我可以查找 IP 地址 GeoLocation。

这里的问题是我正在加载大量记录。目前，供应商提供了近2亿个IP地址Geolocations（即维度查找表）。我从一开始就知道在所有阶段（即硬件配置、表分区和索引策略）都需要进行性能优化。我加载了一周的样本日志数据，大约有 1.5 亿条记录。

注意：日志文件在大约 90% 的记录被忽略的地方被解析——我们只加载了 10% 的记录，所以这里没有性能提升

我在 ExchangeLogs 表上创建了以下索引：

1. 名为 RowId 的整数 IDENTITY 列上的聚集索引
2. ProtocolId（即表示为整数的 IPv4 或 IPv6）、IpHex 上的非聚集索引；其中包含 RowId

我在 IPGeoLocation 表上创建了以下索引：

1. 名为 RowId 的整数 IDENTITY 列上的聚集索引
2. ProtocolId（即表示为整数的 IPv4 或 IPv6）、StartIpHex 和 EndIpHex 上的非聚集索引；其中包含 RowId

在搜索 IP 地理定位时，我按如下方式加入两个数据集：

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.InsideProtocolKey = DE.ProtocolKey
    AND T.InsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Query Execution Plan：估计的InsideIp查询执行计划

实际查询执行计划：等待查询完成

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.OutsideProtocolKey = DE.ProtocolKey
    AND T.OutsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Execution Plan：估计OutsideIp查询执行计划

实际查询执行计划：未完成

注2：必须包含ProtocolId，否则每次IP查找有两种结果：一种用于IPv4，一种用于IPv6。

这似乎是一个非常有效的执行计划，考虑到 95% 的成本用于索引查找，另外 2% 用于索引扫描- 97% 归因于索引工作。

日志文件的每一行都包含内部和外部 IP 地址。对于加载的示例数据：

1. 内部 IP 列表包含 3 个不同的 IP 地址。
2. 外部 IP 列表包含大约 60,000 个不同的 IP 地址。

结果：

1. 内部 IP 列表上的 SELECT 大约需要 9 分钟才能完成。
2. 外部 IP 列表上的 SELECT 在允许其运行 16.25 小时（一夜）后被停止。

我没有对日志表或 IP GeoLocation 表进行分区。这可能会通过两个单独的 LUN 流式传输数据来提高性能，但我仍在尝试从我们的 IT Ops 小组获取硬件配置规范（他们刚刚配置了新服务器，所以我还没有该信息）。

Microsoft Technet 文章建议创建一个辅助文件组作为默认文件组（请参阅下面的参考资料）。辅助文件组应该有许多文件，比如说四个，每个文件都放在不同的磁盘上。作为同事的补充经验法则，文件数应等于 CPU 内核数。

我的理解是，此设置非常适合机械旋转磁盘驱动器，因为旋转磁盘驱动器比固态驱动器慢得多，因此可以通过从多个磁头流式传输数据来提高性能。这种理解是否正确？

如果是，那么我的问题是基于成本的优化器是否考虑了较新的固态驱动器？切换到新的固态驱动器后，旋转硬盘驱动器的性能瓶颈似乎消失了。我们的 IT 运营团队告诉我，虽然我目前分配了一个虚拟驱动器，但数据实际上存储在一个 ISCSI SAN 上，它有多个固态驱动器。

这个问题旨在尝试回答这种规模的大型数据库的最佳设置是什么：

1. 我应该有一个只有一个文件的默认辅助文件组吗？
2. 我是否应该有一个默认的辅助文件组，其中包含的文件数等于 CPU 上的内核数？
3. 使用固态驱动器时对数据库表进行分区是否可以提高性能？

我目前正在进行的项目需要一个扩展的数据库，该数据库的大小将达到几 TB，用于存储大量日志数据。一个一周的样本大约有 1.5 亿条记录，我们需要存储滚动的 3 年日志。因此，我现在正在查看长时间运行的查询以查找数据。我已经将索引调整到几乎所有工作都归因于非聚集索引查找的程度；优化器不建议添加缺失的索引。

笔记

Microsoft SQL Server 上的许可目前是按 CPU 内核进行的。因此，在这个问题上投入更多核心是敏感的，特别是如果这不会提高性能。

此外，我目前正在 SQL Server 2014 上进行开发，但将迁移到 SQL Server 2017 以进行开发和生产。

更新 1

该项目将每晚加载日志，我预计很少（可能没有）更新或删除，因为日志根本不会改变 - 所以它们不会被重新加载。出于分析目的，将读取其他所有内容。

系统表的 PRIMARY 文件组，其中 SECONDARY 默认文件组用于其他所有内容。这样做的原因由这个问题底部引用的链接解释。

将为表分区创建单独的文件组。数据库中还有其他足够小的表，它们将驻留在 SECONDARY 文件组中 - 我只对两个表进行分区，其中一个超过 1 亿条记录（按 IDENTITY 行号分区），另一个将进入数十亿条记录（按时间 [每月] 划分）。

我计划在 3 年内按月进行分区。因此，将有 36 个分区。我会为每年创建文件组，然后将12个文件放入相应的年度文件组中。分区策略是为了减少读取时间，因为出于分析目的将进行大量数据扫描。年度文件组策略严格来说是为了便于 DBA 维护，他们可以通过删除单个文件组来删除一年的数据。

参考：

SQL Server 最佳实践设置默认文件组

我正在尝试搜索 IPGeolocation 数据，该数据由数据供应商提供为 IPv4 和 IPv6 的开始和结束范围。但是，当我转换提供的 IP 时，我得到的结果不正确。

为了按范围搜索，我将VARCHAR(39)IPv6 的表示形式转换为VARBINARY（即十六进制），然后使用BETWEEN运算符查看搜索到的十六进制值是否在特定范围内。

查询逻辑：

SELECT CONVERT(VARBINARY(16), '2600:8800:6a06:2000:1d29:3b7:8c7c:271b')

搜索：

IP: 2600:8800:6a06:2000:1d29:3b7:8c7c:271b

IpHex: 0x323630303A383830303A366130363A32

结果 1

启动：2600:8800:490:0:0:0:0:0

IpStartHex: 0x323630303A383830303A3439303A303A

IpEnd: 2600:8800:77f:ffff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A3737663A6666

结果 2

启动：2600:8800:6a06:1d01:0:0:0:0

IpStartHex: 0x323630303A383830303A366130363A31

IpEnd: 2600:8800:6a06:24ff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A366130363A32