This discussion is archived
2 Replies Latest reply: Apr 16, 2012 5:43 AM by 928132 RSS

SQL linked server twice as slow in Windows 7 as XP

928132 Newbie
928132 Apr 3, 2012 3:59 AM
Currently Being Moderated
We have a transaction based synchronization from SQL Server to Oracle, i.e. both must commit or rollback in one distributed transaction. We achieved this using MSSQL Linked Server using the Oracle Provider for OLE DB and triggers running openquery for speed.

The SQL Sever 2008 was on XP (doesn't need a server) and the Oracle 11g was on Red Hat 5EL. In testing from the first trigger firing to the last trigger finishing, a save test took ~11 Seconds, not perfect but acceptable. Yet when we moved to Windows 7 this time rose to 19 Seconds.

We contacted Microsoft support and they seen to thing it is a multi platform issue. So to prove them wrong we created an oracle DB on a Windows 7 system and ran the tests again, same issue. They will not look into the issue without us paying for premium support at an hourly rate, due to the Oracle DB. So we created one more test environment to try to prove it was a Microsoft issue and not an Oracle driver issue we created 2 SQL servers and ran tests to see if we got the same issue. We got the following results, which seems to point the finger at the Oracle OLE Driver on Win 7:

Windows XP sync to Windows 7:

SQL Server to Oracle = ~8 seconds
SQL Server to SQL Server = ~6.5 seconds

Windows 7 sync to Windows 7:

SQL Server to Oracle = ~19 seconds
SQL Server to SQL Server = ~5.5 seconds

Has anybody else found this issue and if so have they found a cause?

We Have run NetMon and analysed the results and the packets seem to take twice as long in Win 7 as Win XP. This seemed to point to a network issue, but does not explain why 100% Microsoft is faster when synchronising Win 7 to Win 7, as opposed to Win XP to Win 7.

I get the feeling the issue lies somewhere in the changes that were made by Microsoft in Vista in the networking protocols and configuration, causing a side affect in the OLE Driver. It could be that the driver works, but needs to be tuned with the new networking protocols from Microsoft.

We have tried countless network settings, even the ones suggestions from MS Support made no difference at all.

Edited by: 925129 on 03.04.2012 03:57
Corrected a couple of types
  • 1. Re: SQL linked server twice as slow in Windows 7 as XP
    gdarling - oracle Expert
    gdarling - oracle Apr 4, 2012 9:10 AM (in response to 928132)
    Currently Being Moderated
    You may want to open a SR with Oracle Suport so we can lend a hand further defining the issue.

    Are you able to reproduce this complaint outside SQLServer then? Executing a query via some VBScript for example?

    What I'd do to start looking into the issue is get matching client and server sqlnet traces (level 16, with timestamps on) of both the 'fast' and 'slow' environments and compare them. It's hard to relay exactly what I'd look for, but things like a) packet sizes b) are there gaps in the timestamps in the traces where the client is waiting on the database c) is the time between when the packet was sent on the db and received at the client effectively the same d) are all operations a little slower, or are a couple operations responsible for the majority of the difference?

    Hope it helps, but realize it probably doesn't.

    Greg
  • 2. Re: SQL linked server twice as slow in Windows 7 as XP
    928132 Newbie
    928132 Apr 16, 2012 5:43 AM (in response to gdarling - oracle)
    Currently Being Moderated
    Hi Greg,

    Thanks for the answer, sorry for the late reply, I was away for Easter.

    What we have done is to alter the triggers to record their names, start and end times to an SQL Server table. I delete the contents of the table then run a test and run several queries to analyse the trigger data. the difference between the min start time and the max end time is how we get the total time. We run many queries over the data. I can send you the table DDL and some test run results DML, as well as, the queries I use to analyse the results.

    On top of this test we have run Microsoft's NetMon to analyse the network traffic and there it is clear that the packets take roughly twice as long in Win 7 as in Win XP, both in 32-bit and 64-bit OS versions. I know there has been some major network changes after Win XP and Microsoft are convinced this is an Oracle issue. The same OLE Driver is used for both Win 7 and Win XP OS's (excluding the one for 32-bit and one for 64-bit) and my guess is something needs to be tuned for the networking in Win 7. We have really tested this so much over the last 2 months and tried all kinds of network setting tuning in windows but nothing has worked at all.

    After creating an SQL DB to act in the place Oracle DB, the same issue was not present, so it really is a heterogeneous platform issue. In fact if the sync is SQL to SQL it is faster in Win 7 not slower.

    If you would like any of the results of our testing to see if you can spot the issue, you would be welcome.

    Guy

    Edited by: 925129 on 16-Apr-2012 05:40

    Edited by: 925129 on 16-Apr-2012 05:41

Actions

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points