100% CPU with bad execution plan

I have a massive problem with 100% CPU spikes because of a bad execution plan used by a specific query. I spend weeks now solve with by my own.

My Database

My sample DB contains 3 simplified tables.

[Datalogger]

CREATE TABLE [model].[DataLogger](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProjectID] [bigint] NULL,
CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[Inverter]

CREATE TABLE [model].[Inverter](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SerialNumber] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Inverter] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UK_Inverter] UNIQUE NONCLUSTERED 
(
    [DataLoggerID] ASC,
    [SerialNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [model].[Inverter] WITH CHECK
ADD CONSTRAINT [FK_Inverter_DataLogger]
FOREIGN KEY([DataLoggerID])
REFERENCES [model].[DataLogger] ([ID])

[InverterData]

CREATE TABLE [data].[InverterData](
    [InverterID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [DayYield] [decimal](18, 2) NULL,
 CONSTRAINT [PK_InverterData] PRIMARY KEY CLUSTERED 
(
    [InverterID] ASC,
    [Timestamp] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

Stats and Maintanance

The [InverterData] table contains multiple million rows (differs in multiple instances PaaS) partitioned in monthly junks.

All indexer are defragmentated and all stat rebuild/reorganized as needed on a daily/weekly turn.

My Query

The query is Entity Framework generated and also simple. But i runes 1,000 times per minute and performance is essential.

```
SELECT
[Extent1].[InverterID] AS [InverterID],
[Extent1].[DayYield] AS [DayYield]
FROM [data].[InverterDayData] AS [Extent1]
INNER JOIN [model].[Inverter] AS [Extent2] ON [Extent1].[Inverter

Look at the plans, there are a few differences between the good one and the bad ones. First thing to notice is that the good plan performs a seek on InverterDayData where as both the bad plans perform a scan. Why is this, if you check the estimated rows, you'll see that the good plan is expecting 1 row where as the bad plans are expecting 6661 and around 7000 rows.

Now take a look at the compiled parameter values,

Good Plan
@p__linq__1 ='2016-11-26 00:00:00.0000000'
@p__linq__0 =20825

Bad Plans
@p__linq__1 ='2018-01-03 00:00:00.0000000'
@p__linq__0 = 20686

so it's looking to me like it's a parameter sniffing issue, what parameter values are you passing into that query when it's performing badly?

There is an index recommendation in the bad plans on InverterDayData that looks sensible, I'd try running that in and seeing if it helps you. It might allow SQL to perform a scan on the table.

StackExchange Database Administrators Q#194679, answer score: 3