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