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

Article
09/08/2016

SQL Q&AGrow Databases, Use IFilters, and Connect Remotely

Edited by Matthew Graven

Tip: Clear Cache

Ever wonder why a stored procedure might work well in the test environment but work poorly when deployed on the production SQL Server? This may be a cache-related issue. Before you deploy stored procedures to your production environment, test the procedures in the test environment after having cleared cached query plans so that you know how your stored procedure will perform in a "cold" cache environment. Here are a couple handy things to keep in your bag of tricks.

To clear up the procedure cache on a SQL Server:

DBCC FREEPROCCACHE
Go

Query to list all cached plans:

Select * from sys.dm_exec_cached_plans
Go

Q I have a database that has heavy traffic during the day and I don't want to use autogrow because this could potentially cause timeouts if SQL Server®, decides to do this during peak hours. I would like to implement a periodic job to expand the database file by a specific percentage of the space used. How can I do this?

A File growth is an intensive disk I/O operation and if SQL Server has to wait for a data or log file to expand, you could certainly see this harm performance and response times. The default growth increment is 1MB for data files and 10 percent for log files, which can be insufficient growth increments for busy systems. Additionally, relying on autogrow can lead to disk fragmentation because the data or log files are not contiguous on disk—this means response times may be longer than necessary, as data is physically scattered on the disk.

The key to good performance is proactively allocating sufficient file space for data and log files up front. This often requires some trend analysis and growth forecast and will result in better performance because the files will be contiguous on disk and avoid the I/O cost required by autogrow during peak periods. Autogrow should generally stay enabled since a completely full data or log file will prevent access to the database altogether. But keep in mind that autogrow should be considered a safety net rather than a feature for managing databases.

Scheduling regular database file expansions should be avoided since this can also lead to noncontiguous files on disk and, in turn, reduced performance. Proactive monitoring can be achieved by regularly executing a script to determine the percent of free space for each database (executed by a SQL Agent job) and then taking an action (such as sending an e-mail alert via Database Mail). The code in the figure offers a sample script showing how to gather the percent of free space for the current database.

Once an alert has been generated, you can script one-time file growth with the ALTER DATABASE command, and you can use a SQL Agent job to schedule this action to take place outside of peak hours. Try to grow the file to a size that will be sufficient for the foreseeable future to avoid future small incremental file expansions. It's also a good idea to ensure autoshrink isn't enabled on any database—this could needlessly create database shrink or grow cycles.

—Justin Langford

Determining Free Space in a Database

-- 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)

Q My company stores different formats of files within our database using varbinary and image columns. I've heard that SQL Server has integrated functionality that will let me search these different file formats. How can I configure SQL Server to do this?

A This functionality is built into the full-text indexing service. The service provides the flexibility to use IFilter interfaces, making it possible to develop and load filters that can extract useful information from proprietary data. These IFilters are also used for other products, such as Microsoft® Office SharePoint® Server, to gather information about crawled files.

An IFilter is either provided by the creator of the file format or by third-party vendors. SQL Server already contains some IFilters that are loaded when the FulltextService (FTS) is installed—these include filters for HTML and DOC files. However, more IFilters can be added as required. For example, filters for Adobe PDF can be found on the Adobe Web site, and a new filter pack for 2007 Office system extensions was published at the end of 2007. Note that you must know which version of the IFilter is needed. For instance, an IFilter intended for 32-bit systems won't work with 64-bit installations of SQL Server.

After you run the installation package on the client, the IFilter will typically be registered in the ecosystem of the OS. With the bits registered in the OS, you need to perform a few steps to make FTS able to load the Filters. After starting your query execution tool, issue the following commands:

sp_fulltext_service 'load_os_resources',1. (This statement will enable FTS to load the registered bits for processing, including components like wordbreakers and stemmers.)
sp_fulltext_service 'verify_signature',0. (This will bypass SQL Server to check whether the used filters are signed, as many vendors do not sign their filters according to the standard.)
Restart the SQL Server instance and the instance of FTS.
Create your full-text index on the columns having the binary column as the content for the IFilter to crawl and the extension column (meaning the column with the extension type, such as DOCX) for SQL Server to choose the filter to which filter to redirect the content to.

More information can be found at go.microsoft.com/?linkid=7912971.

—Jens Suessmeyer

Q I am unable to connect to a remote SQL server. Do I need to configure the firewall on my client or server machine?

A Remote connections to SQL Server 2005 can fail for many reasons, but firewall configuration is one of the most common problems. The SQL Protocols blog (blogs.msdn.com/sql_protocols) is a great resource for information on making a SQL connection.

The default installation of SQL Server 2005 does not allow remote connections. From the machine running SQL, on the Start menu, select Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration. Here, go to Surface Area Configuration for Services and Connections, select Remote Connections, and select the "Using both TCP/IP and named pipes" radio button. You must then restart SQL for the change to take effect.

By default, SQL Server uses port 1433. To verify the port is open, use the following telnet command, replacing <ipaddress> with the actual IP address of the machine running SQL Server:

telnet <ipaddress> 1433

If you get a connection failed response, open the Windows® Firewall, go to the Exceptions tab, select Add Port, and add TCP port 1433. Telnet should now succeed. (Note that Telnet is not installed by default on Windows Vista®.)

—Rick Anderson

Thanks to the following SQL Server experts for contributing to this column:
Justin Langford works for Coeo Ltd., a Systems Integrator and Microsoft Certified Partner based in England. Jens Suessmeyer is a database consultant at Microsoft located in Germany. Rick Anderson works in Developer User Education at Microsoft. Saleem Hakani is a Senior Database Engineer and SQL Server Community Lead at Microsoft.

Additional resources