Using sql profiler to find out which statement that currently hold or slow

Guys,
Our application usually has very long query within the stored procedure and within a procedure, there can be a sometimes multiple  stored procedure inside the procedure, my questions is when there is a slow performance issues and we find out which procedures is slow, how do I find out in this particular procedure which statement syntax or procedure that cause the slowness?
motioneyeAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
I always use Brent Ozar query in case I need to find out slow running queries.

SELECT  st.text,
        qp.query_plan,
        qs.*
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
      OR qs.max_elapsed_time > 300

Open in new window


Also note that there are multiple DMVs available to get these kind of information. After that you can find out the root cause and fix the problem. If you know the query/Sp that needs improvement pls post it on EE so that experts can look and fix it for you.
0
 
motioneyeAuthor Commented:
But
Can the query be use to find out which query within a stored procedures that cause slowness?
0
 
Daniel JonesData Research AnalystCommented:
SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Open in new window


You can also check the built-in usage reports
>Right click a database,
> Reports,
> Standard Reports,
> Object Execution Statistics.

It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This gives a  good idea about what's keeping the server busy.

You can also take the help of this link to identify slow running queries in SQL server: http://www.sqlmvp.org/are-you-experiencing-slow-running-queries-in-sql-server/
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Yes it will give you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can the query be use to find out which query within a stored procedures that cause slowness?
No. It only sees each query as a single and isolated command. If you don't know by the query in which stored procedure it belongs then you'll need to run a trace to capture all SQL commands sequentially and so you can see step by step which stored procedure has been called. If you don't know how to do it, please follow this article that shows you how to do it.

Btw, the trace is better option since it's keep capturing activities until you stop the process.
A single query only gives you a snapshot of that moment so you'll need to run it often to get what you want (keep pressing F5 in SSMS query window).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.