LCJ's questions

我在 SQL Server 2016 中有一个表，如下所示。每个季度都会将所有合作伙伴的记录插入到表中。

需要约束：对于 Distribution_Type，应仅允许一个 BatchID 用于一年季度的 partnerID。但是，对于分配类型，对于给定的年度季度，合作伙伴允许有多个具有相同 BatchID 的记录。

如何在表中创建这样的约束？如果它可以在没有触发器的情况下完成，那就太好了。

桌子

CREATE TABLE [dbo].[Distribution]
(
    [Distribtution_Result_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartnerID] [int] NOT NULL,
    [Distribution_Year] [int] NOT NULL,
    [Distribution_Quarter] [int] NOT NULL,
    [Distribution_Amount] [decimal](16, 4) NULL,
    [Distribution_Type] [varchar](100) NULL,
    [Batch_ID] [int] NOT NULL
)

来自sys.dm_exec_query_stats的Query_hash 定义 是：

query_hash：在查询上计算的二进制哈希值，用于识别具有相似逻辑的查询。您可以使用查询哈希来确定仅字面值不同的查询的聚合资源使用情况。

但是当我搜索一个特定的 query_hash（如下所示）时，我得到了多个在逻辑和size_in_bytes（对于计划）上差异很大的查询文本。

注意：我使用的是 SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT  
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan  
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest 
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

结果

为什么不相关的查询显示相同的 query_hash？这是一个错误还是数据库中发生了意想不到的事情？

我在 SQL Server 2016 中有一个巨大的查询——当我运行一个记录子集的查询时，它在 15 分钟内完成。当我将其推断为记录总数时，它应该在 1 小时内完成。但3小时后仍未完成。当我检查 master.sys.sysprocesses时有一个 CXPACKET 等待 10293331 等待时间

SELECT waittime, waittype, lastwaittype,  open_tran, cpu, physical_io, [sid], loginame, [sql_handle]      
FROM master.sys.sysprocesses WHERE SPID=58

注意：我执行execsp_Who2得到具体的SPID=58

从如何使用 sp_WhoIsActive 查找慢速 SQL Server 查询

@get_task_info = 2 – 如果一个查询是并行的并且你正在对 CXPACKET 等待进行故障排除，你可以找出查询中的每个任务正在等待什么。

我用@get_task_info = 2 执行了 sp_WhoIsActive。

wait_info给出以下内容：

(5x: 68/168850/635541ms)CXPACKET:15, (3x: 3/16/32ms)CXPACKET:7, (1x: 10207374ms)CXPACKET:1

如何从这些信息中判断出每个任务在等待什么？

更新

这个问题更多的是关于找到一种“方法”来找出等待资源（使用 sp_WhoIsActive 或类似的），而不是通过添加 MAXDOP 或分析执行计划来实际解决当前的性能问题。

我有以下两种查询方法。方法 1 在 20 秒内起作用。方法 2 甚至在数小时后仍未完成。

方法一

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

方法二

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$'; 

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

注意：以上两个是简化查询。实际查询可以在下面的执行计划中看到。

执行计划

实际执行计划——方法 1 https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

预计计划 - 方法 2： https ://www.brentozar.com/pastetheplan/?id=SJddO3en7

注意：方法2的计划使用Nested Loopjoin而不是Hash Matchjoin

问题

我知道这可以通过添加来解决OPTION (RECOMPILE)。如果我理解正确的话，缓慢的发生是因为 SQL Server 正在创建一个计划来适应NULL变量的可能性。

在我的例子中，变量将Non-Null在执行查询之前（保证）

1. 在 SQL Server 2016 中，在不使用 OPTION (RECOMPILE) 的情况下，有什么可能告诉 SQL 引擎变量将为非空值并仅针对这种情况准备计划？

2. 这种慢叫参数嗅探吗？（这里没有参数，是单机查询中的局部变量）

注意：我正在尝试查看选项以在不使用 RECOMPILE 的情况下使查询运行得更快。

参考资料：

1. SQL Server 查询：文字快，变量慢
2. 使用选项（重新编译）、常量折叠和避免参数嗅探问题提高查询性能
3. 参数嗅探 vs 变量 vs 重新编译 vs 未知优化
4. SQL Server 中的局部变量与参数化存储过程

我有以下查询来选择每个用户的最新订单。使用 CTE 的查询是否有任何性能优势？对于我当前的表，两者都给出了相同的执行计划和时间。

注意：我使用的是 SQL Server 2012

DECLARE  @Person TABLE (PersonId INT, NameFirst VARCHAR(100), NameLast VARCHAR(100))
DECLARE @Order TABLE (OrderId INT, PersonId INT, OrderDateTime DATETIME)

--Query1
SELECT A.PersonID,A.NameFirst,A.NameLast, A.OrderID AS LastOrderID,A.OrderDateTime AS LastOrderDateTime
FROM
(
    SELECT P.PersonID,P.NameFirst,P.NameLast,O.OrderID,O.OrderDateTime, 
           ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.PersonID,O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)A
WHERE RowNumber = 1
ORDER BY PersonID

--Query2
;WITH CTE AS
(
    SELECT  P.PersonID,P.NameFirst,NameLast,O.OrderID AS LastOrderID,O.OrderDateTime AS LastOrderDateTime,
            ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)
SELECT A.PersonID,A.NameFirst,A.NameLast, A.LastOrderID, A.LastOrderDateTime
FROM CTE A
WHERE RowNumber = 1
ORDER BY PersonID

我正在尝试找出具有Probe Residual.

需要了解以下内容

1. 哪个物理和逻辑运算符有这个Probe Residual
2. 查询中该运算符 的Cost%是多少
3. 关联执行计划
4. 查询文本

以下是我的尝试——但我无法获取其他详细信息。如何获得这些细节？

注意：我使用的是 SQL Server 2012

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            ]')

探针残差示例

引用自以下 Grant Fritchey 和 Rob Farley 的博客/文章

1. 哈希匹配的探针残差 - 可怕的 DBA 之家
2. 进行哈希匹配时探测残差——执行计划中的隐藏成本

我在 SQL Server 2012 中有一个表，如下所示。RequisitionID 上有一个聚集索引——但此列不是唯一的。一个 RequisitionID 可以有多个 ProductID。

CREATE TABLE [dbo].[RequisitionProducts](
    [RequisitionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [UnitAmount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [pk_RequisitionProducts] PRIMARY KEY NONCLUSTERED 
    (
        [RequisitionID] ASC,
        [ProductID] ASC
    ) 
)

CREATE CLUSTERED INDEX [cidx_RequistionProducts] ON [dbo].[RequisitionProducts]
(
    [RequisitionID] ASC
) 
GO

我搜索了很多，发现聚簇索引可以是非唯一的——但仅限于有限的情况。唯一提到的合适场景是有Range Search的时候。在我的例子中，几乎所有查询都将仅基于 RequisitionID——并且不需要范围搜索。

我还应该添加 ProductID 以使聚簇索引唯一吗？优缺点都有什么？

我的系统中有一个名为“Orders”的现有表。OrderID 是该表中的主键——它是一个聚集索引。我为“OrderCompanyDetails”设计了一个新表，如下所示。1-to-1 它与订单表有关系。在新表中，OrderID 保留为聚簇主键。

只有当订单被批准时，数据才会被插入到新表中。所以插入到新表中的 OrderID 可能不按顺序。OrderID 10 可能会在 OrderID 5 之前插入，具体取决于首先批准的订单。

在 OrderID 上使用聚簇索引有助于我的查询。但是聚集索引位于以随机序列获取数据的列上。这是一个糟糕的索引设计吗？如果是，我是否应该添加一个名为 OrderCompanyDetailID 的新无意义标识列并将其作为聚集索引？

CREATE TABLE [dbo].[Orders]
(
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderType] [char](3) NOT NULL,
    [StatusCD] [char](10) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF__Orders__CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    )
)

CREATE TABLE [dbo].[OrderCompanyDetails](
    [OrderID] [int] NOT NULL,
    [POCompanyCD] [char](4) NULL,
    [VendorNo] [varchar](9) NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF_OrderCompanyDetails_CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_OrderCompanyDetails] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    )
)

更新

最近阅读文章Ever-increasing clustering key – the Clustered Index Debate………….again！. 里面有评论

请记住——狭窄的、静态的、独特的、不断增加的——而且通常是代理键而不是自然键。

我在 SQL Server 2012 中有以下实际查询的简化版本。从容器表中选择数据时，它在搜索谓词中有一个标量运算符。

在这个搜索谓词中标量运算符的目的是什么？

CREATE TABLE #EligibleOrders (OrderID INT PRIMARY KEY,
                             StatusCD CHAR(3),
                              CreatedOnDate DATETIME
                              )
--insert logic into #EligibleOrders

--Final Query
SELECT T2.OrderID ,olic.LineItemID,
        SUM(c.quantity) AS ShippedQty,
        COUNT(DISTINCT c.ContainerID) AS ShippedCases
FROM #EligibleOrders T2
INNER JOIN dbo.OrderLineItemContainers (NOLOCK) AS olic 
    ON  olic.OrderID = T2.OrderID
INNER JOIN dbo.Containers (NOLOCK) AS c
    ON olic.Containerid = c.Containerid
GROUP BY T2.OrderID ,olic.LineitemID 
OPTION (MAXDOP 1)

执行计划

寻求谓词

我有一个 SQL Server 2012 数据库。我注意到Reason for early termination of statement optimization一些查询的价值，并且都给出了Good Enough Plan Found. 现在我的问题是：

1. “提前终止语句优化的原因”的所有可能类型是什么。我确实在 msdn 中搜索过这个，但没有得到完整的值列表。

2. 是否有 DMV 或扩展事件来列出由于找到足够好的计划以外的原因而终止优化的所有查询？我参考了以下两篇文章，其中没有列出完整的可能性列表。[此外，他们在我的数据库中给了我不同的结果]。

   • 发现：查询编译超时
   • 识别不够好的查询计划

阅读以下博客后，我明白hash match聚合导致blocking. 使用适当的索引可以将其制作为stream aggregate.

1. 阻塞/非阻塞聚合运算符
2. 聚集体的阻塞性质 - Rob Farley
3. 哈希聚合-Craig Freedman

我有一个数据库，其中包含多年前创建的 200 多个表。我正在尝试查找当前正在使用hash match聚合运算符的 group by 的所有查询。我发现的一种可能性是像下面这样使用 dmv。但我不知道如何过滤它以仅列出带有hash match聚合运算符的查询。如何做到这一点？此外，从大局来看，除了遵循 dmv 之外，还有哪些其他选项可以获取此信息？

SELECT cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.TEXT LIKE '%GROUP%'

我创建了一个过滤索引（在 SQL Server 2012 中）并将其与非过滤索引进行了比较——我可以看到有希望的改进，如下所示。现在我需要更改过滤条件以使用表中的两列。所需条件是 WHERE InboundQuantity - OutboundQuantity <> 0或 InboundQuantity <> OutboundQuantity

但是当我应用此过滤器时，我收到错误消息

“过滤索引 'IX_WO_PlantCD_FilterQtyNotEqual' 的 WHERE 子句不正确”。

我知道过滤索引有局限性。但是，有没有办法通过两列条件，使用过滤索引等来实现这种改进？

询问

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0


--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

计划

我有一个基于 .net 中两个新创建的表的查询SQL Server 2005。我在这些表上创建了索引。但是当我写一个带JOIN条件的查询时，返回的实际行数太高而估计的行数更少。查询计划使用嵌套循环。[下面给出了查询计划图。]由于这些是新表，我认为通常的陈旧统计信息不是这里的原因。

我可以使用NOT EXISTS（如查询 2 所示）重写此查询，实际行数会减少。但我还有其他要求使用 LWTest 表获取详细信息INNER JOIN——实际行数过多是一个问题。

那么，即使有索引和统计信息，为什么实际行数如此之高的任何线索？可以做些什么来降低它？

注意：TransmittedManifests 中的行数 –904。LWTest 中的行数 -- 829785

更新

注 2：Compatibility_Level 为 90。查询 1 的运行时间为 64 毫秒。查询 2 经过的时间是 6 毫秒。

注 3：在这两个表上尝试了 OPTION（RECOMPILE）、重建索引和 UPDATE STATISTICS WITH FULLSCAN。但是实际的行数还是很高的。

感谢 Martin Smith 提供每次执行计数（估计）和总计数（实际）差异的详细信息。实际行数是所有执行的计数，估计是每次执行的计数。估计行数、实际行数和执行计数。

对于查询 1，ActualExecutionsLWTest 表的值为 ="904"。

询问

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON 
PRINT 'BEGIN -----------------------------------------'

--Query 1
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL

PRINT 'QUERY 1 COMPLETED -----------------------------------------'



--Query 2
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
                  WHERE L.Manifest = T.Manifest
                 )

PRINT 'QUERY 2 COMPLETED -----------------------------------------'


--Query 3
SELECT  T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest

表模式

查询计划 1

查询计划 2

我是 .Net 开发人员。我即将进行数据库升级（从 SQL Server 2005 到 SQL Server 2012）。数据库团队将处理迁移。但是我看到论坛帖子说迁移后，即使数据量和用户数量几乎相同，查询执行的时间也太长了。一个主要原因是execution plan这些服务器之间的变化。参考：从SQL Server 2005迁移到2012后的性能问题

因此，我正在考虑从我当前的生产服务器本身存储实际执行计划的可能性。在 Oracle 中，我可以找到一个基线特性：-

1. SQL 计划管理（第 1 部分，共 4 部分）创建 SQL 计划基线
2. 使用 SQL 计划管理

在 SQL Server 中，实现此目的的通常做法是什么？

注意：我提到了 Creating a baseline for SQL Server，但它没有说明任何关于基线的内容execution plan

我有一张Employee包含一百万条记录的表。我有以下 SQL 用于在 Web 应用程序中分页数据。它工作正常。但是我看到的一个问题是 - 派生表tblEmployee选择表中的所有记录Employee（以创建 MyRowNumber值）。

我认为，这会导致选择Employee表中的所有记录。

真的那么有效吗？或者 SQL Server 是否也经过优化以仅从原始Employee表中选择 5 条记录？

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize