November 21, 2008 at 4:45 am
We have a strange problem with our Main SQL server which seems to slowly take up all 6 Gigabytes of memory on the server until I restart the SQL server service which drops the server memory usage back to 1 gig. In task manager and any of the SQL performance monitoring tools it only reports that the SQL Server process is using 300 Meg of memory and 100 Meg of Virtual memory yet the server memory usage is 6 Gig.
The database I inherited is only 500 Meg with a log of 100-200 Meg but written very badly using hundred of UDFs in stored procedure SELECTs some of the UDFs call other UDFs often nested 2-4 deep. Often the SELECT is accessing the table the data comes from, it seems my previous incumbent did not understand JOINs in any way. I am slowly trying to rewrite the 1000s of queries as the database design is reasonable but it is a slow process. Here is an example SELECT statement from a Stored Procedure:
SELECT CompanyName as CompanyName, Tel as Telephone, dbo.fnGetDefendantOrClaimantNamesByCaseID(@CaseID, 1) as DefName,
dbo.fnGetDefendantOrClaimantNamesByCaseID(@CaseID, 0) as ClaimantName,
dbo.fnGetFullAddressIncludingNameByID(@MainDefendantID) AS MainDefAddress,
FROM Company
WHERE CompanyID=@ClaimantID
This is a relatively simple one some use 10 or more UDFs to get data that is just in a couple of other tables that could be simply joined. In this case two of these functions get data from the Company table.
This is the only thing I can think of that maybe slowly using all the memory and failing to release it. There are numerous cursors that use these UDFs too but they all call DEALLOCATE which should release any memory. Does anyone have any ideas how I can release the held memory without restarting SQL Server or work out why it is failing to release the memory? It must be SQL Server holding onto to the memory even though nothing reports it as such because restarting the service drops the memory usage to what I would expect.
TIA,
David
November 21, 2008 at 5:42 am
Hi David,
an instant helper here would be to limit the amount off memory the server is allowed to use.
Connect to the Server and show the Server properties and under "Memory" you set the maximum to something below 6 GB. A good idea would be to leave somewhere between 1 and 2GB of Memory for the "rest" of the server.
This will then make the server leave some memory out of SQL Server and allow Windows to run, but will NOT solve your problems. You may see a lot of disk swapping and TempDB usage (especially with the cursors you mentioned) by starving SQL Server of the resources. It is basically robbing peter to pay paul.
The next step would be to find the worst performers and start tidying up from there instead of going from script 1 to 999999 in order of appearance. That way the first changes you make will make the largest improvement on the system in general.
Good luck, it sounds like you need it!
GermanDBA
Regards,
WilliamD
November 21, 2008 at 6:56 am
Sorry I should have mentioned that the server has 8 gig of memory with SQL Server only allowed to use 6 gig. I am slowly rewriting the appalling stored procedures and in an attempt to get rid of all the UDFs the problem is that each one of them has at least 20 dependent stored procs.
What I really want to know is how SQL server can be holding all this memory without knowing it is. That is all the performance monitoring tools and system stored procs report it is using less than 500 meg, yet the server memory usage in task manager is 6 gig + yet restarting MSSQLServer reduces this to about 1 Gig. I just can't understand why SQL server doesn't release it and why the Windows Memory Clean up process I've forgotten the name of doesn't release it back to Windows.
TIA,
Dave
November 21, 2008 at 7:03 am
Hi David,
how do you mean the server doesn't know how much memory it is using?
If you are using AWE in SQL Server, then Windows Task Manager doesn't see how much memory SQL Server is using. AWE gives the Memory Control over to SQL Server. You can see the memory usage by using perfmon and looking at the SQL Servere Memory usage.
Another thing to look at is if the windows account that runs the sql server may have the setting "Lock Pages in Memory". This means that if windows runs out of memory and wants to grab some from running services (like sql server), sql server is alloed to tell the o/s to sod off and it will keep the 6 GB regardless of whether the o/s is in desperate need of memory.
HTH
GermanDBA
Regards,
WilliamD
November 21, 2008 at 7:25 am
If it's a 32bit server, you have to use AWE to use anything over 4GB, and to use AWE the lock pages in memory has to be turned on. One question I have though is what's the problem with SQL taking 6GB of memory? This is actually be a good thing as it allows the buffer and procedure caches adequate memory space to process most requests in memory and significantly reduce the I/O hit you would take from numerous physical read/writes and regeneration of execution plans for queries and stored procs. The 2GB you have left over should be more than plenty for the OS and other processes.
November 24, 2008 at 3:28 am
It is a 64bit server, and my two problems are that my application slows down considerably when the server memory usage hits 6gb, and that nothing reports that SQL server is actually using it, it is like it uses the memory for a process, locks it and fogets about it. All the perfomance tools, extended stored procs, and task manager report SQL servers memory usage at under 1 gig, yet restarting MSSQLServer releases the memory. I need to work out why it is holding on to it and not reusing it or releasing it.
The app runs fine until server memory usage hits 6 gig and then it start to crawl.
TIA,
Dave.
November 25, 2008 at 5:03 am
Most people recommend leaving 1 to 2 GB outside SQL Server for Windows use, but the amount you really need to allow is site dependant.
Assuming you do not have any memory leaks, the process of setting the right amount is fairly simple.
1) Restart Windows and get all your normal work running, with normal number of users connected.
2) Use Task Manager or a similar tool to show all the memory in use. You need to do this using an account with Local Admin rights on the box and ensure 'show processes from all users' is selected. Put all the Mem Usage values in a spreadsheet, except for the sqlserver.exe value.
3) Add the System Cache value to the spreadsheet
4) Add an allowance for tasks you know you use but are not currently running (e.g. Backup, BCP, etc)
5) Add all these values together and then add 10% to cover what you forgot or do not know about.
6) Take this value away from the physical memory on the box, and you have the max memory value for SQL Server.
If you still find that SQL Server slows to a crawl, then the main possibilities are:
a) Do you have other SQL Server instances runing on the same box. You need to limit memory for all SQL instances so that memory is not over committed.
b) Do you see a lot of paging activity when SQL Server is slow? Look at the proceses that are doing a lot of paging and see if you can limit the memory these use.
c) If all else fails, you may have a memory leak. A memory leak is when a process runs repeatedly, allocates some new memory whenever it does a task, but does not release the memory when the task ends. One of the best tools to help diagnose this situation is Process Explorer from sysinternals / Microsoft. Google can help you find the download, and give you advice on how to find the leaking process.
When you know what is leaking, there are 2 main options.
i) Fix the leaking process so it no longer leaks.
ii) Work out how long it takes to fill all your server memory, then schedule a reboot before the leaking process affects your performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 26, 2008 at 1:20 am
Thanks, I had come to the conclusion that there has to be a memory leak somewhere so I am currently trying to find and fix the problem, for now I will just have to restart SQL Server at some point over the weekend when it isn't being used.
I'll update the thread if I ever find out exactly what was causing this but I imagine I won't find it until I rewrite the offending bit of code, although it has to be said that most of you would find all the stored procs and UDFs offensive 😉 Just be glad you don't have to work with (or should that be against) this database.
Thanks for all your help,
David
November 26, 2008 at 6:44 am
I wish you the best David. We had a third- party application that had considerable memory leaks and it took nearly a full year for them to fix the problem. At least yours is internally built so you have some control over the time it will take to correct the issue.
November 26, 2008 at 12:07 pm
One last(?) question: Do any of the UDFs use the CLR? I dimly recall there being memory leak problems with the CLR in SQL 2005.
November 27, 2008 at 5:03 am
I wish they had been written that recently, I believe a most were written for SQL Server 7 which may be another issue but all SPs UDFs and Views need rewriting as they are horrible. The query plans for some queries which should be pretty simple run to 20-30 pages when printed due to ridiculous overuse of UDFs and Views that are nested 3-4 deep.
Thanks anyway,
Dave.
November 27, 2008 at 8:18 pm
we had a similar problem when we added additional memory to one of our SQL Servers. Even when SQL itself was limited to 8Gb, the problem continued and this caused problems such as mirrored databases failing over etc.
The entry in the SQL Server log indicating this error was: "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 35972, committed (KB): 71864, memory utilization: 50%."
In the end the issue was solved by applying a patch - I don't have the patch details on hand, but if you think it would help I can find out for you. May have nothing to do with your problem though - just my 2c
November 28, 2008 at 8:05 am
Thanks, but the aforementioned patch has already been applied with no difference.
Dave.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply