Forum Discussion

acim9090's avatar
acim9090
Copper Contributor
Aug 26, 2021

Activity monitor - sql queryies behind

Hello

I need to find the sql queries behind Activity monitor in SSMS  so I can edit a bit. Some ideas how to find these?

  • acim9090 , that's easy, use SQL Server Profiler to trace all SQL statements send from SSMS to SQL Server.

     

    But not all base on query result, e.g. CPU/MEM diagram uses WMI queries to get the values.

     

    Olaf

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    acim9090 , that's easy, use SQL Server Profiler to trace all SQL statements send from SSMS to SQL Server.

     

    But not all base on query result, e.g. CPU/MEM diagram uses WMI queries to get the values.

     

    Olaf

    • acim9090's avatar
      acim9090
      Copper Contributor
      olafhelper, Any idea why I can't run the store proc?

      USE tempdb;
      GO
      EXEC #am_generate_waitstats
      go

      Error:
      Could not find stored procedure '#am_generate_waitstats'.

      Context in ssms is tempdb, where I found this store proc.
      • olafhelper's avatar
        olafhelper
        Bronze Contributor

        acim9090 

        Every object (table, SP) with a name starting with # is a local temporary object and can only be accessed with in the session where the temp object was created.

         

        Example: In SSMS open 2 query windows, in one you create a temp table with

        create table #test (id int null);
        insert into #test (id) values (1);

        And now try to query the temp table in the second query window, like

         

        select * from #test;

        You get the same error message.

         

Resources