July 26, 2006 at 2:23 am
Morning,
I am finding that sqlsevr.exe is eating memory and not releasing it. We get to a situation whereby the box slows down to such an extent that we stop/start SQL, which clears out memory and drops the anount used from 2Gb tp 80Mb. The process of getting and hogging memory then starts again. Memory is configured dynamically.
Surely memory should be released once a process has been completed? Is there a setting that I need to adjust? Any thoughts most welcomed.
We use the same AD username to run several servers, could that be an issue? Is that considered bad practice? What are the implications of changing to different users to run SQLServer
Thanks
Colin
July 27, 2006 at 2:06 am
Database servers really "want" dedicated hardware, they are designed to use what you make available to them, so if you want to choke your database server to 80MB of memory, then configure it that way.
If you "have" to share a server, then reduce the memory and disk spindles that are available to SQL Server until you reach your goal of an "unhappy" median.
If SQL Server is sharing the server with the web server, then you are really in for it, as the web server is also designed to use what is made available to it, so you will always be fighting this battle.
Andy
July 27, 2006 at 6:44 am
I agree that a database server is best served by being on a dedicated box. It's not clear from your post if this is the case. I hope it is - but if you are sharing your box, you could adopt the strategy of choosing a fixed amount of memory (say 1 Gb out of your 2 Gb) and allocating that to SQL Server. To do this...
In Enterprise Manager, right-click "Microsoft SQL Servers";
Click on the Memory tab;
Click on the radio button "use a fixed memory size"
Drag the slider bar to where you want it.
Click OK when you're finished.
The benefit of this approach is that you always know how much memory the Database server is using. The drawback is that database performance will degrade if SQL server wants more memory that it is able to use.
Michael
July 27, 2006 at 10:21 am
I'm slightly intrigued by your description as to the 2gb you see the service using. In normal operation sql server is limited to around 1.6Gb maybe just under 1.7Gb and can only use 2Gb with the 3gb switch or awe ( however the memory used is not reported correctly with awe )
The memory usage you describe is normal for sql server with dynamic memory usage.
Perhaps you could clarify the editions and o/s and ram installed on your server. You say you clear the memory, do you do this by stopping the service or rebooting the server?
I have some shared sql servers, which as suggested above, have fixed memory set, however the apps still cause problems for the server - are you sure you're not having app problems?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 27, 2006 at 5:06 pm
Colin: We've had similar problems when running SQL Server 2000 on laptops (for demo purposes). Try setting SQL Server memory to: Use fixed memory size. In our situation we have 1GB of RAM on these machines so we set SQL Server memory to 500 MB. Haven't had a problem since we starting doing this.
Cheers - DG.
:smooooth:
July 28, 2006 at 2:48 am
Thanks for all the posts, food for thought. We are using SQL Server 2000 SP3 Build 818 on Windows 2003. The maximum memory is set to 4095MB.
Am interested in the comment that the maximum RAM that SQL uses is 1.7Gb as this is the figure at which problems are reported. Is memory still limited to 1.7Gb even when themaximum is set to much higher? We do not have AWE enabled.
Am considering increasing Minimum Query memory from 1Mb to 2MB. Would this assist? Am also starting to think that a particular application may be causing problems and will investigate this further.
Thanks again for the input.
Colin
July 28, 2006 at 3:36 am
No you have to turn on awe, check in BOL , and set the max and min memory required. This assumes you haven't removed the /PAE switch from w2003 and that you're using sql ent edition.
exec
dbo.sp_configure 'min server memory',3250
exec
dbo.sp_configure 'max server memory',3250
exec
dbo.sp_configure 'awe enabled',1
reconfigure
with override
go
the above script will set sql memory to 3,250Mb. This won't work on std edition. run the following script to see memory allocation
select * from master.dbo.sysperfinfo where [object_name]='SQLServer:Memory Manager'
You're mainly interested in total and target, total shows how much memory sql server is currently using.
To be blunt those stupid memory sliders cause more problems than they're worth. I'd always set memory manually to be fixed, my script fixes memory totally, leave out the min memory setting to allow sql server to use up to the max, releasing memory if required.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply