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:
objtype cacheobjtype reused_plan_mb not_reused_plan_mb
Adhoc Compiled Plan 30.32812500000 8.28906250000
Adhoc Compiled Plan Stub NULL 0.00064086914
Check Parse Tree 0.03125000000 0.03125000000
Prepared Compiled Plan 3.04687500000 4.36718750000
Proc Compiled Plan 8.45312500000 1.96093750000
Proc Extended Proc 0.07031250000 NULL
Trigger Compiled Plan 1.96875000000 NULL
UsrTab Parse Tree 0.21875000000 NULL
View Parse Tree 7.14062500000 NULL
NULL NULL 51.25781250000 14.64907836914
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply