Memory Bottlenecks

Question

Memory Bottlenecks

Viewing 6 posts - 16 through 20 (of 20 total)

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 1

Can sp_reconfigure be run and it take affect immediately? If not, this will not be implemented until I can plan for a shutdown/restart.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

Well the max mem size followed by sp_reconfigure is generally immidiate. As long a max size is not being set to lower. The lock pages in memory requires a sql restart, but to be doubly sure thats why i am suggesting to wait until you can do a hard reboot.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 3

Adam,

I am following http://support.microsoft.com/kb/918483 to lock memory. Let me know if this is not correct.

Thanks so much for you and your friend for helping me out.

Patti

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 4

Looks about right.

Good luck, let us know if any issues.

A.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 5

Adam,

Looks like after I did the recofigure the memory messages stopped. I am waiting to have the server rebooted due to windows patches are being applied also.

I ran the sql to query the memory values and this is what it is set to now after I changed it to 5Gig.

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

max server memory (MB) 16 2147483647 5000 5000

I changed rights for service account to lock sql server in memory but that won't take affect until sql is restarted. Again, I am waiting for the server to be rebooted with the windows patches.

So far so good. I just wanted to give you all an update so you would know.

Thanks again for your help. I will know for sure after the server is rebooted that all is completely fixed.

Patti

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 6

Oh yeah, I forgot to run the SQL. This is what I have right now. Ask your colleague what he thinks.

physical_memory_in_bytes

------------------------

8587436032

virtual_memory_in_bytes

------------------------

8796092891136

Number of 8KB buffers in buffer pool

----------------------------------

640000

Number of 8KB buffers needed by the buffer pool

-----------------------------------------------

640000

Status of Dynamic Memory

--------------------------

NULL

Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.

----------------------------------------------------------------------------------

640000