not working with different databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

not working with different databases

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-26 : 02:04:07

Dear Experts,
i've read a good article by greg larsen in sql server performance.com site.

the article name is :Measure TSQL Statement Performance

here is the query

SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE 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) st
ORDER BY total_elapsed_time / execution_count DESC;

with this query, i got the worest running queries. in the same server, i've tried with different databases, but it is giving error
error is :
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '.'

what is the problem? i'm using this version.
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

please help me in this regard

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 02:13:20

Not sure if this is cause of error, but can you try after putting an AS in cross apply step too?

SELECT creation_time 
,last_execution_time 
,total_physical_reads
,total_logical_reads 
,total_logical_writes
, execution_count 
, total_worker_time
, total_elapsed_time 
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE 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 total_elapsed_time / execution_count DESC;

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-26 : 03:19:50

No, that is not....

the query is working for some databases. but for some, it is not working. copy the query and run that in your different databases to check.

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 04:46:02

Are all the databases of SQL 2005 version? You dont have CROSS APPLY in sql 2000

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-26 : 04:53:18

Make sure that all databases have compatibility level of 90.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-26 : 07:13:09

Yea...
you are right Harsh.....
that is depending on compatibility level of 90

not working for sql server2000(80)

thanks a lot

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources