November 18, 2013 at 3:00 am
I have just copied following query from 'Scripts' from our site only. when i execute it i get Error : Incorrect syntax near '.'
i dont know anything about this query. I just wanted to see my top 5 executing queries.
SELECT TOP 5 SQLtxt.text AS 'SQL',
qstats.execution_count
AS 'Execution Count',
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time,
GetDate()) AS 'Logical Writes Per Second',
qstats.total_physical_reads AS [Total Physical Reads],
qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime],
qstats.total_worker_time AS 'Total Worker Time',
DATEDIFF(hour, qstats.creation_time,
GetDate()) AS 'TimeInCache in Hours'
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt
WHERE SQLtxt.dbid = db_id()
ORDER BY qstats.execution_count DESC
I have sql 2008 R2.
pls help
November 18, 2013 at 3:21 am
Nothing wrong with that query. Let me guess, you're running it from a database with compatibility level set to 80. If that's the case, change the database context to master and change the filter on databaseID to a fixed value, that of the database you're interested in.
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
November 18, 2013 at 3:31 am
done.
thanks man, you are always a life saver....:-)
November 18, 2013 at 3:40 am
KcV (11/18/2013)
done.thanks man, you are always a life saver....:-)
Although the avatar is a bit misleading, Gail is a woman
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy