sql-server - SSMS VS 计划作业中运行过程的 CPU 使用率

Question

我有一个 SQL 过程，它使用游标遍历表中的 ~326000 个条目，根据这些条目计算时间，并将每个条目的结果插入另一个表中。如果我从 Microsoft SQL Server Management Studio 运行此过程，它会毫无问题地运行。我希望该程序在每个星期日到星期一晚上运行。所以我为它创建了一个预定的工作。如果作业运行，它将占用 100% 的 CPU 使用率。我试图用它来限制最大 CPU 使用率，但它没有用。

我的程序称为 Player Stats。

CREATE RESOURCE POOL Pool_PlayerStats WITH
(
    max_cpu_percent=10, 
    cap_cpu_percent=10 -- 2012+ only 
);
GO

CREATE WORKLOAD GROUP Group_PlayerStats
  USING Pool_PlayerStats;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT CASE ORIGINAL_LOGIN() WHEN N'Player Stats'
    THEN N'Group_PlayerStats' ELSE N'default' END);
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

我将函数 dbo.RGClassifier 添加到主数据库，而计划的作业应该在另一个数据库上运行。那是正确的吗？

为什么使用 SSMS 我没有任何问题，但我有计划的工作？我怎样才能解决这个问题？

编辑：

正如这里所问的那样，程序是：

BEGIN

DECLARE @PlayerId INT,
  @GameMinutes INT,
  @SumGames INT,
  @SumAfk INT

DECLARE  C CURSOR LOCAL FOR SELECT ID FROM PLAYER  WITH (NOLOCK)

DELETE FROM dbo.PlayerStatistics

OPEN C

FETCH NEXT FROM C INTO @PlayerId

WHILE @@FETCH_STATUS = 0

BEGIN

 select @GameMinutes=COALESCE(sum(a.Diff), 0) from
 (select DATEDIFF(Minute, Game.TimeStarted, Game.TimeEnded) Diff from Player join GamePlayer on Player.Id = GamePlayer.PlayerId
 join Game on Game.Id = GamePlayer.GameId
 where Player.Id = @PlayerId
 and TimeEnded >= DATEADD(DAY, -7, GETDATE())
 ) a

 select @SumAfk=COALESCE(sum(a.Diff), 0) from 
 (
  select DATEDIFF(Minute, Game.TimeStarted, Game.TimeEnded) Diff from Player join GamePlayer on Player.Id = GamePlayer.PlayerId
  join Game on Game.Id = GamePlayer.GameId
  join GameKill on GameKill.GameId = GamePlayer.GameId
  where Player.Id = @PlayerId
  and GameKill.KillMethodId In (9, 17)
  and GameKill.VictimId = @PlayerId
  and TimeEnded >= DATEADD(DAY, -7, GETDATE())
 ) a

 select @SumGames=COALESCE(count(*), 0) from GamePlayer join GameKill on GamePlayer.GameId = GameKill.GameId
 where GameKill.KillMethodId NOT IN (1, 9, 17)
 and GamePlayer.PlayerId = @PlayerId
 and GameKill.TimeStamp >= DATEADD(DAY, -7, GETDATE())
 and GameKill.VictimId = @PlayerId

 SET @GameMinutes = @GameMinutes - @SumAfk

 IF (@GameMinutes < 0)
 BEGIN
  SET @GAMEMINUTES = 0
 END

 insert into dbo.PlayerStatistics(PlayerId, GameCount, InGameMinutes)
 values (@PlayerId, @SumGames, @GameMinutes)

 -- set the donation level

 IF (@GameMinutes < 360)
 BEGIN
  update Player set DonationLevel = 0 where Id = @PlayerId
 END

 IF (@GameMinutes >= 360)
 BEGIN
  update Player set DonationLevel = 10 where Id = @PlayerId
 END

 IF (@GameMinutes >= 600)
 BEGIN
  update Player set DonationLevel = 50 where Id = @PlayerId
 END

 IF (@GameMinutes >= 840)
 BEGIN
  update Player set DonationLevel = 100 where Id = @PlayerId
 END

 FETCH NEXT FROM C INTO @PlayerId

END

END

作业以 PC 管理员身份登录。我应该将函数 dbo.RGClassifier() 更改为 PCNAME\Administrator 吗？

标签： sql-server