Solved

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

Posted on 2016-10-15
5
40 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 16

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
Comment Utility
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
Comment Utility
But
Can the query be use to find out which query within a stored procedures that cause slowness?
0
 
LVL 3

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Yes it will give you.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now