SQL SERVER uses 850 MB of RAM - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL SERVER uses 850 MB of RAM

Author

Topic

roypython
Starting Member

7 Posts

Posted - 2005-08-17 : 18:12:56

Hi.
Your help is very appreciated.

I just upsized ACCESS 2002 app to use SQL SERVER 7.0 as backend (ACCESS does it thorugh linked tables to the SQL SERVER's tables).

Since then, the SQL SERVER chews up 850 MB of RAM (the cpu usage is nill), with a load of about 50 user connections.
Which cause the server to start paging to the HD, and therefore to slow down, after a while, apps that request connection to SQL SERVER recieve timeout(probably beacuse the server work SO SLOW...).

It might be normal, and the answer is to put some more physical RAM, but I doubt it.

ACCESS uses bound forms, which obviously cause some load (it keeps connections open while the ACCES form is open), but I'm not sure that that's the main reason.

The SQL server's performance monitor shows:
Buffer cache hit ratio 100 (scale 1.000)
Page Reads/sec 0 (scale 0.0100000)
Page Writes/sec 0 (scale 0.0100000)
User connections 50 (scale 1.000)
Total server memory (85)KB (scale 0.0001000)
SQL compilations/sec 20 (scale 1.000)

Thanks
Roy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-17 : 18:15:14

In the SQL Server:Memory Manager, what does Target server memory show?

SQL Server is a memory hog. This is why it should be on a dedicated server. 850MB is rather low for memory utilization. Ours typically uses several gigabytes, but it's dependent on what your app is doing and how big the database is.

And yes the answer is to add more memory if SQL Server needs more! But we'll know that from the total and target server memory in Memory Manager.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-17 : 21:06:22

what other apps are you running on your SQL box?

-ec

roypython
Starting Member

7 Posts

Posted - 2005-08-17 : 22:46:57

Thanks for your reply, ec and Tara.
1. The SQL box doesn'r run any other app.
2. Tara, where is the Memory manager please?
This is a snapshot of the windows task manager:

totals
handles 17219
threads 1137
processes 71

commit charge
total 1322508
limit 3046368
peak 1356572

physical mem(k)
total 1048040
available 5076
sys cache 14724

kernek mem
total 51492
paged 35636
nonpages 15864

sqlserver.exe process uses 0 cpu 870,356 K mem

Cheers
Roy

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 03:27:43

"Tara, where is the Memory manager please?
SQL Server:Memory Manager"

I think Tara was referring to Start : Programs : Admin tools : Performance

Press the [+] button, choose the computer name, then "SQL Server:Memory Manager" then "Target Server Memory"

If its jumping around (which I doubt!) let it run a while then note the value in the Maximum box

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-18 : 10:34:37

One important thing to note is that SQL Server is designed to hold on to memory that it acquires, even if its not currently using it (kind of like a high-water mark). Its the same premise as not shrinking a database file - its needs the extra space to work in sometimes so its better not to keep releasing it, reserving it, releasing it, etc

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources