September 5, 2018 at 6:59 pm
Experts,
This is AG Readable Secondary Server.
We have a table with close to 200 millions of data. Every second one records gets inserted in that table you can say.
There is only one PK/Clustered Index on an Identity column. No other non clustered indexes at all
One of the user has written some query, which gets
Select MIN(PK_IdentityColumn)
Where (Some Date Column) > someValue
And (Some Date Column) < somevalue
Naturally it goes for a PK Index scan and is dead slow when server is busy (I know indexes are essential).
However, when the server is idle, it gets the records in NO TIME. Takes hardly 2 or 3 secs to get the result.
My doubt is that, how SQL Server is able to SCAN 200 million records and find some value based on some DATE criteria in such short time ?
I was expecting it to run atleast for few minutes, but it finishes in secs. Execution plan shows it has done clustered index SCAN.
No other indexes or Default values for column or anything in that table.
Please advise. Thanks in advance.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply