?
Solved

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

Posted on 2016-10-15
5
Medium Priority
?
100 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 38

Accepted Solution

by:
Pawan Kumar earned 1000 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 1000 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 38

Expert Comment

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

589 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