実際、興味深いライフストーリーのように、それはすべてタスクのルーチンから始まりました。
先日、テストサーバーでエラーログを確認する必要がありました。 待機の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を有効にするのは悪い考えですか？