Post reply

Capture stored procedure/queries from SQL Profiler

  • August 2, 2013 at 7:32 am

    #281316

    I need to capture all the stored procedures and queries that takes more than 20 seconds to run from SQL Profiler.

    Please guide?

  • August 2, 2013 at 7:48 am

    #1638175

    i have this saved in my snippets as the top 20 slowest performaning queries;

    this might get you started:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT TOP 20

    CAST((qs.total_elapsed_time / 1000000.0) AS DECIMAL(28,2)) as total_elapsed_time,

    CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))

    AS [Total CPU time (s)]

    , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time

    AS DECIMAL(28,2)) AS [% CPU]

    , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /

    qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting], qs.execution_count

    , CAST((qs.total_worker_time) / 1000000.0

    / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]

    , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,

    ((CASE WHEN qs.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]

    , qt.text AS [Parent Query]

    , DB_NAME(qt.dbid) AS DatabaseName

    , qp.query_plan

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    WHERE qs.total_elapsed_time > 0

    ORDER BY [Total CPU time (s)] DESC

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2013 at 8:50 am

    #1638211

    How can i pull the time it Ran?

  • August 2, 2013 at 8:55 am

    #1638214

    There's a last_execution_time column in dm_exec_query_stats.

    John

  • August 2, 2013 at 9:02 am

    #1638220

    Sorry i mean date and time it Ran?

  • August 2, 2013 at 9:39 am

    #1638238

    huum (8/2/2013)

    Sorry i mean date and time it Ran?

    i don't think you can find that, AFAIK all you can find is the last execution time; you cannot see , for example 50 entries, one for each execution.

    just a totals and averages.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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