Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    12

    Unanswered: top 5 queries by hostname query

    I have been told there is a way thru a query of the system tables for the Top 5 Queries or Stored Procedures by hostname in terms of CPU Utilization. I have been unable to develop this query. Can anyone provide me some further guidance?

    Sincerely,

    Patrick Quinn

    Carlson Wagonlit Travel Sybase DBA

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select the info from sysprocesses and save it
    sleep for a few seconds (waitfor '00:00:05')
    select again from sysprocesses and get the differance from the saved value

    See if procs from below will help you
    EXTENDED STORED PROCEDURES

  3. #3
    Join Date
    Mar 2008
    Posts
    96
    If you are using 15.0 mon tables are alredy there but if you are using earlier version then you have to install the monitoring tables using installmon scripts before using the following query

    select s.SPID, s.CpuTime, t.LineNumber, t.SQLText
    from master..monProcessStatement s, master..monProcessSQLText t
    where s.SPID = t.SPID
    order by s.CpuTime DESC

  4. #4
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    If you've got the MDA (monitoring) tables setup then the following SP will list all active spids ordered by running time, followed by CPUTime - naturally you can change the order by to suit your needs.

    sp__mon_sql2

    Change this line:
    order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
    to
    order by hostname desc, CpuTime Desc

    If the hostname bears some significance in locating a problem then running
    sp__mon_sql 1
    will retrieve the OS process ID, tho' this may only work for processes running locally.

    Regards

    Bob
    Last edited by harq; 06-03-09 at 17:23.
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

Posting Permissions

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