Query listed under "Monitoring Query Performance" in SQL Azure doc has syntax errors

Question

The following query in the Monitoring SQL Azure Using Dynamic Management Views has syntax errors:

-- Find top 5queries
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    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) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO

Here is the error:

 I get this error even in the SQL Azure Management Portal.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'query_stats'.

Msg 156, Level 15, State 1, Line 12

Incorrect syntax near the keyword 'as'.

---

Katy Lynn McCullough-Leonard

Answer 1

Can you try running same query in SQL Management studio?. I tried running same query in on SQL Azure Management portal as well as using SQL Management studio. I don't see any issues with this specific query.

Clicking on "Copy" hyper link in http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx and paste your clipboard contents in SQL Management studio.

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

 

Answer 2

I've already tried the same query from both SQL Server Management Studio 2008 and SQL Server Management Studio S008 R2.  I get the same error on both (and in the management portal).

---

Katy Lynn McCullough-Leonard

Answer 3

Hello. 

As i said in other topic, this can be an issue with datacenter. I tried to launch your query against my SQL Azure DB in East Asia datacenter and it's worked perfectly. Can you change a geo-location?