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

项目
12/07/2015

SQL 问题与解答数据库增长、使用 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 必须等待数据或日志文件扩展，则肯定会严重影响性能和响应时间。数据文件的默认增量是 1MB，日志文件的默认增量是 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 已包含一些 Ifilter（它们是在安装 FulltextService (FTS) 时加载的）— 包括用于 HTML 和 DOC 文件的筛选器。但是，还可以根据需要添加更多 IFilter。例如，可在 Adobe 网站上找到用于 Adobe PDF 的筛选器，并且 2007 年年底时还发布了用于 2007 Office 系统扩展的新筛选器包。请注意：必须了解需要哪个版本的 IFilter。例如，针对 32 位系统设计的 IFilter 无法用于 64 位的 SQL Server。

在客户端上运行安装程序软件包之后，IFilter 通常会在操作系统体系中注册。在操作系统中注册了位后，需执行以下几个步骤才能使 FTS 可加载 IFilter。启动查询执行工具后，运行以下命令：

sp_fulltext_service 'load_os_resources',1.（此语句将使 FTS 加载待处理的注册位，包括如断字器和词干分析器之类的组件。）
sp_fulltext_service 'verify_signature',0.（此语句将跳过 SQL Server，以检查所用筛选器是否已经过签名，因为许多供应商都未按标准对其筛选器进行签名。）
重新启动 SQL Server 实例和 FTS 实例。
针对列创建全文索引，将 binary 列作为 IFilter 的爬网内容，并且 SQL Server 使用 extension 列（表示存储扩展类型的列，如 DOCX）来选择将内容重定向到其中的筛选器。

更多信息，请访问 go.microsoft.com/?linkid=7912971。

—Jens Suessmeyer

问：我无法连接到远程 SQL Server。我是否需要在客户端或服务器上配置防火墙？

答：无法远程连接 SQL Server 2005 的原因多种多样，但防火墙配置是最常见的问题之一。SQL 协议博客 (blogs.msdn.com/sql_protocols) 提供了有关建立 SQL 连接的大量信息资源。

SQL Server 2005 的默认安装不允许远程连接。在运行 SQL 的机器上，从“开始”菜单上选择 Microsoft SQL Server 2005 |“配置工具”|“SQL Server 外围应用配置器”。然后，转到“服务和连接的外围应用配置器”，选择“远程连接”，并选中“同时使用 TCP/IP 和 named pipes”单选按钮。要使更改生效，必须重新启动 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 认证合作伙伴。Jens Suessmeyer 是 Microsoft 德国的一名数据库顾问。Rick Anderson 在“Microsoft 开发人员用户教育”部工作。Saleem Hakani 是一名 Microsoft 高级数据库工程师和 SQL Server 社区负责人。

其他资源