This comes off a real world production server but has been anonymised.
This example comes from a site that uses Entity Framework but doesn't use SQL Server profiler to see what queries EF is generating.
This is unfortunately not uncommon, there is often an assumption that there is no need to check every query and possibly rewrite some of them,
queryresult = (From p In ef.sometable Where p.col2 = action Order By p.Col3 Descending).Take(200).ToList
This is a frequently used query, in this example it is called 3 times within a minute although this is not always the case.
At first glance it seem too trivial to be an issue, just give me the 200 newest rows with a particular status, how hard can that be?
Note the huge number of reads and the massive cpu usage.
The reason is that EF generated the following SQL, Note the select of all the rows that match the col2 value and then the select of the top 200 from that result set.
The problem is that the subquery result set is non trivial.
SELECT TOP (200)
[Project1].[id] AS [id],
[Project1].[col2] AS [col2],
[Project1].[col3] AS [col3]
FROM ( SELECT
[Extent1].[id] AS [id],
[Extent1].[col2] AS [col2],
[Extent1].[col3] AS [col3],
FROM [dbo].[sometable] AS [Extent1]
WHERE [Extent1].[col2] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[col3] DESC
By simply removing the subquery we change the number of reads from 41,397 to 20 and reduce CPU usage from 1.201 seconds to 0.0016 seconds.
Nobody would have written the EF query if they were doing it by hand, but I can see why EF did it that way.
But and this is an extremely big but I can't say that a previous or later release of EF would generate the same query.
At first glance this may all sound like so what clearly the server had enough RAM to serve the reads from RAM and hardware is cheap.
I even have some sympathy with this view up to a point, but look at the CPU usage, duration of 0.4 seconds and CPU usage of 1.2 seconds, that suggests three cores full time for almost half a second for just one query.
CPU cores are not cheap and depending upon exactly what licences you have nor will the database server licence be.
Remember that we have just seen that the query could have been trivial, it's not like it has to be this resource intensive.
Of course if you have a high spec server then your backup server needs to be of the same spec.