We have to run a number of large select queries against SQL Server from CentOS 6 boxes. On two servers, when using a pretty bland select statement, the rate of data returned from SQL Server starts fast before slowing down and receiving bursts of data every ~30 seconds. On two other servers, the query runs consistently and finishes in less than 2 minutes. The configuration on all of these boxes for unixODBC and msodbcsql is exactly the same.
Example code that is causing the problem:
import datetime
import pyodbc
db_connection_string = '<connection string info>'
print(datetime.datetime.now(), 'Connecting to db...')
db_connection = pyodbc.connect(db_connection_string, autocommit=True)
print(datetime.datetime.now(), '...connected')
cursor = db_connection.cursor()
try:
sql_statement = 'SELECT data FROM table;'
num = 0
print(datetime.datetime.now(), 'Iterating over cursor...')
for row in cursor.execute(sql_statement):
num += 1
if num % 100000 == 0:
print(datetime.datetime.now(), num)
print(datetime.datetime.now(), num)
print(datetime.datetime.now(), '...iteration completed')
finally:
cursor.close()
db_connection.close()
This uses unixODBC 2.3.0 and msodbc 11.0.2270.0. The servers are CentOS 6.5/6.6 with Python 3.4.
We have tried:
- Monitoring system resources, no spikes in memory or CPU usage except when data is being processed
- strace on the process also only shows changes when data is being processed
- both SQL Server and the Python server seem to be hanging and waiting for each other to do something
- Monitoring network traffic also shows no spikes or packages being dropped or any errors
- scp and sftp'ing files to the servers and between the servers has no problem
- Connecting to a different database type with the same query has no issues
- Rewriting the code in Java and running it had the same issues on the problem servers, but ran fine on the good servers
Any other ideas to help track down this issue would be appreciated.
