September 6, 2018 at 12:45 am
If the table is "hot" (that is getting updated/scanned a lot) then chances are most (or all) of the data is pretty much permanently in memory cache. 200 million rows might be a lot of data, but if the rows aren't very wide then it might not actually be much at all - CPUs can do an enormous amount of work in a very short space of time. Still 2-3 seconds is actually quite slow, with an index on the column I'd expect a near instantaneous answer regardless of whether the server was busy or not.
September 6, 2018 at 6:08 pm
I agree with Andy. Your query would benefit greatly from a covering NCI.
September 9, 2018 at 5:58 pm
SQL Server does not discard query results immediately after presenting it to a user.
All resultsets stay in memory cache in case they're are requested again.
They are removed from memory mainly for 2 reasons:
- being pushed away by results from later queries (if memory is not big enough to accomodate both);
- source data is changed, and the result is not relevant anymore.
On busy time you likely have both conditions match, so your query needs to re-scan the records every time, unless you do a repeating call immediately after the first one.
On idle time your data is static, and there are not too many queries competing for the memory.
So, the server does not need to read anything to know the result of the query. It already has it in memory.
_____________
Code for TallyGenerator
September 10, 2018 at 7:46 am
The results aren't in memory, but rather the table data used to generate the results. When you query the table, SQL Server loads most of the data into the buffer cache and then scans/seeks/joins/etc on that data. As Sergiy notes, this data remains in memory unless the memory is needed for something else.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply