運用チームは、ロードされた環境でのMicrosoft SQLの経験が隠されなくなると考えたため、この記事が誕生しました。 その中で、このDBMSを実際に使用する際のニュアンスについて説明します。

Microsoft SQL Serverは長い間Yandex.Money製品ファミリに位置付けられており、多くの個別のサービスからすべての操作に関する異種情報を収集する問題を解決しています。 このようなアセンブリがなければ、支払いを追跡したり、統計を収集したり、問題を解決したりすることは不可能です。

上記のすべては、Microsoft SQL Server 2012-2014アナリティクスの高速で正確な作業を重視する大規模データベースの管理者に役立ちます。

情報があなたにとって有用で興味深いものであることがわかった場合は、著者がリラックスしないようにコメントで知らせてください。

さらに理解するために、私たちのデータストレージシステムのアーキテクチャについて少し説明します。 Yandex.Money支払いシステムの各コンポーネントは、支払いに関する独自の情報のみを知っているため、何らかの方法で定期的にすべてのコンポーネントから情報を収集し、集約して関係を特定する必要があります。

これらのデータから、数百の測定値を持つキューブが毎日構築され、レポートとレジスタが生成され、調整が行われます。 その後、「combed」データがユーザーと金融システムに提供され、さらに作業が行われます。 強力な統合サービスとETLプロセス（抽出-変換-ロード）を備えたMicrosoft SQL Serverは、これらのタスクに最適でした。

また、 Analysis Servicesを積極的に使用してOLAPキューブを構築しています 。 アナリスト、投資家、製品管理者、幹部が簡単に使用できるスライスを使用して、多次元形式のデータを提供します。

微細なリクエストが1時間で機能する理由

これの最も一般的な理由は、古い統計（テーブル列の状態に関する情報）であり、その結果、最適な計画ではありません。

統計の更新の自動化に関する Habré に関する記事はすでにあります。したがって、この点については詳しく説明しません。 ただし、統計の更新は時間と負荷のオーバーヘッドになる可能性があり、テーブル内のデータは統計の更新よりも速く変化するため、結果は不安定になる可能性があります。 そのような場合、トレースフラグをグローバルにオンにするだけで、統計を更新するための20％のしきい値が、テーブルの変更の動的に変化する割合に変更されます。

DBCC TRACEON (2371,-1) 

フラグを有効にすると、行数が増加するにつれて統計がより頻繁に更新されます。

これは大いに役立ちますが、10億行以上のテーブルでは、通常の計画にもかかわらず、プロセスが非常に遅くなる場合があります。

この場合、統計の非同期更新を有効にすると次のことができます。

 ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON 

統計の更新中、SQLはプロセスが完了するのを待たずに機能し続けます。 テストによると、このオプションを有効にしないデータベースクエリは非常に不安定です。 この実験を自分で試してください。

ただし、適切な計画と最新の統計を使用しても、クエリの実行に原因不明の遅延が発生する場合があります。 私の経験では、（間違った計画の後の）これの最も一般的な理由は、要求されたリソースの正式な不足のために実行のためにキューにあったサーバー要求要求でした。

メモリーに貪欲な5つ以上のクエリを同時に起動し、デフォルトで「片手」の制限を25％にすると、最初の4つにはすべてのメモリーが与えられます。 残りのリクエストは、 RESOURCE_SEMAPHOREインジケーターでのみキューに入れられ、メモリが解放されるのを待ちます。

使用可能なサーバーRAMの約半分が要求の実行に使用されます。 RAMが128 GBを超えるシステムでは、25％が非常に重要です。

私の観察によると、クエリは割り当てられたメモリの20〜80％を使用しないだけです。これは、リクエストの操作中にコマンドを実行すると完全に表示されます。

 select * from sys.dm_exec_query_memory_grants 

コマンドを実行した後、プロセスで発生した場合、データベースで現在処理されているクエリとリソースの期待値が表示されます。

テストデータベースの1つでリクエストを処理した結果。

要求は9 GBを要求して受信しましたが、10％しか使用しませんでした。 ideal_memory_kbフィールドに注意してください -リクエストの未実現の夢は修正されています。

Resource Governorを使用してクエリを実行するときに、無駄なメモリ消費を効率的に処理できます。 CPU使用量とメモリ使用量に制限を設定できます。
sp_WhoIsActiveストアドプロシージャの使用もお勧めします 。 動的表現（DMV）を介してサーバーステータス情報を収集します。 非常にシンプルで強力なツール。

このパラメーターセットでsp_WhoIsActiveプロシージャを使用します。

 sp_whoisactive @not_filter = 'ReportServer', @not_filter_type = 'database', @get_plans=1, @find_block_leaders = 1, @get_task_info = 2, @get_additional_info=1--, @get_locks = 1 

このようなリクエストは、過剰な負荷がかかったときにアクティブなリクエストを観察するだけで識別できます。 また、5〜10分ごとにsys.dm_exec_query_memory_grantsクエリからselect *を実行し、結果をテーブルに保存するタスクを作成することもできます。 grant_memory_kbとmax_used_memory_kbの違いにより、合計値を分析し、問題のあるクエリを見つけることができます。

次に、必要以上にメモリを使用する接続を特定し、ResourceGovernorを使用して、メモリが80％ではなく10-20％のマージンで割り当てられているグループに接続をリダイレクトする必要があります。

REQUEST_MAX_MEMORY_GRANT_PERCENTパラメーターの値（使用可能なメモリーの割合として設定）は、次のスクリプトを使用して計算できます。

 SELECT res.name, sem.target_memory_kb /1024 as target_memory_Mb, sem.available_memory_kb / 1024 as available_memory_MB, sem.granted_memory_kb/1024 as granted_memory_Mb, sem.used_memory_kb / 1024 as used_memory_Mb, sem.grantee_count, sem.waiter_count FROM sys.dm_exec_query_resource_semaphores sem join sys.resource_governor_workload_groups res on sem.pool_id = res.pool_id where sem.resource_semaphore_id =0 

その結果、サーバーがクエリおよび追加または構成する各グループ用に使用できるメモリ量を決定できます。

上記のすべての操作の後、要求がメモリ割り当てを単に待機し、何もしないという最小限の状況で、要求間のメモリの分散がより効率的になります。

使用中のデータベースでのAlwaysON

データベースの高可用性環境であるAlwaysONの長所と短所は、怠け者によってだけ書かれたものではありません。 ただし、テラバイトのデータベースに対するこの技術の実際の使用に関する情報はそれほど多くありません。 2013年にYandex.Moneyに高可用性グループを導入したとき、軍事環境での実際の運用に関する情報すらありませんでした。 当時のメインデータベースは約4 TBしか使用していなかったため、多くのことを自分で行う必要がありました。

4年間で、このデータベースのボリュームは20 TBに増加したため、サーバーは新しい負荷にほとんど対処できず、インデックスの最適化により、以下で説明する追加の問題が発生しました。 ネットワーク経由でキューブを処理する際のデータベースパフォーマンスは、クラスターが別の強力なスイッチに転送されるまで、トラフィックがネットワーク内の他のコンポーネントに干渉するほどでした。

ピーク時には、ノードがお互いを失い、2番目のノードのデータベースが切断された（クラスタークォーラムにこんにちは）ポイントになりました。 しかし、これは新しいネットワークカードの追加によって解決されました。 そして、新しいマシンでは、すぐに10 GBのカードを入れました。

リストされたアーキテクチャ上の問題を除いて、AlwaysONテクノロジの一般的な印象は肯定的です。

• ノード間のトラフィックは80〜90％に圧縮されます。

  
  

• 読み取り専用（RO）レプリカは、長時間シャットダウンするとすぐに同期し、サービスに戻ります。

  
  

• データベースをテスト環境に複製するには、 ログ配布 （トランザクションログの配信）を手動モードで使用できます。

  
  
• AlwaysONは非常に信頼できますが、Windows Serverフェールオーバークラスタリング（WSFC）がクラッシュし、AlwaysONグループをプルすることがあります。

クラスタリングサービスの不合理な決定の数を最小限に抑えて、2つのノードを持つ構成でクラスターを消滅させるために、セカンダリノードの音声を強制的に奪うソリューションを開発しました。

 Import-Module FailoverClusters $node = "Srv1" (Get-ClusterNode $node).NodeWeight = 0 

このようなパラメータを使用すると、セカンダリノードでの作業中に穏やかになります。

AlwaysONグループが同期していない

AlwaysONグループで負荷分散を行う大規模なデータベースの操作中に、メインノードとセカンダリノードの同期が発生する場合があります。 メインノードで同時に更新されているテーブルに対してROノードで長いSELECT操作を実行すると、変更ログチェーン（LSN）の回復がブロックされます。 非同期はすべてのテーブルに影響し、クエリが実行されている限り保持されます。この時点で、レプリカ上のデータベースのデータは無関係になります。

通常、非同期の明らかな兆候はありませんが、 saとして起動された読み取り専用ノードに新しいプロセスが表示されます。 カスケードロックも頻繁に発生します。これは、上記のsp_whoisactiveプロシージャを使用してすばやく検出できます。

その理由は、Selectが分離レベルREAD COMMITTED（つまり、デフォルト値）で実行されていることにあります。

解決策として、クエリ自体でWITH（NOLOCK）パラメータを使用するか、データベースまたはセッションの分離レベルをSNAPSHOTに変更して、テーブルへの長いクエリクエリ中にそれを分離できます。

ユーザーの前に同期が外れていることを知るために、監視用のスクリプトを使用します。 5分に1回、同期のステータスを確認し、問題が発生した場合に電子メールを送信します。

 declare @Delay int set @Delay = 2 select * into #tmpag_availability_groups from master.sys.availability_groups select group_id, replica_id,replica_server_name,availability_mode into #tmpdbr_availability_replicas from master.sys.availability_replicas select replica_id,group_database_id,database_name,is_database_joined,is_failover_ready into #tmpdbr_database_replica_cluster_states from master.sys.dm_hadr_database_replica_cluster_states select * into #tmpdbr_database_replica_states from master.sys.dm_hadr_database_replica_states select replica_id,role,is_local into #tmpdbr_availability_replica_states from master.sys.dm_hadr_availability_replica_states select ars.role, drs.database_id, drs.replica_id, drs.last_commit_time into #tmpdbr_database_replica_states_primary_LCT from #tmpdbr_database_replica_states as drs left join #tmpdbr_availability_replica_states ars on drs.replica_id = ars.replica_id where ars.role = 1 SELECT --'<tr><td align="center">' + AR.replica_server_name + '- ' + AG.name + ' ' + dbcs.database_name + '</td>' AS SRV_AG_DB, AR.replica_server_name + '- ' + AG.name + ' ' + dbcs.database_name AS SRV_AG_DB, --CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END AS [EstimatedDataLoss], --'<td align="center">' + Cast(ISNULL(dbr.last_redone_time, 0) AS varchar(50)) + '</td>' As LastRedoneTime , Cast(ISNULL(dbr.last_redone_time, 0) AS varchar(50)) As LastRedoneTime , --'<td align="center">' + Cast((CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END)as nvarchar(10)) + '</td>' AS [EstimatedDataLoss2], Cast((CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END)as nvarchar(10)) AS [EstimatedDataLoss2], --'<td align="center">' + Cast(ROUND(ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1),-1) as nvarchar(10)) + '</td> </tr>' AS [EstimatedRecoveryTime] Cast(ROUND(ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1),-1) as nvarchar(10)) AS [EstimatedRecoveryTime] INTO #tt FROM #tmpag_availability_groups AS AG INNER JOIN #tmpdbr_availability_replicas AS AR ON AR.group_id=AG.group_id INNER JOIN #tmpdbr_database_replica_cluster_states AS dbcs ON dbcs.replica_id = AR.replica_id LEFT OUTER JOIN #tmpdbr_database_replica_states AS dbr ON dbcs.replica_id = dbr.replica_id AND dbcs.group_database_id = dbr.group_database_id LEFT OUTER JOIN #tmpdbr_database_replica_states_primary_LCT AS dbrp ON dbr.database_id = dbrp.database_id INNER JOIN #tmpdbr_availability_replica_states AS arstates ON arstates.replica_id = AR.replica_id WHERE --(AG.name='Nastro') and ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1) > @Delay --(AG.name=@AGN) and ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1) > @Delay IF EXISTS (SELECT * from #tt) BEGIN declare @tableHTML nvarchar(max) set @tableHTML =N'<H3><FONT SIZE="3" FACE="Tahoma">AlwaysOn Status </FONT></H3>' set @tableHTML = @tableHTML +N'<table border="1" bgcolor=D7D1F8>' + -- change the background color if you want N'<FONT SIZE="2" FACE="Calibri">' + N'<tr><th align="center">Server Group DB</th>' + N'<th align="center">LastRedoneTime</th>' + N'<th align="center">EstimatedDataLoss</th>' + N'<th align="center">EstimatedRecoveryTime</th>' + N'</tr>' + ISNULL(CAST ( ( SELECT * from #tt FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ),'') + N'</FONT>' + N'</table>' ; --send email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mail', -- change here !! @recipients='Admin1@yandex.ru;Admin2@yandex.ru', -- change here !! @subject = 'AlwaysON Report', @body = @tableHTML, @body_format = 'HTML' ; END DROP TABLE #tmpdbr_availability_replicas DROP TABLE #tmpdbr_database_replica_cluster_states DROP TABLE #tmpdbr_database_replica_states DROP TABLE #tmpdbr_database_replica_states_primary_LCT DROP TABLE #tmpdbr_availability_replica_states drop table #tmpag_availability_groups SELECT * from #tt drop table #tt 

同期解除のもう1つの一般的な原因は、インデックスのメンテナンスです。 たとえば、500 GBの容量を持つテーブルでクラスターインデックスを再構築すると、10時間の同期が取れなくなりました。

この時間を最小化する最も簡単な方法は、インデックスを作成または再構築するときにMAXDOPオプションを使用することです。 通常、10〜20 GBのインデックスには「2」、それより小さな「4」には値「2」を設定します。

読み取り専用要求ルーティング

読み取り専用データベースレプリカからの読み取り要求のニュアンスは、接続文字列でApplicationIntent = ReadOnlyを指定するだけでは十分ではないことです。 ルーティングを構成する必要もあります 。 もちろん、この設定は、一度構成するとタッチしなくなるものに適用されますが、His下のチャンスは破られ、そのようなスキームはありません。 たとえば、作業の過程で、レプリカROからメインリクエストにリクエストをリダイレクトする必要がある場合があります。

これを行うには、レプリカ自体の設定を使用し、ROへのリクエストを単に禁止していました。 しかし、レプリカ設定を切り替える次の機能のために、いくつかの不快な状況の後、アプローチを変更する必要がありました。

• 切り替えは困難であり、現在のRO要求は失われましたが、それほどクールではありません。

  
  
• トラフィックがROに返されると、すべてのリクエストが、それぞれのhadr_sync_commitインジケーターでブロックされることがよくありました。 そして今、これはまったく面白くありません。なぜなら、ハングしている間はコミットが行われないからです。 レプリカが正規化されるまで、クライアントアプリケーションの接続アドレスを変更する必要がありました。

後で、ルーティングの変更を通じてRO要求をリダイレクトする方が正しいことが判明しました。

次に、2ノードの分散負荷構成の例を示します。

 ALTER AVAILABILITY GROUP [AGGroupName] MODIFY REPLICA ON N'PrimaryDB01′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SecondaryDB01','PrimaryDB01'))); -- ,  RO       SecondaryDB01         ALTER AVAILABILITY GROUP [AGGroupName] MODIFY REPLICA ON N'PrimaryDB01′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('PrimaryDB01'))); --          PrimaryDB01; 

ルーティングを変更するプロセスでは、リクエストは通常​​完了し、次のリクエストはメインのレプリカに直接送られます。

システムの動作の4年間で、ROレプリカの障害が1回発生しました。 データファイルにCRCエラーがあるセクションの1つが落ちました。 メインノードは引き続き機能しましたが、ROレプリカにはベースがありません。

ROノードの基本レベルでの障害処理とメインノードへの自動トラフィックスイッチングは、同じルーティングを使用して実装できます。

1. データベース内の任意のテーブルへのクエリを使用してタスクを作成します。

   
   

2. 要求がエラーで完了した場合、同じ方法でメインノードのデータベースを確認します。

   
   
3. メインノードですべて問題なければ、トラフィックを切り替えます。 週末または夜に、それはあなたが良い夜の睡眠を得て、新鮮な心で問題に対処することを可能にします。

このような障害が発生した後のデータベースの復元については、可用性グループから大きなデータベースを削除し（メインノードではリスナーを介しても動作し続けます）、非復旧モードで読み取り専用ノードにバックアップを復元する方が簡単です。 次に、復元したデータベースを参加専用モードの可用性グループに追加します。

追加：
アプリケーションが遅く、SSMSが速い...-リンクAlanDentonをありがとう