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

Identfying long running queries in SQL 2000. Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 12:17 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 11:31 PM
Points: 61, Visits: 191
Dear all,

We are using SQL Server 2005 for production and the database compatability level is kept for 80 not 90. I have a qery for
finding the long running queries which supports on SQL 2005 not 2000 database. Its throwing error when i run the query
to identify the long running queries.

Query :

select top 50
qs.total_worker_time / execution_count as avg_worker_time,
substring(st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text,
*
from
sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by
avg_worker_time desc

ERROR :
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'apply'.


Can anyone please have a look into this to work fine for 2000 compatibility databases.

Or else anyone please provide me the query for identifying the long running queries.


Thanks,
CH&HU.
Post #683071
Posted Wednesday, March 25, 2009 12:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Try ...
SELECT TOP 50 qs.total_worker_time / execution_count as avg_worker_time,
(SELECT SUBSTRING(st.text,
(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN
datalength(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_sql_text(qs.sql_handle) AS ST) AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
ORDER BY avg_worker_time DESC

Thanks.

EDIT: Forgot to format the query so it was hard to read ... Sorry.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing.


How to ask for help .. Read Best Practices here.
Post #683077
Posted Wednesday, March 25, 2009 1:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 11:31 PM
Points: 61, Visits: 191
--------------------------------------------------


Error : Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '.'.





Thanks,
CH&HU
Post #683104
Posted Wednesday, March 25, 2009 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
If the database is running in 80 compatibility mode, you won't be able to run 90 type queries, which include using the CROSS APPLY query.

----------------------------------------------------
"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 #683237
Posted Wednesday, March 25, 2009 10:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Urg sorry; that wouldn't work because the function returns a table which was not supported in SQL 2000.

You can run that query in a database that is 90 and it will return the SQL Statements to you. Please note this is instance wide and not database based.

When I run it on masters database I don't just get master database statements I get statements from all server statements.

Thanks.

Mohit.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing.


How to ask for help .. Read Best Practices here.
Post #683440
Posted Wednesday, March 25, 2009 10:26 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:30 PM
Points: 119, Visits: 346
dbcc opentran will tell for oldest open transaction.


look for last_batch and status

then runn dbcc inputbuffer(spid)
to see whats it running.





Umar Iqbal
Post #683453
Posted Tuesday, August 30, 2011 11:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 12, 2013 6:07 AM
Points: 3, Visits: 45
If your problem is a database with compatibility level 80 on an SQL Server 2005, just run the query from some other database on that server which has compatibility level 90, e.g. tempdb.
Post #1167893
Posted Friday, May 4, 2012 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:30 PM
Points: 1, Visits: 20
I get the following error:

Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '.'.

Have been looking at it a while, but not sure how to fix.

Please advise.
Thank you.
Post #1295231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse