Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Location
    India
    Posts
    50

    Unanswered: Idetifying query used by a SPID

    Hi,

    Is there any way that I can find out, what query is currently being executed by a SPID. i.e

    1> sp_who
    2> go
    |fid |spid |status |loginame |origname |hostname |blk_spid|dbname |cmd |block_xloid|
    |------|------|------------|------------|------------|----------|--------|----------|----------------|-----------|
    | 0| 2|sleeping |NULL |NULL | |0 |master |NETWORK HANDLER | 0|
    | 0| 3|sleeping |NULL |NULL | |0 |master |DEADLOCK TUNE | 0|
    | 0| 4|sleeping |NULL |NULL | |0 |master |MIRROR HANDLER | 0|
    | 0| 5|sleeping |NULL |NULL | |0 |master |ASTC HANDLER | 0|
    | 0| 6|sleeping |NULL |NULL | |0 |master |CHECKPOINT SLEEP| 0|
    | 0| 7|sleeping |NULL |NULL | |0 |master |HOUSEKEEPER | 0|
    | 0| 29|running |sa |sa |axews89 |0 |master |SELECT | 0|

    (7 rows affected)
    (return status = 0)
    1>


    i.e in the above output, is there any way that I can find out, what query is being executed by SPID : 29 in database.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Yes. Depends on your version (please, always post your ASE version with questions) and what optional features you have installed (eg. MDA tables). Each optional feature has a performance penalty, so use the core features first:
    dbcc traceon(3604)
    dbcc sqltext(29)
    ...
    dbcc traceoff(3604)

    Also look at master..sysprocesses --> systransactions, there are quie a few columns more than sp_who.

    There are limits to the length of SQL. If you've got long strings of it, ala Dynamic SQL, you will run into problems (with Dynamic SQL you will run inton many problems, that's just one).
    Last edited by Derek Asirvadem; 09-01-09 at 11:18.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Quote Originally Posted by Derek Asirvadem
    Yes. Depends on your version (please, always post your ASE version with questions) and what optional features you have installed (eg. MDA tables). Each optional feature has a performance penalty, so use the core features first:
    Hi Derek,

    Thanks for info. I am using ASE 15.0.2 & I am not sure whether optional features are installed or not.

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    In ASE 15.0.2, MDA tables are "not an option", they are installed and ready to go. But they are quite a big task to set up to be useful:
    - the need a capture and store mechanism (your code plus a permanent set of your tables, located somewhere)
    - MDA uses Component Integration Services (virtual tables with loopback); you need to understand that and administer sybsystemdb
    - according to TechWave presentations by the Sybase proponents, they are 5 to 15% additional overhead for the monitoring functions and 15 to 22% for the diagnostic functions (your requirement is diagnostic)
    - depending on what tables you do use, turn on each metric via sp_configure
    Basically, unless you do that properly, and with experience gained, make changes; collection interval; etc, they are difficult. And not useful until you do. Certainly worth a play, though.

    I have a full capture-and store mechanism, for customers who really want it, but I stay away from them as much as I can.

    There are 3rd party monitoring/diagnostic tools which have 0 to 3% overhead, but they are not free. For rich, concise monitoring info sp_sysmon is still streets ahead of MDA, it has 0.1% overhead.

    Hence, I recommended starting with dbcc sqltext first. If that isn't enough, sure, go to 3rd party monitoring tools or MDA, with the caveats.
    Last edited by Derek Asirvadem; 09-02-09 at 10:41.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  5. #5
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Duplicate post. Deleted.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The MDA table to look at is monProcessSQLText
    Also some history in monSysSQLText but cleared on every select

Posting Permissions

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