?
Solved

Monitor queries executing on Pervasive SQL at run time

Posted on 2006-03-28
10
Medium Priority
?
3,605 Views
Last Modified: 2013-12-25
Hi,
I have an application whihc uses Pervasive SQL as a backend.
Is there any tool for Pervasive similar to Microsoft's Profiler for MS SQL server?
i would like to monitor the queries being executed behind the application?
can this be achieved using Audit master?
So far that i have checked .. i think that Audit Master only displays modifications made to the database, not the retrievals or the queries being used.

Regards,
Nabeel Moeen
0
Comment
Question by:nabeelmoeen
  • 5
  • 3
9 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 16309674
Query Plan Viewer is about the only thing for monitoring SQL queries.  A PDF that Pervasive sent me is at http://www.mirtheil.com/files/queryplanviewer.pdf.  
AuditMaster only tracks changes.
The Pervasive Monitor will give you general information about the database.

On thing to consider is that most PSQL applications don't use SQL and so the QPV won't help.  Does the application use SQL?  
0
 

Author Comment

by:nabeelmoeen
ID: 16309744
i'm assuming you mean executin sQL directly from the App as opposed to Stored procedures?
i guess it does for generating reports, as there arent any sp's in the database.
And what i want to determine is the tables its accessing to fetch the data.
0
 

Author Comment

by:nabeelmoeen
ID: 16309759
and the queries used to fetch the data.
I'll take a look at the pdf. btw, the first page mentions its for Pervasive 2000 ... would it work with version 8.x even if this is THE tool =)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:mirtheil
ID: 16310153
The QPV applies to all versions of PSQL (2000, v8, and v9).  The QPV monitors all SQL queries executed at the engine.  This means standard SQL and Stored Procedures.

THe application may not use SQL. It may use the Btrieve API.  If it does, the QPV will NOT help.  

To determine which interface the application uses, call the vendor of the application.
0
 
LVL 29

Accepted Solution

by:
Bill Bach earned 200 total points
ID: 16317415
Here's how to tell for sure:

First, start the Pervasive Monitor utility (either from the server or from a workstation, then use Options/Connect to get to the server).  Go to the Microkernel/Active Users screen.  If all of the users are showing up as regular user names, then your application is purely Btrieve-based.  If you see users listed like "SRDE:<anonymous>", then you will *ALSO* have SQL/ODBC users accessing the data.  You can tell for sure the exact location of each user by looking at the "Network Address" line in the Monitor -- this should give you the exact address of the computer related to that connection.

If you are working on a SQL application, then the Query Plan Viewer is the first/best tool to start with.  However, you can ALSO watch the connections via the SQL/Active Connections window on the Monitor to get some basic idea of queries that are taking a long time to run (i.e. they show "Active" for a long time).  Finally, you can watch the related SRDE:<...> connections in the Microkernel/Active Users screen to see how many database requests are being issued by the query.  An app that reads 10 million records on a small database can probably be optimized somehow.

If you are monitoring a Btrieve-based application, then the rules change somewhat.  You'll have to monitor the connection carefully from ther Monitor while the user is working.  This will show you the files being accessed (on the right side of the screen) at the time the app is running.  This will also show you basics about the number of records Read, Inserted, Updated, and Deleted.  However, it doesn't go any further than that.

To TRUELY figure out a Btrieve app, the process is fairly "simple", but execution can be complicated.  First, get all users out of the database, then get one user to get the application to the point that you are interested in.  In the PCC, go to the Configuration/Server/Debugging  settings and enable MKDE Tracing for all operations.  (Don't forget to "Apply" the change.)  Next, run the application for the process you want to check out.  When it is done, do back to disable MKDE Tracing and Apply again.  We also have a free program oon our web site called SetTrace that can flip the trace flag on and off for you.  Get it from http://www.goldstarsoftware.com/download.asp if you want to try it instead of using the PCC.

Now, you can review the contents of the MKDE.TRA log file you just created.  This is a developer-level log file, but any Btrieve developer can read most of the information in there to let you know exactly what transpired which that process was running.  You will see operation codes, data buffers, and such.  See the online manuals for exact details if you need help with the analysis, or post samples here for help.

Two last tips:
1) You must trace with only one user in.  If you don't, you'll have to manually figure out which calls are from which user.
2) Do not leave tracing enabled for too long -- it is a huge performance drain, especially with mutiple users in the system.
0
 

Author Comment

by:nabeelmoeen
ID: 16317911
thanx for the help mirthell,
i got the QPV working, though it just displayed the top level query
it might be that the application is using Btrieve APIs :(
0
 

Author Comment

by:nabeelmoeen
ID: 16317928
Great help Bill
Saw your solution a bit late though
i'll definitely give it a shot
0
 

Author Comment

by:nabeelmoeen
ID: 16318039
Bill, whats the default location of the trace file?
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 16318135
The default location is "C:\PVSW\BIN\MKDE.TRA".  It's configurable in the PCC under Debugging (I think).
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question