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

發行項
08/18/2016

SQL 問答集擴大資料庫，使用 IFilters，以及從遠端連線

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 必須等候資料或記錄檔擴大的話，肯定會折損效能，也會影響回應時間。資料檔案預設的成長幅度是 1MB，而記錄檔則是 10%，這種成長幅度對忙碌的系統來說可能不夠。此外，仰賴自動成長可能會導致磁碟分散，因為資料或記錄檔在磁碟上並不是連續的，也就是說，回應時間可能會比需要的更長，因為資料實際上是四散在磁碟上。

達到良好效能的關鍵，是一開始就為資料和記錄檔主動配置足夠的檔案空間。而配置足夠的檔案空間通常需要進行一些趨勢分析和成長預測，這麼做也可以提高效能，因為檔案在磁碟上是連續的，可以避免自動成長在尖峰時段所耗費的 I/O 成本。自動成長通常應該保持在啟用狀態，因為資料或記錄檔若是全滿的話，會完全阻礙資料庫的存取。但是要記住，自動程式應該被視為安全網，而不是當作管理資料庫的一項功能。

定期擴大資料庫檔案能免則免，因為這也會導致磁碟上的非連續檔案，進而折損效能。不過您可以進行主動監視，定期執行指令碼來判斷每個資料庫的可用空間百分比 (由 SQL Agent 工作執行)，然後採取行動 (例如傳送電子郵件警示或透過 Database Mail)。圖中程式碼所提供的範例程式碼，就在說明如何收集目前資料庫的可用空間百分比。

萬一產生警示，則可以使用 ALTER DATABASE 命令編寫指令碼一次擴大檔案，再以 SQL Agent 工作，將這個動作排在尖峰時段以外的時間進行。您可以將檔案擴大到足以供可預見的未來使用，以避免檔案在未來少量遞次擴大。同時，最好能夠確定所有資料庫上都沒有啟用自動壓縮 — 因為自動壓縮可能會產生不必要的資料庫壓縮或成長週期。

—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 和影像資料行，在資料庫裡面儲存了不同格式的檔案。我聽說 SQL Server 有整合的功能可以讓我搜尋這些不同的檔案格式。我要如何設定 SQL Server 這麼做？

答：這項功能是內建在全文檢索服務中。這個服務提供了使用 IFilter 介面的彈性，因此能夠開發和載入從專利資料擷取實用資訊的篩選器。其他產品 (像是 Microsoft® Office SharePoint® Server) 也可以使用這些 IFilter，來收集編目檔案的相關資訊。

IFilter 是由檔案格式的建立者或是協力廠商所提供。SQL Server 已經含有一些在安裝 FulltextService (FTS) 時所載入的 Ifilter (其中包括 HTML 和 DOC 檔案的篩選器)，不過必要時也可以另外加入其他的 IFilter。例如，Adobe 網站上就提供了 Adobe PDF 用的篩選器，2007 年底也發行了 2007 Office System 延伸模組用的新篩選器套件。但是您必須知道您所需要的是哪一個版本的 Ifilter。比方說，針對 32 位元系統設計的 Ifilter，就不能用於 64 位元的 SQL Server 安裝版本。

在用戶端上執行安裝封裝後，一般會在 OS 的生態系統內註冊 IFilter。在 OS 註冊位元之後，必須再執行幾個步驟，讓 FTS 能夠載入 IFilter。當您啟動查詢執行工具之後，請發出下列命令：

sp_fulltext_service 'load_os_resources',1 (這個陳述式會讓 FTS 載入註冊的位元以進行處理，其中包括像是文字分隔和字幹等元件)。
sp_fulltext_service'verify_signature',0 (此處 SQL Server 不會檢查使用的篩選器是否經過簽署，因為很多廠商都不根據標準簽署他們的篩選器)。
重新啟動 SQL Server 執行個體和 FTS 的執行個體。
在下面兩種資料行上建立全文檢索：將二進位資料列作為供 IFilter 編目的內容的資料行，以及讓 SQL Server 選擇要將哪個篩選器的內容重新導向哪個篩選器的延伸資料行 (表示含延伸類型的資料行，例如 DOCX)。

如需其他詳細資訊，請參閱 go.microsoft.com/?linkid=7912971。

—Jens Suessmeyer

問：我連不上遠端 SQL 伺服器。我需要在用戶端還是伺服器電腦上設定防火牆呢？

答：SQL Server 2005 遠端連線失敗的原因有很多，而防火牆組態只是最常見的問題之一。SQL 通訊協定部落格 (blogs.msdn.com/sql_protocols) 是尋找有關進行 SQL 連線資訊的絕佳資源。

SQL Server 2005 的預設安裝並不允許遠端連線。請在執行 SQL 的電腦上，從 [開始] 功能表選取 [Microsoft SQL Server 2005] | [組態工具] | [SQL Server 介面區組態]。再從此處前往 [服務和連接的介面區組態]、選取 [遠端連線]，然後選取 [使用 TCP/IP 和具名管道] 選項按鈕。接下來必須重新啟動 SQL，讓變更生效。

根據預設，SQL Server 會使用連接埠 1433。若要確認此連接埠處於開啟狀態，請使用下面的 telnet 命令，把 <ipaddress> 換成執行 SQL Server 之電腦的實際 IP 位址：

telnet <ipaddress> 1433

如果您收到連線失敗的回應，請開啟 Windows® 防火牆、前往 [例外狀況] 索引標籤、選取 [新增連接埠]，然後新增 TCP 連接埠 1433。這樣 Telnet 應該就能順利運作了 (請注意，Windows Vista® 預設並不會安裝 Telnet)。

—Rick Anderson

感謝下列 SQL Server 專家對本專欄的貢獻：
Justin Langford 服務於 Coeo Ltd.，這是一家位於英國的系統整合廠商和微軟認證夥伴 (Microsoft Certified Partner)。Jens Suessmeyer 是德國 Microsoft 的資料庫顧問。Rick Anderson 任職於 Microsoft 的開發人員使用者教育 (Developer User Education) 小組。Saleem Hakani 在 Microsoft 擔任資深資料庫工程師兼 SQL Server 社群組長。

Share via

其他資源