This is a very good question. When using Outsourcer, you may find a job run longer or shorter from time to time and in my experience, it is because the source database is waiting on something. It is the bottleneck 99% of the time. So why is it slow today versus yesterday?
Databases have wait stats and SQL Server has this information too. You can query SQL Server to find what it is waiting on by logging into SQL Server and executing a SQL command. With the code provided below, you can leverage Outsourcer to query SQL Server from Greenplum!
First create a connection for Outsourcer:
INSERT INTO os.ext_connection (type, server_name, instance_name, port, database_name, user_name, pass) VALUES ('sqlserver', 'jonnywin', null, null, null, 'os_test', 'os_password'); --get the ID you just inserted SELECT id FROM os.ext_connection WHERE type = 'sqlserver' AND server_name = 'jonnywin' --in my example, the value is 1.
Next create the schema in Greenplum for this table:
CREATE SCHEMA sqlserver;
This will create the External Table in Greenplum for Outsourcer:
SELECT os.fn_create_ext_table('sqlserver.sessions', ARRAY['sql_time timestamp','start_time timestamp','status varchar(30)', 'session_id smallint','sqltext text'], 1, 'SELECT getdate() as sql_time, req.start_time, req.status, req.session_id, sqltext.TEXT as sqltext FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext order by req.start_time');
Now you can execute the query to get the session information:
select * from sqlserver.sessions;
I have found that the most critical thing to look for from SQL Server is the “status”. You will see it “Running” when it is actually doing something but “Suspended” when it is waiting on a resource.
So if you see queries generated by Outsourcer “Suspended” in SQL Server, that is why it is slower today versus yesterday. Work with your SQL Server DBA to determine what resource(s) SQL Server is waiting on next.
More information on External Tables is in the Outsourcer documentation.