June 9, 2008 at 4:47 am
Hi,
Can anybody tell how can we see long running queries with tsql i have query for sql 2005 but it doesnt work with 2000.
so plz help me
Regards
Jagpal Singh
June 9, 2008 at 2:42 pm
What's wrong with Profiler?
_____________
Code for TallyGenerator
June 9, 2008 at 3:12 pm
Could you post the query that doesn't work on SQL 2000?
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 9, 2008 at 3:16 pm
Hi,
There is query which works with sql 2005 .
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg_IO]
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset
end - qs.statement_start_offset)/2) as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle,
qs.creation_time
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg_IO] DESC;
June 10, 2008 at 9:04 am
There's no 2000 equivalent for that. Your query checks the plan cache and retrieves the reads and writes for queries that have run in the past whos plans are still in cache. There's no way to get that info in SQL 2000.
You can get info for currently running queries using sysprocesses, but to get data like you want, you're probably going to have to use profiler or a server-side trace
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply