Question _sqlsrv2 Low Cpu Very Long Running Query

Joe Meyer

New Member
We're currently using Progress 10.2B via ODBC and recently ran into the following issue:

Progress KB - Running SQL query with large WHERE IN clause crashes _SQLSRV2 on some UNIX systems

The work around for us was to increase the -SQLStack parameter (2000 wasn't high enough to get us around our issue we ended up going to 20,000). Now however, it appears that this may have had some other side effects and I've been unable to find much on what might be causing this new issue.

Also worth noting, that while we can now use large WHERE IN queries (some of them we're using are well over 100 parameters), using *large* JOIN ON condition sets (3 or 4 conditions) seems to present us with issues still (in fact queries that used to work before the SQLStack increase, no longer work in some cases).

Now, our reports run great 99% of the time, however every now and then we'll load up the web page, fire off an ODBC connection, I can see the _sqlsrv2 process spin up on our database server, but it's only using 3-4% of CPU of one core (normally this report uses 80-90% of one core for a second and it's done). Additionally, this process will sit there and run for several minutes (usually the web page that's firing off the ODBC connection times out before it finishes).

I'm not sure what my next step should be as far as tracking this down, any tips would be welcomed, explicit instructions preferred.
 
Last edited:

TheMadDBA

Active Member
Download ProTop and see what is happening from a database IO perspective. If you can find the problem queries run an explain plan to see if the path is anywhere near correct.
 
Top