4

I m using SQL server 2012. I have a stored procedure that will eventually be tuned, but I have one question. My server memory is 32 GB and max memory assigned in SQL Server is 20GB. When I execute this SP, the logical reads is around 4000000, so 4M data pages times with 8kb, divide by 1024, i will get 30 GB worth of data in memory.

My question is how SQL Server can keep 30 GB of data in memory if i only have assigned 20 GB of memory? Might be my question look silly but need your help guys. Thanks

Memory

1
  • 2
    4000000 logical reads means roughly that much data pages. Since each page is 8 KB this means memory required is 500,000KB which means 499 MB of memory approx. – Shanky May 14 '20 at 7:29
4

The math here regarding your situation, how much memory is allocated to SQL Server, and how much data was read for this query is quite irrelevant. The relevant concepts of how SQL Server works are what you're missing.

SQL Server doesn't use memory to store every bit of data that is read for the entire duration of the query. If there is ample memory and the query doesn't use much memory, then it certainly can do that. But for queries that return a large result, require a large result to be sorted, or (insert reason for large amount of memory needed here), SQL Server will eventually utilize TempDB to store that data.

Most of the memory used by SQL Server is for buffering data pages to reduce the need to read from disk. Disk access, even on the fastest SSDs, is far slower than memory access. So for a single query that needs to use a gigabyte (for example) to sort the result, SQL Server uses tempdb instead of evicting a gigabyte of data from the buffer in order to sort it. If it did work that way, one bad query could run SQL Server out of memory and empty the buffer and performance in general would be dismal.

2
  • "But for queries that return a large result, require a large result to be sorted, etc), SQL Server will eventually utilize TempDB to store that data." - why doesn't SQL Server just let the OS handle paging to disk without muddying tempDB? And doesn't using TempDB introduce a risk of data-leakage - e.g. in a multi-tenant situation (as data from different databases that should be kept isolated from each other will end-up potentially sharing the same memory-space etc)? Or is TempDB merely a conceptual database that doesn't ever actually exist? – Dai May 15 '20 at 8:50
  • Paging memory to disk is brutal and inefficient. It destroys system performance like nothing else. Tempdb is a real database. There is no more risk of what you are calling "leakage" than using memory. Many brilliant engineers designed this stuff--at some point you have to accept that you and I know .01% of what they know about it, and we can't even begin to suggest something better. Someday storage will be as fast as RAM and then it will be time for architectural changes. – Tony Hinkle May 15 '20 at 11:17
9

First, you have a scan count of 17. I.e., it re-reads the same set of data over and over again, and the total for those are 4616999 logical reads. Assuming that the exact same set of data is re-read 17 times, you would divide your 30 GB with 17 to get the amount of data read from disk. So, at first pass it reads som 1.8 GB data, and then it re-reads this same set of data 16 times more. 1.8 GB data is no problem to fit in memory.

Second, even if you did have a scan count of 1, and the data wouldn't fit in memory, then where would the problem be? As it read some data, that data can later removed from memory during the read as it get further along the read operation.

Finally, don't get fooled by "physical reads" from statistics IO. You can also have physical I/O done as read-ahead, blob reads etc. Make sure you read the whole line and all numbers after the logical reads should be summed to get the total physical reads. Also, the perfmon buffer cache hit ratio counter don't include read-aheads, meaning it is very misleading.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.