I'm having a problem with a query usually being so slow on first time run that it times out and then executing in under a second once it's loaded into cache.
The query looks something like this
exec sp_executesql N'SELECT [Project1].RecordID AS [RecordId], ... FROM ( SELECT [Extent1].RecordId] AS [RecordId], ... FROM Items AS [Extent1] LEFT OUTER JOIN ItemParameters as [Extent2] ON [Extent1].[RecordId] = [Extent2].[SampleId] WHERE ... ) as [Project1] ORDER BY [Project1].CreatedDate DESC, [Project1].RecordId ASC, ...
The query is generated by EntityFramework for me and only has this one join. The Items table is ~3.5 million rows and ~2.5 GB data and the ItemParameters is ~23.6 million rows and ~19.2 GB data. The query returns 11400 rows and, as far as I can tell, properly hits the indexes.
I've written a test program that does this in a loop, forever:
- Restart the SQL Server service (MSSQLSERVER).
- Sleep for 6 minutes. To allow it to start up.
- Execute above query.
- Write execution time to a file.
The results are disturbing. Sometimes it takes 4 minutes and sometimes it takes 4 seconds and I don't see why. It is running on a VM Ware virtual machine. On my developer laptop the same query (against a third of the data) is completed in 3 seconds.
I've run it with
set statistics io on and
set statistics time on and the output looks like this:
(11409 rows affected) Table 'ItemParameters'. Scan count 3803, logical reads 19689, physical reads 2724, read-ahead reads 4261, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Items'. Scan count 109, logical reads 40423, physical reads 4, read-ahead reads 4089, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 546 ms, elapsed time = 92636 ms. SQL Server Execution Times: CPU time = 702 ms, elapsed time = 94012 ms.
Things I've done to speed up performance:
sp_blitzFirstfrom https://www.brentozar.com/blitz/. Gave nothing.
- Set SQL Max memory to 90 % of my 32 GB as per https://www.brentozar.com/blitz/max-memory/.
- Rebuilt all the indexes and column statistics.
- Disabled the SQL Full Text service. We're not using it.
- Restarted the server various times.
What can I do to make sure the query executes quickly enough every time? Any help is greatly appreciated.
- Execution plan for poor-performing query (132 seconds): https://www.brentozar.com/pastetheplan/?id=Syxbf-q5m Ex
- Execution plan against cache (1 second): https://www.brentozar.com/pastetheplan/?id=ByegNZcqX