从应用程序服务器执行存储过程时速度较慢

在尝试优化查询的过程中,我遇到了一个有点奇怪的情况。 在一个存储过程中,我有一个查询,它返回一个用户等级:

SELECT @p_rank = COUNT(*) + 1
FROM leaderboard ur
WHERE ur.score > (SELECT ur2.score FROM leaderboard ur2 WHERE ur2.id = @p_id);

基于此查询,我在SP中编写了第二个查询,以获得按国家排列的排名。我创建了一个新的带有国家/地区列的排行榜表,并尝试了以下查询(基于第一个执行得非常好的查询)

SELECT @p_local_rank = COUNT(*) + 1
FROM leaderboard ur
WHERE country = @p_countryand and ur.score > 
(SELECT ur2.score FROM leaderboard ur2 WHERE ur2.id = @p_id);

我添加了一个 国家指数 ,这没有帮助,但实际上使事情变得更慢。 查询处理器建议添加 包括得分的国家指数 ,我尝试了这个建议。添加后,查询的性能比之前的所有查询都要好得多。 我更换了SP并在SSMS中进行了测试,效果非常好。

我一在生产中试用新SP,CPU就达到了 95+% 几乎立即(通常在附近 45% ). 只有删除此查询才能将CPU降低到正常值。 请注意,此SP大约每分钟执行100-150次。

在阅读了大量有关相关问题的问题后,我试图解决以下问题:

1. 已将SP更改为使用局部变量以防止 Parameter sniffing
2. 已清除执行计划缓存。这是azure DB,所以我使用了 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
3. 将ANSI\U NULLS设置为ON;根据建议 here
4. 使用更新的统计信息 sp_updatestats

似乎没有任何帮助,只要我将SP移至生产环境,CPU就达到95%+。

表格:

CREATE TABLE [dbo].[leaderboard](
[id] [int] NOT NULL,
[score] [int] NOT NULL CONSTRAINT [df_score_value]  DEFAULT ((100)),
[level] [int] NOT NULL CONSTRAINT [df_level_value]  DEFAULT ((1)),
[stage] [int] NOT NULL CONSTRAINT [df_stage_value]  DEFAULT ((1)),
[insert_date] [datetime] NULL,
[update_date] [datetime] NULL,
[daily_score] [int] NOT NULL CONSTRAINT [DF_leaderboard_daily_score]  DEFAULT ((0)),
[weekly_score] [int] NOT NULL CONSTRAINT [DF_leaderboard_weekly_score]  DEFAULT ((0)),
[country] [nchar](45) NULL,
CONSTRAINT [PK_leaderboard] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

索引位于以下列上:

1. 分数
2. 每日\u分数
3. 每周\u分数
4. 国家/地区包含分数

该表包含约300万条记录。

可以查看执行计划 here

有什么其他建议吗? `