Inquisition中に
SQL Serverが存在した場合、
運用サーバーにいくつかのオプションを含めるには、ホットアイロンで罰せなければなりません。 ただし、歌詞を破棄する場合は、
AUTO_CLOSEを有効にする必要がない理由と、このオプションの使用が何につながるかをさらに調べます。
実際、興味深いライフストーリーのように、それはすべてタスクのルーチンから始まりました。
先日、テストサーバーで
エラーログを確認する必要がありました。 待機の2分目に、
SSMSはログに保存されているメッセージの量が多かったためにかなりひどくなり、
xp_enumerrorlogsを使用してログの重量を確認することにし
ました 。
DECLARE @t TABLE (lod_id INT PRIMARY KEY, last_log SMALLDATETIME, size INT) INSERT INTO @t EXEC sys.xp_enumerrorlogs SELECT lod_id, last_log, size_mb = size / 1048576. FROM @t
lod_id last_log size_mb -------- --------------------- --------------- 0 2016-01-05 08:46:00 567.05288505 1 2015-12-31 12:53:00 1370.39249420 2 2015-12-18 11:32:00 768.46394729 3 2015-12-02 13:54:00 220.20050621 4 2015-12-02 13:16:00 24.04152870 5 2015-11-16 13:37:00 80.07946205 6 2015-10-22 12:13:00 109.33527946
原則として、テストサーバーでは、
SQL Serverが起動するたびにログファイルが周期的に変更されるため、ログのサイズに苦労しないようにしてい
ます ：現在の
エラーログの名前が
errorlog.1に変更され、空の
エラーログが作成され、最も古い
エラーログ
が削除されます6 。
ログをクリアする必要がある場合、
sp_cycle_errorlogが
助けになります。 しかし、雑誌をクリアする前に、私はまだそのような興味深いことについてそれらに書かれたものを理解したかったです。
xp_readerrorlogストアドプロシージャを使用して現在のログから読み取ります。
EXEC sys.xp_readerrorlog
そして、私の周辺のビジョンが多くのメッセージに目を引きました。
Starting up database '...'.
一方で、それは何も悪いことではありません。
SQL Serverは、起動するたびにデータファイルを開き、ブートページを確認します。
Starting up database '...'. CHECKDB for database '...' finished without errors on ... (local time).
しかし、検索メッセージでフィルタリングした後、選択の結果は私に考えさせました：
DECLARE @t TABLE (log_date SMALLDATETIME, spid VARCHAR(50), msg NVARCHAR(4000)) INSERT INTO @t EXEC sys.xp_readerrorlog 0, 1, N'Starting up database' SELECT msg, COUNT_BIG(1) FROM @t GROUP BY msg HAVING COUNT_BIG(1) > 1 ORDER BY 2 DESC
------------------------------------------------------ -------------------- Starting up database 'AUTOTEST_DESCRIBER'. 127723 Starting up database 'MANUAL_DESCRIBER'. 12913 Starting up database 'AdventureWorks2012'. 12901
AUTO_CLOSEオプションを含めると、これらのタイプのメッセージが多数発生する可能性があり
ます 。
ドキュメントによると、
AUTO_CLOSEオプションを有効にすると、このデータベースを使用する最後のユーザー接続がなくなると、データベースは自動的に閉じて、占有しているすべてのリソースを解放します。 繰り返しアピールすると、ベースは自動的に再開します...など、無限に続きます。
物理レベルで読んだことがあり
ますが、
SQL Serverの古いバージョンの
AUTO_CLOSE操作は、データベースファイルを繰り返し開いたときに長時間の遅延を引き起こす可能性がある完全に同期したプロセスでした。
SQL Server 2005以降、
AUTO_CLOSEは非同期になり、いくつかの問題はなくなりました。 残っているものは何ですか？ このオプションを使用しないでください...
パフォーマンスを最適化するために、
SQL Serverはバッファーキャッシュでページの変更を実行し、各変更後にこれらのページをディスクに書き込みません。 代わりに、
SQL Serverは定期的にチェックポイントを作成し、メモリ内の変更された現在のページをメモリからディスクへのトランザクションログ情報と共に書き込みます。 データベースが閉じられると、
CHECKPOINTが自動的に実行されます。 したがって、データベースが絶えず閉じられると、ディスクの負荷が大幅に増加する可能性があります。
また、データベースが閉じられるたびに、そのプロシージャキャッシュがクリアされます。 したがって、データベースを再度開くと、新しい実行計画の実行計画を生成する必要があります。 さらに悲しいことに...閉じると、バッファキャッシュもクリアされ、クエリを実行するときのディスクの負荷が増加します。
Microsoftは私と
同意しており、
AUTO_CLOSEを含めることも推奨していません。
AUTO_CLOSEがONに設定されている場合、このオプションは、各接続後にデータベースを開いたり閉じたりするオーバーヘッドが増加するため、頻繁にアクセスされるデータベースのパフォーマンスを低下させる可能性があります。 AUTO_CLOSEは、各接続後にプロシージャキャッシュもフラッシュします。ただし、いくつかのニュアンスがあります。
SQL Server 2000または
Expressエディションを使用している場合、新しいデータベースを作成すると、
AUTO_CLOSEオプションが暗黙的に有効になります。
USE [master] GO IF DB_ID('test') IS NOT NULL DROP DATABASE [test] GO CREATE DATABASE [test] GO SELECT is_auto_close_on FROM sys.databases WHERE database_id = DB_ID('test')
is_auto_close_on ---------------- 1
ブラボー
マイクロソフト ！ スタンディングオベーション...
一方、
SQL Server Expressの場合、この動作は理解できますが、このエディションでは使用されるRAMのサイズに制限があります（1 GB以下）。
ただし、将来的に、スクリプトを使用してデータベースを突然デプロイする必要がある場合は、安全にプレイして明示的に
AUTO_CLOSEを無効にすることをお
勧めします。
ALTER DATABASE [test] SET AUTO_CLOSE OFF
その過程で、別の興味深い点に気付きました。いくつかの関数またはシステムビューにアクセスすると、
AUTO_CLOSEオプションがオンになっているすべてのデータベースが開きます。
USE [master] GO IF DB_ID('p1') IS NOT NULL DROP DATABASE [p1] GO CREATE DATABASE [p1] GO ALTER DATABASE [p1] SET AUTO_CLOSE ON GO IF DB_ID('p2') IS NOT NULL DROP DATABASE [p2] GO CREATE DATABASE [p2] GO ALTER DATABASE [p2] SET AUTO_CLOSE ON GO EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p' GO
LogDate ProcessInfo Text ----------------------- ------------ ---------------------------------- 2016-01-25 17:36:40.310 spid53 Starting up database 'p1'. 2016-01-25 17:36:41.980 spid53 Starting up database 'p2'.
p1に戻ります。
WAITFOR DELAY '00:03' GO SELECT DB_ID('p1') GO EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p'
しかし、
p2は「会社のために」起きます。
LogDate ProcessInfo Text ----------------------- ------------ ---------------------------------- 2016-01-25 17:36:40.310 spid53 Starting up database 'p1'. 2016-01-25 17:36:41.980 spid53 Starting up database 'p2'. 2016-01-25 17:39:17.440 spid52 Starting up database 'p1'. 2016-01-25 17:39:17.550 spid52 Starting up database 'p2'.
そして最後に、私たちは真実の底に着きました。 サーバーでは、さまざまなユーザーがメタデータにアクティブにアクセスしました...これにより、
AUTO_CLOSEを有効にしてデータベースが起動し、暗黙的にログが増大しました。
ちなみに、予防策は非常に単純なものでした：
DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_CLOSE OFF WITH NO_WAIT;' FROM sys.databases WHERE is_auto_close_on = 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') EXEC sys.sp_executesql @SQL
すべてがMicrosoft SQL Server 2012（SP3）（KB3072779）-11.0.6020.0（X64）でテストされました。この記事を英語圏の聴衆と共有したい場合：
AUTO_CLOSEを有効にするのは悪い考えですか？