问题[sql-server-2016](dba)

我有一个与映射网格上的点相关的 X 和 Y 值表，其中每对值引用每个正方形的左下角，每个值以 500 平方米的增量间隔，以形成网格结构中的点。

下面是一个包含一些类似数据的表格示例，其中 XY_ID 只是 X 和 Y 值的串联。

然后我有另一个表，每一行都有一个特定的 X 和 Y 值，该值在精确到米的值方面更具体。因此，例如特定行的 X_Value 为 252996，Y_Value 为 652818。

我想要实现的是能够查找上面显示的网格表并通过 X_GridPoint 和 Y_GridPoint 拉取特定行的 X_Value 和 Y_Value 在 500m 增量范围内的位置。

因此，对于 252996 的 X_Value 和 652818 的 Y_Value，这将找到 252500 的 X_GridPoint 和 652500 的 Y_GridPoint，这是上面示例表的第 6 行。这些值基本上会“回退”到该值之前的任何 500m 网格点，并且它永远不会“向上舍入”到下一个 500m 网格点。

带有网格点的表格将包含大约 25,000 行这些组合，并希望链接到具有特定 X 和 Y 值的大型数据集，所以想知道是否有人可以建议最好的方法来解决这个问题以及什么最有效性能。任何想法将不胜感激。

使用 SQL Server 2016

我正在尝试查询如下所示的 xml。

<wfs:FeatureCollection xmlns:gml="http://www.opengis.net/gml" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <gml:featureMember>
    <SiteList>
      <Site>123 ABC Road</Site>
      <Location>
        <gml:Point>
          <gml:pos>-38.987654321 177.12345678</gml:pos>
        </gml:Point>
      </Location>
    </SiteList>
  </gml:featureMember>
</wfs:FeatureCollection>

这是我的查询。我需要网站 & gml:pos (lat/Long)

select 
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Site)[1]', 'varchar(250)') as [SiteName],  
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Location/gml:Point/gml:pos)[1]', 'varchar(250)') as [Location]
    from  #xml t
    cross apply t.yourXML.nodes('//wfs:FeatureCollection/gml:featureMember/SiteList') xml (xmldata)

我收到以下错误：

Msg 2229, Level 16, State 1, Procedure dbo.DW_sp_Extract_Sites, Line 102 [Batch Start Line 2]
XQuery [#xml.yourXML.nodes()]: The name "wfs" does not denote a namespace.

谁能告诉我我做错了什么？任何帮助或建议将不胜感激。我一直在与 xquery 语法作斗争。

我正在使用 SQL Server 2016 和 SQL Server 2019 标准版。我们有这份工作已经有一段时间了。它曾经很小，但现在它运行了几十个SELECT/DELETE语句。（语句之间没有GO分隔符。）

• 我想知道 SQL Server 如何为作业步骤分配 RAM？
• SQL 会尝试一次为整个步骤分配 RAM 吗？
• 或者它会在运行该步骤时为每个查询分配 RAM？
• 考虑到 RAM 的使用，对工作执行不同的步骤会更好吗？

无论如何，我可能最终会分离这份工作，即使只是为了监控目的。但由于我们使用的是 SQL 标准版，因此我密切监视 RAM 使用情况，无法找到有关 SQL 代理作业内部分配的信息。

PAGELATCH_EX由于大量插入而导致的等待问题确实需要一些帮助。我们看到一个简单的插入到表的等待中PAGELATCH_EX和等待中的随机峰值。_SH

每天只发生几次，但当出现短暂的锁定和等待时间增加时，可能会导致我们的 Web 应用程序排队。服务器超出了规范，SSD 磁盘似乎没有接近 I/O 容量。

我读PAGELATCH_EX的是由于表中的顺序聚集主键以及最后一页争用，所以我已将聚集索引更改为 GUID，并将原始顺序 ID 更改为非聚集索引，因为我们仍然需要基于此查询ID。

不幸的是，它看起来并没有解决问题。我还尝试在索引上设置 70% 的填充因子。顺序非聚集索引还会导致PAGELATCH_EX？我们是否也需要将非唯一数字作为该索引的一部分？

这是带有 GUID 和所有索引的新表：

CREATE TABLE [dbo].[data_202210](
    [Id] [uniqueidentifier] NOT NULL,
    [sequentialnumber] [bigint] NOT NULL,
    [w1] [bigint] NOT NULL,
    [h1] [bigint] NOT NULL,
    [datetime] [datetime] NOT NULL,
    [p1] [bigint] NOT NULL,
    [memberid] [bigint] NULL,
    [datatype] [int] NOT NULL,
    [datavalueid] [bigint] NOT NULL,
    [categoryid] [bigint] NULL,
    [categorytype] [int] NULL,
    [orderid] [bigint] NULL,
    [ordertotal] [decimal](10, 2) NULL,
    [url] [nvarchar](2048) NULL,
    [img] [nvarchar](2048) NULL,
 CONSTRAINT [PK_data_Id_202210] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[data_202210] ADD  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[data_202210]  WITH CHECK ADD  CONSTRAINT [CC_data_202210_Id_datetime] CHECK  (([datetime]>='2022-10-01' AND [datetime]<'2022-11-01'))
GO

ALTER TABLE [dbo].[data_202210] CHECK CONSTRAINT [CC_data_202210_Id_datetime]
GO

--this is the old clustered index which is sequentially created number in our application
--still used in a queries to join to another table
CREATE NONCLUSTERED INDEX [pigdataid_202210] ON [dbo].[data_202210]
(
    [sequentialnumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

-- covering indexes for querying the data
CREATE NONCLUSTERED INDEX [lookup_202210] ON [dbo].[data_202210]
(
    [w1] ASC,
    [p1] ASC,
    [datetime] ASC,
    [datatype] ASC
)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 [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [trending_lookup_202210] ON [dbo].[data_202210]
(
    [w1] ASC,
    [datatype] ASC,
    [datetime] ASC
)
INCLUDE ([datavalueid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

我使用各种查询来查看等待以及 RedGate SQL Monitor。

服务器是 16 个 CPU 和 128GB RAM。磁盘为 10,000 IOPS，500 MB/s 吞吐量。峰值时每秒大约 400 次插入。我不会认为 400 很多，但等待肯定指向PAGELATCH_EXand PAGELATCH_SH。插入大约需要 1 毫秒。其他等待还可以。

不幸的是，我们无法迁移到 SQL Server 2019。

链接：https ://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#RDBMSSP

对于NUMA Aware Large Page Memory and Buffer Array Allocation表说不

在下面它说：

Standard Edition 和基于 SQL Server + CAL 的许可可以限制 SQL Server Standard 可以使用的处理器数量，但 SQL Server Standard 支持 NUMA。

这个问题是问SQL标准版（2016、2017、2019）是否支持NUMA，因为表和上面的引用显示的信息冲突。

SQL Server 2017 和 2019 的表在没有任何引号的情况下说“不”。那么 NUMA 是否在 2016 年受支持而在 2017 年和 2019 年不受支持？

链接：

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-2017#RDBMSSP

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017#RDBMSSP

我有两个与链接服务器连接的 SQL Server 2016。当我sys.dm_exec_describe_first_result_set从远程服务器查询时，它返回列表的数字数据类型而不是十进制。

第一个查询（从远程服务器执行）

select * from sys.dm_exec_describe_first_result_set (N'select top 1 * from LinkedServerFoo.DatabaseFoo.dbo.TargetTable', NULL, 0) [source]

第二个查询（从 sql server 执行）

select * from sys.dm_exec_describe_first_result_set (N'select top 1 * from DatabaseFoo.dbo.TargetTable', NULL, 0) [source]

结果

这对我来说是个问题，因为我根据这些查询的输出比较数据类型。

知道可能是什么问题，为什么会这样以及如何解决？

• smalldatetimesys 表错误返回的位置也存在相同的问题datetime

目前我们的服务器在 Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows Server 2016 Standard 10.0 (构建 14393:)。这是辅助只读 AG 副本。我们在主副本和辅助副本上都安装了 SP3。但是最近第二个节点在 Windows 更新中声明了这一点

可用更新：SQL Server 2016 Service Pack 2 (KB4052908)

如果服务包是累积的，为什么服务器会这样说。有什么想法吗？谢谢。

我有一个查询，我在查询存储中强制执行一个计划（该计划是为此查询编译的一个 SQL Server）如果我在强制执行该计划后立即运行查询，NO_PLAN尽管数据库没有更改，但我会得到 last_force_failure_reason_desc。我可以成功地为同一个查询强制执行不同的计划

问题如下图所示：

创建我们的测试数据库

USE [master]
CREATE DATABASE NO_PLAN
ALTER DATABASE [NO_PLAN] SET QUERY_STORE = ON
ALTER DATABASE [NO_PLAN] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL)
GO

USE NO_PLAN
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableA') DROP TABLE MyTableA
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableB') DROP TABLE MyTableB

/* create  our tables */
CREATE TABLE [dbo].[MyTableA](
    [Column1] VARCHAR(50) NULL ,
    [Column2] VARCHAR(255) NULL ,
    [Column3] INT NULL ,
    [Column4] DATETIME NULL ,
    [Column5] INT NULL ,
    [Column6] VARCHAR(50) NULL ,
    [Column7] VARCHAR(255) NULL ,
    [Column8] INT NULL ,
    [Column9] DATETIME NULL ,
    [Column10] INT NULL ,
    [Column11] INT NULL ,
    [Column12] DATETIME NULL ,
    [Column13] VARCHAR(50) NULL ,
    [Column14] VARCHAR(50) NULL ,
    [Column15] DATETIME NULL ,
    [Column16] DATETIME NULL ,
    [Column17] VARCHAR(8) NULL ,
    [Column18] DATETIME NULL ,
    [Column19] INT NULL ,
    [Column20] INT NULL ,
    [Column21] VARCHAR(50) NULL ,
    [Column22] VARCHAR(255) NULL ,
    [Column23] VARCHAR(50) NULL ,
    [Column24] VARCHAR(255) NULL ,
    [Column25] VARCHAR(50) NULL ,
    [Column26] INT NULL ,
    [Column27] INT NULL ,
    [Column28] INT NULL ,
    [Column29] INT NULL ,
    [Column30] INT NULL ,
    [Column31] INT NULL ,
    [Column32] INT NULL ,
    [Column33] INT NULL ,
    [Column34] INT NULL ,
    [Column35] VARCHAR(50) NULL ,
    [Column36] VARCHAR(50) NULL ,
    [Column37] VARCHAR(50) NULL ,
    [Column38] VARCHAR(50) NULL ,
    [Column39] VARCHAR(255) NULL ,
    [Column40] INT NULL ,
    [Column41] VARCHAR(50) NULL ,
    [Column42] INT NULL ,
    [Column43] VARCHAR(255) NULL ,
    [Column44] INT NULL ,
    [Column45] VARCHAR(255) NULL ,
    [Column46] INT NULL ,
    [Column47] DATETIME NULL ,
    [Column48] DATETIME NULL ,
    [Column49] DATETIME NULL ,
    [Column50] INT NULL ,
    [Column51] VARCHAR(50) NULL ,
    [Column52] VARCHAR(255) NULL ,
    [Column53] VARCHAR(50) NULL ,
    [Column54] VARCHAR(255) NULL ,
    [Column55] VARCHAR(50) NULL ,
    [Column56] VARCHAR(255) NULL ,
    [Column57] VARCHAR(50) NULL ,
    [Column58] VARCHAR(50) NULL ,
    [Column59] CHAR NULL ,
    [Column60] CHAR NULL ,
    [Column61] CHAR NULL ,
    [Column62] CHAR NULL ,
    [Column63] CHAR NULL ,
    [Column64] CHAR NULL ,
    [Column65] CHAR NULL ,
    [Column66] CHAR NULL ,
    [Column67] CHAR NULL ,
    [Column68] CHAR NULL ,
    [Column69] CHAR NULL ,
    [Column70] CHAR NULL ,
    [Column71] CHAR NULL ,
    [Column72] CHAR NULL ,
    [Column73] CHAR NULL ,
    [Column74] CHAR NULL ,
    [Column75] CHAR NULL ,
    [Column76] DATETIME NULL ,
    [Column77] INT NULL ,
    [Column78] INT NULL ,
    [Column79] VARCHAR(50) NULL ,
    [Column80] VARCHAR(255) NULL ,
    [Column81] VARCHAR(50) NULL ,
    [Column82] VARCHAR(255) NULL ,
    [Column83] VARCHAR(50) NULL ,
    [Column84] VARCHAR(255) NULL ,
    [Column85] VARCHAR(50) NULL ,
    [Column86] VARCHAR(255) NULL ,
    [Column87] VARCHAR(50) NULL ,
    [Column88] VARCHAR(255) NULL ,
    [Column89] VARCHAR(50) NULL ,
    [Column90] VARCHAR(255) NULL ,
    [Column91] VARCHAR(50) NULL ,
    [Column92] VARCHAR(255) NULL ,
    [Column93] VARCHAR(50) NULL ,
    [Column94] VARCHAR(255) NULL ,
    [Column95] VARCHAR(50) NULL ,
    [Column96] VARCHAR(255) NULL ,
    [Column97] VARCHAR(50) NULL ,
    [Column98] VARCHAR(255) NULL ,
    [Column99] VARCHAR(50) NULL ,
    [Column100] VARCHAR(255) NULL ,
    [Column101] VARCHAR(50) NULL ,
    [Column102] VARCHAR(255) NULL ,
    [Column103] VARCHAR(50) NULL ,
    [Column104] VARCHAR(255) NULL ,
    [Column105] VARCHAR(50) NULL ,
    [Column106] VARCHAR(255) NULL ,
    [Column107] VARCHAR(50) NULL ,
    [Column108] VARCHAR(50) NULL ,
    [Column109] VARCHAR(50) NULL ,
    [Column110] VARCHAR(255) NULL ,
    [Column111] VARCHAR(50) NULL ,
    [Column112] VARCHAR(255) NULL ,
    [Column113] VARCHAR(50) NULL ,
    [Column114] VARCHAR(255) NULL ,
    [Column115] VARCHAR(50) NULL ,
    [Column116] VARCHAR(255) NULL ,
    [Column117] VARCHAR(50) NULL ,
    [Column118] VARCHAR(255) NULL ,
    [Column119] VARCHAR(50) NULL ,
    [Column120] VARCHAR(50) NULL ,
    [Column121] VARCHAR(255) NULL ,
    [Column122] VARCHAR(50) NULL ,
    [Column123] VARCHAR(255) NULL ,
    [Column124] VARCHAR(50) NULL ,
    [Column125] VARCHAR(255) NULL ,
    [Column126] VARCHAR(50) NULL ,
    [Column127] VARCHAR(255) NULL ,
    [Column128] VARCHAR(50) NULL ,
    [Column129] VARCHAR(255) NULL ,
    [Column130] VARCHAR(50) NULL ,
    [Column131] VARCHAR(255) NULL ,
    [Column132] DATETIME NULL ,
    [Column133] VARCHAR(50) NULL ,
    [Column134] VARCHAR(255) NULL ,
    [Column135] VARCHAR(50) NULL ,
    [Column136] INT NULL ,
    [Column137] VARCHAR(50) NULL ,
    [Column138] VARCHAR(255) NULL ,
    [Column139] VARCHAR(50) NULL ,
    [Column140] VARCHAR(255) NULL ,
    [Column141] VARCHAR(50) NULL ,
    [Column142] VARCHAR(255) NULL ,
    [Column143] VARCHAR(50) NULL ,
    [Column144] VARCHAR(255) NULL ,
    [Column145] VARCHAR(50) NULL ,
    [Column146] VARCHAR(255) NULL ,
    [Column147] VARCHAR(50) NULL ,
    [Column148] VARCHAR(255) NULL ,
    [Column149] VARCHAR(50) NULL ,
    [Column150] VARCHAR(255) NULL ,
    [Column151] VARCHAR(50) NULL ,
    [Column152] VARCHAR(255) NULL ,
    [Column153] VARCHAR(50) NULL ,
    [Column154] VARCHAR(255) NULL ,
    [Column155] VARCHAR(50) NULL ,
    [Column156] VARCHAR(255) NULL ,
    [Column157] VARCHAR(50) NULL ,
    [Column158] VARCHAR(255) NULL ,
    [Column159] INT NULL ,
    [Column160] INT NULL ,
    [Column161] VARCHAR(50) NULL ,
    [Column162] VARCHAR(50) NULL ,
    [Column163] VARCHAR(50) NULL ,
    [Column164] VARCHAR(50) NULL ,
    [Column165] VARCHAR(50) NULL ,
    [Column166] VARCHAR(50) NULL ,
    [Column167] VARCHAR(50) NULL ,
    [Column168] VARCHAR(50) NULL ,
    [Column169] VARCHAR(255) NULL ,
    [Column170] INT NULL ,
    [Column171] VARCHAR(50) NULL ,
    [Column172] INT NULL ,
    [Column173] VARCHAR(50) NULL ,
    [Column174] VARCHAR(50) NULL ,
    [Column175] VARCHAR(50) NULL ,
    [Column176] VARCHAR(255) NULL ,
    [Column177] VARCHAR(50) NULL ,
    [Column178] VARCHAR(255) NULL ,
    [Column179] VARCHAR(50) NULL ,
    [Column180] VARCHAR(50) NULL ,
    [Column181] VARCHAR(50) NULL ,
    [Column182] VARCHAR(255) NULL ,
    [Column183] VARCHAR(50) NULL ,
    [Column184] VARCHAR(255) NULL ,
    [Column185] VARCHAR(50) NULL ,
    [Column186] VARCHAR(255) NULL ,
    [Column187] VARCHAR(50) NULL ,
    [Column188] VARCHAR(255) NULL ,
    [Column189] VARCHAR(50) NULL ,
    [Column190] VARCHAR(50) NULL ,
    [Column191] VARCHAR(50) NULL ,
    [Column192] VARCHAR(255) NULL ,
    [Column193] VARCHAR(50) NULL ,
    [Column194] VARCHAR(255) NULL ,
    [Column195] VARCHAR(50) NULL ,
    [Column196] VARCHAR(50) NULL ,
    [Column197] VARCHAR(255) NULL ,
    [Column198] INT IDENTITY (1,1) ,
    [Column199] VARCHAR(500) NULL ,
    [Column200] VARCHAR(255) NULL ,
    [Column201] VARCHAR(50) NULL ,
    [Column202] VARCHAR(255) NULL ,
    [Column203] CHAR NULL ,
    [Column204] CHAR NULL ,
    [Column205] VARCHAR(50) NULL ,
    [Column206] VARCHAR(255) NULL ,
    [Column207] BIGINT NULL ,
    [Column208] VARCHAR(50) NULL ,
    [Column209] VARCHAR(50) NULL ,
    [Column210] VARCHAR(50) NULL ,
    [Column211] VARCHAR(255) NULL ,
    [Column212] VARCHAR(50) NULL ,
    [Column213] VARCHAR(255) NULL ,
    [Column214] VARCHAR(50) NULL ,
    [Column215] VARCHAR(50) NULL ,
    [Column216] VARCHAR(50) NULL ,
    [Column217] VARCHAR(50) NULL ,
    [Column218] VARCHAR(50) NULL ,
    [Column219] VARCHAR(50) NULL ,
    [Column220] VARCHAR(50) NULL ,
    [Column221] VARCHAR(50) NULL ,
    [Column222] DATETIME NULL ,
    [Column223] VARCHAR(50) NULL ,
    [Column224] VARCHAR(50) NULL ,
    [Column225] CHAR NULL ,
    [Column226] CHAR NULL ,
    [Column227] CHAR NULL ,
    [Column228] CHAR NULL ,
    [Column229] CHAR NULL ,
    [Column230] CHAR NULL ,
    [Column231] VARCHAR(50) NULL ,
    [Column232] VARCHAR(50) NULL ,
    [Column233] VARCHAR(50) NULL ,
    [Column234] VARCHAR(255) NULL ,
    [Column235] VARCHAR(50) NULL ,
    [Column236] VARCHAR(50) NULL ,
    [Column237] VARCHAR(255) NULL ,
    [Column238] VARCHAR(50) NULL ,
    [Column239] VARCHAR(255) NULL ,
    [Column240] VARCHAR(50) NULL ,
    [Column241] VARCHAR(255) NULL ,
    [Column242] CHAR NULL ,
    [Column243] CHAR NULL ,
    [Column244] DATE NULL ,
    [Column245] DATE NULL ,
    [Column246] DATE NULL ,
    [Column247] VARCHAR(50) NULL ,
    [Column248] VARCHAR(255) NULL ,
    [Column249] VARCHAR(50) NULL ,
    [Column250] VARCHAR(255) NULL ,
    [Column251] DATE NULL ,
    [Column252] DATE NULL ,
    CONSTRAINT [PKC_MyTableA] PRIMARY KEY CLUSTERED 
    (
        [Column198] ASC
    )
)
GO

CREATE TABLE [dbo].[MyTableB]
(
    Column1 [INT] IDENTITY(1,1) NOT NULL,
    Column2 [INT] NULL,
    Column3 [VARCHAR](255) NOT NULL,
    Column4 [VARCHAR](255) NULL,
    Column5 [CHAR](1) NOT NULL,
    Column6 [VARCHAR](MAX) NULL,
    Column7 [VARCHAR](50) NULL,
    CONSTRAINT [PK_MyTableB] PRIMARY KEY CLUSTERED 
    (
        Column3 ASC
    )
)
GO

插入一些虚拟数据：

DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON; 

INSERT INTO [MyTableA] (' 

SELECT  @valsSQL += c.name + ','
FROM    sys.columns c
        JOIN sys.tables t
            ON c.object_id = t.object_id
WHERE   t.name = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')

SET @valsSQL += ' VALUES ( '

SELECT  @valsSql +=
        CASE
            WHEN c.system_type_id = 167 OR --varchar 
                    c.system_type_id = 175 -- char
            THEN '''' +  REPLICATE('a',c.max_length) + ''''
            WHEN c.system_type_id = 61
            THEN '''' +  CONVERT(NVARCHAR,GETDATE(),120) + ''''
            WHEN c.system_type_id = 56 OR --int OR
                c.system_type_id = 47 OR -- bigint
                c.system_type_id = 127
            THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
            WHEN c.system_type_id = 40
            THEN '''' +  '1900-01-01' + ''''
        END + ','
FROM    sys.columns c
        JOIN sys.types t
            ON c.system_type_id = t.system_type_id
WHERE   OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'

EXEC sp_executesql @stmt = @valsSQL 
GO 500

现在数据库已经建立，运行查询：

USE NO_PLAN
SELECT  1
        -- my unique text to find this query in query store views
FROM    MyTableA 
        INNER JOIN MyTableB Alias  
            ON Alias.Column3 = 'value'
        LEFT JOIN MyTableB  
            ON MyTableB.Column3 =  'value'
WHERE   MyTableB.Column4 IS NULL

NB - 实际的执行计划在这里

使用查询存储 DMV 获取查询 ID 和计划 ID，以便我们可以强制执行计划：

SELECT  t.query_sql_text,
        q.query_id,
        p.plan_id,
        p.query_plan,
        p.is_forced_plan,
        p.last_force_failure_reason_desc,
        p.last_execution_time 
 FROM    sys.query_store_plan p
        JOIN sys.query_store_query q
            ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id 
 WHERE   t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
        t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */

我的输出如下：

现在强制 SQL 服务器使用它刚刚编译的计划，每次它运行这个查询

EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6

再次运行查询：

USE NO_PLAN
SELECT  1
        -- my unique text to find this query in query store views
FROM    MyTableA 
        INNER JOIN MyTableB Alias  
            ON Alias.Column3 = 'value'
        LEFT JOIN MyTableB  
            ON MyTableB.Column3 =  'value'
WHERE   MyTableB.Column4 IS NULL

检查查询存储 DMV 以查看它是否使用了计划：

SELECT  t.query_sql_text,
        q.query_id,
        p.plan_id,
        p.query_plan,
        p.is_forced_plan,
        p.last_force_failure_reason_desc,
        p.last_execution_time
FROM    sys.query_store_plan p
        JOIN sys.query_store_query q
            ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id
WHERE   t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
        t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */

我们可以看到 NO_PLAN 的失败原因：

如果我通过截断表、清除查询存储然后只向表中添加 20 行（或删除数据库并运行上述所有设置但使用GO 20而不是GO 500）来重置：

USE NO_PLAN;
ALTER DATABASE NO_PLAN SET QUERY_STORE CLEAR;
TRUNCATE TABLE [MyTableA];

DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON; 

INSERT INTO [MyTableA] (' 

SELECT  @valsSQL += c.name + ','
FROM    sys.columns c
        JOIN sys.tables t
            ON c.object_id = t.object_id
WHERE   t.name = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')

SET @valsSQL += ' VALUES ( '

SELECT  @valsSql +=
        CASE
            WHEN c.system_type_id = 167 OR --varchar 
                    c.system_type_id = 175 -- char
            THEN '''' +  REPLICATE('a',c.max_length) + ''''
            WHEN c.system_type_id = 61
            THEN '''' +  CONVERT(NVARCHAR,GETDATE(),120) + ''''
            WHEN c.system_type_id = 56 OR --int OR
                c.system_type_id = 47 OR -- bigint
                c.system_type_id = 127
            THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
            WHEN c.system_type_id = 40
            THEN '''' +  '1900-01-01' + ''''
        END + ','
FROM    sys.columns c
        JOIN sys.types t
            ON c.system_type_id = t.system_type_id
WHERE   OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'

EXEC sp_executesql @stmt = @valsSQL 
GO 20

然后再次运行查询，我得到了不同的计划（注意过滤器运算符的位置已更改）

如果我然后重复获取 query_id 和 plan_id 的过程，强制执行计划并重新运行查询，这一次它将强制执行计划：

我可以确认 NO_PLAN 计划不能通过OPTION (RECOMPILE, USE PLAN N'<planxmlhere>')提示强制执行，我明白了

Msg 8698, Level 16, State 0, Line 5 Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

A number of articles suggest that the NO_PLAN failure reason is due to changing indexes, however, as can be seen from the example above, nothing has changed between forcing and running the query for the second time.

Article A

Article B

Why can SQL server not be forced to use a plan it just generated, when nothing has changed? What is it about the first plan that causes the forcing to fail an why is that not an issue for the second plan?

我在查询存储中有一个查询，我已经为此制定了一个计划。

我可以确认该计划是被迫的

SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1

并且计划显示在结果中

但是，如果我查看该last_force_failure_reason_desc列，我会看到NO_PLAN

一些谷歌搜索把我带到了以下文章：

肯德拉小

Deepthi Goguri

这两者都表明更改计划使用的索引是导致NO_PLAN失败的原因。

我在第二篇文章中设置了扩展事件会话：

CREATE EVENT SESSION [Querystoreforcedplanfailures] ON SERVER 

ADD EVENT qds.query_store_plan_forcing_failed
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\qserror.xel'),
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

我可以看到相关计划的事件，其中包含以下文本：

查询处理器无法生成查询计划，因为 USE PLAN 提示包含无法验证为合法查询的计划。删除或替换 USE PLAN 提示。为获得成功执行计划的最大可能性，请验证 USE PLAN 提示中提供的计划是 SQL Server 为同一查询自动生成的计划

该查询作为数据仓库构建的一部分每晚运行，其中 DDL 命令很常见，因此我决定设置一个数据库审计规范来捕获SCHEMA_OBJECT_CHANGE_GROUP操作类型，以查看是否有任何索引被更改

USE [master]
GO

CREATE SERVER AUDIT [PlanForceAlters]
TO FILE 
(   FILEPATH = N'P:\Audit\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'd2b6b090-395f-42f9-a8bb-c0ba742ce30e')
ALTER SERVER AUDIT [PlanForceAlters] WITH (STATE = ON)
GO


USE [MyDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [PlanForceAlters]
FOR SERVER AUDIT [PlanForceAlters]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

当我如下询问结果时：

SELECT  o.name,
        a.statement
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
WHERE   o.name IN ('MyTableA','MyTable')

我可以看到 IN 子句中表的所有更改（哪些表是从查询中选择的表，我试图强制执行谁的计划）

我所能看到的只是外键删除和重新创建，这对于我们的数据仓库来说是相当正常的。外键使用与删除时相同的名称重新创建。事件顺序是

1. 存在约束（不可信）
2. 约束被删除
3. 查询运行
4. 重新创建约束（同名和 NOCHECK）

强制执行的计划是查询处理器在上面第 3 点生成的计划，因此事件的顺序每晚都相同，我会认为约束的变化是无关紧要的吗？

我更进一步，计算出被删除/重新创建的外键在哪些列上：

SELECT  o.name,
        a.statement,
        c.name
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
        LEFT JOIN sys.foreign_keys fk
            ON  statement LIKE '%ADD CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%ADD  CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP  CONSTRAINT%' + fk.name + '%'
        LEFT JOIN sys.foreign_key_columns fkc
            ON fk.object_id = fkc.constraint_object_id
        LEFT JOIN sys.all_columns c
            ON c.column_id = fkc.parent_column_id AND
                c.object_id = fkc.parent_object_id
WHERE   o.name IN ('MyTableA','MyTableB')

并且这些都不是 query_plan 中使用的任何非聚集索引中的列

我试图在 AdventureWorks2016 数据库上重新创建一个示例，在该示例中我强制执行一个计划，该计划对具有可信外键的列执行 NCI 搜索，然后观察优化器仍然使用该计划，尽管外键被删除并且在重新创建不受信任时仍然使用：

/* create our stored proc */
CREATE OR ALTER PROCEDURE sp_SalesbyProduct
    @ProductID INT
AS
SELECT 
  SalesOrderID, 
  OrderQty,
  UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
GO

/* create an index to support the query */
CREATE INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
(
    ProductId
) 
WITH 
(
    DROP_EXISTING = ON
)

/* add a trusted foreign key on the column IX_SalesOrderDetail_ProductID is on */
ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* run the proc and ensure differing plans */
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710 /* seek on IX_SalesOrderDetail_ProductID with key lookup */
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870 /* CI Scan*/
GO

/* force the seek / lookup plan */
EXEC sp_query_store_force_plan 222, 224;

/* verify the plan is forced */
SELECT  *
FROM    sys.query_store_plan
WHERE   is_forced_plan = 1

/* run the queries again and ensure both use the seek / lookup plan */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

/* drop the constraint on the column in the index */
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT FK_MyKey 

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

/* re-add the FK but make it untrusted */
ALTER TABLE Sales.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

是什么导致了NO_PLAN错误？这与删除/创建外键约束有关吗？

实际上，我有一个配置如下 SQL Server 2016 的分布式可用性组：

端点配置如下：

USE [master]
GO

CREATE ENDPOINT [endpoint_istance2]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP=ALL)
    FOR DATA_MIRRORING (ROLE=ALL,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM AES)
GO
 
GRANT CONNECT ON ENDPOINT::[endpoint_istance2] TO [mydomain\sqlmirrorer]
GO

现在，我需要添加另一个数据中心，而不是在同一个 Microsoft 域中。我需要得到这个：

• 这第三个 AG 在 WORKGROUP 中。
• 我无法添加信任。

我可以想象两种方式：

1. 是否可以对 enpoint 进行混合身份验证？我知道我不能在一个实例上拥有多个端点。
2. 我是否需要更改每个端点以仅使用证书？如何在不破坏 DAG 的情况下做到这一点？