How to Monitor ODBC connections and ADO connections on a Windows server?

I am having performance problems with a Pervasive SQL database running on Windows 2008 server. I learned earlier on EE that no SNMP monitoring is available for PSQL. But I suspect that the slowness problems I am seeing with PSQL are related to ODBC and/or ADO.NET connections querying this server. Is there a means of monitoring the ODBC connection requests or other aspect of the ODBC connections on the target server? SNMP? WMI??
LVL 2
amigan_99Network EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
Yes, there are several options.  

The first solution is to enable the Query Plan Viewer on the ODBC DSN itself.  (This only works if you are going through the ODBC DSN, of course.  Accessing through ADO or JDBC may bypass this.)  Find the ODBC DSN in the registry, create two REG_SZ fields:
   QryPlanOutput: Set this to the path of the file you want to create.
   QryPlan: Set this to 1 to enable the query planner.
Once you do this, the NEXT connection to the database via this DSN will start capturing SQL statements to the file for you.  You can view this with the Query Plan Viewer (W3SQLQPV.EXE).  However, be careful not to leave this running for long periods of time, as this will cause the query file to grow & grow, and it will slow the system down.  Also, in rare cases, this can cause the engine to crash.

Another option which is less intrusive is to capture the network traffic coming into the server and then parse that traffic.  You can capture packets coming inbound to TCP port 1583 and look at them by hand in Wireshark, or use a tool like SQLInterceptor.  The nice thing about this option is that you see the requests coming in AND the replies going out, so you can see how long each query took, too.

Finally, if you are really quick, you might be able to watch each connection through the PSQL Monitor.  In the Active Sessions screen, look for the "SRDE:<user>" connections, and then watch the lower-left corner for disk accesses and cache accesses.  You can use these numbers to gauge system workload.  A long-running query will amass a lot of page accesses.  You can then use the network address of this user to narrow down your search with Wireshark.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amigan_99Network EngineerAuthor Commented:
Great information. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.