SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

大文字 / 小文字の混在によるクエリキャッシュへの影響

leave a comment

SQL 文を書くとき、

  • SELECT * FROM Table1
  • select * from table1

というように基本的なステートメントの個所に関しては大文字 / 小文字を混在させることが可能です。
# オブジェクト名や検索条件に関しては照合順序の設定に依存しますが。

このような記述をしたことによるクエリキャッシュへの影響を軽く見ていきたいと思います。

■大文字 / 小文字によるクエリキャッシュへの影響


以下のようなクエリを実行してみます。

SELECT * FROM QueryTest
GO
select * from QueryTest
GO

 

SELECT / FROM が大文字、小文字になっている以外は全く同一のクエリです。
このクエリを実行した後にクエリキャッシュの状態を見てみます。

SELECT text, execution_count, query_hash, query_plan_hash
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE ‘%QueryTest%’

同一のクエリハッシュ / クエリプランハッシュを持つクエリとして実行されていますが、別々にキャッシュされているのが確認できます。
image

ハッシュ値が同一ということは、同様のクエリ実行プランになるはずなのですが、別々にクエリがキャッシュされている状態ということになります。

プランの状態も見てみたいと思います。

SELECT st.text, memory_object_address, usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY
sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE ‘%QueryTest%’

image

キャッシュ エントリのメモリ アドレスも異なる値が表示されており、プランが別にキャッシュされていますね。

強制パラメータ化されたときは少し動作が変わってくるようなのですが、通常のパラメータ化クエリを使用して以下のクエリを実行してみます。

EXEC sp_executesql
@stmt = N’SELECT * FROM QueryTest where Col1 = @Param1′
, @params = N’@Param1 int’, @Param1 = 1
GO
EXEC sp_executesql
@stmt = N’select * from QueryTest where Col1 = @Param1′
, @params = N’@Param1 int’, @Param1 = 1
GO

こちらも大文字 / 小文字のみを変えています。
この時のキャッシュの状態がこちらです。
image

こちらも先ほどと同じ状態ですね。
image

クエリのキャッシュに関してはバイナリ照合順序ですでにキャッシュされているかを判断し、キャッシュとの比較は大文字 / 小文字が区別されるという仕組みだったはずです。

ストアドプロシージャを使用すれば処理の内部を意識しないでクエリを実行することができますので、大文字 / 小文字の区別により別にキャッシュされてしまうのを減らすことができるかと思います。
コーティング規約で決めてしまうというのも重要かも。

同様のクエリが少しの入力の違いで別にキャッシュされてしまうのはもったいないですよね。

Written by masayuki.ozawa

12月 14th, 2011 at 8:14 am

Posted in SQL Server

Tagged with

No Responses to '大文字 / 小文字の混在によるクエリキャッシュへの影響'

Subscribe to comments with RSS or TrackBack to '大文字 / 小文字の混在によるクエリキャッシュへの影響'.

  1. アプリ側の視点だと、開発者がステートメントを意識する必要がないように、O/Rマッパーを使うといった方法があります(O/Rマッパーで出力されるクエリは、大文字であることが多いです)
    .NETで身近なものだと、LINQ To SQLやADO.NET Entity Frameworkで実現できますね
    ただ、上記技術は特殊な技術であるが故、開発者全員が使い方を熟知していないと、プロジェクトで利用できないことが欠点です
    それでも、アプリとデータベースを切り離して、各チームの生産性を向上できるメリットは素晴らしいです
    (餅は餅屋

    Tomohiro Kinoshita

    16 12月 11 at 09:58

Leave a Reply

*