The index is fine.
The predicate is not.
Example
WHERE YEAR(OrderDate) = 2024
Why it fails
Functions on columns prevent index seeks.
Rewrite
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
Cause → Effect
Function → scan → CPU spike → slow queries.