My SQL Server has a weird problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used dbcc memorystatus
to check the problem. I found MEMORYCLERK_SQLOPTIMIZER
singlepage allocator ocuuping a lot of memory. I guess it could be why my SQL server restarted.
So I used sp_WhoIsActive
to find which query script holds memory. After I collect some data, I don't know how to distinguish the relationship between sp_WhoIsActive
result and dbcc memorystatus
.
Could you help me and give me some hints to solve the problem?
The following is some information about my SQL Server:
- Server: CPU: intel E5645*2, RAM 64GB, HD: 10krpm SAS
- SQL Server: 2008r2 with failover cluster feature enabled
- max memory:52GB,
- instance:1
- No of db: 1700(Don't be surprised. There are 1700 dbs in my SQL Server.)
The attach file can be downloaded here.
After unzipping, there are 3 files.
- att1.txt - The status before server is going to restart.
- att2.txt - The status that the server has run for two weeks after restarting.
- whoisactive.bak - The result for sp_WhoIsActive.
You can restore it in the SQL server2012.
My SQL Version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Finally I turned on optimize for ad hoc workloads
, but it didn't work. I am not sure what happened. Now I restart service when optimizer memory almost holds up all the memory. And all the memory is released.
MEMORYCLERK_SQLOPTIMIZER
andMEMOBJ_EXECCOMPILETEMP
will grow very large due to an issue with TEMP tables being passed as parameters to nested stored procedures.