Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-15
5
Medium Priority
?
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 30

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 30

Expert Comment

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

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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