Fresh install of SQL Server 2022 Express on Windows 2019 very slow running selects

Question

I restored a database from SQL Server 2016 Express on a Windows 2012 server and now the selects are taking ages to run. Everything worked fine on SQL Server 2016 Express.

This is a new Windows 2019 server (8GB RAM, Intel Xeon Processor (4 CPUs), ~2.5GHz) and SQL Server 2022 Express install.

Windows Defender Antivirus Service is installed but as I am renting the server I am unable to stop it.

select * 
from question with (NOLOCK) 
where quizId = 100 

takes less than 1 second to run

select * 
from question with (NOLOCK) 
where quizId = 100 
order by displayno 

This however takes 25 seconds to run on new server but runs instantly on the old server.

Does anyone know if I need to change some settings to get the selects to work as they did before?

Thanks.

Answer 1

The answer table has 8 million records and I found the query that bottlenecked the system.

I changed this

SELECT * FROM dbo.answer INNER JOIN dbo.question ON dbo.answer.questionId = dbo.question.questionId  where question.quizId = @quizId ORDER BY dbo.answer.answerId

To this

SELECT * FROM dbo.answer INNER JOIN dbo.question ON dbo.answer.questionId = dbo.question.questionId  where question.quizId = @quizId ORDER BY dbo.question.questionId, dbo.answer.answerId

I do not know why this caused an issue in SQL Express 2022 and not in SQL Express 2016