Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19

    Unanswered: Queries of active processes

    Hi,
    I am using Embarcadero DBArtisan to check the queries that users are running so that I can avoid blocks between them, or to investigate the exact query that caused the block. I know that the data about processes is gathered from master..sysprocesses but where can I get the actual query that a process is running without messing with the mda tables?
    Actually I am trying to figure out the way that DBArtisan is doing, so that I can replicate it in a stored procedure for statistical use (gather with queries cause most blocks or take the longest so that they can be optimized).

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You can try

    dbcc traceon (3604)
    dbcc sqltext (<spid>)

    But it might chop large queries

    Edit: PS It is usefull with sp_showplan <spid>,null,null,null
    Last edited by pdreyer; 06-19-06 at 06:57.

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I would still consider taking a look at the MDA tables - they provide a *lot* of information that comes in handy when you are faced with a performance problem.

    Setting them up only takes a couple of minutes, and the overhead at the server level is barely noticeable.

    Michael

  4. #4
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19
    I set up the mda tables because I actually need the output captured and inserted in a table for logging purposes.

    My question now is weather it is safe to create an insert trigger for monProcessSQLText or if that will seriously affect the overall server performance (I want to log daily all the sql commands send by all users).

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I don't think you can create a trigger on monProcessSQLText.

    You should instead take a look at monSysSQLText (which is a "pipe"), and see how to regularily extract the data from that table to a permanent storage location.

    Consider checking Rob Vershoor's MDA documentation at http://www.sypron.nl/mda.html

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •