SE の雑記

DELETE FROM T1 WHERE no>= 1 and no <= 100
&#91;/sourcecode&#93;
</pre>
</div>

<p>&#160;</p>

<p>削除を行うと、DMV の情報は次のように変化します。</p>

<ul><!--EndFragment-->
  <li>sys.dm_db_index_operational_stats: leaf_ghost_count: 100 カウントアップ (削除されたデータ件数)</li>

  <li>sys.dm_db_index_usage_stats: user_updates: 1 カウントアップ (削除操作の回数)</li>
</ul>

<p>sys.dm_db_index_operational_stats から詳細な情報を確認することができます。</p>

<p>&#160;</p>

<h2>キャッシュアウトについての考慮</h2>

<p><a href="https://techcommunity.microsoft.com/t5/sql-server-blog/correction-to-my-prior-post-on-sys-dm-db-index-operational-stats/ba-p/383555" target="_blank" rel="noopener">Correction to my prior post on sys.dm_db_index_operational_stats</a> に記載されていますが、DMV の情報はキャッシュアウトについての考慮が必要となります。</p>

<p>sys.dm_db_index_operational_stats は、対象のオブジェクトとして NULL を指定することで、すべてのインデックス情報を取得するというような使い方をするケースがありますが、厳密にはすべてのインデックスの情報は取得できないということが記載されています。</p>

<p>これは、<a href="https://learn.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql?view=sql-server-ver16" target="_blank" rel="noopener">sys.dm_db_index_operational_stats</a> の次の記載となります。</p>

<blockquote>
  <p>メタデータ キャッシュ内のカウンターのリセット方法</p>

  <p><strong>sys.dm_db_index_operational_stats</strong> により返されるデータが存在するのは、ヒープまたはインデックスを表すメタデータ キャッシュ オブジェクトが使用できる間だけです。 このデータは持続性はなく、トランザクション上の一貫性もありません。 つまり、これらのカウンターを使って、インデックスが使用されているかどうかや、インデックスが最後に使用されたのはいつであるかを判断することはできません。 詳細については、「 <a href="https://learn.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16">sys.dm_db_index_usage_stats (Transact-SQL)」</a>を参照してください。</p>

  <p>ヒープまたはインデックスに対するメタデータがメタデータ キャッシュに組み込まれるたび、各列の値はゼロに設定されます。統計値は、キャッシュ オブジェクトがメタデータ キャッシュから削除されるまで累積されます。 したがって、アクティブヒープまたはインデックスは常にキャッシュ内にメタデータを持ち、SQL Serverのインスタンスが最後に開始されてからのアクティビティが累積カウントに反映される可能性があります。 アクティブになる頻度が低いヒープやインデックスのメタデータは、使用状況に応じてキャッシュ内外に移動されます。 その結果、使用できる値が存在する場合と、存在しない場合が発生します。 インデックスを削除すると、対応する統計はメモリから削除され、この関数ではレポートされなくなります。 インデックスに対するその他の DDL 操作によって、統計の値がゼロにリセットされる場合もあります。</p>
</blockquote>

<p>この DMV はキャッシュされているメタデータから情報を取得しており、次のような操作により統計がリセットされることがあります。</p>

<ul>
  <li>メモリ使用率の上昇により参照頻度が低いオブジェクトの情報のためキャッシュアウトされる:レコードが削除 (キャッシュアウト) される</li>

  <li>インデックスの REBUILD のような DDL を実行する: レコードの統計がゼロリセットされる</li>
</ul>

<p>上記は私が把握できている操作となるため、これ以外にも統計がリセット (or キャッシュアウト) されることはあるかと思います。</p>

<p>私の経験上、キャッシュアウトされる頻度はそれほど多くないと思うのですが、メモリ負荷を上昇させるとキャッシュアウトされるケースがあることは、実際の挙動として確認ができています。</p>

<p>上記の記載のようなカウンターのリセットについては、<a href="https://learn.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16" target="_blank" rel="noopener">sys.dm_db_index_usage_stats</a> には記載がされていません。

  <br />こちらの DMV については、使用されたインデックスの情報のみが確認でき、使用されていないインデックスの情報は確認できない / 操作回数については集約された情報となるため、細かな操作回数は取得できないのですが、リセットは行われていないようです。</p>

<p>メモリ上のデータとなっているようではあるので「リセットされないという保証」については、難しそうなのですが、sys.dm_db_index_operational_stats がキャッシュアウトされる状況下になっても、sys.dm_db_index_usage_stats はキャッシュアウトされずに情報が保持されていました。</p>

<p>sys.dm_db_index_operational_stats もキャッシュアウトされなければ精度の高い情報を取得することができますので、どちらか片方を取得するのではなく、sys.dm_db_index_operational_stats と sys.dm_db_index_usage_stats の情報を組み合わせて取得することを意識したほうが良いのではないでしょうか。</p>

<p>&#160;</p>

<h2>メタデータキャッシュについて</h2>

<p>sys.dm_db_index_operational_stats と sys.dm_db_index_usage_stats の情報はメモリ上にキャッシュされているため、メモリの消費量については、次のようなクエリで確認できるようです。</p>

<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:e4eacd9a-3d00-41b2-82d5-590fb0e0d5fc" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>
[sourcecode language='sql' ]
select * 
from sys.dm_os_memory_clerks 
where 
	type in ('USERSTORE_SCHEMAMGR',  'USERSTORE_DBMETADATA') and
	parent_memory_broker_type = 'MEMORYBROKER_FOR_CACHE' 
order by pages_kb desc

DBCC FREESYSTEMCACHE('SchemaMgr Store')
DBCC FREESYSTEMCACHE('<DB 名>')