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.

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.

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.

Katmai release date

According to numerous blog posts, SQL Server 2008 should now be out Q3 2008. I agree with the majority on this that I am happy for this to be delayed as long as we get a stable product. Of course , if this slips any further it could be renamed to SQL Server 2009 however based on the current CTPs I am sure this will not be the case.

iFTS

As SQL Server 2008 CTP6 is now with us I will be doing a post on the new integrated full text search (iFTS) shortly. iFTS includes a number of key changes including support for STOPLIST, mixed query performance, thesaurus improvements and of course full text indexes are stored and maintained inside SQL Server now. There is so much more to write about this and I intend to cover one or two features in separate posts.

More TVP..

I found this screencast on MSDN’s Channel9, its a great addition to Simon’s post especially if you are a more visual person.

Table Valued Parameters

Table Valued Parameters

Simon Sabin posted an excellent article on SQL Server 2008’s Table Valued Parameters today. This saves me writing about it. To read his post, follow the link below:

Table Valued Parameters

If you have any particular feature you would like me to report on , please leave a comment.

All Change

I will putting together a piece on Change Tracking over the next couple of days. This is a new feature that will be available in all editions of SQL Server 2008.

A Date with Dates

There are some interesting changes in the upcoming release of SQL Server 2008, some of which are powerful and sexy (“Spatial data” anyone?) and some have a less of a wow factor but never less are extremely useful. As a start, I wanted to write a special introductory post about the new date and time datatypes.

In the currnet (2005) and previous versions of Microsoft’s SQL Server there is no date specific datatype to store only the date element. You must use the DATETIME or SMALLDATETIME types. Working with the data sometimes became a chore, especially but not exclusivily when designing reporting solutions. We had to implement CAST and or CONVERT hacks to get around this. How many of us have had to write something along the lines of:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) >= @StartDate
AND < CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) <@EndDate

Well, not anymore. Assuming of course you don’t want the time element of a date, it is now possible to use the new DATE datatype. The following example will output the date without the time element:

DECLARE @MyDt AS DATE
SET @MyDt = GETDATE()
PRINT @MyDt

The previous query can be simplified like this:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
DateEntered >= @StartDate
AND < DateEntered <@EndDate

Also , if you still need the granularity of the time the query is still a lot simpler than before:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(DateEntered AS DATE) >= @StartDate
AND < CAST(DateEntered AS DATE) <@EndDate

The range of this new DATE datatype is from 0001-01-01 through to 9999-12-31. The TIME datatype works in much the same way, it stores only the time element with a range of 00:00:00.0000000 through to 23:59:59.9999999 – providing a very useful type for certain applications.

Lastly, there is an unusual hybrid datatype called DATATIME2. Ok, not the greatest named datatype in the history of naming but it is very interesting. What this allows us to do is store a DATETIME with fractional granuluarity. For instance , do you want to store the date/time without the milliseconds? No problem for Mr DateTime2:

 

DECLARE @MyDt DATETIME2(0)
SET @MyDt = GETDATE()
PRINT @MyDt

These are the other available fractional settings you can use:

 

Fraction	Output
0	2007-10-28 22:11:20
1	2007-10-28 22:11:19.7
2	2007-10-28 22:11:19.70
3	2007-10-28 22:11:19.703
4	2007-10-28 22:11:19.7030
5	2007-10-28 22:11:19.70300
6	2007-10-28 22:11:19.703000
7	2007-10-28 22:11:19.7030000

 

I will be delving deeper into the datatypes in future blog posts.