Ambient ConsultingのdbaおよびSQL Serverパフォーマンス最適化コンサルタントとして、初めて目にするSQL Serverインスタンスのパフォーマンスボトルネックを分析する必要性にしばしば遭遇します。これは困難な作業になる場合があります。原則として、ほとんどの企業は自社のデータベースに関するドキュメントを持っていません。存在する場合、それは古くなっているか、検索に数日かかります。

この記事では、システム関数、ストアドプロシージャ、テーブル、dmvを使用してメタデータ情報を掘り下げる基本的なスクリプトセットを共有します。一緒に、目的のインスタンス上のデータベースのすべての秘密-サイズ、ファイルの場所、列、データ型、デフォルト値、キー、インデックスなどの設計を明らかにします。

GUIを使用してこの情報の一部を取得しようとしたことがある場合、これらのスクリプトの助けを借りて即座に取得される情報量に驚くことでしょう。

他のスクリプトと同様に、運用環境で実行する前に、まずテスト環境でテストします。 AdventureWorksや pubsなどのMSテストベースで実行することをお勧めします。

さて、十分な言葉で、スクリプトを紹介しましょう！

サーバーを調査します

サーバーに関する情報を提供するクエリから始めましょう。

基本情報

まず、基本的な情報を提供するいくつかの簡単な@@関数 。

--     Select @@SERVERNAME as [Server\Instance]; --  SQL Server Select @@VERSION as SQLServerVersion; --  SQL Server Select @@ServiceName AS ServiceInstance; --   (,     ) Select DB_NAME() AS CurrentDB_Name;

最後の再起動からSQL Serverが実行されている時間はどれくらいですか？ tempdbシステムデータベースは、SQL Serverを再起動するたびに再作成されることに注意してください。サーバーが最後に再起動されたときを判断する1つの方法を次に示します。

 SELECT @@Servername AS ServerName , create_date AS ServerStarted , DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning , DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig FROM sys.databases WHERE name = 'tempdb'; GO

リンクされたサーバー

リンクサーバーは、SQL Serverが他のデータサーバーにアクセスできるようにする接続です。分散リクエストは、異なるリンクサーバーで実行できます。データベースサーバーが他のサーバーから分離されているかどうか、または他のサーバーに接続されているかどうかを知ることは役立ちます。

 EXEC sp_helpserver; --OR EXEC sp_linkedservers; --OR SELECT @@SERVERNAME AS Server , Server_Id AS LinkedServerID , name AS LinkedServer , Product , Provider , Data_Source , Modify_Date FROM sys.servers ORDER BY name; GO

すべてのデータベースのリスト

まず、サーバー上のすべてのデータベースのリストを取得します。どのサーバーにも、4つまたは5つのシステムデータベース（レプリケーションを使用する場合は、master、model、msdb、tempdb、およびdistribution）があることに注意してください。次のクエリでこれらのデータベースを除外することをお勧めします。 SSMSでデータベースのリストを表示するのは非常に簡単ですが、これらのクエリはより複雑なクエリの「ビルディングブロック」になります。

T-SQLのすべてのデータベースのリストを取得する方法はいくつかありますが、以下にそれらのいくつかを示します。各メソッドは同様の結果を返しますが、いくつかの違いがあります。

 EXEC sp_helpdb; --OR EXEC sp_Databases; --OR SELECT @@SERVERNAME AS Server , name AS DBName , recovery_model_Desc AS RecoveryModel , Compatibility_level AS CompatiblityLevel , create_date , state_desc FROM sys.databases ORDER BY Name; --OR SELECT @@SERVERNAME AS Server , d.name AS DBName , create_date , compatibility_level , m.physical_name AS FileName FROM sys.databases d JOIN sys.master_files m ON d.database_id = m.database_id WHERE m.[type] = 0 -- data files only ORDER BY d.name; GO

最後のバックアップ？

やめて！先に進む前に、すべての適切なdbaは、新しいバックアップがあるかどうかを確認する必要があります。

 SELECT @@Servername AS ServerName , d.Name AS DBName , MAX(b.backup_finish_date) AS LastBackupCompleted FROM sys.databases d LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D' GROUP BY d.Name ORDER BY d.Name;

最後のバックアップのあるファイルへのパスをすぐに見つけた方が良いでしょう。

 SELECT @@Servername AS ServerName , d.Name AS DBName , b.Backup_finish_date , bmf.Physical_Device_name FROM sys.databases d INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D' INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id ORDER BY d.NAME , b.Backup_finish_date DESC; GO

アクティブなユーザー接続

特にパフォーマンスの問題に対処する場合は、現在どのデータベースが使用されているかを理解しておくといいでしょう。

翻訳者のメモ ：これはSQL Server 2012以降でのみ機能します。以前のエディションでは、dmv sys.dm_exec_sessionsにはdatabase_id列がありませんでした。ユーザーが現在作業しているデータベースを調べるには、sp_whoを使用できます。

 --  ,      sp_who SELECT @@Servername AS Server , DB_NAME(database_id) AS DatabaseName , COUNT(database_id) AS Connections , Login_name AS LoginName , MIN(Login_Time) AS Login_Time , MIN(COALESCE(last_request_end_time, last_request_start_time)) AS Last_Batch FROM sys.dm_exec_sessions WHERE database_id > 0 AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' ) GROUP BY database_id , login_name ORDER BY DatabaseName;

データベースを研究しています

さらに詳しく見て、ディレクトリとdmvのさまざまな表現を使用して、すべてのデータベース内のオブジェクトに関する情報を収集する方法を見てみましょう。このセクションで提示されるクエリのほとんどは1つのデータベースの「内部」にあるため、SSMSで目的のデータベースを選択するか、use databaseコマンドを使用することを忘れないでください。また、select db_name（）を使用して、クエリが実行されるデータベースのコンテキストを常に見ることができることも覚えておいてください。

sys.objectsシステムテーブルは、データモデルを構成するオブジェクトに関する情報を収集するためのキーの1つです。

 --   U -  --     type  WHERE USE MyDatabase; GO SELECT * FROM sys.objects WHERE type = 'U';

以下は、情報を取得できるオブジェクトの種類のリストです（ MSDNの sys.objectsのドキュメントを参照）

sys.objects.type

AF =統計関数（CLR環境）;

C =チェック制限。

D = DEFAULT（制限または分離）;

F =外部キー制約。

PK = PRIMARY KEY制約。

P = SQLストアドプロシージャ。

PC =ストアドビルドプロシージャ（CLR）;

FN =スカラーSQL関数。

FS =スカラービルド関数（CLR環境）;

FT =テーブルリターンビルド関数（CLR）;

R =ルール（古いスタイル、分離）;

RF =複製フィルター手順。

S =システムベーステーブル。

SN =同義語;

SQ =サービスキュー。

TA = DMLアセンブリトリガー（CLR環境）;

TR = DML SQLトリガー。

IF =組み込みのテーブル値SQL関数。

TF =テーブル値のSQL関数。

U =テーブル（ユーザー定義）;

UQ = UNIQUE制約。

V =ビュー;

X =拡張ストアドプロシージャ。

IT =内部テーブル。

sys.tablesやsys.viewsなどの他のディレクトリビューは、sys.objectsにアクセスし、特定の種類のオブジェクトに関する情報を提供します。これらのビューとOBJECTPROPERTY関数を使用すると、データベーススキーマを構成する各オブジェクトに関する膨大な情報を取得できます。

データベースファイルの場所

メインデータファイル（mdf）やトランザクションログファイル（ldf）など、選択したデータベースの物理的な場所は、これらのクエリを使用して取得できます。

 EXEC sp_Helpfile; --OR SELECT @@Servername AS Server , DB_NAME() AS DB_Name , File_id , Type_desc , Name , LEFT(Physical_Name, 1) AS Drive , Physical_Name , RIGHT(physical_name, 3) AS Ext , Size , Growth FROM sys.database_files ORDER BY File_id; GO

テーブル

もちろん、SSMSのオブジェクトエクスプローラーには、選択したデータベース内のテーブルの完全なリストが表示されますが、GUIを使用する情報の一部は、スクリプトを使用するよりも入手が困難です。 ANSI標準ではINFORMATION_SCHEMAビューが必要ですが、標準の一部ではないオブジェクト（トリガー、拡張プロシージャなど）に関する情報は提供されないため、SQL Serverカタログビューを使用することをお勧めします。

 EXEC sp_tables; -- ,      ,   --OR SELECT @@Servername AS ServerName , TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ; --OR SELECT @@Servername AS ServerName , DB_NAME() AS DBName , o.name AS 'TableName' , o.[Type] , o.create_date FROM sys.objects o WHERE o.Type = 'U' -- User table ORDER BY o.name; --OR SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS TableName, t.[Type], t.create_date FROM sys.tables t ORDER BY t.Name; GO

テーブル内のエントリの数

テーブルについて何も知らない場合、すべてのテーブルは等しく重要です。テーブルについて学習すればするほど、テーブルを条件付きで重要度が高くなり、条件付きで重要度が低くなります。一般に、膨大な数のレコードを持つテーブルは、パフォーマンスに深刻な影響を与えることがよくあります。

SSMSでは、任意のテーブルを右クリックして[ストレージ]タブのプロパティを開き、テーブル内のレコード数を確認できます。

すべてのテーブルに関するこの情報を手動で収集するのはかなり困難です。繰り返しますが、各テーブルにSELECT COUNT（*）FROM TABLENAMEを記述した場合、大量に出力する必要があります。

T-SQLを使用してスクリプトを生成する方がはるかに便利です。以下のスクリプトは、現在のデータベースの各テーブルの行数を取得するためのT-SQLステートメントのセットを生成します。実行して、結果を新しいウィンドウにコピーして実行します。

 SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.' + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + o.name + ';' AS ' Script generator to get counts for all tables' FROM sys.objects o WHERE o.[type] = 'U' ORDER BY o.name; GO

翻訳者のメモ ：リクエストはうまくいきませんでした。テーブル名にスキーマを追加しました。

 SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.' + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name + ';' AS ' Script generator to get counts for all tables' FROM sys.objects o WHERE o.[type] = 'U' ORDER BY o.name;

sp_msForEachTable

Sp_msforeachtableは、データベース内のすべてのテーブルを「通過」し、クエリを実行し、「？」に置き換えて、文書化されていない関数です。現在のテーブルの名前。データベースレベルで機能する同様の関数sp_msforeachdbもあります。

この文書化されていない機能には、オブジェクト名に特殊文字を使用するなど、いくつかの問題があります。つまりテーブルまたはデータベースの名前に「-」記号が含まれている場合、以下にリストされているストアドプロシージャは失敗します。

 CREATE TABLE #rowcount ( Tablename VARCHAR(128) , Rowcnt INT ); EXEC sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' SELECT * FROM #rowcount ORDER BY Tablename , Rowcnt; DROP TABLE #rowcount;

レコードの数を取得する最速の方法-クラスターインデックス

以前のすべてのメソッドはCOUNT（*）を使用していましたが、テーブルに50万件を超えるレコードがある場合は、徐々に機能します。

テーブル内のレコード数を取得する最速の方法は、クラスターインデックスまたはヒープ内のレコード数を取得することです。この方法は最速ですが、情報の更新に少し時間がかかるため、インデックスエントリの数とテーブル内の実際の行数に関する情報は一致しない可能性があるとMSが言っていることを覚えておいてください。ほとんどの場合、これらの値は同じか、非常に近い値であり、すぐに同じ値になります。

 --       -- Hint:   ,    SELECT @@ServerName AS Server , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName , OBJECT_NAME(p.object_id) AS TableName , i.Type_Desc , i.Name AS IndexUsedForCounts , SUM(p.Rows) AS Rows FROM sys.partitions p JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' ) -- This is key (1 index per table) AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys' GROUP BY p.object_id , i.type_desc , i.Name ORDER BY SchemaName , TableName; -- OR --     ,    DMV dm_db_partition_stats SELECT @@ServerName AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName , OBJECT_NAME(ddps.object_id) AS TableName , i.Type_Desc , i.Name AS IndexUsedForCounts , SUM(ddps.row_count) AS Rows FROM sys.dm_db_partition_stats ddps JOIN sys.indexes i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' ) -- This is key (1 index per table) AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys' GROUP BY ddps.object_id , i.type_desc , i.Name ORDER BY SchemaName , TableName; GO

ヒープの検索（クラスター化インデックスのないテーブル）

ヒープの操作は、データベースではなくフラットファイルの操作に似ています。クエリの実行時にテーブル全体のスキャンを保証する場合は、ヒープを使用します。通常、すべてのヒープテーブルに主キーを追加することをお勧めします。

 --  ( 1) SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS HeapTable , t.Create_Date FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.type_desc = 'HEAP' ORDER BY t.Name --OR --  ( 2) SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS HeapTable , t.Create_Date FROM sys.tables t WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0 ORDER BY t.Name; --OR --  ( 3) +   SELECT @@ServerName AS Server , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName , OBJECT_NAME(ddps.object_id) AS TableName , i.Type_Desc , SUM(ddps.row_count) AS Rows FROM sys.dm_db_partition_stats AS ddps JOIN sys.indexes i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id WHERE i.type_desc = 'HEAP' AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys' GROUP BY ddps.object_id , i.type_desc ORDER BY TableName;

テーブル内のアクティビティを処理します

パフォーマンスの最適化に取り組むとき、どのテーブルがアクティブに読み取られ、どのテーブルがアクティブに書き込まれるかを知ることが非常に重要です。前に、テーブルにあるレコードの数を確認しましたが、今度はレコードの書き込みと読み取りの頻度を確認します。

dmvからのこの情報は、SQL Serverを再起動するたびに消去されることに注意してください。サーバーの実行時間が長いほど、統計の信頼性が高くなります。 1週間に収集された統計よりも、30日間に収集された統計の方がはるかに自信があります。

 -- /  --   ,     --   ,      SQL Server SELECT @@ServerName AS ServerName , DB_NAME() AS DBName , OBJECT_NAME(ddius.object_id) AS TableName , SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AS Reads , SUM(ddius.user_updates) AS Writes , SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups + ddius.user_updates) AS [Reads&Writes] , ( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0 FROM master.sys.databases WHERE name = 'tempdb' ) AS SampleDays , ( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig FROM master.sys.databases WHERE name = 'tempdb' ) AS SampleSeconds FROM sys.dm_db_index_usage_stats ddius INNER JOIN sys.indexes i ON ddius.object_id = i.object_id AND i.index_id = ddius.index_id WHERE OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1 AND ddius.database_id = DB_ID() GROUP BY OBJECT_NAME(ddius.object_id) ORDER BY [Reads&Writes] DESC; GO

このクエリのより高度なバージョンは、サーバー上のすべてのデータベースのすべてのテーブルに関する情報を収集するカーソルで表されます。一般に、私はカーソルのパフォーマンスが低いため、カーソルのファンではありませんが、異なるデータベースを移動することはカーソルにとって素晴らしいアプリケーションです。

 --     --  ,     --  ,    SQL Server --           --  ,   tempdb DECLARE DBNameCursor CURSOR FOR SELECT Name FROM sys.databases WHERE Name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'distribution' ) ORDER BY Name; DECLARE @DBName NVARCHAR(128) DECLARE @cmd VARCHAR(4000) IF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL BEGIN DROP TABLE tempdb..TempResults END CREATE TABLE tempdb..TempResults ( ServerName NVARCHAR(128) , DBName NVARCHAR(128) , TableName NVARCHAR(128) , Reads INT , Writes INT , ReadsWrites INT , SampleDays DECIMAL(18, 8) , SampleSeconds INT ) OPEN DBNameCursor FETCH NEXT FROM DBNameCursor INTO @DBName WHILE @@fetch_status = 0 BEGIN ---------------------------------------------------- -- Print @DBName SELECT @cmd = 'Use ' + @DBName + '; ' SELECT @cmd = @cmd + ' Insert Into tempdb..TempResults SELECT @@ServerName AS ServerName, DB_NAME() AS DBName, object_name(ddius.object_id) AS TableName , SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AS Reads, SUM(ddius.user_updates) as Writes, SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups + ddius.user_updates) as ReadsWrites, (SELECT datediff(s,create_date, GETDATE()) / 86400.0 FROM sys.databases WHERE name = ''tempdb'') AS SampleDays, (SELECT datediff(s,create_date, GETDATE()) FROM sys.databases WHERE name = ''tempdb'') as SampleSeconds FROM sys.dm_db_index_usage_stats ddius INNER JOIN sys.indexes i ON ddius.object_id = i.object_id AND i.index_id = ddius.index_id WHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True AND ddius.database_id = db_id() GROUP BY object_name(ddius.object_id) ORDER BY ReadsWrites DESC;' --PRINT @cmd EXECUTE (@cmd) ----------------------------------------------------- FETCH NEXT FROM DBNameCursor INTO @DBName END CLOSE DBNameCursor DEALLOCATE DBNameCursor SELECT * FROM tempdb..TempResults ORDER BY DBName , TableName; --DROP TABLE tempdb..TempResults;

翻訳者注 ：リストにONLINE以外の状態のデータベースがある場合、カーソルは機能しません。

視聴回数

表現は、比較的言えば、データベースに格納されているクエリです。それらは仮想テーブルと考えることができます。データはビューに保存されませんが、クエリではテーブルと同じ方法で参照します。

SQL Serverでは、場合によってはビューを使用してデータを更新できます。読み取り専用ビューを取得するには、作成時にSELECT DISTINCTを使用できます。ビューの「スルー」データは、ビューの各行が「ベース」テーブルの1行のみに対応する場合にのみ変更できます。この基準を満たさない表現、つまり複数のテーブル上に構築されるか、グループ化、集計関数、および計算を使用して、読み取り専用になります。

 SELECT @@Servername AS ServerName , DB_NAME() AS DBName , o.name AS ViewName , o.[Type] , o.create_date FROM sys.objects o WHERE o.[Type] = 'V' -- View ORDER BY o.NAME --OR SELECT @@Servername AS ServerName , DB_NAME() AS DBName , Name AS ViewName , create_date FROM sys.Views ORDER BY Name --OR SELECT @@Servername AS ServerName , TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' ORDER BY TABLE_NAME --OR -- CREATE VIEW Code SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , o.name AS 'ViewName' , o.Type , o.create_date , sm.[DEFINITION] AS 'View script' FROM sys.objects o INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID WHERE o.Type = 'V' -- View ORDER BY o.NAME; GO

同義語

私のキャリアの中で何度か、クエリがアクセスしているテーブルを理解できない状況に遭遇しました。単純なSELECT * FROMクライアントクエリを送信します。 Clientというテーブルを探していますが、見つかりません。まあ、私はそれがこのビューであるに違いないと思う、私はクライアントと呼ばれるビューを探していますが、それを見つけることができません。データベースが間違っていたのでしょうか？その結果、Clientは顧客の同義語であり、テーブルは実際にはCustomerと呼ばれています。マーケティング部門は、このテーブルをクライアントとして参照することを望んでいたため、同義語が作成されました。幸いなことに、同義語を使用することはまれですが、準備ができていない場合は訴訟が困難になる可能性があります。

 SELECT @@Servername AS ServerName , DB_NAME() AS DBName , o.name AS ViewName , o.Type , o.create_date FROM sys.objects o WHERE o.[Type] = 'SN' -- Synonym ORDER BY o.NAME; --OR --     SELECT @@Servername AS ServerName , DB_NAME() AS DBName , s.name AS synonyms , s.create_date , s.base_object_name FROM sys.synonyms s ORDER BY s.name; GO

ストアドプロシージャ

ストアドプロシージャは、単一の実行プランにコンパイルされるスクリプトのグループです。カタログ表現を使用して、作成されるKP、実行されるアクション、およびテーブルを決定できます。

 --   SELECT @@Servername AS ServerName , DB_NAME() AS DBName , o.name AS StoredProcedureName , o.[Type] , o.create_date FROM sys.objects o WHERE o.[Type] = 'P' -- Stored Procedures ORDER BY o.name --OR --     SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , o.name AS 'ViewName' , o.[type] , o.Create_date , sm.[definition] AS 'Stored Procedure script' FROM sys.objects o INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id WHERE o.[type] = 'P' -- Stored Procedures -- AND sm.[definition] LIKE '%insert%' -- AND sm.[definition] LIKE '%update%' -- AND sm.[definition] LIKE '%delete%' -- AND sm.[definition] LIKE '%tablename%' ORDER BY o.name; GO

WHEREに単純な条件を追加すると、たとえばINSERT操作を実行するストアドプロシージャに関する情報のみを取得できます。

 WHERE o.[type] = 'P' -- Stored Procedures AND sm.definition LIKE '%insert%' ORDER BY o.name …

WHEREの条件を少し変更することにより、特定のテーブルを更新、削除、またはアクセスするプロセッサーに関する情報を収集できます。

機能

関数はSQL Serverに格納され、いくつかのパラメーターを取得して特定のアクションまたは計算を実行し、結果を返します。

 --  SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , o.name AS 'Functions' , o.[Type] , o.create_date FROM sys.objects o WHERE o.Type = 'FN' -- Function ORDER BY o.NAME; --OR --     SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , o.name AS 'FunctionName' , o.[type] , o.create_date , sm.[DEFINITION] AS 'Function script' FROM sys.objects o INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID WHERE o.[Type] = 'FN' -- Function ORDER BY o.NAME; GO

トリガー

トリガーは、このトリガーが属するテーブルで特定のアクションに応じて実行される一種のストアドプロシージャです。たとえば、INSERT、UPDATE、およびDELETEトリガーを作成できます。

 --  SELECT @@Servername AS ServerName , DB_NAME() AS DBName , parent.name AS TableName , o.name AS TriggerName , o.[Type] , o.create_date FROM sys.objects o INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id WHERE o.Type = 'TR' -- Triggers ORDER BY parent.name , o.NAME --OR SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , Parent_id , name AS TriggerName , create_date FROM sys.triggers WHERE parent_class = 1 ORDER BY name; --OR --     SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , OBJECT_NAME(Parent_object_id) AS TableName , o.name AS 'TriggerName' , o.Type , o.create_date , sm.[DEFINITION] AS 'Trigger script' FROM sys.objects o INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID WHERE o.Type = 'TR' -- Triggers ORDER BY o.NAME; GO

チェック制限

CHECK制約は、データベースにビジネスロジックを実装する良い方法です。たとえば、一部のフィールドは正または負である必要があります。または、1つの列の日付が他の列の日付よりも大きい必要があります。

 -- Check Constraints SELECT @@Servername AS ServerName , DB_NAME() AS DBName , parent.name AS 'TableName' , o.name AS 'Constraints' , o.[Type] , o.create_date FROM sys.objects o INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id WHERE o.Type = 'C' -- Check Constraints ORDER BY parent.name , o.name --OR --CHECK constriant definitions SELECT @@Servername AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName , OBJECT_NAME(parent_object_id) AS TableName , parent_column_id AS Column_NBR , Name AS CheckConstraintName , type , type_desc , create_date , OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition FROM sys.Check_constraints ORDER BY TableName , SchemaName , Column_NBR GO

データモデルの詳細

以前は、データベースを構成するオブジェクトの「トップレベル」のアイデアを提供するスクリプトを使用しました。カラム、それらのデータ型、与えられたデフォルト値、存在する（存在するべき）キーおよびインデックスなど、テーブルに関するデータをさらに取得する必要がある場合があります。

このセクションで提示されるクエリは、既存のデータモデルのエンジニアリングをほぼ逆にする手段を提供します。

列

次のスクリプトは、データベース全体のテーブルと列について説明しています。このクエリの結果はExcelにコピーできます。Excelでフィルターと並べ替えを構成し、データベースで使用されるデータ型をよく見ることができます。また、同じ名前で異なるデータ型の列を見てください。

 SELECT @@Servername AS Server , DB_NAME() AS DBName , isc.Table_Name AS TableName , isc.Table_Schema AS SchemaName , Ordinal_Position AS Ord , Column_Name , Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX) Is_Nullable , Column_Default , Table_Type FROM INFORMATION_SCHEMA.COLUMNS isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name -- WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View' ORDER BY DBName , TableName , SchemaName , Ordinal_position; --      --         / SELECT @@Servername AS Server , DB_NAME() AS DBName , Column_Name , Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length , COUNT(*) AS Count FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' GROUP BY Column_Name , Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length; --      SELECT @@Servername AS ServerName , DB_NAME() AS DBName , Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length AS [Length] , COUNT(*) AS COUNT FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' GROUP BY Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length ORDER BY Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length -- Large object data types or Binary Large Objects(BLOBs) -- ,            "online" SELECT @@Servername AS ServerName , DB_NAME() AS DBName , isc.Table_Name , Ordinal_Position AS Ord , Column_Name , Data_Type AS BLOB_Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length AS [Length] FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' AND ( Data_Type IN ( 'text', 'ntext', 'image', 'XML' ) OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' ) AND Character_Maximum_Length = -1 ) ) -- varchar(max), nvarchar(max), varbinary(max) ORDER BY isc.Table_Name , Ordinal_position;

デフォルト値

デフォルト値は、挿入中に列に値が指定されていない場合に保存される値です。多くの場合、日付を格納する列にはget_date（）が設定されます。また、監査にはデフォルト値が使用されます。特定のアクションを実行したユーザーアカウントを判別するためにsystem_userが挿入されます。

 -- Table Defaults SELECT @@Servername AS ServerName , DB_NAME() AS DBName , parent.name AS TableName , o.name AS Defaults , o.[Type] , o.Create_date FROM sys.objects o INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id WHERE o.[Type] = 'D' -- Defaults ORDER BY parent.name , o.NAME --OR -- Column Defaults SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName , OBJECT_NAME(parent_object_id) AS TableName , parent_column_id AS Column_NBR , Name AS DefaultName , [type] , type_desc , create_date , OBJECT_DEFINITION(object_id) AS Defaults FROM sys.default_constraints ORDER BY TableName , Column_NBR --OR -- Column Defaults SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName , t.Name AS TableName , c.Column_ID AS Ord , c.Name AS Column_Name , OBJECT_NAME(default_object_id) AS DefaultName , OBJECT_DEFINITION(default_object_id) AS Defaults FROM sys.Tables t INNER JOIN sys.columns c ON t.object_id = c.object_id WHERE default_object_id <> 0 ORDER BY TableName , SchemaName , c.Column_ID GO

計算列

計算列とは、原則として、テーブルの他の列の値に基づいて値が計算される列です。

 --   SELECT @@Servername AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(object_id) AS SchemaName , OBJECT_NAME(object_id) AS Tablename , Column_id , Name AS Computed_Column , [Definition] , is_persisted FROM sys.computed_columns ORDER BY SchemaName , Tablename , [Definition]; --Or -- Computed Columns SELECT @@Servername AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName, t.Name AS TableName , c.Column_ID AS Ord , c.Name AS Computed_Column FROM sys.Tables t INNER JOIN sys.Columns c ON t.object_id = c.object_id WHERE is_computed = 1 ORDER BY t.Name , SchemaName , c.Column_ID GO

ID列

IDENTITY列には、システムによって一意の値が自動的に入力されます。一般に、レコードのシーケンス番号をテーブルに保存するために使用されます。

 SELECT @@Servername AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(object_id) AS SchemaName , OBJECT_NAME(object_id) AS TableName , Column_id , Name AS IdentityColumn , Seed_Value , Last_Value FROM sys.identity_columns ORDER BY SchemaName , TableName , Column_id; GO

キーとインデックス

前に書いたように、テーブルに主キーとそれに対応するインデックスを持つことは、ベストプラクティスの1つです。別のベストプラクティスは、外部キーに含まれる列に外部キーのインデックスも構築することです。外部キーインデックスは、テーブルの結合に最適です。これらのインデックスは、レコードを削除するときのパフォーマンスにも良い影響を与えます。

どんなインデックスがありますか？

現在のデータベースのすべてのテーブルのすべてのインデックスを検索するスクリプト。

 SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , o.Name AS TableName , i.Name AS IndexName FROM sys.objects o INNER JOIN sys.indexes i ON o.object_id = i.object_id WHERE o.Type = 'U' -- User table AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes ORDER BY o.NAME , i.name; GO

欠落しているインデックスは何ですか？

SQL Serverは、以前に実行されたクエリに基づいて、データベース内の欠落しているインデックスに関する情報を提供します。作成すると、生産性が向上します。

盲目的にこれらのインデックスを追加しないでください。提案された各インデックスについて考えます。たとえば、含まれている列を使用すると、ボリュームが大幅に増加するので便利です。

 --    DMV SELECT @@ServerName AS ServerName , DB_NAME() AS DBName , t.name AS 'Affected_table' , ( LEN(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END, ',', '')) ) + 1 AS K , COALESCE(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + COALESCE(ddmid.inequality_columns, '') AS Keys , COALESCE(ddmid.included_columns, '') AS [include] , 'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(20)) + ' On ' + ddmid.[statement] COLLATE database_default + ' (' + ISNULL(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(ddmid.inequality_columns, '') + ')' + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement , ddmigs.user_seeks , ddmigs.user_scans , CAST(( ddmigs.user_seeks + ddmigs.user_scans ) * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' , avg_user_impact , ddmigs.last_user_seek , ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds FROM sys.databases WHERE name = 'tempdb' ) SecondsUptime FROM sys.dm_db_missing_index_groups ddmig INNER JOIN sys.dm_db_missing_index_group_stats ddmigs ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details ddmid ON ddmig.index_handle = ddmid.index_handle INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID WHERE ddmid.database_id = DB_ID() ORDER BY est_impact DESC; GO

外部キー

外部キーはテーブル間の関係を定義し、参照整合性を制御するために使用されます。エンティティ関係図では、テーブル間の線は外部キーを示します。

 -- Foreign Keys SELECT @@Servername AS ServerName , DB_NAME() AS DB_Name , parent.name AS 'TableName' , o.name AS 'ForeignKey' , o.[Type] , o.Create_date FROM sys.objects o INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id WHERE o.[Type] = 'F' -- Foreign Keys ORDER BY parent.name , o.name --OR SELECT f.name AS ForeignKey , SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName , OBJECT_NAME(f.parent_object_id) AS TableName , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName , SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName , OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id ORDER BY TableName , ReferenceTableName; GO

外部キーインデックスがありません

先ほど言ったように、外部キーに含まれる列にインデックスを構築することが望ましいです。これにより、通常は外部キーで結合されているテーブルの結合が大幅に高速化されます。これらのインデックスは、削除操作も大幅に高速化します。そのようなインデックスがない場合、SQL Serverは、「最初の」テーブルからレコードが削除されるたびに、リンクされたテーブルのテーブルスキャンを実行します。

 -- Foreign Keys missing indexes -- ,            --  ,      ,   SELECT DB_NAME() AS DBName , rc.Constraint_Name AS FK_Constraint , -- rc.Constraint_Catalog AS FK_Database, -- rc.Constraint_Schema AS FKSch, ccu.Table_Name AS FK_Table , ccu.Column_Name AS FK_Column , ccu2.Table_Name AS ParentTable , ccu2.Column_Name AS ParentColumn , I.Name AS IndexName , CASE WHEN I.Name IS NULL THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''') AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON);' ELSE '' END AS SQL FROM information_schema.referential_constraints RC JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME LEFT JOIN sys.columns c ON ccu.Column_Name = C.name AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID) LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID AND c.column_id = ic.column_id AND index_column_id = 1 -- index found has the foreign key -- as the first column LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID AND ic.index_Id = i.index_Id WHERE I.name IS NULL ORDER BY FK_table , ParentTable , ParentColumn; GO

依存関係

状況によって異なります...前にこの表現を聞いたことがあると思います。データベースの依存関係を「リバースエンジニアリング」するための3つの異なる方法を検討します。最初の方法は、sp_msdependeciesストアドプロシージャを使用することです。2番目は、外部キーに関連付けられたシステムテーブルです。3番目の方法は、CTEを使用することです。

sp_msdependencies

Sp_msdependenciesは、複雑なテーブルの相互依存関係の解析に非常に役立つドキュメント化されていないストアドプロシージャです。

 EXEC sp_msdependencies '?' -- Displays Help sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd name: name or null (all objects of type) type: type number (see below) or null if both null, get all objects in database flags is a bitmask of the following values: 0x10000 = return multiple parent/child rows per object 0x20000 = descending return order 0x40000 = return children instead of parents 0x80000 = Include input object in output result set 0x100000 = return only firstlevel (immediate) parents/children 0x200000 = return only DRI dependencies power(2, object type number(s)) to return in results set: 0 (1 - 0x0001) - UDF 1 (2 - 0x0002) - system tables or MS-internal objects 2 (4 - 0x0004) - view 3 (8 - 0x0008) - user table 4 (16 - 0x0010) - procedure 5 (32 - 0x0020) - log 6 (64 - 0x0040) - default 7 (128 - 0x0080) - rule 8 (256 - 0x0100) - trigger 12 (1024 - 0x0400) - uddt shortcuts: 29 (0x011c) - trig, view, user table, procedure 448 (0x00c1) - rule, default, datatype 4606 (0x11fd) - all but systables/objects 4607 (0x11ff) – all

sp_msdependenciesを使用してすべての依存関係を導出すると、Type、ObjName、Owner（Schema）、Sequenceの4つの列が得られます。

シーケンス番号（シーケンス）に注意してください-1から始まり、順次増加します。シーケンスは、依存関係の「シリアル番号」です。

非常に大きなデータベースでアーカイブまたは削除を実行する必要があるときに、この方法を数回使用しました。テーブルの依存関係がわかっている場合、「ロードマップ」があります。データをアーカイブまたは削除するには、どの順序で必要ですか。[シーケンス]列の値が最も高いテーブルから開始し、逆の順序（大きいものから小さいものへ）で移動します。同じSequence値を持つテーブルは同時に削除できます。このメソッドは外部キーの制限に違反せず、制約を一時的に削除および再構築せずにレコードを転送/削除できます。

 EXEC sp_msdependencies NULL --     EXEC sp_msdependencies NULL, 3 --

SSMSでは、テーブル名を右クリックすると、「依存関係の表示」と「TABLENAMEに依存するオブジェクト」を選択できます。

この情報は次の方法でも取得できます。

 -- sp_MSdependencies —    -- ,      EXEC sp_msdependencies N'Sales.Customer',null, 1315327 -- Change Table Name

SSMSの依存関係を表示するウィンドウで、[TABLENAMEに依存するオブジェクト]を選択し、すべてのレベルを展開すると、次の

ように表示されます。sp_msdependenciesによって同じ情報が返されます。

 -- sp_MSdependencies -   -- ,      EXEC sp_MSdependencies N'Sales.Customer', NULL, 266751 -- Change Table Name

また、SSMSでは、選択したテーブルが依存するオブジェクトを確認できます。

msdependenciesを使用した次のクエリは、同じ情報を返します。

 -- ,      EXEC sp_MSdependencies N'Sales.Customer', null, 1053183 -- Change Table

テーブルの依存関係のリストが必要な場合は、一時テーブルを使用して、タイプごとに依存関係をフィルタリングできます。

 CREATE TABLE #TempTable1 ( Type INT , ObjName VARCHAR(256) , Owner VARCHAR(25) , Sequence INT ); INSERT INTO #TempTable1 EXEC sp_MSdependencies NULL SELECT * FROM #TempTable1 WHERE Type = 8 --Tables ORDER BY Sequence , ObjName DROP TABLE #TempTable1;

システムカタログビューへのリクエスト

データベースの依存関係を「リバースエンジニアリング」する2番目の方法は、外部キーに関連付けられたディレクトリシステム表現を照会することです。

 --Independent tables SELECT Name AS InDependentTables FROM sys.tables WHERE object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) -- Check for parents AND object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) -- Check for Dependents ORDER BY Name -- Tables with dependencies. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS ParentTable , OBJECT_NAME(parent_object_id) AS DependentTable , OBJECT_NAME(constraint_object_id) AS ForeignKeyName FROM sys.foreign_key_columns ORDER BY ParentTable , DependentTable -- Top level of the pyramid tables. Tables with no parents. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS TablesWithNoParent FROM sys.foreign_key_columns WHERE referenced_object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) ORDER BY 1 -- Bottom level of the pyramid tables. -- Tables with no dependents. (These are the leaves on a tree.) SELECT DISTINCT OBJECT_NAME(parent_object_id) AS TablesWithNoDependents FROM sys.foreign_key_columns WHERE parent_object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) ORDER BY 1 -- Tables with both parents and dependents. -- Tables in the middle of the hierarchy SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS MiddleTables FROM sys.foreign_key_columns WHERE referenced_object_id IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) AND parent_object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) ORDER BY 1; -- in rare cases, you might find a self-referencing dependent table. -- Recursive (self) referencing table dependencies. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS ParentTable , OBJECT_NAME(parent_object_id) AS ChildTable , OBJECT_NAME(constraint_object_id) AS ForeignKeyName FROM sys.foreign_key_columns WHERE referenced_object_id = parent_object_id ORDER BY 1 , 2;

CTEを使用する

依存関係の階層を取得する3番目の方法は、再帰CTEを使用することです。

 -- How to find the hierarchical dependencies -- Solve recursive queries using Common Table Expressions (CTE) WITH TableHierarchy ( ParentTable, DependentTable, Level ) AS ( -- Anchor member definition (First level group to start the process) SELECT DISTINCT CAST(NULL AS INT) AS ParentTable , e.referenced_object_id AS DependentTable , 0 AS Level FROM sys.foreign_key_columns AS e WHERE e.referenced_object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) -- Add filter dependents of only one parent table -- AND Object_Name(e.referenced_object_id) = 'User' UNION ALL -- Recursive member definition (Find all the layers of dependents) SELECT --Distinct e.referenced_object_id AS ParentTable , e.parent_object_id AS DependentTable , Level + 1 FROM sys.foreign_key_columns AS e INNER JOIN TableHierarchy AS d ON ( e.referenced_object_id ) = d.DependentTable ) -- Statement that executes the CTE SELECT DISTINCT OBJECT_NAME(ParentTable) AS ParentTable , OBJECT_NAME(DependentTable) AS DependentTable , Level FROM TableHierarchy ORDER BY Level , ParentTable , DependentTable;

おわりに

したがって、1〜2時間で、上記の「リバースエンジニアリング」の方法を使用して、データベースの内部を把握できます。

翻訳者注：テキスト内のすべてのクエリ（1つを除き、テキスト内にマークされています）は、SQL Server 2005 SP3以降のエディションで動作します。テキストはかなりボリュームがあります。可能な限り差し引いてミス（文体、構文、セマンティックなど）を見つけようとしましたが、何かに気がつかなかったら、おそらく気づかなかったので、PMを書いてください。

T-SQLを使用したデータベースの探索