September 6, 2012 at 12:33 pm
Hello all. It's been a while.
So, I inherited this query which I have used to great effect to get the most offensive Queries out of a database. I make no claim to writing this or coming up with what it does (i.e. it is not of my own creation). However, I can't find anything like it out on the Web or in these forums.
I use this pretty often to figure out where problems might be occurring due to bad Stats and the like. Recently when running this on a 2008 or 2008 R2 Database Server I get the error:
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '.'.
Mostly, I'm just looking for a reason why I'm getting this Incorrect Syntax message when I have not modified anything from the thousands of times I have executed this before. I've tried to figure it out, but thought that it could not hurt to a) share and b) get the talented folks out here to give it a go!
Here is the statement as I normally run it (I will uncomment certain sections depending on what I am looking for):
--Queries by "cost"
select top 30
so.name as ObjectName
--Sql Statement
/* , REPLACE(REPLACE(REPLACE(
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)
, CHAR(10), ' '), CHAR(13), ' '), ' ', ' ') as statement_text
*/
, creation_time as Compile, getdate() as CollectionTime, plan_generation_num as [Plan#], execution_count
--Worker Time
, total_worker_time as Tot_CPU
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_worker_time) / execution_count) as Avg_CPU
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_worker_time) / sum(total_worker_time) over() * 100) as [%_CPU]
--I/O
--, total_physical_reads
--, total_logical_reads
--, total_logical_writes
, total_physical_reads + total_logical_reads + total_logical_writes as Tot_IO
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,6), (total_physical_reads + total_logical_reads + total_logical_writes)) / execution_count) as Avg_IO
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), (total_physical_reads + total_logical_reads + total_logical_writes)) / sum((total_physical_reads + total_logical_reads + total_logical_writes)) over() * 100) as [%_IO]
--Elapsed Time
, total_elapsed_time as Tot_Elapsed
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_elapsed_time) / execution_count) as Avg_Elapsed
--, (select query_plan from sys.dm_exec_query_plan(qs.plan_handle)) as QueryPlan
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
left join sys.objects as so on st.objectid = so.object_id
where (st.dbid = db_id() or st.dbid is null)
order by Tot_CPU desc
-- order by Avg_CPU desc
-- order by Tot_IO desc
-- order by Avg_IO desc
-- order by Execution_Count desc
Regards, Irish
September 6, 2012 at 12:35 pm
Jeffrey Irish (9/6/2012)
Hello all. It's been a while.So, I inherited this query which I have used to great effect to get the most offensive Queries out of a database. I make no claim to writing this or coming up with what it does (i.e. it is not of my own creation). However, I can't find anything like it out on the Web or in these forums.
I use this pretty often to figure out where problems might be occurring due to bad Stats and the like. Recently when running this on a 2008 or 2008 R2 Database Server I get the error:
Msg 102, Level 15, State 1, Line 30Incorrect syntax near '.'.
Mostly, I'm just looking for a reason why I'm getting this Incorrect Syntax message when I have not modified anything from the thousands of times I have executed this before. I've tried to figure it out, but thought that it could not hurt to a) share and b) get the talented folks out here to give it a go!
Here is the statement as I normally run it (I will uncomment certain sections depending on what I am looking for):
--Queries by "cost"select top 30
so.name as ObjectName
--Sql Statement
/* , REPLACE(REPLACE(REPLACE(
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)
, CHAR(10), ' '), CHAR(13), ' '), ' ', ' ') as statement_text
*/
, creation_time as Compile, getdate() as CollectionTime, plan_generation_num as [Plan#], execution_count
--Worker Time
, total_worker_time as Tot_CPU
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_worker_time) / execution_count) as Avg_CPU
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_worker_time) / sum(total_worker_time) over() * 100) as [%_CPU]
--I/O
--, total_physical_reads
--, total_logical_reads
--, total_logical_writes
, total_physical_reads + total_logical_reads + total_logical_writes as Tot_IO
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,6), (total_physical_reads + total_logical_reads + total_logical_writes)) / execution_count) as Avg_IO
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), (total_physical_reads + total_logical_reads + total_logical_writes)) / sum((total_physical_reads + total_logical_reads + total_logical_writes)) over() * 100) as [%_IO]
--Elapsed Time
, total_elapsed_time as Tot_Elapsed
, CONVERT(DECIMAL(18,3), CONVERT(DECIMAL(18,2), total_elapsed_time) / execution_count) as Avg_Elapsed
--, (select query_plan from sys.dm_exec_query_plan(qs.plan_handle)) as QueryPlan
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
left join sys.objects as so on st.objectid = so.object_id
where (st.dbid = db_id() or st.dbid is null)
order by Tot_CPU desc
-- order by Avg_CPU desc
-- order by Tot_IO desc
-- order by Avg_IO desc
-- order by Execution_Count desc
When you get this error, have you double clicked on the error message in the messages tab to see where the error is located?
September 6, 2012 at 12:36 pm
FYI, I just ran this query as is with no problems.
September 6, 2012 at 12:41 pm
Lynn Pettis (9/6/2012)
FYI, I just ran this query as is with no problems.
Lynn,
Thanks for the reply.
Yes, I have. It shows me this line:
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
If I comment that out, I get the same error where the "sys.dm_exec_sql_text(qs.sql_handle)" shows up in line 28.
I'm sure it is something simple that I am missing, but I don't see it.
I did see in some other posts where similar queries are selecting form DMV's and they had Offline Databases. I don't think that is it since I'm only looking for statements from the Database that I currently have selected (line 33)
where (st.dbid = db_id() or st.dbid is null)
Regards, Irish
September 6, 2012 at 12:43 pm
Can you check the compatibility level of the database you're running the query on?
September 6, 2012 at 12:52 pm
Luis Cazares (9/6/2012)
Can you check the compatibility level of the database you're running the query on?
See? It's the simple things! :pinch:
Compatibility Level was set to 80 (SQL 2000). Changed it to 90 (SQL 2005) and BAM!
Thank you, Thank you, Thank you!!!
Regards, Irish
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply