我有一个查询应用程序扫描带有文本字段的整个表格。
查询执行了这么多次读取：
扫描计数 1，逻辑读取 170586，物理读取 3，预读读取 174716，lob 逻辑读取 7902578，lob 物理读取 8743，lob 预读读取 0。
如果我从选择中删除文本字段，则读取内容如下：
扫描计数 1，逻辑读取 170588，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。
我不明白的是 lob 读取的工作原理：
如果我将逻辑读取与 lob 逻辑读取相加，我总共得到8.073.164 逻辑读取，如果我是正确的，大约是 64GB。
但是整个数据库只有7GB！
我可能遗漏了一些关于添加逻辑读取和 lob 逻辑读取的内容。
lob逻辑读取的数量实际上代表什么？
我不断从我的 SQL 监控服务中收到以下警报：
错误：41145，严重性：16，状态：1。无法将数据库加入可用性组。数据库已加入可用性组。这是一条情报信息。无需用户操作。
这个错误大约每 2 小时就会出现一次，但我不明白为什么。
此外，在 SQL 错误日志中，我有数千个我从未在其他 SQL Server 安装中注意到的日志条目：
这是在一个新的 SQL Server 安装上，它将成为我的生产环境，所以我想在我们执行服务器切换之前确保它没有任何问题。
我们计划在此物理架构上安装 SQL Server：
我一直认为，为数据文件、日志文件、临时数据库文件等提供多个磁盘是一种常见的最佳做法......
但这种最佳实践是指准系统安装。
所以，我要问的是：在上述架构（如果有的话）上这样做的实际好处是什么？
我创建了一个带有INSTEAD OF触发器的索引视图，但是当在基表级别发生插入时它不会触发。
这是正常插入生成的计划（注意聚集索引插入，VW_X.VW_CI其上是表示视图的聚集索引）
有什么方法可以跟踪视图上的插入，尽管插入并没有真正发生在那里，而是在创建视图的基表中发生？
我有一个查询 1000 万行表的应用程序。
查询正在按索引的整数列进行过滤。此列仅包含NULLs 并且查询始终查找非NULL值，因此它应该进行得很快。
问题是应用程序发送查询，sp_prepare然后sp_execute它基本上是OPTIMIZE FOR (UNKNOWN)（有关此的更多信息，请参阅Erik 的回答）。
简而言之：
sp_execute从应用程序=>optimize for (unknown)NULL)，因此估计 = 行数由于我无法更改应用程序代码，因此我认为计划指南OPTIMIZE FOR (@P0 = 1)可以解决所有问题。
我从扩展事件会话中获取了查询文本（从 sql_text 列复制，但删除了该(@P0 bigint)部分）
然后我像这样创建了计划指南：
EXEC sp_create_plan_guide
@name = N'Test_1',
@stmt = N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0 ',
@type = N'SQL',
@params = N'@P0 bigint',
@hints = N'OPTION (OPTIMIZE FOR (@P0 = 1))'
GO
我还创建了一个在语句末尾没有空格的计划指南，只是为了确定，但应用程序没有使用这些空格。
奇怪的是，如果我自己运行查询，计划指南会被考虑并且它的执行就像我想的那样。
declare @p1 int;
exec sp_prepare
@p1 output,
N'@P0 bigint',
N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0 ';
exec sp_execute @p1, 123123;
exec sp_unprepare @p1;
我还注意到，当我在 SSMS 中运行上面的查询时，它在扩展事件中显示为sql_batch而不是rpc. 也许这与问题有关？
我目前在 Windows 10 上使用最新版本的 SSMS (17.8.1)，每次我尝试创建数据库图表时，它都会崩溃而没有给我任何错误消息。我已经尝试重新安装 SSMS，但问题仍然存在。
我有一个日志表，目前保存着数百万条记录。我想在那个表上启用分区，所以我现在做的是：
T1）将当前日志表中的数据复制到新的分区表。接下来的步骤是将最后剩余的记录从 to 复制T1并重Tnow命名这两个表，以便应用程序开始写入新的分区表。
当然日志表是经常访问的，所以我的问题是：
如何确保在此过程中不会丢失任何数据？我可以做到让用户不会注意到任何事情，或者我是否必须在这短暂的时间内停止应用程序？或者我可以阻止该表以便应用程序继续运行吗？如果是这样，怎么做？
我有一个包含 2200 万条记录的表。
我注意到一列的统计数据有偏差，即使该列具有恒定分布：事实上，每个值都重复了两次。
为了帮助您想象这种情况，请考虑一个表，其中包含另一个表的每个唯一 ID 的签入和签出日期（2 条不同的记录）。
这些是完整扫描的统计数据：
这些是自动采样的统计数据：
为什么会有这种奇怪的行为？是否有一些经验法则来确定正确的采样率或是否需要全扫描？我应该为某些统计信息创建一个带有全扫描的统计更新作业吗？如果是这样的话，我怎么知道哪些统计数据需要这种处理？
附加信息：
numeric(14,0) NULL
我发现，通过查询存储，一个查询平均执行 297582 次逻辑读取。
我想看看我是否能够稍微调整一下该查询，然后尝试再次执行该查询以查看是否有任何改进。
问题是我在缓存计划中找不到编译参数值。
我错过了什么吗？也许是某些原因/设置阻止了参数值的缓存？
即使以 XML 格式打开执行计划，我也找不到参数。
附加信息：查询由第三方应用程序执行，该应用程序准备语句，然后使用sp_prepare和执行它们sp_execute。
查询店铺：
计划缓存 DMV：
我有一张桌子，上面有nonclustered index一张桌子datetime2。
在同一张表上，我有一个字段(char[1])用于逻辑删除记录，并且可以有 2 个不同的值：（A活动）或D（已删除）。
datetime2字段设置为的记录有 451047 条，NULL但标记为 的只有 7095条A。
应用程序中的每个查询仅查找活动记录，因此，NULLs在日期时间字段中查找的每个查询都得到了非常糟糕的估计，因此执行计划也很糟糕。
简单示例：
然后我决定创建一个过滤的非聚集索引，但估计仍然不正确：
看起来我仍然得到旧的估计，即使查询正确使用了过滤索引。有谁知道这种行为的原因？
这些是过滤索引的新统计信息：
表定义：
CREATE TABLE [TYDATPRD].[HAND00F](
[STDRECSTS] [char](1) NULL,
[HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
[HDCHKINDT] [datetime2](7) NULL,
--lots of other columns which I don't think are needed
CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED (
[HDHAND] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]
非聚集过滤索引定义：
CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] (
[HDCHKINDT] ASC
)
WHERE [STDRECSTS]='A'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
我正在尝试在 SQL Server 2016 上设置多服务器管理。我有两台服务器，一台主服务器和一台目标服务器。我创建了一个测试作业，它执行一个简单的选择语句，然后发送一封电子邮件。我希望该作业同时在主服务器和目标服务器上运行，但似乎我做不到。
我不敢相信这是不可能的（试图用谷歌搜索但没有找到任何东西）。
我有一个累积快照事实表，用于跟踪终端中容器的进入和退出。
集装箱可以通过3 种不同的方式进出，所以我想创建一个特定的维度表，列出这 3 种可能的方式（火车、船只或卡车）。
然后我读了这篇文章，基本上说这种技术是错误的，但我不明白为什么。
第一篇：
有时，当事实表中有一长串事实稀疏地填充在任何单独的行中时，很容易创建一个度量类型维度，将事实表行折叠为由度量类型维度标识的单个通用事实。我们一般不推荐这种方法。虽然它删除了所有空的事实列，但它会将事实表的大小乘以每行中占用的列的平均数，这使得列内计算变得更加困难。当潜在事实的数量非常多（数百个）时，此技术是可以接受的，但适用于任何给定事实表行的只有少数。
我知道，如果为事务事实表实施“度量类型维度”，它可能会产生其他文章所说的问题，但如果用于累积快照事实，我看不到任何缺点。
第二篇文章：（ 实施“度量类型维度”的一些缺点）
- [...] 如果我们使用“度量类型维度”，我们将失去这种分析能力。如果一项措施与其他措施不兼容，我们就无法将它们相加。
- [...] 我们的 SQL 需要运行以生成报告的遍数越多，报告就越慢。
- [...] 在 BI 工具上，如果您不放置度量类型过滤器，您就有可能让用户获得“垃圾信息”。从可用性的角度来看，这种设计是垃圾。
非常好的方法，我从来没有想过。
另一件事：将集装箱运入码头的车辆的每次进出都有一个唯一的 ID，它为我提供了其他信息，例如：车辆的预期到达，实际到达，如果是船只则码头，如果是卡车则收费站和许多其他信息...
这是 3 个不同的事实表，它们必须以某种方式链接到容器事实表。
我以为航次的ID是a degenerate dimension，所以直接进入集装箱事实表。所以，我的疑问是：我应该在集装箱事实表中添加 6 个不同的字段（vessel_voyage_in_key、vessel_voyage_out_key、train_voyage_in_key、train_voyage_out_key、truck_voyage_in_key、truck_voyage_out_key）还是仅添加 2 个动态链接到各种航程表的其他字段（voyage_in、voyage_out）？
我希望我的疑问是清楚的，谢谢。