Solved

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

Posted on 2016-10-15
5
59 Views
Last Modified: 2016-10-24
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?
0
Comment
Question by:motioneye
5 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41845099
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
 

Author Comment

by:motioneye
ID: 41846019
But
Can the query be use to find out which query within a stored procedures that cause slowness?
0
 
LVL 4

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
ID: 41846146
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41846224
@Author - Yes it will give you.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41846249
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question