SQL Server 2008 Full Text slowness

I was lucky enough to visit Microsoft during the CTP period and was testing out integrated full text search in 2008. An issue we experienced was that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table. 

It appears to happen if:

• You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
• You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
• One or more of your queries are complicated or take some time to complete.

You can check to see if your system is slow due to this by:

• SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
• Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.

The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build.  To use this, type the following Transact-SQL statement:

DBCC TRACEON (7646, -1)

This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….

I believe there is a knowledge base article on this.

Insert into temporary table from Stored Procedure

For this to work, you need SQL Server 2008.

We use temporary tables and table variables occasionally and sometimes need to fill those temporary objects with data from the results of a stored procedure or dynamically built query (executed with sp_ExecuteSQL).  This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.

DECLARE @jobs TABLE
( jobcount int
)
INSERT
INTO @jobs
EXEC
sp_executesql
N’select 1 AS jobcount’
SELECT
*
FROM @jobs

Here, I define a table variable and then insert into this from the execution of a query. I then select from the variable to proove that data was populated. This could easily be a Stored Procedure.

I am sure you can find uses for this , I think this is another good example of the lesser known additions Microsoft has included in 2008.

FDHost Account Password Expiry Issue In CTP6

Simon Sabin posted about this a few weeks back but I hit the issue he described today so I wanted to write about it and what I had to do.

The issue I am talking about is related to the account Full Text host uses to perform out of process tasks. This account, in CTP6, is created when CTP6 is installed. However it will use whatever default expiry conditions you have within your infrastructure.

Now, all this was fine when we first did some testing. However, coming back to it today I needed to drop some full text indexes and create them again. I dropped them but when I tried to create them, the full crawl wasn’t working. After checking the error log, it was clear that root cause of this was the FDHost Account’s password had expired….

On our system, the user name was called FDH$MSSQLSERVER. As per Simon’s post, the first step is to change the password on your Server and set any expiry details you need to (e.g. not expire). Next you will need to sync this with SQL Server. There is Stored Procedure for this, which can be used as follows:

EXEC sp_fulltext_resetfdhostaccount
@username = ‘FDH$MSSQLSERVER’,
@password = ‘Katmai2008′

This worked nicely. However, to get the full text index to start to work you need to give it a little kick. You can RESUME the population of the index by running:

ALTER
FULLTEXT
INDEX
ON

[MyFullTextTable]

RESUME
POPULATION

If you are not sure of the indexes that haven’t been populated yet and want to resume all of them , you can run this simple query to generate the script needed. Then simple run the script that gets returned.

SELECT
‘ALTER FULLTEXT INDEX ON ['
+
OBJECT_NAME(object_id)
+

'] RESUME POPULATION ‘

FROM
sys.fulltext_indexes

WHERE has_crawl_completed = 0

By the way, this issue has been fixed in the RC0 (refresh) release due out in May. It will now ask you to specify an account to use for the FDHost process. Upgrading an existing install will also prompt you to change this. However, if anyone is using CTP6 currently you might have the same issue I experienced today.

Ranking Methods in Full Text Search

This is direct from MSDN but I was asked about this specifically so I thought I’d post about it. If anyone has used full text search you will know that the default way it ranks results is pretty good. It uses a known method called Jaccard Coefficient. However, there are times when you might want something els.e. Sharepoint 2007 (and versions onwards I assume) uses Enterprise Search and supports the following ranking methods. I have no idea if or when this kind of thing will be supported in 2008 or future versions of SQL Server out of the box.

JACCARD COEFFICIENT (default if not specified)

Calculates ranking results from the relative proportion of matching terms, excluding any terms that are not matched.

DICE COEFFICIENT

Calculates ranking results from the frequency of multiple terms found together, compared with the probability that they are found in isolation.

INNER PRODUCT

Calculates ranking results by using the integral of the products of the ranks of the individual matching documents.

MINIMUM

Calculates ranking results from the lowest rank score from all the matching documents.

MAXIMUM

Calculates ranking results from the highest rank score from all the matching documents.

Now , if you want to specify the RANKMETHOD when you perform your ISABOUT query you can simply state the method you want to use.

ISABOUT ( <match_terms> RANKMETHOD <rank_method> )

An example of which is:

WHERE CONTAINS(Description, ‘ISABOUT(“computer”,”software”) RANKMETHOD INNER PRODUCT’)

I hope this helps clarify the different ranking methods there are.

Fulltext index creation taking a long time?

This may be only relevant to 2008 but if you find Fulltext index creation taking a long time to complete you may be affected by a potential bug in CTP6. If you are experiencing this problem you can try running the following statement prior to the creation of the index. This will disable the checking of the OFFICE filter signature.

sp_fulltext_service
‘verify_signature’, 0

On machines we have here, the signature does not appear to exist for the dll and therefore the crawl is taking a long time to complete.

A Pratical use for Dynamic SQL and FOR XML

SQL Server 2005 or 2008 required for this.

I don’t pretend to be a fanboy of dynamic SQL but there are times when it is needed. I came across such a situation today where I needed to update an aging procedure which had lumps of hard coded T-SQL.

Consider this scenario. You have full text catalogs for each culture your system supports; in our environment this is only Dutch, British English and German (1043, 2057 and 1031). These have to be separate tables due to the nature of full text. However, for one of our processes we need to union join the results.

Now, in the original procedure this was achieved by hard-coding the table names. When a new culture is added to our system , we would have to change the procedure again. If this was the only procedure that needed changing I would almost consider doing this but we have 200+ procedures that have an element to them where the culture is used.

So, it was decided that we needed to change this and others so that the addition of a new culture is not an issue. Right, back to the original procedure. We already have a Culture table; this holds the CultureId, the Culture (as in en-GB, nl-NL) and the descriptive Culture (e.g. British English). For the purpose of dynamically building my query I need to produce a query that gives me a string that holds the Union T-SQL.

So you can see this in action, here is the T-SQL to create the Culture table and fill it with some data:

CREATE TABLE Culture (CultureId INT, Culture CHAR (5) , 
CultureLanguage VARCHAR (25))

INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (1043, 'nl-NL','')

INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (2057, 'en-GB','')

INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (1031, 'de-DE','')

Now you have a table with some culture values in, this is my solution to generating the Union T-SQL:

DECLARE @UnionSQL NVARCHAR (500)

SET @UnionSQL = (

SELECT DISTINCT

STUFF((

SELECT  'UNION ALL SELECT MyId FROM [Visits_' +
        T2.Culture + '] WITH (NOLOCK) '

FROM    Culture T2

FOR

XML PATH('')),1,9,'') AS Cultures

FROM      Culture T1

)

PRINT @UnionSQL

In our environment , all the culture based full text tables are named with the Culture extension so the select is really simple. The clever bit is the use of FOR XML PATH and STUFF. This produces the following output:

SELECT MyId

FROM [Visits_nl-NL] WITH (NOLOCK)

UNION ALL

SELECT MyId

FROM [Visits_en-GB] WITH (NOLOCK)

UNION ALL

SELECT MyId

FROM [Visits_de-DE] WITH (NOLOCK)

For me, this is a really simple solution and it means that any new cultures added to the Culture table are automatically included in the procedure. I do have some additional checks prior to this to ensure the tables exist plus obviously a lot more code within the procedure itself.

This particular procedure runs once a day for a process we have , I am sure this may not be the best way to achieve the solution if you needed to run this 100 times a minute and I would welcome any comments on this scenario and alternative solutions. For instance, I know you can achieve the same result with CTEs.

Which of your Stored Procedures are no longer Valid?

The script presented in this article is for SQL Server 2005 or SQL Server 2008.

Here’s the problem; SQL Server has no problem when you create a Stored Procedure that references a non-existing table. It has no problem when you use a column that is simply not there. It is not validated until it’s executed. All that is checked (parsed) is the syntax. This is made worse when the structure of your database changes or people within your organisation come and go and those one-off or test procedures are left in your database.

How much dead wood is left in your database? Who wants to remove a procedure, it may be used by some process somewhere but no one is actually that sure. Who is going to take the risk?.

Well, there are a number of ways to see procedures that are no longer in use – you can check the procedure cache and list out procedures that dont exist in the cache. This is quite a quick and simple way to see procedures that have not been used in a while. However, there might be procedures that are still valid – they still may work if run, whenever that may be. They still might be valid.

What we really want is a quick way to actually check the procedures to see if they still work against our current structure. We want to do this without actually executing them and potentially causing a 3 year old bug to appear or wipe out some table because a particular procedure was created to do just that, once , at some point in our application’s history.

As it turns out, it isn’t that straightforward but it can be done.

What I am going to show you here is a script which will obtain a list of your procedures, grab their parameters and then check each procedure , one at a time; if it turns out they are not valid they will be added to a temp table for listing at the end of the script.

First, I will outline the key steps. Then I will show the full script you can use. The first part of process is to list the procedures , their parameters and dummy (NULL) assignment. This will be used to execute the procedures.

SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'

Next, when it has the list of procedures it creates a test harness for the procudure. E.g.

CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC up_TtDay_insert @Id=NULL;
SET FMTONLY ON;
EXEC up_TtDay_insert @Id=NULL; SET FMTONLY OFF

Then, finally it runs the test harness procudure; removes it and then moves onto the next procedure. Any errors are logged in a temporary table.

Use this script without warranty, we have found it very useful but please use wisely and in non-production environments until you are personally happy with it’s output:

SET NOCOUNT ON;
DECLARE @tblInvalid TABLE ( -- invalid objects
[type_desc] NVARCHAR(60), [name] sysname, [error_number] INT, [error_message] NVARCHAR(2048), [type] CHAR(2) );
DECLARE @type CHAR(2); -- object type
DECLARE @type_desc NVARCHAR(60); -- object type description
DECLARE @name sysname; -- object name
DECLARE @params NVARCHAR(MAX); -- parameters
DECLARE @sql NVARCHAR(MAX); -- dynamic sql
DECLARE curObjs CURSOR LOCAL FAST_FORWARD FOR
SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
OPEN curObjs;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM curObjs INTO @name, @type, @type_desc, @params;
IF (@@FETCH_STATUS != 0) BREAK;
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_compare_dbs_temp_invalid_sp_check;
SET @sql = N'

CREATE PROCEDURE up_sqldev_SPCheck_exec

AS

EXEC '+ @name +' '+ @params +';'
PRINT @sql
EXEC (@sql);
BEGIN TRY
BEGIN TRANSACTION;
SET @sql = N'SET FMTONLY ON; EXEC '+ @name +' '+ @params +'; SET FMTONLY OFF;';
PRINT @sql;
EXEC (@sql);
ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
IF ( ERROR_NUMBER() != 0 )
INSERT INTO @tblInvalid
SELECT @type_desc, @name, ERROR_NUMBER(), ERROR_MESSAGE(), @type;
END CATCH
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_sqldev_SPCheck_exec;
END;
CLOSE curObjs;
DEALLOCATE curObjs;
SET NOCOUNT OFF;
SELECT [type_desc], [name], [error_number], [error_message]
FROM @tblInvalid
ORDER BY CHARINDEX( [type], ' U V PK UQ F TR FN TF P SQ ' ), [name];
GO

I want to give most of the credit for this script to Hue Holleran, a personal friend of mine who regularly presents at SQL Server themed events. For those of you who caught his presentation on SQLCMD at the March 2008 SQLBits, he won presenter of the day. Myself and Hue bounced around various of versions of this until Hue came up with the winning script. This is also forming part of a bigger script Hue is working on , so stay tuned for an follow up post that I hope Hue does to explain this.

If you have any suggestions for improvement or have any general questions about this please comment on this blog entry.

Insert multiple rows

How many times have we had to write multiple INSERT INTO statements? Well, SQL Server 2008 comes to the rescue. Its a small feature but still very useful.

You can now insert multiple rows into a table in a single statement.

INSERT INTO T1 (Col1,Col2,Col3)
VALUES

(‘Row1′, ‘Value’,‘Test’),
(‘Row2′, ‘Value’,‘Test’),
(‘Row3′, ‘Value’,‘Test’)

This inserts 3 rows into the T1 table. Certainly time-saving and makes those sometimes hefty insert scripts smaller.

Fulltext Fragmentation (iFTS)

One neat feature of SQL Server 2008 is the ability to see how fragmented your fulltext indexes are. By running the following you can see how many fragments you have.

SELECT * FROM sys.fulltext_index_fragments

The rule of thumb is that if you have between 30-50 fragments per table you should be thinking of reorganizing (REORGANIZE) your fulltext index.

For a clear picture, use this query:

SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS Fragments
FROM sys.fulltext_index_fragments
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) >=30

 

RML Utilities and named parameters

I have been doing some benchmark/tracing for Katmai and came across the excellent RML Utilities. However I wanted to blog about a bug in this which has meant that the ostress tool couldn’t be used to  replay the trace.

ReadTrace allows you to read a trace file and output/split this into RML files (one per spid). You can then do some fancy stuff with ostress to replay these files. However,  readtrace appears to have a bug where it incorrectly parses your trace file if you have used named parameters in your rpc calls.

This is an example call from a trace file

exec up_Table_Search @tableid=1, @namedpar1 = N‘test’, @namedpar2 = 2, @namedpar3 = N‘test2′

When this is parsed by readtrace, it will produce a RML file that calls the proc like this:

exec up_Table_Search 1, ‘test’,2,‘test2′

Why is this a problem? Well, what if the order of parameters isn’t the same in the proc..

ReadTrace does not seem to cater for named parameters and products an RML file which can potentially either cause data corruption or fail to run at all if the datatypes are incompatible.

Please , someone let me know if there is a command line switch to fix this. There doesn’t seem to be.