none
Monitoring queries by Activity Monitor

    Question

  • Hi,

    the Activity Monitor allows me to view the recent expensive queries. But, I'd like to look inside the calling stored procedure.

    Thanks

    Monday, October 21, 2013 10:52 AM

Answers

All replies

  • Hello,

    Run the SQL Profiler, then you can see all queries executed by SSMS / Activity Monitor.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 21, 2013 11:01 AM
    Moderator
  • Hi Olaf, thanks for your reply. So Activity Monitor doesn't allow to understand which stored has launched an expensive query.

    I need to avoid the use of the profiler in the prod environment, and I hope that the Activity Monitor was enough.

    Thanks

    Monday, October 21, 2013 11:36 AM
  • I need to avoid the use of the profiler in the prod environment,

    The use a test enviroment / a local installed Express Edition; you just want to get the query. You can also see the queries in Activity Monitor => Processes itself, but only the last executed. Looks like:

    SET NOCOUNT ON;
    
    DECLARE @previous_collection_time datetime;
    DECLARE @previous_request_count bigint;
    DECLARE @current_collection_time datetime;
    DECLARE @current_request_count bigint;
    DECLARE @batch_requests_per_sec bigint;
    DECLARE @interval_sec bigint;
    
    -- Get the previous snapshot's time and batch request count
    SELECT TOP 1 @previous_collection_time = collection_time, @previous_request_count = request_count 
    FROM #am_request_count
    ORDER BY collection_time DESC;
    
    -- Get the current total time and batch request count
    SET @current_collection_time = GETDATE();
    SELECT @current_request_count = cntr_value 
    FROM sys.sysperfinfo
    WHERE counter_name = 'Batch Requests/sec' COLLATE Latin1_General_BIN;
    
    SET @interval_sec = 
        -- Avoid divide-by-zero
        CASE
            WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) = 0 THEN 1
            ELSE DATEDIFF (second, @previous_collection_time, @current_collection_time)
        END;
    
    -- Calc the Batch Requests/sec rate for the just-completed time interval. 
    SET @batch_requests_per_sec = (@current_request_count - @previous_request_count) / @interval_sec;
    
    -- Save off current batch count
    INSERT INTO #am_request_count (collection_time, request_count) 
    VALUES (@current_collection_time, @current_request_count);
    
    -- Return the batch requests/sec rate for the just-completed time interval. 
    SELECT ISNULL (@batch_requests_per_sec, 0) AS batch_requests_per_sec;
    
    -- Get rid of all but the most recent snapshot's data
    DELETE FROM #am_request_count WHERE collection_time < @current_collection_time;
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 21, 2013 12:09 PM
    Moderator
  • You can use below query

    SELECT DISTINCT TOP 10
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    --ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn--,
    --ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
    ORDER BY
    s.max_elapsed_time DESC
    GO

    • Proposed as answer by Durval Ramos Monday, October 21, 2013 3:45 PM
    Monday, October 21, 2013 1:28 PM
  • Yes, you can do that.

    Under Events, go to Stored Procedures, and turn on SP:StmtCompleted.

    And be sure to put a filter on Duration, usually 1000 or 2000 ms (1 or 2 seconds) is a good level, otherwise you can get flooded with useless information of fast-running stuff, and it will impact the server.

    Josh

    Monday, October 21, 2013 2:43 PM
  • To avoid using the profiler in production, use extended events if you are on 2008R2 or 2012. They have a tiny performance overhead compared to the profiler.

    Using extended events


    www.minidba.com | Sql Server & Azure Db Performance Tuning & Monitoring Software | Free Lite Edition

    Tuesday, October 22, 2013 2:47 AM
  • ... So Activity Monitor doesn't allow to understand which stored has launched an expensive query.

    I need to avoid the use of the profiler in the prod environment, and I hope that the Activity Monitor was enough.

    Actually in Activity Monitor you do have the "Recent Expensive Queries" - and those you can right-click to get the query plan and also the T-SQL Code.

    Avoiding Profiler, which is also deprecated since SQL 2012, is indeed a very good idea.

    You may also be able to use Extended Events instead, dependig on your version like Alister McPherson also noted.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, October 22, 2013 11:30 AM