April 22, 2015 at 2:48 pm
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
Thanks
April 22, 2015 at 11:18 pm
SQL Guy 1 (4/22/2015)
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).How to find particular queries/stored procedures that causing this?
Thanks
Quick thought, analyze the problem at a host/server level, not at query level, most likely there is some other activity causing this.
😎
April 23, 2015 at 2:15 am
SQL Guy 1 (4/22/2015)
How to find particular queries/stored procedures that causing this?
Probably none.
SQL doesn't use the paging file. If you're looking for stuff that's causing hard page faults (which is what Memory:Pages/sec tracks), then look at what is on the box other than SQL Server
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
April 23, 2015 at 10:50 am
Thank you both for replies.
But as I understand correctly, once SQL Server reached its max. server memory, it does not give it back to OS. For example in my situation we have 98 GB total Physical Memory, and max. server memory is 86 GB. Besides SQL Server we have other processes running on this box, like Explorer, various svchosts, etc., but as I understand they all may consume memory only within remaining 12 GB.
The metrics that I monitor is from a performance tool called Ignite. It has a page where it displays "Memory paging rate (pages/sec)" in a time slice. Since this is SQL Server monitoring tool, they won't display this metrics for other, non-SQL Server processes. If I am wrong in my assumptions, I think it's safe to ignore this metrics?
April 23, 2015 at 2:05 pm
SQL Guy 1 (4/23/2015)
Besides SQL Server we have other processes running on this box, like Explorer, various svchosts, etc., but as I understand they all may consume memory only within remaining 12 GB.
Yes, and if that 12 GB isn't enough, Windows will start paging them and maybe even SQL server if the memory pressure gets bad enough.
Since this is SQL Server monitoring tool, they won't display this metrics for other, non-SQL Server processes. If I am wrong in my assumptions, I think it's safe to ignore this metrics?
No, I wouldn't say that. Pages/sec, if it's looking at the Memory counter, is a system-wide counter, not per process.
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
April 24, 2015 at 10:30 am
OK, that's OS, now I am convinced. But here is another question: how to find OS processes that contribute the most to pages/sec?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply