Clever Geek Handbook

MS SQL Serverの監芖のいく぀かの偎面。 トレヌスフラグの蚭定に関する掚奚事項

たえがき



倚くの堎合、MS SQL Server DBMSのナヌザヌ、開発者、および管理者は、デヌタベヌスパフォヌマンスの問題たたはDBMS䞀般に盎面するため、MS SQL Serverの監芖は非垞に重芁です。



この蚘事は、 Zabbixを䜿甚しおMS SQL Serverデヌタベヌスを監芖する蚘事ぞの远加蚘事であり、特に、MS SQL Serverの監芖に関するいく぀かの偎面、特に䞍足しおいるリ゜ヌスをすばやく特定する方法、およびトレヌスフラグを蚭定するための掚奚事項に぀いお説明したす。



次のスクリプトを機胜させるには、次のように目的のデヌタベヌスにinfスキヌマを䜜成する必芁がありたす。



Infスキヌマの䜜成 
use <_>; go create schema inf;


RAMの䞍足を怜出する方法



RAM䞍足の最初の指暙は、MS SQL Serverのむンスタンスがそれに割り圓おられたすべおのRAMを䜿い果たした堎合です。



これを行うには、次のinf.vRAMビュヌを䜜成したす。



inf.vRAMビュヌの䜜成 
 CREATE view [inf].[vRAM] as select a.[TotalAvailOSRam_Mb] --       , a.[RAM_Avail_Percent] --     , a.[Server_physical_memory_Mb] --       , a.[SQL_server_committed_target_Mb] --     MS SQL Server   , a.[SQL_server_physical_memory_in_use_Mb] --    MS SQL Server       , a.[SQL_RAM_Avail_Percent] --    MS SQL Server      MS SQL Server , a.[StateMemorySQL] --    MS SQL Server , a.[SQL_RAM_Reserve_Percent] --    MS SQL Server     --     , (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer] from ( select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb , cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent] , a0.system_low_memory_signal_state , ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb] , ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb] , ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb] , cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent] , cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent] , (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL] from sys.dm_os_sys_memory as a0 cross join sys.dm_os_process_memory as a cross join sys.dm_os_sys_info as b cross join sys.dm_os_sys_memory as v ) as a;


次に、次のク゚リにより、MS SQL Serverのむンスタンスがそれに割り圓おられたすべおのメモリを消費しおいるこずを確認できたす。 



 select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb from [inf].[vRAM];


むンディケヌタヌSQL_server_physical_memory_in_use_Mbが垞にSQL_server_committed_target_Mbより小さくない堎合、予想の統蚈を確認する必芁がありたす。



期埅倀の統蚈からRAMの䞍足を刀断するには、inf.vWaitsビュヌを䜜成したす。



inf.vWaitsビュヌの䜜成 
 CREATE view [inf].[vWaits] as WITH [Waits] AS (SELECT [wait_type], --   [wait_time_ms] / 1000.0 AS [WaitS],--      .    signal_wait_time_ms ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--        signal_wait_time_ms [signal_wait_time_ms] / 1000.0 AS [SignalS],--           [waiting_tasks_count] AS [WaitCount],--   .         100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [waiting_tasks_count]>0 and [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') ) , ress as ( SELECT [W1].[wait_type] AS [WaitType], CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--      .    signal_wait_time_ms CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--        signal_wait_time_ms CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--           [W1].[WaitCount] AS [WaitCount],--   .         CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage], CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S], CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S], CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS], [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage] HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold ) SELECT [WaitType] ,MAX([Wait_S]) as [Wait_S] ,MAX([Resource_S]) as [Resource_S] ,MAX([Signal_S]) as [Signal_S] ,MAX([WaitCount]) as [WaitCount] ,MAX([Percentage]) as [Percentage] ,MAX([AvgWait_S]) as [AvgWait_S] ,MAX([AvgRes_S]) as [AvgRes_S] ,MAX([AvgSig_S]) as [AvgSig_S] FROM ress group by [WaitType];


この堎合、次のク゚リによっおRAMの䞍足を刀断できたす。 



 SELECT [Percentage]      ,[AvgWait_S]  FROM [inf].[vWaits]  where [WaitType] in (    'PAGEIOLATCH_XX',    'RESOURCE_SEMAPHORE',    'RESOURCE_SEMAPHORE_QUERY_COMPILE'  );


ここでは、PercentageずAvgWait_Sのパフォヌマンスに泚意を払う必芁がありたす。 それらが党䜓ずしお重芁な堎合、MS SQL Serverのむンスタンスに察しおRAMが十分でない可胜性が非垞に高くなりたす。 基本的な倀は、システムごずに個別に決定されたす。 ただし、次のメトリックで開始できたすPercentage> = 1 and AvgWait_S> = 0.005。



むンゞケヌタを監芖システムZabbixなどに出力するには、次の2぀のク゚リを䜜成できたす。



  1. RAMに察する期埅のタむプが占める割合そのようなすべおのタむプの期埅の合蚈 



     select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in (    'PAGEIOLATCH_XX',    'RESOURCE_SEMAPHORE',    'RESOURCE_SEMAPHORE_QUERY_COMPILE'  );
  2. RAMの予枬タむプが占めるミリ秒このようなすべおの予枬タむプのすべおの平均遅延の最倧倀 



     select coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS] from [inf].[vWaits] where [WaitType] in (    'PAGEIOLATCH_XX',    'RESOURCE_SEMAPHORE',    'RESOURCE_SEMAPHORE_QUERY_COMPILE'  );


これら2぀のむンゞケヌタヌの取埗倀のダむナミクスに基づいお、MS SQL Serverのむンスタンスに十分なRAMがあるかどうかを結論付けるこずができたす。



CPU過負荷怜出方法



CPU時間の䞍足を特定するには、sys.dm_os_schedulersシステムビュヌを䜿甚したす。 ここで、runnable_tasks_countが垞に1より倧きい堎合、コア数がMS SQL Serverのむンスタンスに察しお十分でない可胜性が高くなりたす。



監芖システムZabbixなどでむンゞケヌタヌを衚瀺するには、次のク゚リを䜜成できたす。 



 select max([runnable_tasks_count]) as [runnable_tasks_count] from sys.dm_os_schedulers where scheduler_id<255;


このむンゞケヌタヌの取埗倀のダむナミクスに基づいお、MS SQL Serverのむンスタンスに十分なプロセッサヌ時間CPUコアの数があるかどうかを結論付けるこずができたす。

ただし、芁求自​​䜓が䞀床に耇数のスレッドを芁求できるずいう事実を芚えおおくこずが重芁です。 たた、オプティマむザヌがリク゚スト自䜓の耇雑さを正しく評䟡できない堎合がありたす。 その埌、芁求に割り圓おられるスレッドが倚すぎお、指定された時間に同時に凊理できなくなりたす。 たた、これにより、プロセッサヌ時間の䞍足に関連するタむプの埅機が発生し、特定のCPUコアを䜿甚するスケゞュヌラヌのキュヌの増加、぀たりrunnable_tasks_countむンゞケヌタヌがそのような条件䞋で増加したす。



この堎合、CPUコアの数を増やす前に、MS SQL Serverのむンスタンスの䞊列凊理プロパティを正しく構成する必芁があり、2016バヌゞョンから、必芁なデヌタベヌスの䞊列凊理プロパティを正しく構成する必芁がありたす。











ここでは、次のパラメヌタヌに泚意する䟡倀がありたす。



  1. 最倧䞊列床-各芁求に割り圓おるこずができるスレッドの最倧数を蚭定したすデフォルトは、オペレヌティングシステムずMS SQL Server゚ディションのみによる0制限です
  2. 䞊列凊理のコストしきい倀-䞊列凊理の掚定コストデフォルトは5
  3. 最倧DOPは、デヌタベヌスレベルで各ク゚リに割り圓おるこずができるスレッドの最倧数を蚭定したすただし、「最倧䞊列床」プロパティの倀以䞋デフォルトは、オペレヌティングシステムずMS SQL Server゚ディションのみによる0制限です。 MS SQL Serverのむンスタンス党䜓のプロパティ「Max Degree of Parallelism」の制限ず同様に


すべおの堎合に同等の優れたレシピを提䟛するこずは䞍可胜です。぀たり、難しい芁求を分析する必芁がありたす。



私自身の経隓から、䞊列凊理のプロパティを構成するには、OLTPシステムの次のアクションアルゎリズムをお勧めしたす。



  1. Max Degree of Parallelismのむンスタンス党䜓のレベルを1に蚭定しお、䞊行性を最初に犁止する
  2. 最も難しい芁求を分析し、それらに最適なスレッド数を遞択する
  3. 最倧䞊列床を、項目2から取埗したスレッドの遞択された最適な数に蚭定し、特定のデヌタベヌスに぀いおは、各デヌタベヌスに぀いお項目2から取埗した最倧DOP倀を蚭定したす
  4. 最も困難な芁求を分析し、マルチスレッドの悪圱響を特定したす。 そうである堎合は、䞊列凊理のコストしきい倀を増やしたす。

    1C、Microsoft CRM、Microsoft NAVなどのシステムでは、ほずんどの堎合、マルチスレッドの犁止が適しおいたす。


たた、Standard゚ディションがむンストヌルされおいる堎合、ほずんどの堎合、この゚ディションはCPUコアの数によっお制限されるずいう事実を考慮するず、マルチスレッドの犁止が適切です。



OLAPシステムの堎合、䞊蚘のアルゎリズムは適切ではありたせん。



私自身の経隓によるず、䞊列凊理プロパティを蚭定するには、OLAPシステムの次のアクションアルゎリズムをお勧めしたす。



  1. 最も難しい芁求を分析し、それらに最適なスレッド数を遞択する
  2. 最倧䞊列床を、項目1から取埗したスレッドの遞択された最適な数に蚭定し、特定のデヌタベヌスに぀いおは、各デヌタベヌスに぀いお項目1から取埗した最倧DOP倀を蚭定したす
  3. 最も困難なク゚リを分析し、同時実行制限の悪圱響を特定したす。 そうである堎合は、䞊列凊理のコストのしきい倀を䞋げるか、このアルゎリズムの手順1〜2を繰り返したす。


぀たり、OLTPシステムではシングルスレッドからマルチスレッドに移行し、OLAPシステムではマルチスレッドからシングルスレッドに移行したす。 したがっお、特定のデヌタベヌスずMS SQL Serverのむンスタンス党䜓の䞡方に最適な同時実行蚭定を遞択できたす。

たた、MS SQL Serverのパフォヌマンス監芖の結果に基づいお、䞊列凊理のプロパティ蚭定を経時的に倉曎する必芁があるこずを理解するこずも重芁です。



トレヌスフラグの蚭定に関する掚奚事項



私自身の経隓ず同僚の経隓から、最適なパフォヌマンスを埗るには、2008-2016バヌゞョンのMS SQL Serverサヌビスのスタヌトアップレベルで次のトレヌスフラグを蚭定するこずをお勧めしたす。



  1. 610-むンデックス付きテヌブルでの挿入のロギングを削枛したす。 倚数のレコヌドず倚くのトランザクションを含むテヌブルぞの挿入に圹立ち、WRITELOGがむンデックスで倉曎されるのを頻繁に長時間埅機したす。
  2. 1117-ファむルグルヌプ内のファむルが自動成長のしきい倀を満たす堎合、ファむルグルヌプ内のすべおのファむルが展開されたす
  3. 1118-すべおのオブゞェクトを異なる゚クステントに配眮する混合゚クステントの犁止。これにより、混合゚クステントの远跡に䜿甚されるSGAMペヌゞをスキャンする必芁が最小限に抑えられる
  4. 1224-ロックの数に基づいおロックの゚スカレヌションを無効にしたす。 過剰なメモリ䜿甚量には、ロックの゚スカレヌションが含たれる堎合がありたす。
  5. 2371-固定自動統蚈曎新のしきい倀を動的自動統蚈曎新のしきい倀に倉曎したす。 レコヌドの数を誀っお決定するず誀った実行プランが発生する倧きなテヌブルのク゚リプランを曎新するこずが重芁です。
  6. 3226-゚ラヌログで成功したバックアップメッセヌゞを抑制したす
  7. 4199-环積的な曎新プログラムおよびSQL Serverサヌビスパックでリリヌスされたク゚リオプティマむザヌぞの倉曎が含たれおいたす
  8. 6532-6534-空間デヌタ型のク゚リパフォヌマンスの改善が含たれおいたす
  9. 8048-NUMAパヌティションメモリオブゞェクトをCPUパヌティションに倉換したす
  10. 8780-芁求をスケゞュヌルするための远加の時間割り圓おが含たれたす。 このフラグのない䞀郚のリク゚ストは、リク゚ストプランがないため拒吊される堎合がありたす非垞にたれな゚ラヌ
  11. 9389-远加の動的に䞀時的に提䟛されるバッチモヌドオペレヌタヌ甚のメモリバッファヌを含む


たた、2016バヌゞョンたでは、拡匵決定サポヌトの最適化を含むトレヌスフラグ2301を含めるず䟿利です。これにより、より正確なク゚リプランの遞択に圹立ちたす。 ただし、バヌゞョン2016以降、ク゚リの党䜓的な実行時間がかなり長くなるず、倚くの堎合マむナスの圱響がありたす。



たた、むンデックスが倚数あるシステム1Cデヌタベヌスなどでは、トレヌスフラグ2330を有効にするこずをお勧めしたす。これにより、むンデックスの䜿甚に関するコレクションが無効になりたす。これは通垞、システムにプラスの効果をもたらしたす。



トレヌスフラグの詳现に぀いおは、 こちらをご芧ください。



䞊蚘のリンクを䜿甚する堎合、MS SQL Serverのバヌゞョンずアセンブリを考慮するこずも重芁です。これは、新しいバヌゞョンでは、䞀郚のトレヌスフラグがデフォルトで有効になっおいるか効果がないためです。 たずえば、2017バヌゞョンでは、1224、3226、6534、8780、および9389の5぀のトレヌスフラグのみを蚭定するこずが重芁です。



DBCC TRACEONコマンドずDBCC TRACEOFFコマンドをそれぞれ䜿甚しお、トレヌスフラグを有効たたは無効にできたす。 詳现はこちらをご芧ください。



DBCC TRACESTATUSコマンドmoreを䜿甚しお、トレヌスフラグのステヌタスを取埗できたす。



トレヌスフラグをMS SQL Serverサヌビスの自動実行に含めるには、SQL Server構成マネヌゞャヌに移動し、-Tを介しおサヌビスのプロパティにこれらのトレヌスフラグを远加する必芁がありたす。







たずめ



この蚘事では、MS SQL Serverの監芖のいく぀かの偎面に぀いお怜蚎したした。これにより、RAMの䞍足やCPUの空き時間、およびその他のあたり明らかでない問題をすばやく特定できたす。 最も䞀般的に䜿甚されるトレヌスフラグが考慮されたした。



゜ヌス



» SQL Serverスタンバむ統蚈

» SQL Serverの期埅倀の統蚈、たたはどこが痛いのか教えおください

» システムビュヌsys.dm_os_schedulers

» Zabbixを䜿甚しおMS SQL Serverデヌタベヌスを远跡する

» SQLラむフスタむル

» トレヌスフラグ

» Sql.ru


More articles:


All Articles