主页 / user-67252

Greg's questions

Martin Hope
Greg
Asked: 2023-06-15 07:43:01 +0800 CST
  • 6

我有一个对非常大的表执行聚簇索引扫描的查询，该扫描在某些情况下会导致超时。需要帮助理解为什么它不使用定义的非聚集索引。

这是查询：

DECLARE @StartDate datetime = '2023-03-16 00:00:00';

DECLARE @TerminalIds [dbo].[udtBigInt]; -- user defined table with a BIGINT col
INSERT INTO @TerminalIds ([Id])
SELECT [EquipmentId]
FROM #mechanicsTerminal;

SELECT [DataRecId]
    , [RawData]
    , [RecordingTime]
    , [EquipmentId]
FROM [dbo].[Data]
WHERE [EquipmentId] IN (SELECT [Id] FROM @TerminalIds)
AND [RecordingTime] >= @StartDate
ORDER BY [DataRecId] DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

这是表定义：

CREATE TABLE [dbo].[Data](
    [DataRecId] [bigint] IDENTITY(1,1) NOT NULL,
    [RawData] [nvarchar](max) NOT NULL,
    [CreatedDateUTC] [datetime] NOT NULL,
    [RecordingTime] [datetime] NOT NULL,
    [EquipmentId] [bigint] NOT NULL,
    [DataSetId] [uniqueidentifier] NULL,
    [SourceType] [nvarchar](50) NULL,
    [Name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED ( DataRecId] ASC)
GO
ALTER TABLE [EJ].[Data]  WITH CHECK ADD  CONSTRAINT [chk_Data_RawData] CHECK  ((isjson([RawData])=(1)))
GO

以下是索引：

CREATE INDEX [nc_Data_DataSetId_includes] 
ON [dbo].[Data] ( [DataSetId] ) INCLUDE ( [DataRecId], [RawData], [RecordingTime]);
GO
CREATE INDEX [nc_Data_EquipmentId_includes] 
ON [dbo].[Data] ( [EquipmentId] ) INCLUDE ( [DataSetId], [RawData]);
GO
CREATE INDEX [nc_Data_EquipmentId_RecordingTime_Name_includes] 
ON [dbo].[Data] ( [EquipmentId], [RecordingTime], [Name] ) INCLUDE ( [DataRecId], [RawData]);
GO

这是实际的执行计划：

https://www.brentozar.com/pastetheplan/?id=B1oq7TDD3

使用此特定数据，查询将在亚秒级执行。

然而，有一种情况是 中只有三个记录@TerminalIds，而 中没有匹配的记录[dbo].[Data]，查询永远不会完成。这是 45 秒后的计划。

https://www.brentozar.com/pastetheplan/?id=rJJMRavDn

我试过的：

  • 更新统计数据并重新编译主过程
  • 继续而不是用子句做子INNER JOIN查询@TerminalIdsIN
query-performance
Martin Hope
Greg
Asked: 2022-10-01 13:04:45 +0800 CST
  • 0

SQL 2017 上的这个简化查询需要 40 多秒才能完成，我怀疑它的参数嗅探问题，但不是 100% 确定。

exec sp_executesql N'
SELECT
    T.[TicketRecId]
    , T.[Title]
FROM dbo.Ticket T
INNER JOIN [dbo].[State] S  
    ON S.[StateRecId] = T.[StateRecId]  
INNER JOIN [dbo].[StateType] ST  
    ON S.[StateTypeRecId] = ST.StateTypeRecId
INNER JOIN [dbo].[Board] B  
    ON B.[BoardRecId] = T.[BoardRecId]  
WHERE 1=1
    AND ST.[Name] NOT IN (''Closed'',''Canceled'')
    AND T.BoardRecId IN (SELECT items FROM dbo.Split(@BoardRecId, '',''))
    --AND T.BoardRecId = @BoardRecId2
    --AND T.BoardRecId = 17
ORDER BY T.[TicketRecId] ASC
OFFSET (@PageNo - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
',N'@PageNo int,@PageSize int,@TeamRecId int,@BoardRecId VARCHAR(2),@BoardRecId2 INT'
    ,@PageNo=1,@PageSize=50,@TeamRecId=4,@BoardRecId ='17',@BoardRecId2=17
  • dbo.Ticket此查询返回表中 170 万行中的 15 行
  • 表State, StateType,Board真的很小，分别有 400、20、35 行
  • 在WHERE子句中，如果我将IN过滤器换成=for T.BoardRecId，它会在 7 秒内完成
  • 删除OFFSET FETCH，原始查询在 13 秒内完成，前一个查询在 1 秒内完成
  • 如果我设置参数值@BoardRecId='14'，持续时间会提高（大部分表包含具有此值的行）
  • 尝试使用表的全扫描更新统计信息，性能没有变化
  • 尝试创建不同的索引，性能没有提高
  • 试过OPTION (RECOMPILE)里面sp_executesql没有帮助
  • 尚未尝试重建索引，因为这必须在维护时间内完成
  • 尝试用dbo.Split表变量和/或临时表替换，没有改进

我需要支持 multiple 的能力BoardRecIds，这就是函数背后的原因dbo.Split，它所做的只是打破一个逗号分隔的字符串，以便在IN子句中使用。

该模式在列方面要大得多，因此尝试简化它，请注意连接的列都有索引。

CREATE TABLE [Support].[Ticket] (
    [TicketRecId]             BIGINT             IDENTITY (1, 1) NOT NULL,
    [BoardRecId]              INT                NOT NULL,
    [StateRecId]              INT                NOT NULL,
    [Title]                   VARCHAR (250)      NOT NULL,
    [IsDeleted]               BIT        NOT NULL,
    [IsTemplate]              BIT        NOT NULL,
    CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
    CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO

CREATE TABLE [Support].[Ticket] (
    [TicketRecId]             BIGINT             IDENTITY (1, 1) NOT NULL,
    [BoardRecId]              INT                NOT NULL,
    [StateRecId]              INT                NOT NULL,
    [Title]                   VARCHAR (250)      NOT NULL,
    [IsDeleted]               BIT        NOT NULL,
    [IsTemplate]              BIT        NOT NULL,
    CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
    CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO

CREATE TABLE [dbo].[State] (
    [StateRecId]     INT                IDENTITY (1, 1) NOT NULL,
    [BoardRecId]     INT                NOT NULL,
    [StateTypeRecId] INT                NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [SortOrder]      SMALLINT           NOT NULL,
    [IsDefault]      BIT                NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_State_StateRecId] PRIMARY KEY CLUSTERED ([StateRecId] ASC),
    CONSTRAINT [FK_State_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [dbo].[Board] ([BoardRecId]),
    CONSTRAINT [FK_State_StateTypeRecId] FOREIGN KEY ([StateTypeRecId]) REFERENCES [dbo].[StateType] ([StateTypeRecId]),
);
GO

CREATE TABLE [dbo].[StateType] (
    [StateTypeRecId] INT                IDENTITY (1, 1) NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_StateType_StateTypeRecId] PRIMARY KEY CLUSTERED ([StateTypeRecId] ASC),
);
GO

CREATE TABLE [dbo].[Board] (
    [BoardRecId]     INT                IDENTITY (1, 1) NOT NULL,
    [TeamRecId]      INT                NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [IsExternal]     BIT                DEFAULT ((0)) NOT NULL,
    [IsDefault]      BIT                DEFAULT ((0)) NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    [UpdatedDateUTC] DATETIMEOFFSET (0) DEFAULT (SYSUTCDATETIME()) NOT NULL,
    CONSTRAINT [PK_Board_BoardRecId] PRIMARY KEY CLUSTERED ([BoardRecId] ASC),
    CONSTRAINT [FK_Board_TeamRecId] FOREIGN KEY ([TeamRecId]) REFERENCES [dbo].[Team] ([TeamRecId]),
);
GO
CREATE NONCLUSTERED INDEX [nc_TicketType_BoardRecId]
    ON [dbo].[Board]([BoardRecId] ASC);
GO

使用@BoardRecId IN 的简化计划（SELECT items FROM dbo.Split(...) - 40+ 秒
使用@BoardRecId = @BoardRecId 的简化计划- 7 秒
使用@BoardRecId = 17 和 dbo.Split(...) 的简化计划- 12 秒

这些持续时间都不是最佳的，但第 2 和第 3 比 40+ 秒要好得多，所以只是想弄清楚如何充分利用糟糕的情况，并希望有人能在这里提供灵丹妙药。

sql-server query-performance
Martin Hope
Greg
Asked: 2021-03-13 12:29:05 +0800 CST
  • 1

问题：

在运行 sqlpackage.exe 以应用 sql dacpac 时，它偶尔会在代理/SignalR 相关对象上失败，这会破坏构建：

*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the service 'Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver', because it does not exist or you do not have permission.
Error SQL72045: Script execution error.  The executed script:
DROP SERVICE [Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver];

这是我们的sqlpackage.exe命令行参数：

/p:TreatVerificationErrorsAsWarnings=True /p:DoNotAlterChangeDataCaptureObjects=True /p:DropIndexesNotInSource=True /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers /p:BlockOnPossibleDataLoss=False

同样，失败率是每 10 次运行中的 1 次，并且没有人手动删除这些对象，因此我不知道为什么如果它知道它就无法删除该对象。有什么好的解决方案可以使其始终如一地应用吗？

sql-server service-broker
Martin Hope
Greg
Asked: 2018-04-21 10:58:37 +0800 CST
  • 2

尝试在生产中的大型表上启用压缩，但我们现在没时间了（已经运行了 6.5 小时）。

取消操作并让它回滚有什么影响？

试图确定我们是否应该通电并让它完成或杀死它并让它回滚。但是，如果回滚 6.5 小时的操作需要同样长的时间，那么我们可能只想让它完成。

我们在磁盘速度较慢的 QA 中对其进行了测试，它在 4 小时内完成，因此我们不完全确定为什么要花这么长时间。

须知：

  • 表大小为 271 GB，大约。3000万行
  • 在 3 小时标记处，驱动器已满，我们在大约 40 分钟后将其扩展
  • 普遍的等待类型是 IO_Completion
  • sp_whoisactive 显示 CPU 和 physical_reads 增加，而读/写在相当长的一段时间内没有移动
  • 这是在 SQL Server 2017 标准版、Windows Server 2016 上

这是我们的声明：

ALTER INDEX [PK_t1] ON [dbo].[t1] REBUILD PARTITION = ALL 
WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF
    , MAXDOP = 3, DATA_COMPRESSION = PAGE );
sql-server index
Martin Hope
Greg
Asked: 2017-11-10 12:46:54 +0800 CST
  • 3

我们有将 XML 数据存储为 varchar(MAX) 的大型表。数据仅供参考/历史用途，未经查询。根据我所阅读的内容，存储为 XML 数据类型而不是 VARCHAR(MAX) 应该会节省空间，但我的测试显示并非如此。请参见下文，其中 t1_XML 的大小小于 t1_NVARCHARMAX，但大于 t1_VARCHARMAX。

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';

在此处输入图像描述

sql-server sql-server-2012
Martin Hope
Greg
Asked: 2017-11-03 10:51:02 +0800 CST
  • 2

我正在尝试恢复几个 dev-dbs，其中数据目录位于无意中删除的跨区分区上。我们能够使用恢复软件恢复文件夹/文件，但是实际的 MDF 文件似乎有一些问题。

任何连接或修复的尝试都将失败，并出现以下情况：

SQL Server 检测到基于逻辑一致性的 I/O 错误：页面损坏（预期签名：0x00000000；实际签名：0x7bd02384）。它发生在读取文件“G:\data\database.mdf”中数据库 ID 9 中偏移量 0000000000000000 处的页面 (1:0) 期间。

我尝试过的步骤：

  • 为附加创建数据库
  • dbcc checkdb(database, repair_allow_data_loss) - 在创建空数据库并将 MDF 文件复制到新数据库之后）

注意 - 恢复过程识别了具有不同扩展名的同一文件的几个不同版本，我已经尝试过每个文件，同样的错误。没有备份。

还有什么可以尝试的吗？

sql-server sql-server-2012
Martin Hope
Greg
Asked: 2017-03-23 08:13:57 +0800 CST
  • 2

我正在寻找替换 CLR 代码（SQL 2012）中的一些硬编码值，我在这里遇到了配置文件的使用 - https://stackoverflow.com/questions/16177769/assembly-configuration-clr-stored-procedure。一些回复说，与配置文件相比，将键/值对保存在表中更好，原因之一是它在 SQL 2014+ 中不受支持。

我们的开发人员说它在 SQL 2014 中运行良好，所以我不关心这一点，但我确实认为将值存储在本地数据库的表中是一个更好的主意。

  1. 其他人可以确认 SQL 2014+ 是否支持配置文件吗？
  2. 是否有任何关于在表中存储键/值对的最佳实践的文档，或者有什么理由不这样做？
sql-server sql-server-2012
Martin Hope
Greg
Asked: 2017-02-10 10:35:30 +0800 CST
  • 2

我有版本sp_BlitzFirst 2016-12-10。

我执行了以下操作：

sp_blitzfirst @ExpertMode = 1, @CheckProcedureCache = 1, @OutputType = 'Opserver1'

下面的输出显示 SPID 124 被 SPID 143 阻止，但是 SPID 143 没有出现在任何输出中。还有另一种方法可以让 sp_BlitzFirst 也显示 blocking_sesesion_id 的信息，这样我就可以看到正在执行什么查询吗？

在此处输入图像描述

sql-server sp-blitzfirst
Martin Hope
Greg
Asked: 2016-06-15 18:04:20 +0800 CST
  • 7

这适用于 SQL Server 2012。

我正在对 Dynamics AX 环境中的整体性能低下进行故障排除。一切都很慢 - 应用程序本身、报告、临时等。马上，我可以看到没有配置 SQL Server 的最佳实践 - MAXDOP、tempdb、特定于 Dynamics 的跟踪标志和索引/碎片化维护。我很确定存在低效查询、缺失或非最佳索引等。

我花了一点时间查看等待统计数据，但现在我想知道我是否应该首先应用所有推荐的最佳实践（DynamicsAX 的行业标准最佳实践），然后解决缓慢的问题，或者深入研究每个问题一个，并在它出现时解决它？我知道有人会说，在您确定需要翻转之前不要开始翻转开关。

您将如何处理这种情况？

sql-server sql-server-2012