我有一位开发人员需要能够在 SQL Server 代理中创建作业。它们是安全组 SQLDA 的一部分,我在 msdb 中授予了该组 SQLAgentUserRole。他们仍然只能查看访问权限。我是否还需要在 msdb 中授予他们 db_datawriter 以便他们能够创建工作?
运行 SQLServer 2019。权限通过 Active Directory 组进行管理。
昨天下午晚些时候,我将 4 个 tempdb 数据文件添加到现有的 4 个中,总共 8 个(SQL 服务器上的 16 个处理器)。我还预先将它们种植到接近 100% 的可用空间。
今天,我们的一位开发人员联系了我,她说昨天她正在运行一个查询,该查询将在 3-5 秒内将一组初始结果返回到 ssms 显示,整个查询将在 2-3 分钟内完成。今天查询需要 5 分钟才能完成,大约 2.5 分钟内没有结果显示在 ssms 中。
当时没有其他查询在运行。CPU 使用率很低。我反弹了服务器,没有任何改善。所以现在我想知道我的 tempdb 更改是否导致性能下降。我看不出如何,但偏执狂开始了。我运行了查询并在它执行时监视了 tempdb,它似乎根本没有使用 tempdb,我觉得这很奇怪,因为它有一个我认为使用的 GROUP BY 语句临时数据库。该表包含大约 2200 万行,查询返回大约 780 万行。
select
DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0) as Quarter
,[PartnerCD]
,[PartnerNM]
,[PartnerGRP]
,[BizMemberID]
,[MemberID]
,[BizType]
,[RateCD]
,[Rate]
,sum([MemberMonth]) as MemberQuarter
,[CurrentAge]
,[AgeAtTime] = CASE WHEN dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') < 0 THEN NULL ELSE dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') END
,AgeCategory = dbo.fn_CalculateAgeCatYrsOrdered(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)))
,[BirthDT]
,[ZipCD]
FROM [Partner].[dbo].[Membership]
group by DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)
,[PartnerCD]
,[PartnerNM]
,[PartnerGRP]
,[BizMemberID]
,[MemberID]
,[BizType]
,[RateCD]
,[Rate]
,[CurrentAge]
,[BirthDT]
,[ZipCD]
可能是我的更改导致性能下降还是巧合?
我正在运行 SQL Server 2019 企业版。每个月,我们都会运行一个存储过程来加载数百万条服务日期可以追溯到五年前的记录。我为 4 个数据文件分配了 400GB 的空间,并为日志文件分配了 100GB 的空间。作业经常失败,因为日志文件因活动事务而被填满。数据库处于简单恢复模式。所以,我相信它应该在每笔交易结束时清除。开发人员更改了工作,使其一次循环并加载一年的记录。
DROP TABLE IF EXISTS #UnpvtDx;
SELECT ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.DxCode
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
DxCode FOR CodeLine
IN
(
Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
)
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;
DROP TABLE IF EXISTS #UnpvtPointer;
SELECT ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.Pointer
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
Pointer FOR CodeLine
IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;
INSERT INTO PROD.Claim.ClaimDiag
(
ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
DiagnosisCD = dx.DxCode,
DiagnosisDESC = diag.DiagnosisDESC,
DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
ON dx.ClaimDetail_ID = det.ClaimDetail_ID
AND dx.ClaimHeader_ID = det.ClaimHeader_ID
AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
ON point.ClaimDetail_ID = det.ClaimDetail_ID
AND point.ClaimHeader_ID = det.ClaimHeader_ID
AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
ON dx.DxCode = diag.DiagnosisCD
AND diag.ICDVersion = 'ICD10CM'
AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;
使用以下命令从 SQL 代理作业执行存储过程:
DECLARE @year INT
DECLARE cur CURSOR FOR
SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)
OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Claim.sp_UpdateClaimDiag @year
FETCH NEXT FROM cur INTO @year
END
CLOSE cur
DEALLOCATE cur
循环的结束是否被视为一个事务,因此日志文件应该在处理完每一年的记录后清空,或者日志文件是否继续填充,直到作业遍历每个循环并加载所有年份的记录?
我还将日志文件增加到 150GB,但这会最大化可用空间(不会低于 10% 的缓冲区)。
发布整个存储过程代码。
USE [Prod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
BEGIN
SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- variable declaration
DECLARE
@transactional BIT
, @trancount INT
, @err INT
, @procname SYSNAME
, @error INT
, @message VARCHAR(4000)
, @xstate INT
, @RecordCount int;
SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
-- 0 = no: will not execute batches inside a transaction; a partial success of procedure is possible
-- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
, @transactional = 0
-- optionally begin transaction and begin try block
IF @transactional = 1 SET @trancount = @@TRANCOUNT
BEGIN TRY
IF @trancount = 0 and @transactional = 1
BEGIN TRANSACTION
ELSE IF @transactional = 1
SAVE TRANSACTION p1
----------------------------------------------------------------------------------------
---Unpivot columns to Rows into Temp tables
----------------------------------------------------------------------------------------
---ICD9CM & ICD10CM
DROP TABLE IF EXISTS #UnpvtDx;
SELECT
ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.DxCode
INTO #UnpvtDx
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
DxCode FOR CodeLine IN
(
Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
Diagnosis13CD
)
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;
--Select top 100 * from #UnpvtDx where DxCode is null
DROP TABLE IF EXISTS #UnpvtPointer;
SELECT
ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.Pointer
INTO #UnpvtPointer
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
Pointer FOR CodeLine IN
(
DiagPointer1, DiagPointer2,
DiagPointer3,DiagPointer4
)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;
--Select top 100 * from #UnpvtPointer
----------------------------------------------------------------------------------------
--- INSERT INTO yearly records from the temp table
----------------------------------------------------------------------------------------
INSERT INTO Prod.Claim.ClaimDiag (
ClaimHeader_ID,
ClaimDetail_ID,
SourceID,
EDWLoadDTS,
PartnerCD,
PartnerNM,
ClaimID,
ClaimServiceLine,
ClaimStatus,
CCOMemberID,
MemberID,
PlaceOfServiceCD,
ServceFromDT,
ServiceToDT,
ClaimForm,
TypeOfBillCD,
DiagnosisCD,
DiagnosisDESC,
DiagPointer
)
SELECT DISTINCT
det.ClaimHeader_ID,
det.ClaimDetail_ID,
det.SourceID,
det.EDWLoadDTS,
det.PartnerCD,
det.PartnerNM,
det.ClaimID,
det.ClaimServiceLine,
det.ClaimStatus,
det.CCOMemberID,
det.MemberID,
det.PlaceOfServiceCD,
det.ServiceFromDT,
det.ServiceToDT,
det.ClaimForm,
det.TypeOfBillCD,
DiagnosisCD = dx.DxCode,
DiagnosisDESC = diag.DiagnosisDESC,
DiagPointer = point.Pointer
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
ON dx.ClaimDetail_ID = det.ClaimDetail_ID
AND dx.ClaimHeader_ID = det.ClaimHeader_ID
AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
ON point.ClaimDetail_ID = det.ClaimDetail_ID
AND point.ClaimHeader_ID = det.ClaimHeader_ID
AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
ON dx.DxCode = diag.DiagnosisCD
AND diag.ICDVersion = 'ICD10CM'
AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year
--AND Year(det.ServiceFromDT) = 2021--for testing
--and det.ClaimID ='21006E06455'--for testing
----------------------------------------------------------------------------------------
--insert into updatelog table
SET @RecordCount = @@ROWCOUNT;
DECLARE @procName1 SYSNAME
SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
SELECT GETDATE(),
'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
@procName1,
'Claim.ClaimDiag'
----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #UnpvtDx
DROP TABLE IF EXISTS #UnpvtPointer
----------------------------------------------------------------------------------------
SPEXIT:
IF @transactional = 1 and @trancount = 0 COMMIT
END TRY
----------------------------------------------------------------------------------------
-- error handling with catch
BEGIN CATCH
SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
IF @transactional = 1 and @xstate = -1 ROLLBACK
IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
DROP TABLE IF EXISTS #claims
SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4))
RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message)
RETURN @error
END CATCH
RETURN 0
END
GO
我有一个存储过程,它将三个大表连接在一起(每个大约 2000 万条记录)并将记录加载到一个临时表中。然后将临时表中的数据合并到一个包含大约 6000 万条记录的现有表中。
服务器离线并显示以下错误消息:
由于内存压力,AppDomain 2 (SSISDB.dbo.[runtime].1] 被标记为卸载。
让服务器重新联机后,我重新启动 SQL 服务以清除任何可能一直存在的进程。再次开始工作,并且没有问题地完成。
我正在运行具有 128GB RAM 的 SQL Server 2019。64 位虚拟服务器上的最大服务器内存为 117964MB。有人在任务管理器中告诉我内存使用率为 94%,这可能是问题所在。但是 SQL 不会占用所有可用内存并保留它吗?所以看起来它正在按预期运行。
sp_WhoIsActive
揭示了一些状态为“暂停”和“等待命令”的查询,但我认为这些不会产生太大影响。128GB 的内存似乎足够了,但我想这与它被要求做的工作有关。知道如何排除故障或防止再次发生吗?
服务器的数据驱动器约为 1.6TB。连接中的两个较大的数据库是 10GB 的 1900 万行和 13GB 的 2000 万行。这些进入一个临时表,然后MERGE
进入一个有 5300 万行的 26GB 表。
请求的内存授权为 45GB,实际为 30GB。这项工作是在正常工作时间之外进行的,所以应该没有竞争性查询,但我不能 100% 确认有人没有工作到很晚。
我确实注意到它也必须这样做CONVERT_IMPLICIT
。这对所需的内存有重大影响吗?
查询计划链接: https ://www.brentozar.com/pastetheplan/?id=SyXaty7xK
我有一堆没有主键的表。有些确实已经有一个非聚集索引,该索引由主键(ClaimID、LineNBR)组成。使用的列当前设置为接受 NULL,因此我需要更改它。为此创建主键是否有价值,是否会使在相同列上的非聚集索引变得多余?最好只留下非聚集索引,删除非聚集索引并使其成为主键,还是添加主键并保留非聚集索引?
我在两个不同的服务器上有一个生产数据库和一个开发数据库。这两个数据库都是从登台服务器上的同一个数据仓库提供的。有一系列自动化的 SQL 作业可以运行和填充数据库中的表。作业从 Dev 复制到 Prod,因此它们也应该相同。然而,Prod 的增长速度远远快于 Dev。
数据库中大约有 10 亿行数据。Prod 中的总数据文件大约大 123GB。总索引文件大约大 31GB。我对此很陌生,但我希望这两个数据库的大小非常相似。如果有的话,我希望 Dev 有一些额外的“垃圾”并且可能是更大的数据库。
任何想法如何找到这种尺寸差异的来源?如果需要,我可以增加 Prod 上的磁盘空间,但向我表明可能存在需要解决的问题。如果可能的话,我想收回 153GB。
诚然,在这方面我是个菜鸟,但我检查了每个环境中的 Index 文件夹,它们似乎都有一个 Index。我检查了属性,它们看起来也一样。更多索引是否也会导致数据文件大小增加?
我正在 Prod 和 Dev 中运行来自 Ola Hallengren 的 IndexOptimize 脚本,并希望它能够充分处理任何重要的碎片。我实际上并没有将任何数据从 Dev 迁移到 Prod。我们有一个托管数据的临时服务器。一组 SSIS 和存储过程将数据从登台移动到 Dev 上的数据库。更多 SSIS 作业和存储过程填充了 Dev 中的表。SSIS 和存储过程从 Dev 提升到 Prod,并在 Prod 上独立运行。Prod 作业访问与 Dev 相同的登台服务器。