sqlservr consumes too much memory

Question

sqlservr consumes too much memory

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 1

Nils Gustav Stråbø (6/6/2010)
The numbers look very good. SQL Server has even got unused memory that can be used for caching. That never happens on my systems 🙂
Just keep in mind that if total memory starts to get close to target memory, it doesn't mean that your system will perform poorly. But when it happens you should keep a look at the I/O counts and waits, page life expectancy and buffer cache hit ratio.
You can also monitor I/PO activity for each database file by executing to query below. I guess you will see very little activity, except for the log files which is perfectly normal since CUD operations has to be logged.

here are the result

14331550156

21053163

11428598144160889398

2700620496

12141260181

200122082

14138114515047

2213667145

1291551292058

2301265244

1582941160145

21502504

1446782092

2201901

12023912823252

2402133204

11158605020340370

21303256973

1347166115864153

28011114574

11715512716321

27416931599

1370919846128180054

2982181523

1857341989154332111

211737853

116581431561

22014175923

15495183085

2531904

183062064

2001207

142485051

2001201

17387410561363281402153

26737533245714

1154595071

2002502

112510635928145

213820494

127529993053

2201403

1627800100

2002102

1212120068

2001601

111771301290

25011505

1560119722810306

236591585

1581263818295179

23738132374

126422350483112

24831013294

16231820120

200800

12171210122

21101405

113881561291

2211907

133311362540167

226301454

14125453048120

2194411156

11203252113333

24022028

1262223930502

211319019

11147102083

2102305

11141115057

2102103

1171221363538058

2201002

169569388616236132887

2143172823814

111701051253

238113026

11753271443362

22621227

111853810513137

22361165

183787082

20078050

1940110042

2102405

1940112098

2101204

193997036

21047022

19621470232

22611206

13900149011031239126

219422123674

114461561251

21931524

11603423922299

228212114

11981112181128127

2194104341035

1201101112160

22311112

12320712715121

23771764

Dear , what do you mean by "SQL Server has even got unused memory that can be used for caching"???:)

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

Your disk system seems slow to me. IO stalls are too high in my opinion.

How many disk volumes do you have for all these databases?

And I was a little unclear about memory consumption. What I meant to say was that SQL Server has committed over 15GB of memory, but only 9 of those are used for buffer and procedure cache.

You can run DBCC MEMORYSTATUS or do a select from sys.dm_os_memory_clerks to get more details about how memory is used. Both are described in in the DBCC MEMORYSTATUS link.

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 3

Nils Gustav Stråbø (6/6/2010)
Your disk system seems slow to me. IO stalls are too high in my opinion.
How many disk volumes do you have for all these databases?
And I was a little unclear about memory consumption. What I meant to say was that SQL Server has committed over 15GB of memory, but only 9 of those are used for buffer and procedure cache.
You can run DBCC MEMORYSTATUS or do a select from sys.dm_os_memory_clerks to get more details about how memory is used. Both are described in in the DBCC MEMORYSTATUS link.

do you think this is can cause a problem in the performance and need more attention .

I have 2 Volumes one for Data Files and the other for Logs RAID 0,1

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

It might cause problems. You have a lot of databases on the disk volume.

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 5

what Do you mean by these number of databases on one volume

my SQL is clusterd and this SAN storage

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 6

I mean that there is a lot of read and write activity going on on the same volume. I don't know how the volume on the SAN is configured, how many spindels it is made up of, is it shared with other servers etc. You should consult with you SAN administrator.

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 7

based on your valubale info and step by step trace I have got this conclusion

I have a problem with Disks (Read\Write) but have no problem with Memory.please advise

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

sqlguy-549681 (6/7/2010)
I have a problem with Disks (Read\Write)

whats resolution have you made for it ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 9

wrote:

So far I don't know :), coz I have put my data and log files on separate drive and put Tempdb on another drive and use Raid 5 for data and 1 for LOg

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

Well, yes, it seems so by the data you have provided. But you should monitor memory usage and disk I/O. Use sys.dm_io_virtual_file_stats to monitor read on write activity over time. If there is a lot of reads and memory consumption is low, the I suspect there could be other applications using memory. What is your setting of "min server memory"? Are other applications running on this server, and how much memory are they consuming?

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 11

Nils Gustav Stråbø (6/7/2010)
Well, yes, it seems so by the data you have provided. But you should monitor memory usage and disk I/O. Use sys.dm_io_virtual_file_stats to monitor read on write activity over time. If there is a lot of reads and memory consumption is low, the I suspect there could be other applications using memory. What is your setting of "min server memory"? Are other applications running on this server, and how much memory are they consuming?

I don't have any applications other SQL Server running on the server, I didn't set the max value yet

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 12

How often do you rebuild/reorganize indexes? This kind of operations will hit your disks and memory quite hard. You will see spikes in disk I/O during these operations.

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 13

Nils Gustav Stråbø (6/7/2010)
How often do you rebuild/reorganize indexes? This kind of operations will hit your disks and memory quite hard. You will see spikes in disk I/O during these operations.

I just have a job to rebuild statistic run every 3 days. that's it and trnsaction log backup run every one hour

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 14

Updating statistics (especially when doing a FULLSCAN) will also perform a lot of reading from disk, but if this is done off hours then your users won't notice it.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 15

Trace disk activity (sys.dm_io_virtual_file_stats) during normal operation hours. That will give you a good picture of usage and bottlenecks during normal hours.