3

I have a query:

SELECT *
FROM OPENQUERY
(
    oracle,
    'SELECT *
    FROM [some_table]'
)

It runs for about 7 hours. I only need to run this query once to import data into a new system when we change system.

I want to know is there some table in SQL Server 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

  • 7hrs is a lot of time. Have you looked into BCP or SSIS for a good possibility to speed this up ? – Kin Shah Apr 14 '14 at 13:10
  • Yes have looked at SSIS packages but have written a script that will create SQL Server Agent jobs dynamicly to start the import processes instead. because I can get this to adjust the process and order of imports based on how the other imports have gone. I have about 150 tables importing but the longest one is the one that is taking 7 hours. – user802599 Apr 15 '14 at 2:05
3

Is there some table in MS SQL 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

The simplest way to do this is the query the management views associated with the currently-executing request, the session, or the connection:

The first two expose a row_count attribute that contains the number of rows returned by the query so far. If you know the total number of rows that will be returned (even roughly) this can provide a simple progress indication.

The third view provides num_reads and num_writes indicating how many network packets have been sent and received by the connection. The size of the network packet in use for that connection is given in the net_packet_size attribute. The reads and writes include protocol overhead, and are not reset to zero at the start of each query or statement, so you would need to record the values before the query of interest starts executing.

All three views allow you to specify the session_id you are interested in, for example:

SELECT row_count 
FROM sys.dm_exec_requests AS DER 
WHERE session_id = 52;
  • Thanks Paul, I think if I create a separate login for each on the store procedures I am going to run to import data, I can poll this data and store it with a WHILE loop and a WAITFOR DELAY and then store data and compare the current import and I should be able to get a fairly accurate progress bar for the import. – user802599 Apr 15 '14 at 1:15
1

Nowadays, network latency is rarely a big issue, so if your query lasts long, it's because of the disk. The best way to monitor the progress, would be to look exactly where it does most of the work, ie. the Oracle server. So try to gain access on the OEM, and monitor the execution from there. You might also find out why it lasts so long. As long as your disks (on the SQL Server running machine) are not used at 100% (according to the resource allocation profile), the bottleneck may be on the remote server.

  • Hi Daniel I have checked the wait_resource and the reason for most of the waits is because of the remote server. The last few minutes is the disk but it is mostly the time to download the data. – user802599 Apr 15 '14 at 1:34
  • The server it has to download the data from is accessed over the internet and the table it is downloading is about 8Gig so 7 hours to download is about as fast as it can download on a Business Internet connection in Australia. We are actually downloading the data from the main server to the second server and then copying the data to a usb and driving the data to the location of the new servers, because of the time it would take to transfer through the internet, so the network/internet speed is still a big issue for this. – user802599 Apr 15 '14 at 1:35
  • Oh, I thought you were working over a intranet or something. You could either use a network monitoring tool and check how much data was transferred via that port, or, in SQL Server there is a procedure called sp_spaceused <tablename>; call it before executing the query, and then repeatedely after to check how the size of your table increases. – ddaniel Apr 15 '14 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.