sqlserver 2012内存问题，最大内存设置与任务管理器里显示大致相同，却在数据库里查询dmv 只有一半不到？

Question

如题   

从windows计数器上看明显是内存不足，但是数据库却没有去占用那么多。如果是clr 或者是link 占用怎么清楚？ 

谢谢指教。

Answer 1

Either clr or linked server use memory outside of sql, did you check memory usage in perfmon?

Answer 2

target 能看到 total 看不到   调整最大内存之后  target 值也不变   以前2008r2 的时候 是变化的 

Answer 3

It changes in sql2012/4 too.

Answer 4

maybe not, if some third part dl running in sql server process,then will use the memory of sqlservr.exe.

---

chenl

Answer 5

also,in sql 2012,the memroy of clr is controlled by max server memroy of sql server,so clr use memory inside sql server

---

chenl

Answer 6

查询下sys.dm_os_memory_clerks，看看哪些东西占用内存大，如果是一下计划缓冲之类的，清除掉，如果是clr或者linkserver，这个。。。。。在线等高手吧。

---

chenl

Answer 7

also,in sql 2012,the memroy of clr is controlled by max server memroy of sql server,so clr use memory inside sql server

---

chenl

Following is from msdn http://msdn.microsoft.com/en-us/library/ms131047.aspx:

Scalability: Common memory management

The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory. 

Answer 8



Answer 9

我用 启动参数 -g  设置sqlserver 占用内存 能限制吗

Answer 10

It changes in sql2012/4 too.

2012上这个值 （target） 真没变化 ，我记得08r2 不需要重启就生效， 在2012上我改了最大内存后也重启了 还是没变化。

Answer 11

等我把sp2打上 看看情况 谢谢回复。

Answer 12

我发现启动日志有 ： 

Large Page Allocated: 32MB  

Using locked pages in the memory manager.  

这个是什么意思 ？  数据库的内存分配是多页的  ？ 怎么调整

Answer 13

hi ,zhanggq

how did you get that out of memory in perfmon on server?

which counters you are using for monitor?

what program running on server except for sql?

pls provide related data make us catch the root of problem

thankyou 

Answer 14

我发现启动日志有 ： 

Large Page Allocated: 32MB  

Using locked pages in the memory manager.  

这个是什么意思 ？  数据库的内存分配是多页的  ？ 怎么调整

Here's details of large page http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx, it's normal.

Regarding locked pages, did you enable 'lock pages in memory' option? Not necessary on 64-bit machine.

Answer 15

i have wrote an article before regarding the large page too

you can check it out 

http://www.cnblogs.com/lyhabc/p/3633452.html

hopefully it can help you

thanks

Answer 16

 谢谢回复。问题解决了。

sp2 打上 解决。  在dmv里发现  MEMORYCLERK_SQLOPTIMIZER 这个内存特别大  几十个G 。

谢谢。