September 20, 2017 at 2:43 pm
Our environment requires that I create a link server from SQL to Oracle.
The current production system functions as expected. However the new system connects but the same data is returned 7-8 minutes later. All servers are on the same sub-net and both SQL servers are under the same VM instance.
Here's what I have:
Older server that works efficiently
VM server, 1 proc, 16GB memory
Win2008 stnd
SQL 2014 Stnd
Oracle client 11g
From SLQ manager I run the following - select * from openquery(LinkServer, 'select * from Table')
Over 300,000 rows are returned in 1min 15sec
Now my new server.....
VM server 4 proc, 32GB memory
Win 2016 stnd
SQL 2016 Stnd
Many different attempts using 11g and 12c client, not on top of each other I used VM snap shots to restore before each install
From SLQ manager I run the following - select * from openquery(LinkServer, 'select * from Table')
Over 300,000 rows are returned in over 7min
What am I missing with the new install that would be causing the query to come back 6 to 7 minutes later?
- The link servers work fine
- TNS ping is good
- I'm thinking there is an underling Oracle option I'm missing to optimize my select statement
- does SQL 2016 not like link servers?
Any help would be greatly appreciated.
September 20, 2017 at 4:01 pm
Check the the linked server properties and ensure they are the same on both servers.
And I am assuming you are using the oledb provider, not the ODBC one, on both servers.
September 21, 2017 at 6:20 am
Sorry I should have included those settings also. Yes the properties of both link servers (current & new ) are the same.
Allow inprocess under Link server providers OraOLEDB.Ocarle is checked off/selected.
Properties of the Link server:
General
link server name - name
Provider - Oracle Provider for OLE DB
Product name - Oracle
Data source - name.world
Security:
Be made using this secure context:
Username
Password
Server Options:
F
T
T
T
T
Collation Name No Setting/Blank
0
0
F
F
F
F
F
September 21, 2017 at 8:39 am
Does the new server experience slowness to other resources (backups, file shares, etc.). If so, it could be someone misconfigured the virtual network card.
Also, check in sys.dm_exec_requests as to what the wait type is. I expect it will be OLEDB or NULL for a healthy process, and the wait times individually will be low. If you are getting other wait types (preemptive OS wait types especially), there may be contention for CPU resources.
Lastly, how big is tempdb? I have noticed that SQL Server seems to cache the remote data in tempdb before returning it to the client. If tempdb needs to grow to accommodate the 300k records, this could also be a contributing factor.
September 25, 2017 at 8:22 am
Hello,
Sorry for the delay..
1) the server seems to respond normally to other requests
2) I'm running select * from sys.dm_exec_requests while my slow query is also running. I don't see anything in regards to OLEDB but I do see my select statement running wait_type = PREEMPTIVE_COM_GETDATA & wait_time = 0
3) temp DB is 6GB with 10% growth on a 400GB drive. Since this is a new test system there is no data yet on the drive.
4) something I didn't mention before the server has the latest SQL SP
September 25, 2017 at 12:12 pm
OK. PREEMPTIVE_COM_GETDATA usually (at least from what I have seen) means the process is having problems getting enough CPU cycles for the OLEDB driver to deal with the data. Is there some other process outside of SQL (or in it) that is using a lot of CPU? If these machines are on the same VM host, it could be a manifestation of a noisy neighbor absorbing all the ready time. Can you run a test with the count of vCPUs set to 1 like the old machine? The OLEDB connection to Oracle will be a single thread anyway, so multiple CPUs won;t help it directly. Extra CPUs will just keep other tasks out of the way.
September 25, 2017 at 1:08 pm
Thank you! First test run of the same script with only one vCPU is showing the same results as the older test system (give or take 15 - sec). I'll follow-up up again after I rebuild the system run through my install steps again and see my times. I can't remember (if ever) a time when I had a server with too much hardware...
September 26, 2017 at 1:42 pm
OK here's what I have updated on the server to get my desired results (so far):
1) Changed the NIC in the vSphere Client for the server to be E1000. This card/driver returned the desired results no matter what the rest of the network was set at.
2) Changed from multiple CPUs to just one. I will be working with a resource to re-add the extra CPUs without sacrificing the performance gains with just the one CPU.
3) turned on jumbo frames (under the NIC) and selected the largest setting. Again the rest of my network is not configured for this but when turned off/disabled performance took a hit.
I will follow-up with CPU configuration info once the system is rebuilt for pre-production testing.
Thank you again for your suggestions!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply