Why is SQL Server so slow?

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)
('sqlserver', 'jonnywin', null, null, null, 'os_test', 'os_password');

--get the ID you just inserted
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'], 
'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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.