crokusek's questions

以下查询转换表示 13k 行 x 2 列的打包 CSV 的单个字符串。A 列是一个 bigint。B 列是一个smallint。

declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
      select Value,
             Row = row_number() over (order by (select null)) - 1
      from string_split(@dataCsv, ',') o
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         convert(bigint, i.Value) as ObjectId,
         convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

执行计划： https://www.brentozar.com/pastetheplan/?id =By0hYPmd6

如计划所示，convert() 的评估发生在连接之前，因此有时（取决于输入的长度），它会失败

nvarchar 值“37645”的转换溢出了 INT2 列。使用更大的整数列。

暂时更改从smallint 到int 的转换可以完成查询，并且检查BrandId 列输出表明，在本示例中它始终只包含值“36”。

有没有一种简单的方法可以将转换（smallint，i2.Value）延迟到连接之后，以便仅转换那些预期的CSV位置？

我意识到还有其他方法来打包字符串流（例如使用多个变量，或交错不同的分割字符等），但我对出于此问题的目的以这种方式解决此示例不感兴趣。谢谢！

我们有一个表 Ecom.McProductToVendorProductCodeMap ，它具有多字段 PK，如下所示：

然后，视图包装该表以计算指标，并按该 PK 的前两个字段进行分组：

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

现在，如果我们直接使用这 2 个字段作为谓词来选择视图，则使用这 2 个字段进行索引查找（19k 行，工具提示在 2 个字段上显示“Seek Predicate”）：

select * from ECom.McProductToVendorProductMd5SourceView
where ClientAppPrivateLabelId = 101 and BRandId = 3  

然而，当尝试使用相同的 2 个谓词加入同一视图时，它仅在 ClientAppPrivateLabelId 而不是 BrandId 上查找。循环连接提示没有帮助，用交叉应用替换连接也没有帮助。

select IsNull(convert(smallint, Value), 0) as BrandId 
  into #Brands 
  from string_split('2,3', ',');    
    
select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 inner loop join ECom.McProductToVendorProductMd5SourceView m
    on m.BrandId = b.BrandId
   and m.ClientAppPrivateLabelId = 101;

除了窗口计算之外，视图很简单：

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

为什么不使用 BrandId？原始表将 BrandId 定义为不可为 null 的smallint。

粘贴计划： https://www.brentozar.com/pastetheplan/? id=ryZWp86Hp

更新 #1 (12/5/2023)

将视图转换为表值函数 (TVF)：

alter function ECom.McProductToVendorProductMd5(
   @pBrandId smallint,
   @pClientAppPrivateLabelId smallint
)
returns table as 
return
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  -- Sense any MaterialNumber/VendorProductCode/Status changes
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap m
 where m.BrandId = @pBrandId
   and m.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
 group by ClientAppPrivateLabelId, BrandId

并调整查询以通过交叉应用使用它：

select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 cross apply ECom.McProductToVendorProductMd5(b.BrandId, @pCaplId) m;

同样的问题： https ://www.brentozar.com/pastetheplan/?id=SJnRODaBT

它使用合并连接而不是在 BrandId 上搜索

以下查询在大约 60 个数据库中并行运行。在没有提示的情况下，至少 10% 的数据库中存在大量泄漏和非最佳计划。

使用更大的数据库作为指导，查询被锁定并带有提示（在 1 个 CPU 上约为 75 毫秒）以减少运行时的差异，因为 1 个错误的计划会导致整个运行时终止。我们主要反对让每个 DB 自由调整其计划，因为从长远来看，某些 DB 可能会在生产平台上着火。我们对大型数据库的近乎最佳计划感到非常满意，而小型数据库可能不是最佳计划。

即使在添加了带全扫描的统计信息后，一些 (~5) 较小的数据库仍然表现出小的 1 级溢出（参见计划）。运行时间仍然可以（125 毫秒），但希望消除溢出。

这是 Sql Server 2019。自适应授权功能（2017）是否应该因溢出而调整授权？在 SSMS 和查看计划中重复运行它似乎表明没有变化。

select top (@pMax)
           aig.ObjectId,  
           iif((@pA in (1, 2, 3, 4, 5, 6, 9, 11, 12) and ttm.ObjectId is not null) or
               (@pA in (7, 8, 10, 13, 14, 15)), 1.0, 0.0) as Rank
      from oav.value aig               
      inner merge join Pub.CachedObjectHierarchyAttributes coha
        on coha.ObjectId = aig.ObjectId
       and coha.IsActiveForPublisher = 1
       and coha.IsToolItem = 1
      inner merge join Oav.ValueArray v897
        on v897.PropertyId = 897
       and v897.ObjectId = aig.ObjectId
       and v897.[Value] = @pBrandId
      left hash join oav.valuearray ttm      
        on ttm.ObjectId = aig.ObjectId
       and ttm.PropertyId = 11131  
       and ttm.[Value] = @pToolTypeMapId 
     where aig.PropertyId = 2573        
       and aig.[Value] = @pA
     order by ttm.[Value] desc -- to put TTM matches at the top
     option (maxdop 1); -- limit to 1 cpu since it runs across all pubs

来自 3 个索引的行估计在小于实际行的 1% 内寻找正确匹配。

然而，对最右边的 2 个搜索的第一次合并的估计值偏离了很多，然后通过导致溢出。有了前两个阶段的完美估计，还有什么影响这个估计？

溢出细节：

给定一个没有 PK 但具有基于行的聚集索引的简单的基于行的表，如下所示：

create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value])

然后我希望添加一个列存储索引，该索引的分段顺序与上面的聚集索引相同：

create nonclustered columnstore index CS_IX_PropertyValue on dbo.PropertyValue( 
    PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property 

保留订单的 MaxDop 提示来自：这里

然后使用以下查询报告 PropertyId 列的最小/最大 data_id，并报告 7 个段中的每一个段的完整范围：

create view [Common].[ColumnStoreSegmentationView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose: List ColumnStore table segment min/max of columns.

     Source: https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
             https://dba.stackexchange.com/a/268329/9415

    Modified    By            Description
    ----------  ----------    -----------------------------------------------------------------------------------------
    2020.06.02  crokusek/inet Initial Version 
  ---------------------------------------------------------------------------------------------------------------------*/
select --top 20000000000
       s.Name as SchemaName, 
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p  
    on p.object_id = t.object_id   
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id   
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id 
 --order by s.Name, t.Name, i.Name, c.Name, cs.Segment_Id
GO

我尝试使聚集索引唯一，这确实稍微影响了报告的范围，但仍然不是单调增加。

有任何想法吗？

这是一个以这种方式完成分割的链接，但我看不出有任何区别。

版本：Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

我们有一个针对单项查询优化的视图（200 毫秒无并行性）：

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

它还适用于一小组静态 ID（~5）。

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

但是，如果对象来自外部来源，那么它会生成一个缓慢的计划。执行计划显示视图部分的执行分支忽略了 ObjectId 上的谓词，而在原始情况下它使用它们来执行索引查找。

select v.*
  from 
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o  
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

我们不希望投资于“双重”优化非奇异案例的视图。相反，我们“寻求”的解决方案是对每个对象重复调用一次视图，而不求助于 SP。

大多数情况下，以下解决方案逐行调用视图。但是这次不是，甚至不是只有 1 个对象：

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId 
       order by v_.SomeField
    ) v;

有一次我认为有人声称交叉应用在调用 UDF 时保证逐行执行，但这也失败了：

create function FunctionCallingView(@pObjectId bigint)
returns table
as 
return select *
  from OptimizedForSingleObjectIdView 
 where ObjectId = @pObjectId;             

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

添加选项（强制顺序）没有帮助——但是视图中已经有两个散列提示。暂时删除它们并没有帮助并且减慢了单个案例的速度。

这是基于函数的慢速案例的估计计划的片段。1行的估计是正确的。最右边（未显示）是存在不包括前 1 个结果的搜索谓词的地方。这似乎与我们遇到的其他情况类似，在其他情况下，来自表搜索的单个探测值未用作其他地方的搜索谓词。

从 SQL Server 2008 开始的From Clause Documentation简要提到了 3 种连接提示及其基本机制：

• 减少
• 复制
• 重新分配

然而，似乎没有太多关于何时可能需要使用它们的信息。

看来它们可以与散列、循环和合并结合使用，这些已经为这个问题的目的而被理解。

文档中的相关部分：

对于 SQL 数据仓库和并行数据仓库，这些连接提示适用于两个分布不兼容列上的内部连接。它们可以通过限制查询处理期间发生的数据移动量来提高查询性能。SQL 数据仓库和并行数据仓库允许的连接提示如下：

• REDUCE
  减少连接右侧表要移动的行数，以使两个分布不兼容的表兼容。REDUCE 提示也称为半连接提示。

• REPLICATE
  将连接左侧表的连接列中的值复制到所有节点。右边的表连接到这些列的复制版本。

• REDISTRIBUTE
  强制将两个数据源分布在 JOIN 子句中指定的列上。对于分布式表，并行数据仓库将执行随机移动。对于复制表，并行数据仓库将执行修剪移动。要了解这些移动类型，请参阅并行数据仓库产品文档中“了解查询计划”主题中的“DMS 查询计划操作”部分。当查询计划使用广播移动来解决分布不兼容的连接时，此提示可以提高性能。

如果我弄错了请纠正我，但据我了解，当计划执行查询时，引擎会考虑可用的空闲线程数并相应地调整查询计划。

例如，如果一台机器负载很高，以至于只有一个线程空闲，那么通常在卸载机器上使用多个线程运行的查询可以作为单线程执行。

有没有一种方法可以动态查询可用的空闲线程数，以便可以延迟（while/waitfor）执行特定的关键查询（当 MaxDop < X 时运行不可接受），直到可用的最小空闲线程数为止？

更新：基于 Kin 的回答的起点：

这似乎遵循了一个简单的负载测试，但由于某种原因永远不会返回所有空闲状态。我对这张桌子一无所知。

select count(*) as Cpus, 
       sum(IsIdle) + 1 as IdleCpus  -- +1 since current query should be excluded
  from 
     (
        select Cpu_id, 
               min(convert(int, is_idle)) IsIdle
          from sys.dm_os_schedulers
          group by Cpu_id
     ) q
   ;

我目前正在使用这场灾难通过读取system_health扩展事件环形缓冲区来定位最近的死锁。

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')), 
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),          
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from 
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

它工作正常，但是事件似乎不会持续很长时间（比如 24 小时？）我猜这是它的“环形缓冲区”部分。现在我偶然发现了一个链接，该链接正在读取system_health具有类似信息的“文件”：

select event_data = CONVERT(XML, event_data) 
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'  

该文件是否与环形缓冲区相同，但停留时间更长？使用文件有什么缺点吗？有一些 XML 技能的人想要转换顶级脚本吗？

目标是将 XdlFile 字段复制/粘贴到新文件中，并使用“文件打开”将其直接读入 SSMS 或 Sql Sentry Plan Explorer。

环形缓冲区链接：

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

死锁错误未返回死锁 SQL

文件：

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)

在查询数据库文件大小...

1）大多数（如果不是全部）这些答案并不总是与文件资源管理器中显示的物理文件大小相匹配：

https://stackoverflow.com/questions/5945360

接受的答案报告：      

对比

2）这篇文章似乎与文件资源管理器中显示的物理文件大小相匹配：

是否有不依赖 dm_os_performance_counters 来报告物理大小的查询？第一组查询是否忽略了可用空间？

文件资源管理器报告的实际大小：

为什么 Tempdb 显示数据和日志文件大小的差异，如图所示？

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

使用 Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)。

给定一个表和索引：

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

以下每个查询的实际行数为 3.1M，估计行数显示为注释。

当这些查询在 View 中提供另一个查询时，优化器会因为 1 行估计而选择循环连接。 如何在此基础上改进估计以避免覆盖父查询连接提示或求助于 SP？

使用硬编码日期效果很好：

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

这些等效查询是视图兼容的，但都估计 1 行：

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

尝试一些提示（但 N/A 无法查看）：

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s    
    on s.CreatedUtc > d.MinCreatedUtc  
option (recompile);

尝试使用参数/提示（但 N/A 以查看）：

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

统计数据是最新的。

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

显示了直方图的最后几行（总共 189 行）：

这个问题的一个例子表明 SQL Server 将选择全索引扫描来解决这样的查询：

select distinct [typeName] from [types]

[typeName] 上有一个非聚集的、非唯一的升序索引。他的示例有 2 亿行，但只有 76 个唯一值。对于这种密度（~76 多次二进制搜索），搜索计划似乎是更好的选择？

他的情况可以正常化，但问题的原因是我真的想解决这样的问题：

select TransactionId, max(CreatedUtc) 
from TxLog 
group by TransactionId

上有一个索引(TransactionId, MaxCreatedUtc)。

使用规范化源 (dt) 重写不会改变计划。

select dt.TransactionId, MaxCreatedUtc
 from [Transaction] dt -- distinct transactions
 cross apply
   (
        select Max(CreatedUtc) as MaxCreatedUtc 
          from TxLog tl
         where tl.TransactionId = dt.TransactionId         
   ) ca

仅将 CA 子查询作为标量 UDF 运行确实显示了 1 次查找的计划。

select max(CreatedUtc) as MaxCreatedUtc
 from Pub.TransactionLog 
 where TransactionID = @TxId;

在原始查询中使用该标量 UDF 似乎可行，但会失去并行性（UDF 的已知问题）：

select t.typeName, 
       Pub.ufn_TransactionMaxCreatedUtc(t.TransactionId) as MaxCreatedUtc
  from Pub.[Transaction] t

使用内联 TVF 重写会将其恢复为基于扫描的计划。

来自回答/评论@ypercube：

select TransactionId, MaxCreatedUtc        
 from Pub.[Transaction]  t
  cross apply
   (
        select top (1) CreatedUtc as MaxCreatedUtc 
        from Pub.TransactionLog l
        where l.TransactionID = t.TransactionId
        order by CreatedUtc desc                     
   ) ca

计划看起来不错。没有并行性但毫无意义，因为速度太快了。将不得不在某个时候尝试解决更大的问题。谢谢。

给定两个级联的、独立的（没有真正的表）递归 CTE：

create view NumberSequence_0_100_View
as
with NumberSequence as
(
    select 0 as Number
    union all
    select Number + 1
      from NumberSequence    
     where Number < 100
)
select Number
  from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
       v100.Number * 100 + v1.Number as Number
  from Common.NumberSequence_0_100_View v100
 cross join Common.NumberSequence_0_100_View v1
 where v1.Number < 100
   and v100.Number * 100 + v1.Number <= 10000
    -- please resist complaining about "order by in view" for this question
 order by v100.Number * 100 + v1.Number 
go

然后生成估计/实际计划：

select * from NumberSequence_0_10000_View

估计 实际

运行时间 23 毫秒，但估计只有一行用于最终输出（仅第一个视图有 2 行）。

问题是，当它被用作连接真实数据的子查询时（例如“DaysAgo”），计划通常是一个非常慢的嵌套循环，我经常需要添加连接提示/反向顺序等。

有没有办法在保持 CTE 方法的同时改进估计？是否曾经请求过“with (AssumeMinRows=N)”提示？对于许多情况（不仅仅是 CTE）来说，这似乎是一个很好的通用帮助程序。

标量 UDF 强制执行整体串行计划，这是相当有据可查的。

并行运行函数

鉴于大量行进入管道中必须计算 UDF 的点，为什么引擎不能在处理器之间分配它们？如果 UDF 中没有状态，则顺序无关紧要。

有人声称 UDF 是一个必须使用光标的黑盒。我可以看到，对于在迭代之间保持某些状态但似乎应该是可并行化的情况，用户光标无法在 SP 内并行化。

额外的点来解释为什么引擎强制整个计划是串行的，而不仅仅是 UDF 计算阶段。

对并行 UDF 的支持是否是一个合理的请求功能？

一个例子：

create proc [dbo].[usp_SlowProc]         
as
begin try    
  set xact_abort on;  -- Ensures rollback on some errors that bypass the try/catch mechanism  
  raiserror('Waiting...', 0, 1) with nowait  
  waitfor delay '00:00:10'
  raiserror('Completed successfully.', 0, 1) with nowait  
end try
begin catch
    raiserror('Caught It.', 0, 1) with nowait   
    -- Rollback, log the error.
end catch

然后在 SSMS/Toad 中运行它：

exec dbo.usp_SlowProc;    

然后在它完成之前取消它（使用 SSMS/Toad）。catch 块不运行。

假设从此链接中用户中止与 .NET 在客户端超时时所做的相同（结果相同）。

SQL Server 事务超时

表的 CacheId 列存在自定义统计信息。经过一夜的统计数据更新后：

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 26 2014  2:04AM             121482                          121482                          6                               0                               4                               NO                                                              121482                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1666667                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               

1) 针对此表中现有数据集的连接性能，其中 CacheId = 41033 表现良好，估计值良好（23622 与 20247 的实际值）。

2) 然后使用 CacheId = 41273 of 20247 行执行插入。

3) 然后，针对这个新插入的数据集的连接显示对 1 行的估计不佳，导致计划错误。

4) 手动更新统计信息（最初使用全扫描）显示一个新的直方图：

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 28 2014 10:41AM             141729                          141729                          7                               0                               4                               NO                                                              141729                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1428571                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               
41274                           0                               20247                           0                               1                               

5) 为 CacheId = 41274 再次运行相同的连接查询显示完美的估计 (20247) 和良好的性能。

Q1）为什么在数学上原始估计如此糟糕？我的意思是 CacheId 是稀疏的，但不是 20000:1 的比率。

Q2）随着 cacheId 数量的增加，您是否期望新插入数据的估计值自然提高？

Q3）是否有任何方法（gulp，技巧或其他方式）来改进估计（或使其不太确定 1 行）而不必在每次插入新数据集时更新统计信息（例如在更大的 CacheId = 999999）。

以下是表中所有 CacheId 的真实行数：

CacheId Rows
39968   20247
40058   20247
40062   20247
40066   20247
40069   20247
41033   20247
41274   20247

[我认为不需要 QP 来回答这个问题，并且需要一些工作来清理它们。如果需要，我可以回答具体问题！]

在这个接受的答案的末尾有一个声明，如果在“For Delete”触发器中发生错误，删除操作将被回滚，因为它是隐式事务的一部分。

但是，这里有一个示例显示即使引发错误，已删除的行仍然被删除：

create table T1 ( i1 int  );
go

create trigger T1_ForDelete on T1
for delete
as
raiserror('Raised 16', 16, 1);
raiserror('Raised 18', 18, 1);
raiserror('Raised #2 16', 16, 255);
raiserror('Raised #2 18', 18, 255);
go

insert into T1 (i1) values (1);

set xact_abort on; -- makes no difference

delete from T1;

输出（顺序更改）：

Msg 50000, Level 16, State 1, Procedure T1_ForDelete, Line 4
Raised 16
Msg 50000, Level 16, State 255, Procedure T1_ForDelete, Line 6
Raised #2 16

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure T1_ForDelete, Line 5
Raised 18
Msg 50000, Level 18, State 255, Procedure T1_ForDelete, Line 7
Raised #2 18

然后

select * from T1; -- Returns no records

这是预期的行为还是有办法防止删除错误（例如不同的严重性/状态）？

我不能使用“代替删除”，因为使用了“删除级联”。 创建触发器（MSDN），搜索“For INSTEAD OF”

为什么 ResultFloat = 0 在下面的查询中？

我在cast/convert、sql_variant文档中遗漏了一点吗？

declare
    @v sql_variant = convert(decimal(28,8), 20.0);

select sql_variant_property(@v, 'BaseType') as BaseType,         -- 'decimal',
       iif(convert(int, 10.0)     < @v, 1, 0) as ResultInt,      -- 1
       iif(convert(decimal, 10.0) < @v, 1, 0) as  ResultDecimal, -- 1
       iif(convert(float, 10.0)   < @v, 1, 0) as  ResultFloat,   -- 0 !
       iif(convert(float, 10.0)   < convert(float, @v), 1, 0) as  ResultFloatFloat,  -- 1              
       iif(convert(float, 10.0)   < convert(decimal(28,8), @v), 1, 0) as  ResultFloatDecimal;   -- 1

数据库服务器 2012

对于这种特定情况（跟踪负载均衡器开关），我们希望优化更新插入，以便

• 它没有表现出任何竞争条件，
• 导致任何 PK 违规，或
• 获得任何超大锁。

我知道更大的锁（页面）可能更有效，但出于质疑的目的，目标是最小的（行）。关于 upsert/lock 主题有很多链接，但答案有些不一致（尤其是 updlock 和multi-statements），这个特殊情况涉及嵌入式子查询。

表定义：

create table [User].[SessionWebServerLog] (
  [SessionId] bigint not null,
  [IsSSL] bit not null default ((0)),
  [LastRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  [WebServerProcessInstanceId] bigint not null,
  [RequestCount] int not null default ((1)),
  [FirstRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  foreign key ([SessionId]) references [User].[Session] ( [SessionId] ) on delete cascade,
  primary key clustered ([SessionId] asc, [IsSSL] asc, [LastRequestUtc] desc, [WebServerProcessInstanceId] asc)
  with ( 
    allow_row_locks = on,
    allow_page_locks = off,  -- Needed else page locks were taken
  )
)

仅当 Session+IsSsl 组合自最近一次请求 Session+IsSsl 以来更改了服务器 ID 时，SP 才应插入：

create proc [User].[usp_LogSessionWebServerRequest]    
    @pSessionId                   bigint, 
    @pWebServerProcessInstanceId  bigint,    
    @pIsSsl                       bit,        -- True for https, false for http
    @pDebug                       bit = 0     -- debug flag for print statements
as    
begin try        
    set xact_abort on;
    begin transaction;

        update l
           set RequestCount = RequestCount + 1,
               LastRequestUtc = sysutcdatetime()
          from [User].SessionWebServerLog l             
          with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
         cross apply
             (
               select top(1) WebServerProcessInstanceId, LastRequestUtc
                 from [User].SessionWebServerLog 
                 with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
                   -- PK supports this join:  SessionId, IsSsl, LastRequestUtc (desc), WebServerProcessId                       
                where SessionId = @pSessionId
                  and IsSSL = @pIsSsl
                order by LastRequestUtc desc 
             ) prev -- previous request
         where SessionId = @pSessionId
           and IsSSL = @pIsSsl
           and prev.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.LastRequestUtc = prev.LastRequestUtc;

        if (@@rowcount = 0) -- if no update occurred, insert new
        begin
            insert into [user].SessionWebServerLog
                 ( SessionId, WebServerProcessInstanceId, IsSSL )
            values 
                 ( @pSessionId, @pWebServerProcessInstanceId, @pIsSsl );                
        end            

    commit;            
end try
begin catch    
   if (xact_state() = -1 or @@trancount > 0)
    rollback;
   -- log, etc.
end catch

通过使用两个窗口进行测试并在每个窗口内执行事务的前半部分并检查阻塞，此例程似乎适用于简单的情况。

Q1：当更新与任一窗口的任何行都不匹配但它们是不同的键时，它会阻塞。是否因为键范围锁仅保留在现有键上而发生阻塞？

赢1：

declare
    @pSessionId                   bigint = 3, -- does not exist in table
    @pWebServerProcessInstanceId  bigint = 100,    
    @pIsSsl                       bit = 0;

 sp_lock 72:

spid  dbid  ObjId      IndId   Type       Resource  Mode      Status
  72    16      0          0     DB                 S          GRANT 
  72    16  388964512      1    KEY (6c2787a590a2)  RangeX-X   GRANT
  72    16  388964512      0    TAB                 IX         GRANT

赢2：

 declare
        @pSessionId                   bigint = 4,  -- does not exist in table
        @pWebServerProcessInstanceId  bigint = 100,    
        @pIsSsl                       bit = 0;

    sp_lock 92:

    spid  dbid      ObjId   IndId   Type  Resource         Mode   Status
    92      16          0       0     DB                    S      GRANT
    92      16  388964512       1    KEY  (6c2787a590a2) RangeX-X   WAIT
    92      16  388964512       0    TAB                    IX     GRANT

声明@pSessionId bigint = 4，@pWebServerProcessInstanceId bigint = 100，
@pIsSsl bit = 0；

Q2：如果我在 PK 上允许页锁（默认），为什么即使指定了行锁提示，页锁也会被取消？

spid  dbid      ObjId   IndId   Type     Resource      Mode      Status
72      16          0       0      DB                    S       GRANT
72      16  388964512       1     PAG       1:444       IX       GRANT
72      16  388964512       1     KEY (6c2787a590a2)  RangeX-X   GRANT
72      16  388964512       0     TAB                   IX       GRANT

事务隔离级别是默认的“read committed”。我选择不针对此特定内容进行更改，因为与仅使用表锁 (imo) 相比，恢复它似乎更混乱（对于成功和失败以及假设/确定默认值）。

零案例的查询计划：

当 WebSession+Ssl 存在多行不同日期时的查询计划（从分支到顶部正好一行，完美，显然使用日期 PK）：

Q3：这是否矫枉过正——是否有其他提示可以实现目标？（请不要为了这个问题的目的重新安排查询或尝试转换为合并语句）。

每隔一段时间（例如〜几个月），SQL Server 代理每小时作业将开始报告错误 15404 并继续这样做直到被干预。

[298] SQLServer 错误：15404，无法获取有关 Windows NT 组/用户 'DOMAIN_NAME\SomeDomainAccount' 的信息，错误代码 0x6e。[SQLSTATE 42000] (ConnIsLoginSysAdmin)

有时第一次失败会在手动重新启动 SQL Server 引擎和 SQL Server 代理服务后立即发生。问题可以通过重新启动机器来解决。

作业所有者是错误消息中列出的名称，并且是 SQL Server 管理员。

SQL Server 引擎服务帐户看起来是一个服务帐户（我相信它是默认安装帐户（比通用 NetworkService 好一个档次，以防止引擎/代理实例之间的干扰）：

   NT Service\MSSQL$INSTNAME

如果作业总是失败，那将是一回事，但由于作业在重新启动后成功，这让我认为像这样的服务帐户应该正在工作，并且存在一些 A/D 计时问题或可能存在错误。当 IT 被问及 A/D 配置时，回答通常是“没有任何改变”。

• 重新启动引擎和代理服务可能会导致作业开始失败。
• 机器重新启动可以解决问题。
• 随后立即重新启动引擎和代理不再导致作业失败。

链接： 如何解决 SQL Server 8198 错误

当以秒为单位指定并行度阈值（在特定硬件上，见下文）但估计的 CPU 成本应该是无单位时，优化器如何决定是否使用并行度？估计是按秒处理还是根据某些硬件指标在内部发生转换？

来自 MS： 并行选项的成本阈值

成本是指在特定硬件配置上运行串行计划所需的估计耗用时间（以秒为单位）

我如何阅读查询成本并且它总是一个百分比

sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit