Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Performance Tuning » Identfying long running queries in SQL 2000.
Add to briefcase

Identfying long running queries in SQL 2000. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
CH&HU-1021641
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
Mohit K. Gupta
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: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041
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
CH&HU-1021641
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
Grant Fritchey
Posted Wednesday, March 25, 2009 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #683237
Mohit K. Gupta
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: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041
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
umar iqbal
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: Tuesday, May 21, 2013 12:13 PM
Points: 118, Visits: 274
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
bkriszio
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: Saturday, August 25, 2012 2:10 AM
Points: 3, Visits: 41
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
mvinokur
Posted Friday, May 04, 2012 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 6:33 AM
Points: 1, Visits: 10
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.