SQL Server 2005 動的管理ビューでエラーが発生する

Question

いつもお世話になっております。

初めて質問をさせていただきます。

DBのパフォーマンス分析のため、”sys.dm_exec_query_stats”動的管理ビューにて、特定クエリの分析を進めたいと考えております。
以下、MSのサイトに動的管理ビューの使い方が記載されており、このサイトの例である。”次の例では、CLR の平均時間に基づく上位 5 つのクエリに関する情報を返します”
で紹介されているクエリを実行したところエラーが出力されて実行することができませんでした。
http://msdn.microsoft.com/ja-jp/library/ms189741(v=SQL.90).aspx

【実行したクエリ】
SELECT TOP 5 creation_time, last_execution_time, total_clr_time,
    total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
    execution_count,
    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_clr_time/execution_count DESC;
GO

【発生したエラー内容】
メッセージ 102、レベル 15、状態 1、行 10
'.' 付近に不適切な構文があります。
※”CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st”に問題があると思われる。

【環境】
Win2003SP2
SQLServer2005SP2 9.00.3042.00

【これまで調査した内容】
・クエリ実行ユーザが、sys.dm_exec_sql_textに適切な権限があることを確認。
・sys.dm_exec_sql_textがmasterに存在することを確認（システム関数/テーブル値関数)
・sys.dm_exec_sql_textを利用しない場合は正常にクエリが実行される。sqlをsql_handle値で表示

現在、調査に行き詰っており、有識者の方々にアドバイスをいただきたく。
大変お手数ですが、アドバイスをいただけると幸いです。

何卒、よろしくお願いいたします。

Answer 1

投稿に貼ってある SQL をコピー＆ペーストして実行しても正常に実行されましたよ。

↓の SQL が実行できるなら、単純ミスを疑った方がよいです。

SELECT sql_handle, st.text FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

･･･ちなみにクライアントは何を使ってますか？私は SSMS を使ってます。

 

---

Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう

Answer 2

M_Lewis様

ご返信ありがとうござます。
お伝えする情報が不足しておりました。

添付したクエリですが、別のサーバ（以下別サーバ）（OS:Win2003SP2 SQLServer2005 9.00.1399.06)で実行したところ正常に
実行されます。
こちらも、SSMSを使ってクエリを実行しております。SSMS:9.00.1399.00

M_Lewis様からいただきましたクエリですが、
問題が発生しているサーバ(以下問題サーバ)では、同様のエラーメッセージが発生してしまいます。（コピペで実行）
別サーバでは、正常に値を取得できました。

問題サーバと別サーバで何が異なっているのか？ということは現在洗い出し中であり、
マイナーバージョン以外、特別違いはまだ見当たりません。

 

Answer 3

データベースの互換性レベルが 80 以下だとそういう問題がおきるらしいですよ。

CROSS APPLY doesn't work in some databases

互換性レベルを 90 に設定するには

sp_dbcmptlevel databasename , 90

を実行します。

 

---

Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう

Answer 4

M_Lewis様

ご連絡ありがとうございます。

互換レベルを変更したところ問題が解消されました。
問題サーバは、SQLServer2000からバージョンアップしたもの(互換レベル：80)、
別サーバは、SQLServer2005で新規構築されたもの（互換レベル：90）であり、互換レベルに差がありました。

こんなに早くに解決していただき、本当にありがとうございました。