May 19, 2014 at 1:08 am
Hi,
This is a question which came out of curiosity to know of how sql server handles memory.
Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, rebuild large indexes, select * from really big tables and so on.
Again, this is only my assumption. Since the memory will not sufficient, some part of data/plans resided in RAM will be flushed out of memory.
Now my question is, whether the flushed out data will be placed/pageout to OS pagefile or it spills to tempdb.
Can anybody provide me same use case/test queries to test this behaviour and confirm(DMV query) the "before" and "after" effect to tell,
1. sql server data has been page out ?
2. how much data in bytes/KB it has been paged out?
3. sql server data has been spilled to tempdb ?
4. how much amount of data has been spilled out?
Basically I am looking for a proper methodology(step-by-step process) to test this behavior. I am getting idea's but was able to implement properly!!
Any inputs will be of great help.
May 19, 2014 at 4:28 am
Oracle_91 (5/19/2014)
Now my question is, whether the flushed out data will be placed/pageout to OS pagefile or it spills to tempdb.
Neither. It'll be discarded.
Next time the data is needed it'll be read from the data file again. If the same query runs again, its plan will be compiled from scratch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2014 at 4:52 am
Neither. It'll be discarded.
Gail, just checking what if it is ongoing transaction. As far as my knowledge goes, for instance, max server memory to a very low value say 500mb, now I am doing a big sort ORDER BY c1 DESC on a heap table with 50 million rows , then must see a tempdb spills. I can check on this and get back to you. Similary, if sql server data is not paged out, then does it mean sql server never uses pagefile at all?
Pl correct me, I might be completely wrong but till now, I am assuming sql server uses pagefile and tempdb whenever required.
Having said that, if there is a working set trimming, I am not sure. You might be correct on those lines.
Could you please explain more on this.
May 19, 2014 at 7:05 am
Oracle_91 (5/19/2014)
Neither. It'll be discarded.
Gail, just checking what if it is ongoing transaction.
Same thing. If there's memory pressure, SQL will discard pages from the data cache to make space for new pages that are needed. If those pages have been changed since being read in, they're written back to disk first.
As far as my knowledge goes, for instance, max server memory to a very low value say 500mb, now I am doing a big sort ORDER BY c1 DESC on a heap table with 50 million rows , then must see a tempdb spills.
Sorts can spill to TempDB, but sorts are not the data cache. Sorts are done in what's called workspace memory and they will often spill to tempDB, memory pressure or no memory pressure
Similary, if sql server data is not paged out, then does it mean sql server never uses pagefile at all?
Correct. The page file is used by the OS. Now, the OS may, if it comes under memory pressure, page SQL out to the page file, but that's none of SQL's doing. That's the OS's action in response to its memory pressure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2014 at 8:22 am
OK. Got it. Thank you very much Gail for clearing some of my misconceptions.
I am trying to reproduce these issues to understand the memory concepts well.
Again, thanks for the help.
May 19, 2014 at 9:11 am
Gail, one last question. just need your clarification on my below statement.
From Neil Hambly blog, I got the below query and it is showing PAGEFAULTS information.
Does it have any relevance with SQL Server or am I missing any context of using this below query
or else are we just pulling/querying out os related info within SQL Server just as we do for os memory info we query "sys.dm_os_sys_info" to get physical memory info ?
Is that correct?
The reason why I am confirming is, as you said, sql server data will never goes into windows page file and sql never uses page file.
Whenever workingset trimming happens all data is flushed out of buffer pool and if user submits same query, sql has to do fresh physical I/O's and cache it in bufferpool.
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') AS MemoryUtilization,
TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '% %'
) AS x
--Also, From another Microsoft PSS Tech lead's blog, I got this query and it is showing PAGEFAULTS information,
--http://mssqlwiki.com/2013/03/29/inside-sys-dm_os_ring_buffers/
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE()) AS Notification_time, a.* , sys.ms_ticks AS [Current Time]
FROM (SELECT x.value('(//Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [ProcessUtilization],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle %],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime) [1]', 'bigint') AS [UserModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime) [1]', 'bigint') AS [KernelModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults) [1]', 'bigint') AS [PageFaults],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta) [1]', 'bigint')/1024 AS [WorkingSetDelta],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization) [1]', 'bigint') AS [MemoryUtilization (%workingset)],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE())
May 20, 2014 at 2:20 am
Oracle_91 (5/19/2014)
The reason why I am confirming is, as you said, sql server data will never goes into windows page file and sql never uses page file.
No. I said:
The page file is used by the OS. Now, the OS may, if it comes under memory pressure, page SQL out to the page file, but that's none of SQL's doing. That's the OS's action in response to its memory pressure.
And
Whenever workingset trimming happens all data is flushed out of buffer pool and if user submits same query, sql has to do fresh physical I/O's and cache it in bufferpool.
I didn't say that either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2014 at 3:20 am
OK Gail. no problem. May be I have misunderstood and misinterpreted your words based on my limited knowledge.
So as a whole, sql server memory and can be paged out by OS whenever there is memory pressure.
Last night, I came across this KB which cleared a lot of things.
http://support.microsoft.com/kb/918483
Again thank you. You have always been helping hand in all my threads. I always wished, I could have a mentor like you.
May 20, 2014 at 3:31 am
Oracle_91 (5/20/2014)
So as a whole, sql server memory and can be paged out by OS whenever there is memory pressure.
When the OS is under memory pressure.
There are two different scenarios. First, the one you were asking about, where max server memory is set too low. SQL is under memory pressure and SQL manages that. The second is when the OS is under memory pressure. Can result from max server memory being too high, or there being other memory demands. In that case the OS manages it, it will ask SQL to trim its working set (reduce its memory allocations) and it may swap some of SQL's memory into the page file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2014 at 4:55 am
Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply