Post reply

joining 'sys.dm_exec_query_stats' with other system tables.

  • June 26, 2014 at 6:06 am

    #287165

    Hi Experts,

    How can we relate the DMV 'sys.dm_exec_query_stats' to other sys tables like sys.objects or sys.databases so that I can atleast find the object id or the database id of the running queries, else I will have to search each and every database on the instance

    I am using this DMV to check the most expensive query.

    Regards,

    Ashif Shaikh:-)

  • June 26, 2014 at 6:51 am

    #1725042

    cross apply that DMV to sys.dm_exec_sql_text, that DMV has the objectID and database (for procedures), you can join that to sys.objects/sys.procedures.

    Ad-hoc queries have neither an object ID or database ID associated with them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 26, 2014 at 7:02 am

    #1725048

    Thanks Got it,

    However for some queries the object id and the database id both are showing as NULL???

  • June 26, 2014 at 7:08 am

    #1725050

    GilaMonster (6/26/2014)

    Ad-hoc queries have neither an object ID or database ID associated with them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 26, 2014 at 7:25 am

    #1725062

    Thanks a LOT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply