February 20, 2011 at 5:11 pm
I have an installation of SQL Server 2008 Workgroup Edition (ver 10.00.4000) running on Windows 2008 64 bit R2 Standard with 8gb of memory. We have an issue where the server memory slowly increases to 100% after 4 to 5 days and hangs the server.
I have set the Maximum server memory to 4096mb and system paging file is currently 12288mb which is greater than the recommended 12198mb.
As far as I know Lock Pages in Memory is not supported on Workgroup Edition.
Any suggestions would be appreciated.
February 22, 2011 at 9:52 am
is this a clean install or are the application running on it
February 23, 2011 at 7:19 am
Make sure the advanced server property "Optimize for Ad Hoc Workloads" is set to true.
you need to find out why your server memory is being poorly used. I don't buy this is a workgroup-only issue, problem must be elsewhere
please post the result to the following query
with cte as (
select
reused = case when usecounts > 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end,
size_in_bytes,
cacheobjtype,
objtype
from
sys.dm_exec_cached_plans
), cte2 as
(
select
reused,
objtype,
cacheobjtype,
size_in_mb = sum(size_in_bytes / 1024. / 1024.)
from
cte
group by
reused, cacheobjtype, objtype
), cte3 as
(
select
*
from
cte2 c
pivot
( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p
)
select
objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb])
from
cte3
group by
objtype, cacheobjtype
with rollup
having
(objtype is null and cacheobjtype is null) or (objtype is not null and cacheobjtype is not null)
--script found somewhere here in SSC
this will output the amount of memory being used and not reused by the query plan cache.
as a last resort you can clear the buffers with
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE ('ALL')
totally not recomended!
--
Thiago Dantas
@DantHimself
February 23, 2011 at 3:03 pm
Hi
I have Rockwell software RSLinx and Factory Talk Transaction manager interfacing to a PLC. Other than that it is only SQL Server running.
I have now set Optimize for Ad Hoc Workloads to true
Here is the query result:
objtypecacheobjtypereused_plan_mbnot_reused_plan_mb
AdhocCompiled Plan30.328125000008.28906250000
AdhocCompiled Plan StubNULL 0.00064086914
CheckParse Tree0.031250000000.03125000000
PreparedCompiled Plan3.046875000004.36718750000
ProcCompiled Plan8.453125000001.96093750000
ProcExtended Proc0.07031250000NULL
TriggerCompiled Plan1.96875000000NULL
UsrTabParse Tree0.21875000000NULL
ViewParse Tree7.14062500000NULL
NULLNULL 51.2578125000014.64907836914
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply