bug: long running query

mschiermschier Posts: 14 New member
edited November 14, 2016 7:28AM in SQL Monitor Previous Versions
Hello,

by long running queries, we will only get in SQL process fragment: Command Type: UPDATE or AWAITING
This is not helpful :?

Comments

  • Hi mschier,

    This is a known issue with internal reference SRP-10404 currently being investigated by the developers. What version of SQL Monitor are you currently using? Also, what is the query duration for the long running queries that are showing this behaviour?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • mschiermschier Posts: 14 New member
    Hi Alex,

    thanks for quick reply.
    We use version 5.1.0.3064.
    The query duration is between 100 and 800 seconds.

    Regards
  • Hi mschier,

    The next time you see this happening and the alert is still ACTIVE, could you run this query on the SQL Server with the long running query (the remote server):

    SELECT *
    FROM sys.sysprocesses as procc
    CROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(64), procc.sql_handle)) AS query 
    WHERE spid = <process id shown on the alert>
    

    And then send us this result?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • Hi Michael,

    Thank you for the query results! The developers have said they know what's going on but just need a little bit more information.

    Could you run this amended query please when you next see this happening and the alert is still ACTIVE?

    SELECT *
    FROM sys.sysprocesses as procc
    CROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(64), procc.sql_handle)) AS query 
    LEFT JOIN sys.dm_exec_requests er ON procc.spid = er.session_id AND procc.request_id = er.request_id
    WHERE procc.spid = <process id>
    

    And then send the results in again.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • HI currently evaluating SQL Monitor 5.2.1
    And I have kind of the same problem with the long running queries alert
    Incomplete
    SQL fragment
    Command Type: EXECUTE etc...

    But also a lot of false alert even though the metric is set to alert on queries taking more than 3 minutes (180 secs) and excluded BACKUP & RESTORE

    I am getting a lot of alerts with query duration of only a couple seconds, which makes it a real pain to find the real alerts (getting over 200 alerts overnight out of which maybe only 4 or 5 really took more than 180 secs)
    here's a couple exemples:

    Process ID: 78
    Process name: SQLCMD
    Database: master
    Host: *******
    User: *********
    Process login time: 1 Jun 2016 7:25 AM
    Query start time: 1 Jun 2016 7:25 AM
    Query duration: 0 sec
    SQL process fragment
    Command Type: EXECUTE


    Process ID: 102
    Process name:
    Database: VCDB
    Host:**********
    User: ****************
    Process login time: 1 Jun 2016 3:02 AM
    Query start time: 1 Jun 2016 6:32 AM
    Query duration: 0 sec
    SQL process fragment
    Command Type: UPDATE


    Thanks
    Robert
  • Hi Robert,

    Thank you, I've added your further information to the issue SRP-10404 mentioned above. I will update here when we have any progress!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • Hi Michael (et al),

    The issue with the Long-running query process fragment only showing the command type / keyword has been resolved in v5.2.5 (SRP-10404 - see release notes).

    Please update to the latest version and let us know if there are still any issues.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • mschiermschier Posts: 14 New member
    Hi Alex,

    the issue still exist.

    Best regards
    Michael
  • Hi Michael,

    Can you please send in the log files again having updated to the latest version? Please also include a screenshot of the entire alert screen showing the keyword only being shown.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • The issue seems to be due to a data mismatch between the current and historic Query Performance Stats held in 2 tables by red Gate in the TempDB database called

    dbo.redgate_sqlmonitor_topqueries_a.....
    dbo.redgate_sqlmonitor_topqueries_b.....

    Executing command "DBCC FREEPROCCACHE" to clear the current SQL Query Plan Cache / Clear the Current Execution Stats and dropping these 2 tables will fix the issue.

    Hope that this helps

    Thanks :D

    Steve Dixon
  • Hi Steve,

    I've just had a look at it with one of the developers and it doesn't seem like this action would help with the SQL Command text now showing up in the Long-running query (LRQ) alert (the reason for the initial post). The LRQ gets it's command text from the SQL process sampler while the tables you mentioned are for the top queries collections.

    What circumstance or location were you getting only the command type keywords that performing the above resolved the issue for you? Was this also in the LRQ alerts or a different location?

    The developers do believe they have a fix for this appearing in the LRQ alerts and possibly the blocking process alerts as well - there was a circumstance where the collected text could be made null (and so not shown) and this will now always pass the command text from the process on (trimmed of spaces before and after). This will be included in the next release which should be out next week.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • Hi all,

    The 6.0.10 release appears to have fixed this issue, so please update to that or the latest version (6.0.12 at the time of writing). Some people are still experiencing an issue with the duration showing 0 and that is a separate issue to the keywords only being displayed, which is what this addresses.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software
  • mschiermschier Posts: 14 New member
    We have the same issue again with version 7.0.14.8072.
Sign In or Register to comment.