.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

View Complete Post

More Related Resource Links

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

Query execution time from 1 second in SQL 2000 to 20 Seconds in SQL 2008 R2


When I ran this query from SQL 2000 it takes 1 second. now in SQL 2008 R2  the same query is 20 times slower.

This is the query and the numer of records and the time in SQL 2008 R2:

SELECT * FROM dbo.CxP_Saldos 
0 seconds, 923 Rows
SELECT * FROM S_Conta.Cuentas
0 seconds, 662 Rows
SELECT * FROM S_Conta.Cuentas c INNER JOIN dbo.CxP_Saldos s ON c.id = s.CtaProv
18 seconds, 923 Rows

how can I submit the actual execution Plan?

How to create linked Server to connect from SQL Server 2008 to SQL Server 2000


How to create linked Server to connect from SQL Server 2008 to SQL Server 2000.

I following the steps to create the linked server in the GUI but failed with the following error message.

I used the full computer network name as the 'Linked Server' name

 OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my_server_network_name". The provider supports the interface, but returns a failure code when it is used.

Thanks a lot for your help.


Clustered SQL 2008 R2 x64, Oracle linked server works on one node, fails on the other


Juicy bits:

- SQL Server 2008 R2 x64

- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

- Active/passive cluster

- Oracle client 11gR1

- "Allow Inprocess" provider option is enabled


I'm stumped.  Our development, QA and UAT environments are all running the same versions of Windows, SQL, and Oracle client.  Linked server between SQL and Oracle works just fine.


Production is clustered.  The linked server works just fine on one node of the cluster, but if we failover to the other node, attempts to use the linked server in any way, OPENQUERY, GUI, etc..., cause the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-12634: Memory allocation failed".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".


What am I missing?  I've reinstalled the Oracle client on the defective node, verified that the SQL Server service account has full rights to the Oracle client dir

SQL 2008 to iSeries via linked server (IBMDASQL) - TOP 10 * SQL query returns ALL records


I am querying a iSeries - AS400 database from SQL server 2008 server 64 bit,  via a linked server (using OLE DB Provider - IBMDASQL)

The SQL is select top 10 * FROM <linked Svr>.<Dest server>.<DBName>.<TableName>

Instead of returning top 10 rows, the query returns ALL the rows. I use the top 10 sql query in my "Heart beat monitor" to check the viability of the linked server after a server reboot.  If the test query work

Linked Server using Oracle OLEDB 64-bits Win 2008 R2



I have 2 boxes running SQL Server 2008 and using linked Oracle OLEDB that behaves different for same query.

On 1st machine, using win 2003 , SQL Server 2008 SP1 on a 64-bit server I  I created a linked server to Oracle database using Oracle OLEDB provider. When I submit query to a Oracle database using Oracle OLEBD using WHERE clause, this WHERE clause is sent to Oracle, then it uses an index to resolve query and send result already filttered to SQL Server.

I have a 2nd box running Windows 2008 R2, SQL 2008 SP2 on a 64-bit server and queries are working fine, but when I filter some column of a linked server table using WHERE clause, SQL Server sends to Oracle SELECT without where clause, Oracle performs a table scan on that table and then sends results to SQL Server, that applies filter to the result. 

The problem is that some Oracle tables are huge and sending a SELECT without where clause causes table scans on Oracle, what should be avoided.

I already tried to change OLEDB properties and also tried to change linked server properties, but none of them solved the problem.

Does anyone knows what I should change on 2nd server to make it works like 1st server?

Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos p

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

COM+ and Windows 2000: Ten Tips and Tricks for Maximizing COM+ Performance


There are many tricks to getting the most out of COM+, and this article offers the author's top 10. The tips cover the importance of transaction processing, the use of the COM+ catalog, and the design of three-tier distributed systems. Writing components using the correct threading model, knowing when to use compensating transactions, and the importance of stress testing early in the process also make the list. Other indespensible suggestions emphasize the importance of recognizing where an object's state is located, choosing appropriate authentication levels for COM+ applications, using Queued Components correctly, and implementing object pooling.

David S. Platt

MSDN Magazine December 2000

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

SQL Server 2008 Powershel trying to access SQL Server 2000

Below is a script from my SQL server 2008 PowerShell trying to connect a SQL Server 2000 instance. PS SQLSERVER:\SQL\SS2K8\DEFAULT\databases> cd \ PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MySS2KMachine PS SQLSERVER:\sql\MySS2KMachine> cd default PS SQLSERVER:\sql\MySS2KMachine\default> cd Databases PS SQLSERVER:\sql\MySS2KMachine\default\Databases> dir and I see a list of all databases on the default (SS2K) instance. I also see al instances on the machine: PS SQLSERVER:\sql\MySS2KMachine\default> cd .. PS SQLSERVER:\sql\MySS2KMachine> dir Instance Name ------------- DEFAULT MSSQL2005 As you can see from the above, there is also a SQL Server 2005 instance installed on the MySS2KMachine. Now, if I try the same thing for a Sql Server 2000 instance on another server (Windows 2003 R2 with SP1) with no SQL Server 2005 or higher installed on the machine, I get the error below: PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MyPureSS2KMachine WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MyPureSS2KMachine' failed with the following error: SQL Server WMI provider is not available on MyPureSS2KMachine. --> Invalid namespace Set-Location : Cannot find path 'SQLSERVER:\sql\MyPureSS2KMachine' because it does not exist. At line:1 char:3 + cd <<<< MyPureSS2KMachine PS SQLSERVER:\sql> So it see

Query on XML datatype in SQL Server 2008

Hi All, In table I have column with XML datatype. I can able to read the below XML tag. SET   @Title = CAST(@XMLContent.query('data(root/chemicalName)') AS VARCHAR(50)) <root><chemicalName>Sulphuric acid</chemicalName></root> but how can I read (in SQL Server 2008) with multiple rows in it, and how to know the list of child nodes and data of it dynamically? <Root> <Row> < CurrencyCode>GBP</CurrencyCode> < CurrencyName>POUND (STERLING)</CurrencyName> </ Row> <Row> < CurrencyCode>INR</CurrencyCode> < CurrencyName>Rupee</CurrencyName> </ Row></

Why upgrade is required from SQL Server 2000 to SQl Server 2008

Hi all Can some body share with information about "Why upgrade is required from SQL Server 2000 to SQl Server 2008?"   This question was raised by my Boss why upgrade is required.   I stared saying   My: there is no more   support for SQL Server 2000 from Microsoft   Boss: what you will do  as DBA, Why Required Microsoft support   My: 2008 having very good features where 2000 not have...like Compression, resource governing. .Etc..   Boss: do you mean Database backup compression take full backup and compresse it using ZIP   Finally He (Boss) told me that give document proof why I have go for SQL Server 2008 and what down time you required?   Some body help me why really SQLServer 2008 is required and what are benefits   I have a link; please provide your greatest ideas so that I can convince my Boss.   http://www.mssqltips.com/tip.asp?tip=1462   share your greate Ideas please....   Thanks in Advance     SNIVAS

Full text performance of a certain query

I am doing a full text query on a very simple table and when I include the search term "y5v" in the conditions it slows the query down like 8 to 1. The table is defined as: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblTextSrchData]( [intItemId] [int] NOT NULL, [srchTxt] [varchar](max) NOT NULL, CONSTRAINT [PK_tblTextSrchData] PRIMARY KEY CLUSTERED ( [intItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is the query: SELECT II.intItemID, KEY_TBL.[RANK] as Score FROM tblItems II INNER JOIN CONTAINSTABLE(dbo.tblTextSrchData,srchTxt, '("capacitor" OR FORMSOF(INFLECTIONAL, "capacitor") OR "capacitor*") AND ("active" OR FORMSOF(INFLECTIONAL, "active") OR "active*") AND ("y5v" OR FORMSOF(INFLECTIONAL, "y5v") OR "y5v*")') KEY_TBL ON KEY_TBL.[KEY] = II.intItemID If I take any of the additional search terms out it improves performance.  If I remove the yv5 search term the results are returned in less than a second. What seems to be the problem is for some reason when I add yv5 it starts doing these sorts after the full text matching.  These sorts take most of the time.  I'm not sure

SQL Server 2008 Linked Server SELECT INTO issue

I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SSRS 2008 R2 Add-in Performance Issues

I have a very interesting issue with a customer.  They use lots of images in their reports.  The report performance is very poor after deploying the report to Sharepoint Integrated Mode and executing the report from Sharepoint Report Viewer.   The single server is setup with SQL Server 2008 R2.  Sharepoint and Reporting Services are running all on the same server.    The goal is to be able to use the Report Viewer Web Part and Sharepoint Filters to create Dashboards.   We have tried the following ways to get satisfactory performance out of these reports:  ·         When we run the reports in a page viewer web part in Sharepoint against a native SQL 2008 Report Manager on another server, they execute great ·         When we run the reports in a page viewer web part in Sharepoint against the Sharepoint Integrated Report server on same server, they execute way more quickly then when using the Report Viewer against the same Sharepoint Integrated Report server . ·         When we remove the images from the reports  and execute the reports via report viewer or in the Integrated library they execute satisfactorily in both Report Manager and Sharepoint Integrated - but th
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend