PagedMemorySize of Sql Server

Question

PagedMemorySize of Sql Server

IT researcher

SSCertifiable

Points: 7608
More actions
February 1, 2021 at 7:31 am

#3839522

I am using sql server 2008 R2 Express edition.
Below is the Sql process information from Taskmanager:
Instance NonpagedSystemMemorySize PagedMemorySize PagedSystemMemorySize
sqlservr-Instance1 9042656 2005442560 3388872
sqlservr-Instance2 419072 1593159680 354864
Instance VirtualMemorySize WorkingSet
sqlservr-Instance1 606539776 1778012160
sqlservr-Instance2 464953344 1453477888
Sql Server maximum server Memory is 2gb.
The PC RAM is 32GB.
We have 2 sql Instance runinng in this pc.
Whenever the PagedMemorySize value exceed 2GB Sql Sever becomes Non-responsive.
What is this PagedMemorySize? Is there any limit for PagedMemorySize for Sql Sever?
Is it the Value given in 'Maximum sever memory' setting ? By increasing the maximum memory value can we solve this problem? Will it effect other instance of Sql server?
How to detect which Database /query using more memory?
SQL Server buffer pool size (using dm_os_buffer_descriptors) was 550Mb(limit for express edition is 1Gb).
Is there any method to clear PagedMemorySize other than restrating server?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

I don't really know what PageMemorySize might be; I don't see it in my Task Manager. Which OS and bitness are you on? What exactly does "SELECT @@version" report?

But as I recall, SQL Express can only use 1 GB of RAM, so you are well above your quota anyway.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

IIRC, PagedMemorySize is how much room there is in the "page file". If you overrun memory, applications will start to swap stuff in and out of the page file as a "buffer". Of course, that's going be a whole lot slower than if you do things totally in memory.

How much room does SQL Express allocated to it for memory and how much physical memory do you have in the system?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)