官僚機構のギアを介して旅行のリクエストを送信し、独自の一時的な監視を行うことが決定されました。
最初に、SQLサーバーのパフォーマンスメトリックの収集に必要なデータベースとオブジェクトを作成します。
簡単にするために、スクリプトでデータベースを作成するためのオプションを指定しませんでした。
create database monitor -- GO use monitor GO create table perf_counters -- , ( collect_time datetime, counter_name nvarchar(128), value bigint ) GO CREATE CLUSTERED INDEX cidx_collect_time -- , select ON perf_counters ( collect_time ) GO
パフォーマンスカウンターの値は、sys.dm_os_performance_countersシステムビューから取得されます。 スクリプトは、最も人気のある重要なカウンターを説明していますが、もちろん、リストは拡張できます。 ケースについて明確にしたいと思います。 「何か」/秒で測定されるカウンターは増分です。 つまり SQLサーバーは毎秒、現在のカウンター値を既存のカウンター値に追加します。 現在の平均値を取得するには、ビューの値をサーバーの稼働時間で秒単位で割る必要があります。 リクエストにより稼働時間を確認できます。
select DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate())
つまり 現在の瞬間とtempdbの作成時間との差を見つけます。tempdbは、ご存知のように、サーバーの起動時に作成されます。
Granted Workspace Memory（KB）メトリックをすぐにメガバイトに変換します。
収集プロセスは、手順の形式で発行されます。
CREATE procedure sp_insert_perf_counters AS insert into perf_counters select getdate() as Collect_time, Counter = CASE WHEN counter_name = 'Granted Workspace Memory (KB)' then 'Granted Workspace Memory (MB)' ELSE rtrim(counter_name) END, Value = CASE WHEN counter_name like '%/sec%' then cntr_value/DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate()) WHEN counter_name like 'Granted Workspace Memory (KB)%' then cntr_value/1024 ELSE cntr_value END from sys.dm_os_performance_counters where counter_name = N'Checkpoint Pages/sec' or counter_name = N'Processes Blocked' or (counter_name = N'Lock Waits/sec' and instance_name = '_Total') or counter_name = N'User Connections' or counter_name = N'SQL Re-Compilations/sec' or counter_name = N'SQL Compilations/sec' or counter_name = 'Batch Requests/sec' or (counter_name = 'Page life expectancy' and object_name like '%Buffer Manager%') or counter_name = 'Granted Workspace Memory (KB)' GO
次に、論理テーブルからデータを選択するプロシージャを作成します。 endおよびstartパラメーターは、値を表示する時間間隔を指定します。 パラメータが設定されていない場合は、過去3時間の情報を表示します。
create procedure sp_select_perf_counters @start datetime = NULL, @end datetime = NULL as if @start is NULL set @start = dateadd(HH, -3, getdate()) if @end is NULL set @end = getdate() select collect_time, counter_name, value from monitor..perf_counters where collect_time >= @start and collect_time <= @end go
SQLエージェントジョブでsp_insert_perf_countersをラップします。 起動頻度は1分に1回です。
テキストが乱雑にならないように、ジョブを作成するためのスクリプトをスキップします。 最後に、すべてを1つのスクリプトでレイアウトします。
将来的には、これもRAMの不十分なせいによるものだと思うので、すぐにバッファプールのデータベースの「戦い」を確認できるスクリプトを提供します。 データを置くプレートを作成しましょう。
CREATE TABLE BufferPoolLog( [collection_time] [datetime], [db_name] [nvarchar](128), [Size] [numeric](18, 6), [dirty_pages_size] [numeric](18, 6) )
個別のデータベースごとにバッファプールの使用を出力するプロシージャを作成しましょう。
CREATE procedure sp_insert_buffer_pool_log AS insert into Monitor.dbo.BufferPoolLog SELECT getdate() as collection_time, CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END as [db_name], (COUNT(*) * 8.0) / 1024 as Size, Sum(CASE WHEN (is_modified = 1) THEN 1 ELSE 0 END) * 8 / 1024 AS dirty_pages_size FROM sys.dm_os_buffer_descriptors GROUP BY database_id
ダーティページ=変更されたページ。 この手順はジョブにラップされています。 3分ごとに1回実行するように設定します。 そして、選択のための手順を作成します。
CREATE procedure sp_select_buffer_pool_log @start datetime = NULL, @end datetime = NULL AS if @start is NULL set @start = dateadd(HH, -3, getdate()) if @end is NULL set @end = getdate() SELECT collection_time AS 'collection_time', db_name, Size AS 'size' FROM BufferPoolLog WHERE (collection_time>= @start And collection_time<= @end) ORDER BY collection_time, db_name
さて、データは収集されており、歴史的基盤は蓄積されており、表示するための便利な方法を思い付くことが残っています。 そして、ここで古き良きExcelが助けになります。
パフォーマンスカウンターの例を示します。バッファープールを使用する場合は、類推によって構成できます。
Excelを開き、[データ]-[他のソースから]-[Microsoft Queryから]に移動します。
新しいデータソースを作成します。ドライバーはSQL ServerまたはSQL Serverネイティブクライアント用のSQL ServerまたはODBCで、「接続」をクリックしてサーバーを登録し、パラメーターでデータベースを選択します。ステップ4では、テーブルを選択します（必要ありません）。
作成したデータソースをクリックし、[キャンセル]をクリックし、[Microsoft Queryでクエリを変更し続けますか？]の質問で[はい]をクリックします。
[テーブルの追加]ダイアログを閉じます。 次に、「ファイル」→「SQLクエリの実行」に進みます。
exec sp_select_perf_counters
を作成します。 [OK]をクリックして、[ファイル]-[Microsoft Excelにデータを返す]に移動します。
結果を配置する場所を選択します。 パラメーターの上部に2行残すことをお勧めします。
「データ」-「接続」に進み、接続のプロパティに移動します。 [定義]タブに移動し、exec sp_select_perf_counters？、？..を記述します。
[OK]をクリックすると、Excelからこれらのパラメーターを取得するセルを選択できます。 これらのセルを彼に示し、「デフォルトで使用する」チェックボックスと「セルが変更されたときに自動的に更新する」チェックボックスをオンにします。 個人的には、これらのセルに数式を入力しました。
パラメーター1 = TDATE（）-3/24（現在の日時から3時間を引いた時間）
Parameter2 = DATE（）（現在の日付と時刻）
次に、テーブルをクリックして、[挿入]-[ピボットテーブル]-[ピボットグラフ]に移動します。
ピボットテーブルを設定します。
凡例フィールド-counter_name、
軸フィールド-collect_time、
値は値です。
出来上がり！ パフォーマンスメトリックグラフを取得します。 チャートのタイプを「チャート」に変更することをお勧めします。 まだいくつかのストロークがあります。 データのあるページに移動し、接続プロパティに移動して、必要に応じて「X minごとに更新」値を設定します。 SQLサーバーでのジョブの頻度に等しい頻度を設定することは論理的だと思います。
これで、テーブル内のデータが自動的に更新されます。 スケジュールを強制的に更新する必要があります。 「開発者」タブ-「Visual Basic」に移動します。
左側にソースデータがあるシートをクリックし、次のコードを入力します。
Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("").PivotTables("1").PivotCache.Refresh End Sub
どこで
「ピボットテーブル」-ピボットテーブルを含むシートの名前。 VBエディターの括弧内に示される名前。
PivotTable1は、ピボットテーブルの名前です。 ピボットテーブルをクリックして、[パラメータ]セクションに移動すると確認できます。
これで、元のテーブルが更新されるたびにスケジュールが更新されます。 そのようなグラフの例：
ファイルを複製するには、Excelの接続のプロパティで、新しいサーバー名を入力して接続文字列を変更するだけで十分です。
バッファプールのベースの「ファイト」と推奨されるRAMの量の計算については、次のスクリプトを使用してこのファイトを最小限に抑えることができます。 各データベースの最大メモリ使用量と、サーバーに割り当てられたRAMの合計サイズに対するバッファープールのサイズの平均割合を計算し、これらのデータに基づいて、サーバーに必要な「理想的な」RAMサイズを計算します。
DECLARE @ram INT, @avg_perc DECIMAL, @recommended_ram decimal --, SELECT @ram = CONVERT(INT,value_in_use ) FROM sys.configurations WHERE name = 'max server memory (MB)' ORDER BY name OPTION (RECOMPILE); -- Buffer Pool SELECT @avg_perc = avg(t.perc) FROM ( SELECT sum(Size)/@ram*100 AS perc FROM Monitor.dbo.BufferPoolLog GROUP BY collection_time ) t -- SELECT @recommended_ram = sum(t.maxsize)*100/@avg_perc FROM ( SELECT db_name, MAX(Size) AS maxsize FROM Monitor.dbo.BufferPoolLog GROUP BY db_name ) t select @ram as current_RAM_MB, @recommended_ram as Recommended_RAM_MB
これらの計算は、サーバーで実行されているクエリが最適化されており、便利な（そうではない）場合に全テーブルスキャンを実行しないことが確実な場合にのみ意味があることに注意してください。 また、Maximum Granted Workspaceメトリックを監視して、並べ替えおよびハッシュ操作のためにバッファープールの一部を消費するサーバー上の要求がないことを確認する必要があります。
バッファキャッシュのベースの戦争の例（名前がずれる）：
ちなみに、この方法は私たちのzabbiksaよりもはるかに速く動作することが判明したため、私はそれを武器庫に任せました。
約束どおり、T-sql全体が1つのスクリプトで：
スクリプト
create database monitor -- GO use monitor GO create table perf_counters -- , ( collect_time datetime, counter_name nvarchar(128), value bigint ) GO CREATE CLUSTERED INDEX cidx_collect_time -- , select ON perf_counters ( collect_time ) GO CREATE TABLE BufferPoolLog ( collection_time datetime NOT NULL, db_name nvarchar(128) NULL, Size numeric(18, 6) NULL, dirty_pages_size numeric(18, 6) ) GO CREATE CLUSTERED INDEX cidx_collection_time ON BufferPoolLog ( collection_time ) GO create procedure sp_insert_perf_counters -- , AS insert into perf_counters select getdate() as Collect_time, rtrim(counter_name) as Counter, Value = CASE WHEN counter_name like '%/sec%' --, "- " - , .. "" - . , then cntr_value/DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate()) ELSE cntr_value END from sys.dm_os_performance_counters where counter_name = N'Checkpoint Pages/sec' or counter_name = N'Processes Blocked' or (counter_name = N'Lock Waits/sec' and instance_name = '_Total') or counter_name = N'User Connections' or counter_name = N'SQL Re-Compilations/sec' or counter_name = N'SQL Compilations/sec' or counter_name = 'Batch Requests/sec' or (counter_name = 'Page life expectancy' and object_name like '%Buffer Manager%') GO create procedure sp_select_perf_counters -- , @start datetime = NULL, @end datetime = NULL as if @start is NULL set @start = dateadd(HH, -3, getdate()) if @end is NULL set @end = getdate() select collect_time, counter_name, value from monitor..perf_counters where collect_time >= @start and collect_time <= @end go CREATE procedure sp_insert_buffer_pool_log --, AS insert into BufferPoolLog SELECT getdate() as collection_time, CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END as [db_name], (COUNT(*) * 8.0) / 1024 as Size, Sum(CASE WHEN (is_modified = 1) THEN 1 ELSE 0 END) * 8 / 1024 AS dirty_pages_size FROM sys.dm_os_buffer_descriptors GROUP BY database_id GO CREATE procedure sp_select_buffer_pool_log @start datetime = NULL, @end datetime = NULL AS if @start is NULL set @start = dateadd(HH, -3, getdate()) if @end is NULL set @end = getdate() SELECT collection_time, db_name, Size FROM BufferPoolLog WHERE (collection_time>= @start And collection_time<= @end) ORDER BY collection_time, db_name GO -- , USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'collect_perf_counters', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sp_insert_perf_counters', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'sp_insert_perf_counters', @database_name=N'monitor', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 1 minute', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=1, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20161202, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO -- , . BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'BufferPoolUsage', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'sp_insert_buffer_pool_log', @database_name=N'Monitor', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 3 minutes', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=3, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20161117, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
中古品：
» SQL Serverメモリバッファープール：基本を理解する
» パラメータを使用してExcelでストアドプロシージャを実行する方法