• State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 2 replies
  • Subscribers 59 subscribers
  • Views 4188 views
  • Users 0 members are here
Options
Related

Long Running Deadlock/SQL Query

edineli70
over 3 years ago

I see Foglight has a rule by default named Long Running Job (SQL Server)

I would like to know if there is a Rule/Alarm to check any long running deadlock

or long running SQL query. If so, how can I enable them ?

If not so, How  I can create them?

The idea of long running deadlock/SQL query is to find out the existence of a deadlock/sql query  that is taking too much time to process

Thanks in Advance for your kind reply

Parents
  • +1 over 3 years ago

    Hello edineli70,

    there is a rule called Long Lock Running which is alarming if there is a long running lock (>90 secs).
    You can find deadlocks/locks on the DB dashboard under Activity -> Blocking (Current) / Activity -> Deadlocks. Here you will see the statement which is causing the lock, lock duration etc.

    Here an example from my test lab:

    If you are using PI, you will also have the blocking history available under Instance View -> Locked Objects.
    Here a screenshot from PI:


    There is currently no rule for a long running query. It is difficult to define what a long running query is. Some queries run several minutes and it is ok because they do nightly batch jobs or similar and other queries run for 10 seconds and that is not ok.
    There is an enhancement request to get such a rule - look at here:
    support.quest.com/.../alarm-for-a-long-running-sql-server-query

    Please check if the long lock running rule is matching your requirement.

    Kind Regards
    Nicola

Reply
  • +1 over 3 years ago

    Hello edineli70,

    there is a rule called Long Lock Running which is alarming if there is a long running lock (>90 secs).
    You can find deadlocks/locks on the DB dashboard under Activity -> Blocking (Current) / Activity -> Deadlocks. Here you will see the statement which is causing the lock, lock duration etc.

    Here an example from my test lab:

    If you are using PI, you will also have the blocking history available under Instance View -> Locked Objects.
    Here a screenshot from PI:


    There is currently no rule for a long running query. It is difficult to define what a long running query is. Some queries run several minutes and it is ok because they do nightly batch jobs or similar and other queries run for 10 seconds and that is not ok.
    There is an enhancement request to get such a rule - look at here:
    support.quest.com/.../alarm-for-a-long-running-sql-server-query

    Please check if the long lock running rule is matching your requirement.

    Kind Regards
    Nicola

Children