I'm currently working on a database migration, from Oracle 11g to SQL Server 2019. We're talking here of about 10-12 GB, in something like 300 tables (so creating them manually is out of reach, as is creating 300 data flows in SSIS).
The idea was to do it in an "ELT" way: in a first step, to "replicate" the content of the Oracle source database onto a staging database (having a Simple recovery model, and located in the SQL Server instance), and in a second step, to operate all necessary transformations in order to fill the destination tables (in the destination database on the same SQL Server instance). This question only deals with the first step of the process.
The "one-to-one extract and load" first phase goes quite well. First of all, I declared the Oracle server as a linked server (the provider is "OraOLEDB"). Then I created an SP that generates "on the fly" all the required tables on the SQL Server staging database (querying Oracle metadata and translating it to corresponding SQL Server DDL, including primary keys if any), running them without any error.
Finally, the same SP also generates all required queries to fetch the data and runs them. All the required data is transferred as required, but performance is completely impractical (the migration would have to be completed from start to finish in a single night).
The queries generated all have this simple shape (as a matter of fact, most queries don't even have the WHERE clause):
INSERT <DestinationTable> WITH (TABLOCKX, HOLDLOCK) (<ColumnList>) FROM Openquery ([<OracleLinkedServer>] , 'SELECT <ColumnList> FROM <OracleTable> WHERE <SimpleCriteria>') ORDER BY <PrimaryKeyIfAny>;
I expected such a volume to be transferred in, say, 1 or 2 hours, but I'm far from such a performance. At first, this took more than 16 hours.
After some documentation reading, I increased the
FetchSize parameter of the linked server to 10,000, which reduced the whole duration to almost 9 hours, but it is still far too long (I also tried to increase the same parameter to 200,000, without any further performance improvement).
Most of the waits I could see during the process were either "OLEDB" or "PREEMPTIVE_COM_GETDATA", which seem to indicate that the destination is waiting for the source. Take note that in the test environment, both servers are in the same datacenter, so it probably is not a network issue (in the final, real migration, the source server will be overseas, though). As far as I can tell, I didn't see any parallelism happening in the real execution plans I could intercept.
If you need any other technical detail in order to help me, please feel free to ask. (And please forgive any English error, as it is not my native language).
My process has extensive logging, I could tell you about any of the hundreds of tables transferred, but of course it would depend a lot on their volume (whether measured in total row length, or in row count). And by the way, the logging is not the cause of the bad performance. When I ask the SP to fetch just the first row of all required tables (I have a parameter for that), the whole thing takes less than a minute.
I went to my log table and took a fairly typical table for example. It has 100K rows, weighs about 25 MB, and the transfer takes about 45 seconds. If I use SQL Developer on the destination machine in order to extract a CSV of the same table from the source server, it takes only about 9 seconds.
I tried to run manually in SSMS the INSERTs generated by my SP, and I find the same (slow) speed. I also tried to reformulate them using the four-parts linked server syntax, with no difference. But when I try to export the destination tables to a CSV in a folder, using the "Export" feature of SQL Developer, the speed I can get is about 4-5 times higher.
The plan for one of my elementary queries, as returned by
sp_WhoIsActive, can be found here: https://www.brentozar.com/pastetheplan/?id=HJWj7Oqps
wait_info column is NULL most of the time (and indicates 1-2 ms minor waits from time to time). But other columns look more worrying to me: First of all,
context_switches amount to more than 740,000,000. Second,
memory_info indicates that I have 7.3 GB memory available, but that only 10 KB have been granted to my query (and 3 KB are really used), but I'm not sure it's relevant or may be improved.