SQL Server 2005の5、共通の管理タスク

この記事では、いくつかのスクリプトが存在するか、SQL Server 2005の、私は定期的に自分のサーバーの管理の一環として使用するストアドプロシージャ。 これらのスクリプトは、サーバーの使用状況を監視、基地のサイズを変更する一括できるように、インデックスを使用すると、自動再構築またはテーブルの最後の使用の日付をチェックします。

1。 監視サーバの活動状況

のSQL Serverの監視、おそらく1つの管理者の中で最も重要な責務のデータベースです。 これは私の手順sp_WhoIsActiveアダムMachanic開発使用する場合。 このプロシージャは、サーバー上で保留中の要求とは、ユーザーがアプリケーションを実行し、すべての関連情報が表示されますと、CPU、I / Oまたは、アプリケーション自身。

sp_WhoIsActive：総消費量

プロシージャのパラメータが2回指定された間隔（）での活動を測定するために5秒などの手順を実行するには（ほとんど）の異なるアプリケーションは、簡単にほとんどのCPUを使用し消費電力を決定することができますインスタントtempdbまたはなるディスクにアクセスします。

sp_WhoIsActive：消費5秒

また、ブロックを待ってから、発生するクエリの種類についての情報を表示する手順。

sp_WhoIsActive：ウェイツとクエリ

各リンクをクリックしてリクエストでは、アプリケーションが明らかにもリソースを集中的に検討すると便利関連付けられたXMLファイルが表示されます。

詳細は以下の完全かつ迅速に、私は定期的に使用するここでは入手可能です。

2。 監視ディスクの使用領域

私は、手続きsp_SDS（無料登録が必要です）リチャード鼎によってデータベースのサイズにレポートを生成する記述を使用して、インスタンスを記録します。 スペースにも、両方のファイルの種類と全身に計算されます。

sp_SDS

このストアドプロシージャへの代替コピーを簡単にする/貼り付けるExcelのスプレッドシートには次のとおり：

 セレクトSERVERNAMEはASインスタンス@ @
 db.nameのAS基地
合計（ケースはaf.groupid = 0を0以外8192.0E * af.size / 1048576.0E END）はTaille_BaseのAS、
合計（ケースはaf.groupid = 0して8192.0E * af.size / 1048576.0Eそうでなければ0 END）はのAS Taille_Log、
合計（8192.0E）Taille_TotaleのAS / 1048576.0E af.size *
からマスタ..デシベルのAS sysdatabasesの
内部マスタ..オンのAf AFとsysaltfiles JOINを[dbidが] =デシベル[dbidは]
 Db.nameのWHERE NOT INは（'ディストリビューション'、'リソース'、'マスタ'、'tempdb'の'、'モデル'、'msdb'は）
 GROUP BYをdb.name 

あなたのデータベースのサイズを監視するには、それはおそらく、データベース管理では、特定のテーブルを作成するために必要になります。 この表は、あなたが見るのは興味深いものとみなすすべてのデータが含まれます。 これは私のスクリプトDBINFOティムフォードによって書かれた使用する場合。 これは、テーブルファイルのサイズ（データベースおよびログ）、空き容量、ファイル名や復元の方法で記録することができます。

DBINFO

単にSQLジョブを一定の間隔では、あなたやレポートを作成する基地の大きさの進化を予測することができますDBINFOは、テーブル内のデータを格納するために、このスクリプトを実行して作成すると、たとえばSQL Serverを使用してAnalysis Servicesの。

3。 分析性能指数の

経営指標のSQLサーバの管理のためのもう一つの重要な作業です。 関連性と、データベースのテーブルの上で、これらのインデックスの使用を分析するために、私は、 スクリプトジェイソン戦略開発を使用する。 このスクリプトでは）と（ヒープ領域を含むデータベース内には、自分自身のテーブルと言うことですすべてのインデックスを、表示情報の無数の数として、その有用性を判断するスキャンとシーク、列インデックスは、インデックスの大きさや影響を与える（）ユーザーがインデックスをプラス。 スクリプトでは、欠落しているインデックスがSQL Serverによって提案されます。

分析目次

場合、シークを表示するには、ルックアップをすばやくスキャンする場合は、次のスクリプトを使用することができます：

 のSELECT OBJECT_NAMEを（sの[は、OBJECT_ID]）のAS [オブジェクト名]
一[名前]のAS [名前索引]
 USER_SEEKS、
 USER_SCANS、
 USER_LOOKUPS、
 USER_UPDATES
としてSからsys.dm_db_index_usage_statsは
入れ子私として参加sys.indexesに
オン一[は、OBJECT_ID] = sの[は、OBJECT_ID]
とアイエス= index_idのindex_idの
のWHERE OBJECTPROPERTY（sの[は、OBJECT_ID]、'IsUserTable'）= 1 

場合は、このスクリプトを使用することができます：インデックスを挿入、更新および削除の要求の影響を調査するには

 のSELECT OBJECT_NAMEを（1つの。[は、OBJECT_ID]）のAS [オブジェクト名]
一[名前]のAS [名前索引]
 1つの。LEAF_INSERT_COUNT、
 1つの。LEAF_UPDATE_COUNT、
 1つの。LEAF_DELETE_COUNT
からsys.dm_db_index_operational_stats（はNULLはNULLはNULLはNULL）
入れ子私として参加sys.indexesに
オン一[は、OBJECT_ID] = 1つの。[は、OBJECT_ID]
とi. 1つの。= index_idのindex_idの
のWHERE OBJECTPROPERTY（1つの。[は、OBJECT_ID]、'IsUserTable'）= 1 

未使用インデックスを一覧表示するかどうかは、あなたは、次のスクリプトを使用することができます：削除されることが決定

 dbnameはVARCHAR型（100）@ @のDECLARE @ dbidがINT、dbidを= DB_ID（）、@ dbnameは= DB_NAMEパラメータ@のSELECT（）; partitionCTE（は、OBJECT_ID、index_idの、row_countは、partition_count）のAS（セレクト[は、OBJECT_ID]、index_idの、合計と（[のROWS]）AS』row_countは'、COUNT（）は『AS』partition_count'からsys.partitionsのGROUP BY [は、OBJECT_ID]、index_idの）のSELECT OBJECT_NAMEを（一[は、OBJECT_ID]）objectNameは、i.name、ケースとしてときi.is_unique = partition_id 1して'のUNIQUE'ELSE''END +のAS i.type_desc'索引タイプ'ddius.user_seeks、ddius.user_scans、ddius.user_lookups、ddius.user_updates、cte.row_count、ケースはpartition_count"1ならば、'yes'と他の'いいえ'エンドとして'パーティション？'、ケースはi.typeそしてi.is_unique_constraint = 2 = 0し'DROP INDEXは'+ + i.nameを'On'dbnameは+ @ +'dbo'にありません。  + OBJECT_NAMEを（ddius. [は、OBJECT_ID]）+'、'ときi.typeそしてi.is_unique_constraint = 2 = 1ならば'のALTER TABLE'+ dbnameは@ +'dbo'にありません。  + OBJECT_NAMEを（ddius. [は、OBJECT_ID]）+'＆ドロップで、制約'+ i.name +'、'ELSE''END AS』SQL_DropStatement'からsys.indexesに私が入れ子= ddius.OBJECT_ID i.object_id ddius sys.dm_db_index_usage_statsはオン結合とi.index_id = ddius.index_id入れ子= cte.OBJECT_IDそしてi.index_id = cte.index_idのWHERE ddius.database_id ORDER BYを@（ddius.user_seeks + ddius.user_scans + ddius.user_lookups = dbidをi.object_idのAS partitionCTEオンCTEの結合）昇順DESCをuser_updates; 

最後に、このスクリプトを使用することができます：欠落しているインデックスがSQL Serverによって、推奨リストに

セレクトt.nameのAS'テーブル'、'非クラスタ化インデックスを作成しますix_'+ t.name +'_missing_'+キャスト（ddmid.index_handleのAS VARCHAR型（10））+'On'のddmid.STATEMENT + +'（'+ ISNULLを（ ddmid.equality_columns、''）+ケースはddmid.equality_columns is not nullはまた、ddmid.inequality_columns NOT NULLをして'' ELSE''END + ISNULLを（ddmid.inequality_columns、''）+'）'+ ISNULLを（'を含むためです（'+ ddmid.included_columns +');',','）sql_statementのAS、ddmigs.user_seeks、ddmigs.user_scansはCAST（（ddmigs.user_seeks + ddmigs.user_scans）* ddmigs.avg_user_impactとしてint）のAS'est_impactから内部= ddmig.index_group_handle内部のAS = ddmid.index_handle sys.tables内部は、t ONとして参加ddmig.index_handleオンddmid sys.dm_db_missing_index_details JOINをddmigs.group_handleオンddmigsとして参加sys.dm_db_missing_index_group_stats ddmigのAS ddmigs.last_user_seek sys.dm_db_missing_index_groups ddmid.OBJECT_ID = t.OBJECT_IDのWHERE ddmid.database_id DB_ID =（）とCAST（（ddmigs.user_seeks + ddmigs.user_scans）* ddmigs.avg_user_impactのAS INT）を"100 ORDER BYのキャスト（（ddmigs.user_seeks + ddmigs.user_scans）* ddmigs.avg_user_impactのAS INT）はDESCを; 

4。 インデックスの最適化

徐々に、より多くのデータ、更新、または削除を追加されると、インデックスおよび断片化されたパフォーマンスが大幅に低下させることができます。 したがって、断片化インデックスと行動を監視するポリシーを持つことが重要ですすぎの断片化の場合です。

私の場合、私は、ミシェルUffordプロシージャdba_IndexDefrag_sp実行週末ジョブを実行している。 この手順では、断片化のレベルに応じて、または再編成を完全に再生成するすべてのデータベースとサーバーのすべてのインデックスを確認することができます。 オプションの手順では、断片化のレベルは、2つのアクションのトリガを変更するには、できることを確認したり、使用するプロセッサの数を識別するベース、または1つのテーブルを指定します。

5。 最後に使用されるテーブル

本番環境に応じて、そのテーブルの非常に多数の人を左に放棄されたユーザーが作成されますが起こることがあります。 これは、ファイルのインポートでは、例えばその次に他のSQLサーバー上でチェックする興味深いことが起こることができます。 残るかどうか、これらのテーブルはまだまたはアーカイブする前には使用されます。 この場合は、読み取りとテーブルの指定されたデータベースに含まれる記述の最後のお日にちをご提供し、次のスクリプトを使用することができます：

のSET OFFにANSI_WARNINGSは、SET NOCOUNT ONを;、last_user_seek（セレクト[object_idの]のAS aggを選ぶなら、、last_user_lookup last_user_scan、last_user_update sys.dm_db_index_usage_statsはどこから= database_idのDB_ID（））のSELECT [スキーマ] = OBJECT_SCHEMA_NAME（[のobject_id]）、[table_or_viewに] = OBJECT_NAMEを（[のobject_id]）、last_read =マックス（last_read）=マックスlast_write（）、NULLからaggは、UNION ALLのSELECT last_user_seek [object_idを]（セレクト[object_idを]からlast_write、last_user_scan、NULLからaggは、UNION ALLのSELECT [のobject_id]、last_user_lookup、NULLからaggは、UNION ALLのSELECT [のobject_id]、NULLのlast_user_updateからagg）×（[のobject_id]、last_read、last_write）GROUP BYのOBJECT_SCHEMA_NAME（[のobject_id]）、OBJECT_NAMEを（[のobject_id]）ORDER BYを1として、2;