Problems with Memory consumption 93% SQL 2005 Ente

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Problems with Memory consumption 93% SQL 2005 Ente

Author

Topic

turahk1
Starting Member

3 Posts

Posted - 2009-08-07 : 19:13:42

Greetings,

I have a problem with the memory usage on a Stratus server with SQL 2005 enterprises installed. It seems to be having a memory leak and consumes all of the resources in a small period. About a week ago the client reported a problem consisting of processes running slow after the installation of the Service Pack 2 of SQL 2005. Even though I have done various analysis including running the index tuning wizard the problem persist.

Is my understanding that the problem does not concern programming issues since all applications are running without problems. Furthermore, I have 39 other clients using the same applications in their servers and they too have installed the same service pack and no problems have been reported.

The information about the server is enclosed.

OS Windows 2003 Server SP2
Memory 8 GB
DataBase size 53.45 GB
Log Size 165.05 MB

Memory
Total Used 94.40 %
Used By SQL 81.27%
Memory Paging 0.18/s
Buffer Cache hit 99.87%
Proc. Cache hits 79.24%

CPU Usage 34.72%

Disk
Page Reads 9.67/s
Page Writes 34.80/s

RowID Flag Totallogspace usedlogspace status
8 1 256.7 8.4 0

NAME DBID CMPTLEVEL DB_SIZE_IN_MB Status
IHESDB_1 8 80 57116 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, IsAutoCreateStatistics, IsAutoUpdateStatistics, Collation=SQL_Latin1_General_CP1_CI_AS, IsAutoShrink

Single use plans (usecounts=1) Re-used plans (usecounts>1) re-use % total usecounts
184 447 70.84 631

dbName BufferPoolMB
IHESDB_1 6287

DBID objectid table name index_id buffer count
8 1676493697 PAT_ANCILLARY_CHARGES 1 183024
8 1676493697 PAT_ANCILLARY_CHARGES 7 59416
8 1676493697 PAT_ANCILLARY_CHARGES 3 32796
8 1332772401 EHR_PATIENT_HEADER 1 13765
8 284488738 PAT_ACCOUNT_RECORD 1 13284
8 303964805 PAT_INSURANCE_PLANS 37 9702
8 1868494381 MPI_PATIENT_RECORD 35 9212
8 1868494381 MPI_PATIENT_RECORD 89 7846

RowID dbName Flag Fileid FileGroup Total_Space UsedSpace FreeSpace FreePct Report_Date
8 IHESDB_1 0 1 PRIMARY 56860.2 51704.5 5155.7 0.091 8/7/2009

SQL setting for memory
use of AWE enable

Minimum of server memory in (MB) = 0
Maximum of server memory in (MB) = 6656

since the server have 8 gb RAM for OS have 2gb

the problem is since this past monday all process from client to server has dramatically decrease in performance. to give you and idea, process that took 2 second befores SQL SP2 now the clients have to wait more than 3 minutes for the same results.

I follow the querys and here is teh results

http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx

select name, count(*) from sys.dm_os_memory_cache_entries
group by name
order by count(*) desc

name (No column name)
IHESDB_1 1680
SQL Plans 840
Object Plans 600
sxcCacheStore 310
SystemRowsetStore 270
Bound Trees 266
tempdb 146
mssqlsystemresource 125
IHESSystem 105
TokenAndPermUserStore 103
Temporary Tables & Table Variables 86
msdb 78
master 62
Extended Stored Procedures 17
model 14
ReportServer$WORKING 14
ReportServer$WORKINGTempDB 14
ObjPerm - IHESDB_1 12
ObjPerm - IHESSystem 4
ObjPerm - master 3
SOS_StackFramesStore 1

select

* from TokenAndPermUserStore

loaddate sizemb
8/7/2009 0.45

select

* from TokenAndPermFlush

no result empty

SELECT

SUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)"
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'

CurrentSizeOfTokenCache(kb)
464

wait_type wait_time_s pct running_pct
CXPACKET 59055.75 24.09 24.09
WAITFOR 41653.47 16.99 41.08
ASYNC_NETWORK_IO 29141.73 11.89 52.96
PAGEIOLATCH_SH 24913.63 10.16 63.12
SOS_SCHEDULER_YIELD 13433.23 5.48 68.6
OLEDB 13410.91 5.47 74.07
MSQL_XP 10202.8 4.16 78.23
LATCH_EX 8736.33 3.56 81.8
LCK_M_S 8032.2 3.28 85.07
PAGEIOLATCH_EX 7503.28 3.06 88.13
SLEEP_BPOOL_FLUSH 5971.63 2.44 90.57
WRITELOG 4923.81 2.01 92.58
ASYNC_IO_COMPLETION 3983.89 1.62 94.2
BACKUPBUFFER 3932.61 1.6 95.81

%signal (cpu) waits %resource waits
1.2 98.8

Page Life Expectancy
7418

type SPA Mem, Kb
CACHESTORE_OBJCP 52128
CACHESTORE_SQLCP 27248
OBJECTSTORE_SNI_PACKET 7544
CACHESTORE_PHDR 6352
MEMORYCLERK_SOSNODE 5504
MEMORYCLERK_SQLGENERAL 5288
USERSTORE_SCHEMAMGR 3656
MEMORYCLERK_SQLSTORENG 2984
MEMORYCLERK_SQLCONNECTIONPOOL 2888
OBJECTSTORE_LOCK_MANAGER 2584

Page Life expectancy
empty

Any ideas why?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-08-07 : 19:17:02

sql server doesn't have a memory leak.
it's built so it takes all the memory it can because it stores data pages, execution plans, etc in it.
so your sql server is actually experiencing memory pressure. you should either add more memory or optimize your queries to use parameters etc...

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

turahk1
Starting Member

3 Posts

Posted - 2009-08-07 : 19:25:20

thanks for you're message but like i said the server was running nice until SP2 was installed. all query results was on 2 second now almost 3 minutes no changes in the programing so the code is not the problem and in my other 38 clients they dont have any problems. Any ideas What can be causing this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-08-07 : 19:28:53

it could be a number of things, from outdated statistics to fragmented indexes. first check those 2.
if that doesn't work see what's your MAXDOP set to.
sometimes sql server doesn't play nice with parallelism in query plans.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

turahk1
Starting Member

3 Posts

Posted - 2009-08-07 : 19:41:07

All this for installing SQL SP2

I will do youre suggestions update statistics and look for fragmented index

I already set MAXDOP

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current values are
EXEC sp_configure

-- Set MAXDOP = 1 for the instance
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources