May 14, 2010 at 2:04 pm
We are in the middle of creating a datawarehouse for a BI project. We are using linked servers and openquery to gather data from the data sources. Our datawarehouse resides on a windows 2008 R2 64 bit server with about 16 Gb of ram. We have two instances of SQL Server 2008 installed (one is 32 bit and the other is 64 bit). We are using the 32 bit server as a staging DB for loading into the 64 bit instance which will be our datawarehouse. We are running into the issue when we run an openquery with the 32 bit instance that we are using all the VAS memory for the 32 bit instance and the only we we have found to reclaim that memory back is to bounce the sql service. Is there any way to force sql server to reclaim that memory without bouncing the service?
Any help is appreciated, thanks.
May 14, 2010 at 3:11 pm
mbrey (5/14/2010)
We are in the middle of creating a datawarehouse for a BI project. We are using linked servers and openquery to gather data from the data sources. Our datawarehouse resides on a windows 2008 R2 64 bit server with about 16 Gb of ram. We have two instances of SQL Server 2008 installed (one is 32 bit and the other is 64 bit). We are using the 32 bit server as a staging DB for loading into the 64 bit instance which will be our datawarehouse. We are running into the issue when we run an openquery with the 32 bit instance that we are using all the VAS memory for the 32 bit instance and the only we we have found to reclaim that memory back is to bounce the sql service. Is there any way to force sql server to reclaim that memory without bouncing the service?Any help is appreciated, thanks.
I don't think there is a way to do what you want. I am wondering why you have installed the 32-bit version and using that for your staging? Is there a particular system you are accessing through your linked server that doesn't have x64 drivers available?
What I would recommend is to ditch the linked servers and move to an ETL tool instead. SSIS would be a better approach here - and, you can easily use the 32-bit drivers by running the package using the 32-bit version for those systems that don't have x64 drivers.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2010 at 3:20 pm
I tend to agree with you. The problem is that yes, our data source only has a 32 bit driver and linked servers is the only way to connect to it (Rocket UniVerse 10.3 DB). I contacted Rocket and they confirmed this with one of their engineers. Just setting this connection up was a uphill battle.
May 14, 2010 at 3:54 pm
mbrey (5/14/2010)
I tend to agree with you. The problem is that yes, our data source only has a 32 bit driver and linked servers is the only way to connect to it (Rocket UniVerse 10.3 DB). I contacted Rocket and they confirmed this with one of their engineers. Just setting this connection up was a uphill battle.
Never heard of that database - but it doesn't make sense. If you are using an ODBC driver to connect through a linked server, then surely you can use the same ODBC driver to connect using SSIS.
The important part is to use the 32-bit version of SSIS on that machine when running the package. That will allow the usage of the 32-bit drivers and connect using ODBC. If it is not an ODBC driver, then whatever is available to SQL Server linked servers is still going to be available to SSIS (or should be).
Anyways - you could try dropping the linked server and recreating it to see if that frees up the memory. Not sure if that will work though.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2010 at 4:48 pm
What are the min\max memory limits on the two instances set to? You need to ensure that these are properly set and that you are not miscalculating so that your SQL instances and OS are both vying for memory that has already been claimed somewhere.
Joie Andrew
"Since 1982"
May 17, 2010 at 7:20 am
Thanks for the reply, Jeffrey. I had not had much luck in the past querying directly the ODBC, but I will have another go at it. I will post my results (if I get any).
Joie here are the specs you asked for:
32 bit
Min server mem 0
Max server mem 2147483647
Index creation memory 0
Minimum memory per query 1024
64 bit
Min server mem 2048
Max server mem 2147483647
Index creation memory 0
Minimum memory per query 1024
Basically these are the default values for a fresh install.
May 17, 2010 at 12:55 pm
32 bit
Min server mem 0
Max server mem 2147483647
Index creation memory 0
Minimum memory per query 1024
64 bit
Min server mem 2048
Max server mem 2147483647
Index creation memory 0
Minimum memory per query 1024
I would suggest setting the minimum/maximum memory settings on the instances so that each instance and the OS does not need to release memory. Since you stated that you have 16GB, I would suggest start of with something like this:
32-bit instance: min - 2GB, max - 6GB
64-bit instance: min - 2GB, max - 6GB
This will tell both SQL instances to allocate 2GB on startup so other processes do not block SQL from taking its needed memory, and will allow each instance to use up to 6GB in memory each. This will leave 4GB for the OS and other processes to use.
This is just a starting suggestion. I would test and tweak until you have the right combination of settings. The important thing to stress here is that you should configure SQL to where it will not take too much memory; from the OS, other processes or other SQL instances. The settings you have now effectively tell SQL to take as much memory as is possible. So each instance can take all available memory if it figures it needs it. If the memory is not in use and another operation needs memory, SQL will release memory locks, but that operation can cause the pending one to time out.
Joie Andrew
"Since 1982"
June 8, 2010 at 7:14 am
Sorry about the delay but I was able to get SSIS to work and bypass the 32 bit instance. Thank you all for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply