SOLVED

Activity monitor - sql queryies behind

%3CLINGO-SUB%20id%3D%22lingo-sub-2687840%22%20slang%3D%22en-US%22%3EActivity%20monitor%20-%20sql%20queryies%20behind%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2687840%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20need%20to%20find%20the%20sql%20queries%20behind%20Activity%20monitor%20in%20SSMS%26nbsp%3B%20so%20I%20can%20edit%20a%20bit.%20Some%20ideas%20how%20to%20find%20these%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2691009%22%20slang%3D%22en-US%22%3ERe%3A%20Activity%20monitor%20-%20sql%20queryies%20behind%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2691009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1138385%22%20target%3D%22_blank%22%3E%40acim9090%3C%2FA%3E%26nbsp%3B%2C%20that's%20easy%2C%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ftools%2Fsql-server-profiler%2Fsql-server-profiler%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20Server%20Profiler%3C%2FA%3E%20to%20trace%20all%20SQL%20statements%20send%20from%20SSMS%20to%20SQL%20Server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20not%20all%20base%20on%20query%20result%2C%20e.g.%20CPU%2FMEM%20diagram%20uses%20WMI%20queries%20to%20get%20the%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOlaf%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

4 Replies
best response confirmed by acim9090 (New Contributor)
Solution

@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

Thank you @olafhelper . This is actually good idea, will try. 

@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.

@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.