Sqlservr.exe doesn´t free the VMemory Size
-
I would be very pleased if anyone could help me by giving me a short explanation about could be happening to one of my servers.
Once i login in the server i have a look at the taskmgr
and what i can see is that the sqlservr.exe procces has taken 1,7gb VMemory size during several days,having a look a the enterprise manager there is not anything launched and the server doesn´t seems to have any load of work.
How could i free that virtual memory without rebooting the system?,i have the monitoring tool constantly sending alerts cose such a load of vitual memory exceedes the monitoring tool counter.
-
please read BOL, a good sql book or attend a training course. This is how it should be, get familiar with the basics of sql server - this post, which is always appearing, just should not. This should be under newbies posts if at all and if you're actually administering a production system then I'd be worried. Sorry but read BOL.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
very kind....nice way to recieve new users ...i´d be worried of this discussion forum instead from your nice advices,thanks for the advice but there might be training courses for thoughtless hiperhigh level professionals like you who save the world each day.
Dont worry never post back again Thanks
I am just a poor newbie i don´t diserve your time
-
rule-of-thumb is to set up VM pagefile to be 1.5x that of physical ram. SQL will use VM as needed, let it do it's thing. don't worry about it. it's only a problem if physical memory gets low and you enter into a "thrashing" situation where memory is constantly being swapped. this typically occurs when physical memory gets down to around 100MB free or so. use perfmon to monitor it. if you see it dip below 200MB, you could be heading for a thrash. this will bring the server to its knees, so try to stay ahead of it!
-
Thanks a lot! and sorry if it was an unsense questions for this forum...
The whole thing is that sqlservr.exe is taking 100MB of phisical Memory while the Virtual memory is at 1.7GB so don´t understand why if there is not any load of work from sql is still retrieving such amount of Paged memory ,what decreases the system performance...1,7GB means the 80% of the totat paging memory. I supose (may be wrongly) that if tha load of work from sql has finish long time ago SQL should have free that VMemory size unless the advice seen below has the right answer:
"The reason you don't see the SQL Server processes memory usage drop when your load is done is that the server will not release memory space that is has acquired unless it is 'told' to from the OS (i.e. the OS requests memory that isn't available)...this is by design and allows the server to run much more efficiently...SQL will grow to the memory limits you've defined for it and then never shrink it's memory space unless the server comes under memory pressure outside SQL Server process."
Nice and hopefull to see that still there are devoted people.
-
i guess the question is - why do you want to release this memory from SQL? it will just have to grab it back when it needs it again. it isn't going to hurt anything by holding that much VM.
what are you using to monitor? it sounds like the alert is bogus. many monitoring tools come with out-of-the-box alerts and counters which will signal false positives. dont assume just because the tool says it's a problem that it's a problem.
restart SQL, and set up monitoring with perfmon and profiler, and spot the process or query that jacks up your VM usage. that will shed some light on why it grabs so much VM.
-
We are using Tivoli and the counters are set up to send alerts when the amount of VMemory exceedes the 85% of total VMemory, we have also used the profiler before and its specific counters for sql even the sql profiler with the same result, the thing is that the server in question can´t be restarted ...neither the sql services at last not yet, it seems that the best its to wait until we can reboot it or restart the service and reconsider the alert configuration for that process.
w´ll keep on trying
Thanks a lot for your help!
-
ah you should have mentioned Tivoli - a monitoring application of little value with lots of stupid alerts and counters set by ( persumably ) people who know nothing about SQL Server, and usually deployed by people with no knowledge of SQL Server.. and usually monitored by people who know nothing about sql server.
Basically you don't have a problem then - sql server is fine - the administrators of Tivoli do < grin >.
SQL server will generally never use virtual memory - also known as the page file - unless you have 64bit and haven't set maximum memory correctly - or with 32 bit you have set awe and not fixed sql server memory correctly.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hello Julio,
I will have to concur with Colin despite his rather blunt answer. Using 1.7 GB of memory in SQL Server is normal behavior out of the box. If you don't want it to use all the memory, Books Online, the SQL Server help docs have a command called sp_configure where you use "max server memory" as the config option. At any rate, if you have a dedicated database server (which I hope, best practices), then you want the database engine to use as much memory as possible.
Tell the Tivoli monitoring people to ignore this particular alert for SQL Server. This is a false positive.
Thanks,
Eric
-
Hi Strommy,
Thanks for your help!.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply