SQL Server High memory usage

Hello Sreenivasa.

Can you tell us what version of SQL Server you are running and what the OS is underneath? I see both 2005 and 2008 tags on your post: is it 2008 or 2008 R2?

How large are your databases and the specs of the system
How many SQL instances are there and how many databases are there per instance and their sizes.

Are you running active/active clusters?

SQL is a memory ... It consumes as much memory as is available. CPU spikes suggests your systems memory resources are being consumed requiring frequent data sync out to disk and back.

There are SQL benchmarking tools that could further help .........

Check whether the properties of computer, advanced system settings, advanced, pergormance settings, advanced.
Make sure the system is prioritized memory/processes for programs.

Check network connection settings, properties of file and printer sharing to make sure it is prioritized fir throughput versus file transfer.....

DBCC MEMORYSTATUS command

MEMORYSTATUS is an undocumented DBCC command that provides a snapshot of SQL Server's current memory status. This command identify the root cause of the memory pressure on SQL Server and it also analyzes that, how well SQL Server is using its allocated memory.

You should go this one to know more about memory status http://searchsqlserver.techtarget.com/feature/Built-in-tools-troubleshoot-SQL-Server-memory-usage

Please give us the configuration of the both cluster nodes (RAM, CPU, Windows and MSSQL versions, editions and Architecture, ...).

Also what are the values set to MINMEMORY and MAXMEMORY for the SQL Server instance?

Besides SQL Server, which applications has been installed in both nodes?

Hi Experts.
Please find configuration below and help how to reduce the memory usage. Please provide any scripts to measure memory. Thanks.

Cluster type: Active\passive
OS: Windwos server 2008 R2 SP1
SQL Server version: SQL Server 2008R2 Enterprise SP1
Total physical memory: 311286MB
Database Size: 1.2TB
Application type: Finance payments and Invoices processed system
CPU: 3- 20%
Memory usage: 99%
INSTANCE 1:
Min Memory: 92160MB
Max Memory:241586MB
INSTANCE 2:
MIN MEMORY: 9216 MB
MAX MEMORY: 34720 MB

I'll assume that Windows and SQL Server are 64 bit version.
There's no other applications installed? For example SSAS, SSIS and SSRS?

Yes Vitor, Both are 64 bit versions. No other application installed.

Ok. At the first sight your configuration is good but wouldn't be a bad idea to install the latest service packs for Windows and SQL Server.

Can you post the result for sp_who2 command?
If returns a lot of rows please save the result to a text file and post it here.

The information conflicts
Do you have ~300GB of ram while you only allocate between 9-32GB depending on the instance, which I presume means node rather than each system has two instances and you choose to run both on the same node versus running instance1 on node1 while instance2 runs on node2 creating an active/active cluster.
One cluster, two SQL application instances, with each running on a separate node but can run on the same node.
Sum of memory allocated to both not to exceed 3Gb less than total ram ..

For a 1TB DB, 30Gb is the reason
Check properties computer, advanced system settings, pergormance settings, advanced, what does Windows recommend for the page file versus what you gave it set to?

open task manger on the Active Node and check what else likes memory on your box

you should have 34980MB RAM free --
It can be ANtivirus, etc.
check "extra" sql server components that are installed but not in use - for example SSAS - it like memory -> check services..
---

Total physical memory: 311286MB

Memory usage: 99% <=> 308173.14MB
INSTANCE 1:
Max Memory:241586MB

INSTANCE 2:
MAX MEMORY: 34720 MB

Yes. we have Veritas cluster storage component. some times it is occupying 250 GB around memory.Due to licence issue client using active\passive node configuration. I have run sp_who2. Please find attachment for output. and suggest me. thanks.

Regards,
Sreenivasa
Testin.xlsx

I could see UPDATE STATISTICS running and looks like was running for more than 10 hours.
Why having a maintenance task running during activity period? These kind of tasks are resource consuming and should be running only when no activity in the database.

Hi Vitor,

Update statistics job runs on every day mid night 1am. which takes less than five minutes time to complete.Please provide some script to generate actual memory usage of all components in the Windows server.It will be easy for me to identify apart from task manager and perfmon counter.

Thanks,
Sreenivasa

Thanks Vitor, I will fine tune to reduce the memory as per your suggestions.