SQL Server Performance Forum – Threads Archive

SQL Server Memory

My Database is a SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server, with 3.5G RAM.
The /3gb switch is enabled. When I check the Available Bytes it shows 600 MB of Memory as available.
My Questions:
1.)Will the OS take 1GB and limit SQL to 2.5GB ? 2.)Or will SQL grow to 3GB on need basis ? 3.)Or will SQL just remain below 2GB because OS needs 1 gig and so wouldn’t
let SQL use the /3gb ? Thanks for your answers.

If you have 3.5GB of RAM, and use the /3GB switch, then the OS will only take 1GB, leaving 2.5GB to SQL Server. SQL Server will only take as much RAM as it needs, if it is set to use memory dynamically. If you manually set SQL Server to use all the available RAM, it will. If there is 2.5GB of memory available, as in your case, SQL Server can use it all, when it needs it. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Can I now safely increase the number of threads in SQL Server ?
Since I have about 600 MB available , there would be memory for the
thread stacks too since memory to leave area comes from the OS memory.
What could be the negative impact of doing this ?

Chekc the process and queries that are affecting the performance, PROFILER for slow runnign queries and PERFMON to identify the resources usage will help to assess the situation. Increase of threads can be considered if the system is dedicated to SQL Server and disk I/O activity tends to be heavy, then you should enable this option to get substantial performance gains. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I wouldn’t normally consider enabling it on a system that only has 3.5GB of RAM though. Be careful when doing this. Do you have a test system that mirrors this? Why do you think that you need to increase the threads? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>