Stored procedure slow when executed from application server

Question

While in the process of trying to optimize a query, I'm facing a bit odd situation. In a stored procedure I've had this query, which return a user rank:

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

Based on this query I've written a second query in the SP, to get a rank by country. I've created a new leaderboard table with a country column, and tried the following query (based on the first query which performs very good)

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);

This query didn't perform well. I've added a country index, which didn't help, but actually made things much slower. The Query Processor suggested adding a country index which will include also score, and I tried this suggestion. After adding it the query performed much better than all prior queries. I've changes the SP and tested it in SSMS, and it worked very well.

As soon as I tried the new SP in production the CPU reached 95+% almost immediately (usually it's around 45%). Only removing this query lowered the CPU back to it's normal values. Note that this SP is executed approximately 100-150 times per minute.

After reading a lot of questions on related issues, These are the things I've tried to resolve it:

1. Changed the SP to use local variables to prevent Parameter sniffing
2. Cleared execution plan cache. it's an azure DB so I've used ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
3. SET ANSI_NULLS ON; as suggested here
4. Updated statistics using sp_updatestats

Nothing seems to help and as soon as I move the SP to production the CPU reaches 95%+.

The table:

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)
)

Indexes are on the following columns:

1. score
2. daily_Score
3. weekly_score
4. country INCLUDE score

The table contains about 3 Million records.

Execution plan can be viewed here

Any suggestion what else can be done? `

Answer 1

It seems there were some datatype mismatches going on between parameters and columns. This can be very problematic on large datasets because the engine has to convert the data of every single row which can cause huge performance problems. Also there may be some parameter sniffing going but it seems you figured that part out already.

I would also suggest adding a table for countries and then having only the ANSI country code in your table. This would be better from a normalization perspective.