SQL Most costly queries

Question

SQL Most costly queries

teresita.castro

SSC Enthusiast

Points: 196
More actions
October 4, 2010 at 11:00 am

#239267

Hi,
I am using SQL server 2005.
I read an article that mentioned a system table that displayed de most costly queries of a database.
But I lost the link.
Does any one know how to obtain that information o knows the link to this article, thanks a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

terisita i've usually used a server side trace to capture all the DML queries, then order the results on some of the columns in the trace to see whathas high values in the following columns:

Duration

Reads

Writes

CPU

RowCounts;

i use that as a starting point to see what queries can be tweaked to decrease the load, or identify places where a developer is doing a SELECT * FROM MillionRowTable and then limiting the results locally.

I also have this saved in my snippets to find "slow" queries with a long Duration from the DMV's :

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 3

You might be looking for the DMO sys.dm_exec_query_stats. It contains an aggregation of the performance of the queries currently in cache. You can also look at sys.dm_exec_proc_stats in 2008.

"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

teresita.castro SSC Enthusiast Points: 196 More actions · Answer 4

teresita.castro

SSC Enthusiast

Points: 196

October 4, 2010 at 3:39 pm

#1231335

Thank you for your help!!!