Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find the stored procedure causing performnace problem Expand / Collapse
Author
Message
Posted Tuesday, June 7, 2011 5:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:38 AM
Points: 2,301, Visits: 1,432
hi

how can i know which stored procedure causing more issue in performance problem.
Post #1120887
Posted Tuesday, June 7, 2011 5:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 40,411, Visits: 36,861
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1120898
Posted Tuesday, June 7, 2011 5:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
i can think of two ways:
1. set up a DML trace, and after it's run for a while, look at the high values in Duration, Reads,Writes or CPU as a starting point.
I've got an example DML trace here if you want to look at that trace creation script.

2. Check the DMV views for slow queries; The DMV's have a lot of nice information, but they tend to be an unknown resource for a lot of people.

try this snippet in a specific database:

use msdb
go
if not exists (select * from sys.schemas where name = 'MS_PerfDashboard')
exec('create schema MS_PerfDashboard')
go
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1
drop function MS_PerfDashboard.fn_QueryTextFromHandle
go
CREATE function MS_PerfDashboard.fn_QueryTextFromHandle(@handle varbinary(64), @statement_start_offset int, @statement_end_offset int)
RETURNS @query_text TABLE (database_id smallint, object_id int, encrypted bit, query_text nvarchar(max))
begin
if @handle is not null
begin
declare @start int, @end int
declare @dbid smallint, @objectid int, @encrypted bit
declare @batch nvarchar(max), @query nvarchar(max)
-- statement_end_offset is zero prior to beginning query execution (e.g., compilation)
select
@start = isnull(@statement_start_offset, 0),
@end = case when @statement_end_offset is null or @statement_end_offset = 0 then -1
else @statement_end_offset
end
select @dbid = t.dbid,
@objectid = t.objectid,
@encrypted = t.encrypted,
@batch = t.text
from sys.dm_exec_sql_text(@handle) as t
select @query = case
when @encrypted = cast(1 as bit) then N'encrypted text'
else ltrim(substring(@batch, @start / 2 + 1, ((case when @end = -1 then datalength(@batch)
else @end end) - @start) / 2))
end
-- Found internal queries (e.g., CREATE INDEX) with end offset of original batch that is
-- greater than the length of the internal query and thus returns nothing if we don't do this
if datalength(@query) = 0
begin
select @query = @batch
end
insert into @query_text (database_id, object_id, encrypted, query_text)
values (@dbid, @objectid, @encrypted, @query)
end
return
end
go
GRANT SELECT ON MS_PerfDashboard.fn_QueryTextFromHandle TO public
go
use MASTER
GO
SELECT TOP 20 object_schema_name(qt.object_id, qt.database_id) + N'.' + object_name(qt.object_id, qt.database_id) AS 'SPName', qt.query_text , qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'ExecutionCount',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'CallsPerSecond',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'AgeInCache'
FROM sys.dm_exec_query_stats AS qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
WHERE qt.database_id = db_id()
ORDER BY qs.total_worker_time DESC



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120901
Posted Tuesday, June 7, 2011 5:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 40,411, Visits: 36,861
Just bear in mind that the query stats DMV is dependant on the plan remaining in cache. If the plan is recompiled or aged out, the stats are lost. If a plan is never cached, it won't appear in the query stats DMV at all.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1120904
Posted Tuesday, June 7, 2011 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
thanks for the clarification Gail!
you said "... If a plan is never cached, it won't appear in the query stats DMV at all...", can you clarify?
I thought every query got a query plan that is created and thus was cached, but might not ever be reused by another subsequent sql, due to ad hoc char by char differences and stuff...can you think of an example where a plan doesn't get cached at all?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120905
Posted Tuesday, June 7, 2011 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 14,017, Visits: 28,396
Lowell (6/7/2011)
thanks for the clarification Gail!
you said "... If a plan is never cached, it won't appear in the query stats DMV at all...", can you clarify?
I thought every query got a query plan that is created and thus was cached, but might not ever be reused by another subsequent sql, due to ad hoc char by char differences and stuff...can you think of an example where a plan doesn't get cached at all?


If a query includes the recompile hint it won't be stored in cache.

Another option that people should plan on starting to learn is using extended events to capture query executions in the same way as we used to capture trace events. It's going to be a bigger deal in Denali. May as well start practicing.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1120935
Posted Tuesday, June 7, 2011 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Lowell (6/7/2011)
thanks for the clarification Gail!
you said "... If a plan is never cached, it won't appear in the query stats DMV at all...", can you clarify?
I thought every query got a query plan that is created and thus was cached, but might not ever be reused by another subsequent sql, due to ad hoc char by char differences and stuff...can you think of an example where a plan doesn't get cached at all?


There are a fairly large number of situations in which plans are not cached. Details are at http://technet.microsoft.com/en-us/library/cc966425.aspx


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1120943
Posted Wednesday, June 8, 2011 4:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, Visits: 125
You can also use sql profiler to find out which sp in your application is taking a lot of time.You can fine tine the performance of that Sp by fine tuning the querie sin that sp with help of sql profiler and execution plans.
Post #1121608
Posted Wednesday, June 8, 2011 5:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
srivathsani-296624 (6/8/2011)
You can also use sql profiler to find out which sp in your application is taking a lot of time.You can fine tine the performance of that Sp by fine tuning the querie sin that sp with help of sql profiler and execution plans.


That's exactly what Gail was suggesting... those article are worth their weight in gold... and so much more .
Post #1121636
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse