March 11, 2010 at 12:08 am
Dear Experts,
I have a query which fetch 1.3 million records from oracle linked server
and it takes almost 1 hour for the same.
There is 1GB memmory allocated to SQL Server.
If I increase the Memory allocated, Will the query run faster.?
Thanks in advance.
March 11, 2010 at 3:09 am
With Static memory allocation once the maximum amount of memory allocated to SQL Server is reached it will never release the memory. Even if another process needs it. If there is not enough memory to support these other applications then they are likely to experience excessive paging. This highlights the point made above regarding the need for a dedicated system for your SQL Server
Microsoft recommends that you allow SQL Server to configure it memory usage dynamically by leaving the min and max setting as defaults, which is ideal if you have dedicated server to SQL Server but not if you budgets are tight and you have utilise all your hardware. Just ensure you don’t cause excessive paging by configuring these values incorrectly.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 11, 2010 at 4:07 am
Why there is - "There is 1GB memmory allocated to SQL Server." If you have excess memory you can increase based on the requirement. Recommended is 2GB must for OS.
Try PAE enable if the server supports and as per requirements..
What is the configuration of the BOX?
Paging : Check if the paging of the server is utilized perfectly.
hope this adds.
-Win.
Cheers,
- Win.
" Have a great day "
March 11, 2010 at 4:11 am
winslet (3/11/2010)
Recommended is 2GB must for OS.
on what basis, you say so ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 11, 2010 at 4:29 am
winslet (3/11/2010)
Why there is - "There is 1GB memmory allocated to SQL Server." If you have excess memory you can increase based on the requirement. Recommended is 2GB must for OS.
At present total memory available is 2GB.
I was planning to increase the same and allocate more to SQL Server. Just wondering the above mentioned query will run faster if I do so.
Try PAE enable if the server supports and as per requirements..
Will it make any difference to the query I mentioned ?
What is the configuration of the BOX?
P4, 2GB RAM, Win 2003 Server 32 bit, SQL Server 2005 32 bit.
Paging : Check if the paging of the server is utilized perfectly.
How can I check the same ? Please guide.
Thanks.
March 11, 2010 at 5:33 am
on what basis, you say so ?
Its a best practise to have a perfromance..
-Win.
Cheers,
- Win.
" Have a great day "
March 11, 2010 at 5:38 am
If it is 2 GB RAM then you can upgrade memory hope so.
PAE :
Please go through -- http://support.microsoft.com/kb/283037
http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
Cheers,
- Win.
" Have a great day "
March 11, 2010 at 6:43 am
The question isn't whether or not putting more memory on the box will speed up the query. The question is whether or not your query is running slow because of a lack of memory. While the query is running, have you examined the wait statistics? Here is a great article from Microsoft on doing just that.
Also, how are you querying Oracle. If the query is simply against the linked server, it's likely bringing all the data across the line and then filtering it on the SQL Server side. A better method is to run the query on the Oracle side, allowing it to filter the data, and then only bring the data you need back. You can do this using the OPENQUERY function.
Finally, have you looked at the execution plan to see what's happening with your query. That can also suggest places that you tune.
Simply throwing more hardware at problems doesn't always work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2010 at 6:56 am
[Quote] You can do this using the OPENQUERY function.
[/Quote]
I had asked this question before also...
Inorder to use OPENQUERY, Do we need to make the linked server using any specific "provider" ?
Thank you so much for such a detail explanation.
March 11, 2010 at 7:13 am
Joy Smith San (3/11/2010)
[Quote] You can do this using the OPENQUERY function.[/Quote]
I had asked this question before also...
Inorder to use OPENQUERY, Do we need to make the linked server using any specific "provider" ?
Thank you so much for such a detail explanation.
Whatever you can linked servers you can run queries against, you can use OPENQUERY to make those queries run on the linked server. There are a couple of caveats. First, you need to use the syntax of that other server, and you can't pass parameters, but have to build a string.
Other than that, I've found that when I had to use linked servers, OPENQUERY provided much faster results.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2010 at 8:58 am
winslet (3/11/2010)
on what basis, you say so ?Its a best practise to have a perfromance..
So if I have a server with only 2GB of memory in it, I can't give SQL any?
It is a recommended practice to reserve memory for the OS, but it's not a fixed amount (like 2GB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2010 at 4:59 am
I've found that when I had to use linked servers, OPENQUERY provided much faster results.
It's amazing..! Open query is too fast...to be precise, it fetches data 60 times fatser in my case. Thanks a lot.
I had tried it few days back..Was getng some error..so left it thinking there will not be much diff in execution time. But when I did some filtering with Open Query it's taking very less time.
Thanks alot once again.
March 12, 2010 at 5:53 am
Joy Smith San (3/12/2010)
I've found that when I had to use linked servers, OPENQUERY provided much faster results.
It's amazing..! Open query is too fast...to be precise, it fetches data 60 times fatser in my case. Thanks a lot.
I had tried it few days back..Was getng some error..so left it thinking there will not be much diff in execution time. But when I did some filtering with Open Query it's taking very less time.
Thanks alot once again.
I'm glad it helped. And thank you for posting what worked. It's always helpful for someone else hitting the same problem you did to see what was your successful answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2010 at 11:01 pm
you are abosultely right. Will be helpful to others.
Sometimes I myself may search for the same solution over a peroid of time..!!!. It's always better to post what's worked.
March 15, 2010 at 6:31 am
Joy Smith San (3/12/2010)
I've found that when I had to use linked servers, OPENQUERY provided much faster results.
It's amazing..! Open query is too fast...to be precise, it fetches data 60 times fatser in my case. Thanks a lot.
I had tried it few days back..Was getng some error..so left it thinking there will not be much diff in execution time. But when I did some filtering with Open Query it's taking very less time.
Thanks alot once again.
Would you post the code that worked for you please? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply