Finding slow queries with their stored procedure name

1

hi team,

I need your suggestion to find slow running queries in sql server and due to there are multiple stored proc in my application and they all contains same kind of queries so how can i get to know stored proc name for that slow queries.

Please help.

2

SQL Profiler can tell you that - e.g. just record SProc calls that are "slow". However, if you have SProc-A that execs SProc-B and SProc-C then SQL Profiler will only tell you that SProc-A was slow ... not which of SProc-B or SProc-C was the cause.

We have an EXEC at the start and end of EVERY Sproc that we write which logs the Start / End time of every SProc execution (and also the parameters provided to the SProc, and the User Session ID, so we can use that to figure out exactly what the user was trying to do at the time). We have reported that alert us, in real time, to any Sproc that starts to perform "slowly", compared to its normal run-time.

3

You can find the highest IO and CPU queries -- which will almost always be the long-running queries -- like so:

Right-click on the instance/"server" name in SSMS;
Select "Reports";
Select "Standard Reports";
Then select each report under:
Select "Performance - Top Queries by ..."

You can then search the SQL procs for the queries that cost the most. Here's one crude method; other tools may have better methods:

USE db_name
SELECT OBJECT_NAME(object_id), definition
FROM sys.sql_modules
WHERE definition LIKE '%part_of_query_shown_above%'

4

Take a look at here it might be helpful for you: http://www.sqlmvp.org/are-you-experiencing-slow-running-queries-in-sql-server/