June 7, 2011 at 5:16 am
hi
how can i know which stored procedure causing more issue in performance problem.
June 7, 2011 at 5:36 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2011 at 5:39 am
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
June 7, 2011 at 5:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2011 at 5:52 am
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
June 7, 2011 at 6:33 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2011 at 6:43 am
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
June 8, 2011 at 4:18 am
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.
June 8, 2011 at 5:18 am
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 :w00t:.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply