July 20, 2010 at 2:58 pm
Hi
My company has 20 Access databases that are connected to sql server 2008 through ODBC. There are about 20 different databases. The problem that I am having is that every week or so the memory on the sql server gets almost full about 15.4 GB and then the databases will run slow and some will give errors(not enough memory). To reslove the issue I restart sql server services. and then the memory clears up. but then the databases work fine for another week before the same problem comes back. when the users close their databases shouldn't the odbc connection gets disposed? how does it work? when sql server uses all the allocated memory what happens? can some one explains this to me?
Any one can help me?
64bit system, sql server 2008, windows server 2008, 16 GB Ram.
thanks,
July 20, 2010 at 5:38 pm
ashaaban (7/20/2010)
HiMy company has 20 Access databases that are connected to sql server 2008 through ODBC. There are about 20 different databases. The problem that I am having is that every week or so the memory on the sql server gets almost full about 15.4 GB and then the databases will run slow and some will give errors(not enough memory). To reslove the issue I restart sql server services. and then the memory clears up. but then the databases work fine for another week before the same problem comes back. when the users close their databases shouldn't the odbc connection gets disposed? how does it work? when sql server uses all the allocated memory what happens? can some one explains this to me?
Any one can help me?
64bit system, sql server 2008, windows server 2008, 16 GB Ram.
thanks,
[font="Courier New"]
You need to set the MAX memory SQL Server can take using the below commands. I would say leave at-least 2 GB for the OS and if there are any other apps on this server as well leave enough memory for them. For general guidelines for the max. server memory, look at Glenn's post listed below.
***NOTE*** The below is a sample code to set the max server memory taken from BOL. You need to make a judgement on how much to set based on the number of apps running on that box.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
Ref: http://msdn.microsoft.com/en-us/library/ms178067.aspx
Suggested Max Memory Settings for SQL Server 2005/2008
Also make sure lock pages in memory is assigned for the account running SQL Server.
How to: Enable the Lock Pages in Memory Option[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 21, 2010 at 11:07 am
Take a 16 GB SQL Server and limit it to 4 GB for SQL to use? Even giving 2 GB to the OS (seems a bit high to me), that leaves 10 GB for what other purpose? I would say that he should set the max memory to at least 10GB.
And in addition, that isn't going to solve the core issue which is that the memory utilization is too high. He'll just slam against 4 GB instead of 15 GB which means it will show up sooner than before. Without seeing how the Access apps access SQL there isn't much that can be said about that. I can't imagine that 15 GB would be consumed by connections alone, so there may be some design flaws in there somewhere. You'll probably need to profile performance over time to see what queries are using the most memory and try and tweak performance. I am not aware of any memory leak issues or Access query passthrough issues, but others here may know of something.
July 21, 2010 at 11:38 am
jeff.mason (7/21/2010)
Take a 16 GB SQL Server and limit it to 4 GB for SQL to use? Even giving 2 GB to the OS (seems a bit high to me), that leaves 10 GB for what other purpose? I would say that he should set the max memory to at least 10GB.
I didn't quite get it what you were referring to but I see it. The code I gave above is a sample code but in no way I was mentioning to the OP to give only 4 GB to SQL server.
The reason why I didn't press for a specific number because the OP hasn't mentioned what other apps are running on that box, so I wasn't really sure. I will edit my earlier post to reflect this before someone else gets confused just like you were.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 21, 2010 at 11:42 am
Ah, I see what you meant now. I just saw the sample code and thought you were telling him to cap it literally to 4 GB.
Still, though, while I definitely agree that capping memory is a must, I think he's going to have to do more than that. Access apps are definitely notorious for performance concerns (in my experience anyway).
July 21, 2010 at 11:44 am
jeff.mason (7/21/2010)
And in addition, that isn't going to solve the core issue which is that the memory utilization is too high. He'll just slam against 4 GB instead of 15 GB which means it will show up sooner than before. Without seeing how the Access apps access SQL there isn't much that can be said about that. I can't imagine that 15 GB would be consumed by connections alone, so there may be some design flaws in there somewhere. You'll probably need to profile performance over time to see what queries are using the most memory and try and tweak performance. I am not aware of any memory leak issues or Access query passthrough issues, but others here may know of something.
[font="Courier New"]If you don't set the max. server memory, SQL Server will try to use all of the memory on the box competing with OS it can unless windows takes it back from it forcefully. This is where lock pages in memory and max server memory settings help.
SQL Server by nature is memory hungry and you have to look at PLE (page life expectancy) to see how long the pages are in cache. There might be other reasons also for low PLE like excessive large table scans also and so one has to do some monitoring.
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 22, 2010 at 8:05 am
Thanks guys for your help. I did set the max server memory to use by sql server to 14 GB. I think that will help but I dont think it is solving the issue. I think it has some thing to do with the access applications. I checked sqlserver memory usage this morning and it is 8GB and no one is even using the access application. I am trying to look into the access code and I found that it uses recordsets and sometimes it never dispose them. Do you guys think that this might be the issue? The queries in the databases are huge, millions of records. Does disposing the recordset disposes the data in the memory that was created by that recordset? or it disposes the reference to the recordset?
I know this might not be the right place to ask this question but any help would be really appreciated.
July 22, 2010 at 8:18 am
ashaaban (7/22/2010)
Thanks guys for your help. I did set the max server memory to use by sql server to 14 GB. I think that will help but I dont think it is solving the issue. I think it has some thing to do with the access applications. I checked sqlserver memory usage this morning and it is 8GB and no one is even using the access application. I am trying to look into the access code and I found that it uses recordsets and sometimes it never dispose them. Do you guys think that this might be the issue? The queries in the databases are huge, millions of records. Does disposing the recordset disposes the data in the memory that was created by that recordset? or it disposes the reference to the recordset?I know this might not be the right place to ask this question but any help would be really appreciated.
[font="Courier New"]
This is NOT a problem and is the nature of SQL Server. When SQL Server starts the buffer pool will almost be empty. As queries are processed, it brings necessary data from disk to buffer pool [memory usage goes up] and they will stay there unless more data is read and the old data pages are removed from cache. But the memory for buffer pool will NOT come down and SQL Server will keep the data in the buffer pool just in case the data needs to be referenced again. This is because to avoid reads from disk which are expensive.
Look at Buffer management, Reading Pages and Writing Pages
http://msdn.microsoft.com/en-us/library/aa337525.aspx
http://msdn.microsoft.com/en-us/library/ms191475.aspx
http://msdn.microsoft.com/en-us/library/aa337560.aspx
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 26, 2010 at 2:50 am
Ashaaban
what edition is your SQL Server 2008?
i.e. standard, enterprise?
If you're using Standard edition the lock pages in memory option is ignored unless you have SQL Server patched to SP1 CU2!
Please see the following MS KB regarding 64 bit and lock pages in memory
64 bit and lock pages in memory
Once you apply the lock pages in memory OS feature to the SQL Server service account, ensure you set the min and max RAM. Under normal operation SQL Server will release memory when requested by the OS, however it will not release below min memory once that figure has been reached when lock pages in memory has been applied.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply