Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    41

    Unanswered: How to select and group by below the minute precision

    Hi,

    I am able to select the number of events from the audit table with the following query:

    select Rtrim(Ltrim(str(datepart(month,a.eventtime))))+'/'+
    Rtrim(Ltrim(str(datepart(day,a.eventtime))))+'/'+
    Rtrim(Ltrim(str(datepart(year,a.eventtime))))+' '+
    Rtrim(Ltrim(str(datepart(hour,a.eventtime))))+':'+
    Rtrim(Ltrim(str(datepart(minute,a.eventtime)))),st r(count(a.eventtime))
    from sybsecurity..sysaudits_02 a
    where a.eventtime > '2009-06-03 8:00:00' and a.eventtime <='2009-06-03 9:59:59'
    group by datepart(month,a.eventtime),
    datepart(day,a.eventtime),
    datepart(year,a.eventtime),
    datepart(hour,a.eventtime),
    datepart(minute,a.eventtime)

    I would like to sharpen the resolution and go below the minute, let say 10 seconds or 15 seconds resolution. How can I do that ?

    Thanks ahead,

    János

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    As you know a minute has 60 seconds so, you cant use this SQL as is. If you are expecting a group by 10 seconds, then theres no such thing.

    So, if you can be more clear on your requirements (not the one below !!) we can provide a bit more help. Forget the query and tell us the requirements.


    Quote Originally Posted by János Löbb
    Hi,

    I am able to select the number of events from the audit table with the following query:

    select Rtrim(Ltrim(str(datepart(month,a.eventtime))))+'/'+
    Rtrim(Ltrim(str(datepart(day,a.eventtime))))+'/'+
    Rtrim(Ltrim(str(datepart(year,a.eventtime))))+' '+
    Rtrim(Ltrim(str(datepart(hour,a.eventtime))))+':'+
    Rtrim(Ltrim(str(datepart(minute,a.eventtime)))),st r(count(a.eventtime))
    from sybsecurity..sysaudits_02 a
    where a.eventtime > '2009-06-03 8:00:00' and a.eventtime <='2009-06-03 9:59:59'
    group by datepart(month,a.eventtime),
    datepart(day,a.eventtime),
    datepart(year,a.eventtime),
    datepart(hour,a.eventtime),
    datepart(minute,a.eventtime)

    I would like to sharpen the resolution and go below the minute, let say 10 seconds or 15 seconds resolution. How can I do that ?

    Thanks ahead,

    János

  3. #3
    Join Date
    Jul 2003
    Posts
    41
    Hi trvishi,

    My goal is to count audit events with sub-minutes intervals. With that I can predict when the user sessions start to develop problems. In a normal time I get average 5000 audit events a minute. If this number drops below 2000 per minute, I know surely that something is wrong and if I run sp_lock I see sessions already on lock_sleep state. That is usually to late to do anything useful, other than kill the offending sessions. So what I would like to do is to look into the audit table and select the number of events not in the last minute, but rather in the last 15 seconds. As You know the audit tables have no index, so if I can shrink the time interval it executes quicker If that number is below 600-800, then alert me, so I can turn my attention to the problem more pro-actively.

    Well, I can do it for every second, - because there is datepart element for it -, but that is too aggressive, and also spikes in traffic can cause false alarms. However looking into in ever 15 seconds or ever 12 second looks to me the right amount of time. Ultimately I will tie it to a cron job and if the audit events are below a preset threshold, then I will collect sp_who and sp_lock info immediately, because if I wait for the users to report the issue then usually I cannot run any of these procedures.

    The ultimate goal is to find those database sessions and their queries which are the culprits causing lock_sleeps either by bad design or by hugging resources, or by going bad. Now we have cascading lock sleeps almost every week and it is painful. The application software is a vendor supplied software, and we have to live with it

    Thanks ahead,

    János

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by J&#225;nos L&#246;bb
    Hi trvishi,

    My goal is to count audit events with sub-minutes intervals. With that I can predict when the user sessions start to develop problems.
    The ultimate goal is to find those database sessions and their queries which are the culprits causing lock_sleeps either by bad design or by hugging resources, or by going bad. Now we have cascading lock sleeps almost every week and it is painful. The application software is a vendor supplied software, and we have to live with it

    Thanks ahead,

    J&#225;nos
    Im sorry, but this is the lamest idea I have ever seen to solve performance issues in my life. All I can say is drop this idea totally as you are not going to go anywhere.

    Having said this I dont want to leave you high and dry.

    Your better options could be.

    1. Installing MDA tables
    2. Checking sysmon output
    3. Putting some kind of sniffer
    4. Use Ribo
    5. Use dbcc traceons to look at the SQLs coming thro
    6. Have inhouse monitoring scripts

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Like trvishi said, you are going about it in the wrong way.
    However if you insist on shooting yourself in the foot here are some bullets
    Code:
    select dateadd(ss,datediff(ss,'20090101',eventtime)/10*10,'20090101'),count(*)
    from sybsecurity..sysaudits_02
    where eventtime>=dateadd(mi,datediff(mi,'20090101',getdate())-1,'20090101')
    group by dateadd(ss,datediff(ss,'20090101',eventtime)/10*10,'20090101')
    Last edited by pdreyer; 06-04-09 at 04:36.

  6. #6
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    > The ultimate goal is to find those database sessions and their queries which
    > are the culprits causing lock_sleeps

    If this is the ultimate goal, then as has previously been said, there are other, easier ways to do this. You could simply poll sysprocesses for 'lock sleep' for a start. There are plenty of tools for getting spid activity, locking/blocking spids, time blocked, the sql and query plans. What version of ASE are you using?
    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
  •