SQL Q&A: Grow Databases, Use IFilters, and Connect Remotely

[アーティクル]
08/25/2016

SQL に関する Q&Aデータベースの拡張、IFilter の使用、およびリモート接続

Edited by Matthew Graven

ヒント : キャッシュをクリアする

テスト環境では問題なく動作するのに、運用環境の SQL Server に展開するとパフォーマンスが低下するストアドプロシージャについて疑問を感じたことはありませんか。その原因はキャッシュと関連がある可能性があります。ストアドプロシージャを運用環境に展開する前に、テスト環境で、キャッシュされているクエリプランをクリアしてからテストを実行して、ストアドプロシージャに関するデータがキャッシュされていない環境で実行した場合のストアドプロシージャの動作を確認してください。キャッシュをクリアする場合に覚えておくと重宝するものを以下にご紹介します。

SQL Server のプロシージャキャッシュをクリアするコマンド

DBCC FREEPROCCACHE
Go

キャッシュされているすべてのプランを一覧表示するクエリ

Select * from sys.dm_exec_cached_plans
Go

質問 – 日中のトラフィック量が多いデータベースがあるのですが、トラフィックのピーク時に SQL Server® が自動拡張を実行するとタイムアウトになるおそれがあるので、自動拡張は使用していません。一定の割合の領域が使用されたときに、このデータベースファイルを拡張する、定期実行ジョブの実装を考えています。このような方法はありますか。

回答 - ファイルの拡張には集中的なディスク I/O 操作が伴います。また、SQL Server でデータファイルまたはログファイルの拡張を待機する必要が生じると、パフォーマンスと応答時間に確実に影響します。既定の拡張増分値は、データファイルでは 1 MB、ログファイルでは 10% ですが、アクセスの多いシステムでは、この拡張増分値は不十分です。また、自動拡張に依存していると、データファイルまたはログファイルのデータが隣接するディスクに格納されないので、ディスクの断片化の原因になります。つまり、データがディスク上で物理的に分散しているので、応答時間が必要以上に長くなることがあります。

高いパフォーマンスを実現するための鍵は、データファイルとログファイルに、十分なファイル領域を事前に割り当てることです。十分な領域を事前に割り当てるには、なんらかの傾向分析と増分の予測を行う必要がありますが、事前に領域を割り当てると、ファイルのデータがディスク上で連続するようになり、ピーク時間に自動拡張を行った場合に必要になる I/O コストを回避できるので、パフォーマンスが向上します。データファイルやログファイルが最大サイズに達すると、データベースへのアクセスが妨げられるので、多くの場合、自動拡張は有効にすることをお勧めします。しかし、自動拡張はデータベースを管理するための機能というよりも、重大な障害を避けるための保険であることを覚えておいてください。

定期的にデータベースファイルを拡張するように設定すると、ファイルのデータがディスク上で連続しなくなり、パフォーマンスの低下を招きます。そのため、定期的にデータベースファイルを拡張する設定は使用しないことをお勧めします。各データベースの空き領域の割合を判断するスクリプトを (SQL エージェントジョブを使用して) 定期的に実行し、対策をとる (たとえば、データベースメールを使用してメールで通知を送信する) ことによって積極的な監視を実現できます。現在のデータベースの空き領域の割合を集計して表示するサンプルスクリプトを図に示します。

警告が生成されたら、ALTER DATABASE コマンドを一時的に実行するようなスクリプトを作成し、SQL エージェントジョブを使用して、ピーク時を避けてこの処理を実行するように設定できます。今後の拡張の見込みに対して十分なサイズまでファイルを拡張し、少しずつファイルを拡張する必要が生じないようにします。自動圧縮が設定されていると、データベースで不必要に拡張と縮小が繰り返し行われる可能性があるので、自動圧縮を設定しているデータベースがないことを確認することもお勧めします。

- Justin Langford

データベースの空き領域の確定

-- Script to gather size, free space and 
-- calculate % free space for current 
-- USER database
DECLARE @size DEC(15,2)
DECLARE @free DEC(15,2)
DECLARE @result DEC(15,2)

SELECT @size = SUM(size)*1.0/128
FROM sys.database_files

SELECT @free = 
(SUM(unallocated_extent_page_count)*1.0/128)
FROM sys.dm_db_file_space_usage

PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)
PRINT 'Free Space ' + 
CONVERT(VARCHAR(15), @free)

SELECT @result = (@free/@size)*100

PRINT '% Free Space ' + 
CONVERT(VARCHAR(15), @result)

質問 – 私の会社では、varbinary 列と image 列を使用して、形式の異なるファイルをデータベースに格納しています。SQL Server では、このように形式の異なるファイルを検索する機能が導入されたと聞きましたが、その機能を使用できるように SQL Server を設定する方法を教えてください。

回答 – その機能は、フルテキストインデックスサービスに組み込まれています。このサービスは、IFilter インターフェイスを使用する柔軟性を備えているので、組織が所有しているデータから有用な情報を抽出するフィルタを開発して読み込むことができます。この IFilter は、Microsoft® Office SharePoint® Server などの他の製品でも、クロールされたファイルに関する情報を収集する場合に使用できます。

IFilter は、そのファイル形式の作成者、またはサードパーティベンダのいずれかから提供されます。SQL Server には、FulltextService (FTS) がインストールされた段階で読み込まれる IFilter がいくつかあります。これには、HTML ファイルや DOCファイルに対応するフィルタなどがあります。ただし、必要に応じて IFilter を追加することができます。たとえば、Adobe PDF 用のフィルタは Adobe 社の Web サイトで提供されています。2007 Office system の拡張機能として、新しいフィルタパックが 2007 年の終わりに公開されました。IFilter のバージョンを確認する必要があることに注意してください。たとえば、32 ビットシステム用の IFilter は、64 ビット版の SQL Server では動作しません。

通常、クライアントでインストールパッケージを実行すると、IFilter は OS のエコシステムに登録されます。FTS で IFilter を読み込めるようにするには、OS に BITS が登録された後に、いくつかの手順を実行する必要があります。クエリ実行ツールを起動して、次のコマンドを実行します。

sp_fulltext_service 'load_os_resources',1 (このステートメントによって、FTS が登録された BITS を読み込んで、ワードブレーカやステマなどのコンポーネントを含む処理を行います)。
sp_fulltext_service 'verify_signature',0 (標準に従ってフィルタには署名をしていないベンダが多いので、このステートメントによって、使用しているフィルタが署名されているかどうかを SQL Server がチェックするのをバイパスします)。
SQL Server のインスタンスと FTS のインスタンスを再起動します。
IFilter でクロールするコンテンツをバイナリ列として保持している列と、コンテンツのリダイレクト先をフィルタ選択するのに使用するフィルタを SQL Server が選択するのに使用する拡張列 (つまり、DOCX などの拡張子を含む列) にフルテキストインデックスを作成します。

詳細については、go.microsoft.com/fwlink/?LinkId=7912971 を参照してください。

- Jens Suessmeyer

質問 – SQL Server にリモート接続できません。クライアントコンピュータまたはサーバーコンピュータのファイアウォールを構成する必要がありますか。

SQL Server 2005 へのリモート接続ができない理由は複数考えられますが、ファイアウォールの構成は最も一般的な問題の 1 つです。SQL Protocols blog (blogs.msdn.com/sql_protocols) では、SQL 接続に関するさまざまな情報が提供されているので、参照することをお勧めします。

SQL Server 2005 の既定のインストールでは、リモート接続は許可されていません。SQL Server を実行しているコンピュータで、[スタート] ボタンをクリックし、[Microsoft SQL Server 2005]、[構成ツール]、[SQL Server セキュリティ構成] の順にクリックします。表示されたウィンドウで [サービスと接続のセキュリティ構成] をクリックし、[リモート接続] および [TCP/IP および名前付きパイプを使用する] を選択します。この変更を反映するには、SQL Server コンピュータを再起動する必要があります。

既定では、SQL Server はポート 1433 を使用します。ポートが開いていることを確認するには、次の telnet コマンドを使用してください。コマンドの実行時には、<ipaddress> の部分は SQL Server を実行しているコンピュータの実際の IP アドレスに置き換えます。

telnet <ipaddress> 1433

接続に失敗した場合は、Windows® ファイアウォールを開き、[例外] タブで [ポートの追加] を選択し、TCP ポート 1433 を追加しますこの設定を行うと telnet コマンドは成功するようになります (Windows Vista® では telnet が既定でインストールされていないことに注意してください)。

- Rick Anderson

このコラムに貢献してくれた SQL Server の専門家に感謝します。
Justin Langford は、Microsoft Certified Partner として認定されているシステムインテグレータである英国の企業 Coeo Ltd. に勤務しています。Jens Suessmeyer はマイクロソフトのドイツ法人に所属するデータベースコンサルタントです。Rick Anderson はマイクロソフトの Developer User Education チームに所属しています。Saleem Hakani はマイクロソフトでシニアデータベースエンジニアおよび SQL Server コミュニティリードを務めています。

その他のリソース