主页 / dba / 问题

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

Martin Hope
Artashes Khachatryan
Asked: 2023-08-24 21:02:51 +0800 CST
  • 8

我尝试加载测试一个简单的插入存储过程

CREATE TABLE _test(ID BIGINT)

CREATE OR alter PROCEDURE dbo.test_sp
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN
        INSERT INTO _test
        SELECT CAST(RAND() * 10000 AS BIGINT)
    END
END

当我使用 SQLStress 工具运行此 sp 时，我得到的结果不SQL Compilation\sec等于Batch Request\sec.

有趣的事实是SQL Re-Compilations\sec0。性能计数器

optimized for ad-hoc和均已Forced parametrization启用。即使我将 sp 更改为简单的SELECT 1. 任何帮助将不胜感激。我使用 Microsoft SQL Server 2016 (SP3) (KB5003279)。

sql-server-2016
Martin Hope
CaseyR
Asked: 2023-05-12 22:05:59 +0800 CST
  • 5

我有一种情况，我需要将一组详细信息行放在一起，但“父”没有其他属性，“子”没有自己的目的。我从典型的（对我来说）方法开始

GroupOfSomething
    Id

SomethingDetail
    Id
    GroupOfSomethingId (FK)
    LookupToAnotherTable (FK)
    SomethingElse

在上面，Id 是说 INT 的 PK。想知道像下面这样只有一个表而不是父/子关系的东西是否更合适（GroupId 可能是一个 UNIQUEIDENTIFIER）

GroupOfSomethings
    GroupId
    Id
    LookupToAnotherTable (FK)
    SomethingElse

为了澄清，这是针对会计系统之类的东西。这些Somethings是帐户的 FK。将收集这些帐户，然后用户将这些帐户链接到一个或多个供应商。在链接供应商和帐户集合的级别上，还有其他元数据。然后该关系将成为另一个 1:many 集合以及其他 1:1 和 1:many 相关实体的父级。所以它最终看起来像下面这样（使用原始的 PK/FK 结构，没有定义所有其他 FK 表是什么）

GroupOfAccounts
    Id (PK)

GroupOfAccountDetail
    Id (PK)
    GroupOfAccountId (FK)
    AccountId (FK)

VendorGroupOfAccountInfo
    Id (PK)
    VendorId (FK)
    GroupOfAccountId (FK)

VendorGroupOfAccountInfoAdditionalDetail
    Id (PK)
    VendorGroupOfAccountInfoId (FK)
    FeeStructureId (FK)
    StateId (FK)


VendorGroupOfAccountRates
    Id (PK)
    GroupOfAccountDetail (FK)
    RatePercentage
    EffectiveDate

...
sql-server-2016
Martin Hope
youcantryreachingme
Asked: 2023-05-08 07:14:29 +0800 CST
  • 5

首先，这不是这个问题的重复。在那个问题中，这个人每次都在覆盖文件，解决方案是使用 INIT 或 NOINIT。

在我的例子中，我们为每个备份生成一个唯一的文件名——一个时间戳附加到文件名。

我们有一个代理作业，每晚为大约 20 个数据库运行完整备份。在大约一周的时间里，1 个数据库的大小似乎每晚都在显着增加。

我们运行第二个代理作业，每 15 分钟运行一次日志备份，但在晚上 11 点到凌晨 5 点之间停止运行。通常早上的第一件事是日志备份很大，然后它们的大小变小，直到人们开始工作，然后它们的大小各不相同。

所有这些多年来一直没有问题。

20 个数据库中只有一个显示此问题 - 每晚的完整备份都比前一晚大得多。

日志文件备份适用于该数据库和所有其他数据库。

我们有一个包含三个副本的可用性组。两个被设置为相互故障转移，第三个副本是只读的，用于繁重的查询、数据提取和报告。AG 控制台在所有服务器的页面下方一直显示绿色复选标记。

有问题的数据库是 20 个数据库中最大的一个。正常的完整备份大约是 25GB。它慢慢增加如下：33、35、36、41、47、54、64、73 GB。

相比之下，在此问题之前的所有 14 个备份中，文件大小相当稳定，约为 25GB。

我刚刚暂停了所有数据库的所有日志备份，并对受影响的数据库进行了完整备份，最后一次完整备份是 76GB。

sql-server-2016
Martin Hope
kedar
Asked: 2023-04-24 18:58:10 +0800 CST
  • 6

在我们的一个客户站点，我实施了高可用性 (HA) 解决方案。但是在备份的过程中，遇到了一个备份环境配置的问题。我们使用 Ola Hallengren 的 Maintenance Solution 脚本配置了备份环境。环境包括不同的 Windows Server 主机名和@@serverproperty，这导致备份过程因此错误而停止

(Message 50000)  Source: https://sqlhelps.com [SQLSTATE 01000] (Message 50000)       [SQLSTATE 01000] (Message 50000)  The @@SERVERNAME does not match SERVERPROPERTY('ServerName'). See https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server. [SQLSTATE 42000] (Error 50000)      [SQLSTATE 01000] (Message 50000)  Date and time: 2023-04-23 12:46:07 [SQLSTATE 01000] (Message 50000)       [SQLSTATE 01000] (Message 50000).  The step failed.

为了解决这个问题，我注释掉了如下所示的 T-SQL 脚本： --// Check @@SERVERNAME //-- --IF 'MainServer' <> CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) AND SERVERPROPERTY('IsHadrEnabled') = 1 --BEGIN -- INSERT INTO @Errors ([Message], Severity, [State]) -- SELECT 'The @@SERVERNAME does not match SERVERPROPERTY(''ServerName''). See ' + CASE WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server' WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/rename-a-sql-server-failover-cluster-instance' END + '.', 16, 1 --END

注释掉这个脚本后，备份过程恢复正常，全量备份和差异备份都做了。但是，当尝试在主副本上进行事务日志备份时，事务日志没有转储到目的地。

请注意，所有这些备份都是在主副本上执行的，备份首选项为主副本

如果您能就如何解决此问题分享您的想法和想法，我将不胜感激。谢谢。

sql-server-2016
Martin Hope
sql-noob
Asked: 2023-04-11 06:15:53 +0800 CST
  • 5

我有这些表：

Payments

vendor   | VendorLocation | VendorName   | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777      |                | Sony        | 5/1/2020   | 1.50   | 345           | ABC555
777      |                | Sony        | 5/1/2020   | 1.20   | 444           | ABC555
777      |  103           | Sony Music  | 5/1/2020   | 8.50   | 888           | XYZ888
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 789           | XYZ999
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 833           | XYZ111

Vendor Location

vendor   | VendorLocation | LocationType |Address 1   | State | City
---------------------------------------------------------------------
777      |  103           | X            |123 Ave      | CA    | Rivertown
777      |  105           | Z            |666 Ave      | CA    | Northtown
777      |  106           | Z            |888 Ave      | CA    | Southtown

Vendor Address

vendor   | VendorLocation | Address 1  | State | City
---------------------------------------------------------------------
777      |                | 1 Main St  | CA    | Rivertown
777      |  103           | 123 Ave    | CA    | Rivertown
777      |  105           | 666 Ave    | CA    | Northtown

我想要的输出是：

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $5.00   | 666 Ave

但是，我得到的结果低于重复且不正确的 totalSpend 金额：

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $7.70   | 1 Main St


This is query I have:

select distinct
ap.vendor Vendor_ID
,vl.VendorLocation
,vl.LocationType
,ap.VendorName
,count(ap.transactionID) as TotalTransactions
,sum( ap.amount) as TotalSpend
,ad.Address1
from payments ap
join  VendorLocation vl
on ( (vl.vendor=ap.vendor and vl.VendorLocation=ap.VendorLocation)
or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and vl.LocationType in ('Z')
) join VENDORADDRESS ad
on (ad.VENDOR=ap.vendor AND ad.VendorLocation=ap.VendorLocation)
where
vl.LocationType in ('Z') and
ap.CheckDate>='4/1/2022'
group by
vl.vendor
,vl.LOCATION_CODE
,vl.LocationType
,ap.VendorName
,ad.Address1
sql-server-2016
Martin Hope
DynamoRanger
Asked: 2023-02-16 15:42:47 +0800 CST
  • 6

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

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

XY_GridPointID X_网格点 Y_GridPoint
250000650000 250000 650000
250500650500 250500 650500
251000651000 251000 651000
251500651500 251500 651500
252000652000 252000 652000
252500652500 252500 652500
253000653000 253000 653000
253500653500 253500 653500
2540006540​​00 254000 654000
254500654500 254500 654500
255000655000 255000 655000

然后我有另一个表，每一行都有一个特定的 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

sql-server-2016
Martin Hope
Sir Swears-a-lot
Asked: 2022-11-22 19:09:50 +0800 CST
  • 6

我正在尝试查询如下所示的 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
Martin Hope
Danielle Paquette-Harvey
Asked: 2022-10-14 06:54:12 +0800 CST
  • 5

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

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

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

sql-server-2016
Martin Hope
user262197
Asked: 2022-10-13 19:20:36 +0800 CST
  • 1

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。

sql-server sql-server-2016
Martin Hope
variable
Asked: 2022-10-10 21:43:57 +0800 CST
  • -1

链接：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 sql-server-2016