Can SQL Server 2000 be made to populate a fulltext catalogue without blocking the tables it is reading?

Question

I have a database server on SQL Server 2000 (yes I know...) with fulltext catalogues on some of its tables. I'm currently doing a full population overnight in quiet time, and I'd like to be able to update the catalogues during the day so that new data can be considered in searches.

The problem I've noticed is that when an incremental population runs there is a considerable amount of blocking, caused by the population process. The other transactions on this database are using "read uncommitted", or dirty reads, to minimize delays (I don't especially care about up-to-the-second accurate data) so I'm not exactly sure why the population, which itself is only reading data, blocks them.

Any clues, hints?

Answer 1

Short story: no, and the situation isn't much better until recent updates of SQL Server 2008. The RTM version of 2008 had these same issues, as we documented here:

http://www.brentozar.com/archive/2008/11/stackoverflows-sql-2008-fts-issue-solved/

The workaround is to use the fastest storage subsystems that make sense for your budget and your workloads. The full text catalogs need to be on separate arrays from your data and logs, and that way they can finish population faster.

You also mentioned that you're surprised that reading causes locks. We've got articles on SQLServerPedia explaining SQL Server's locking process, like this one:

http://sqlserverpedia.com/wiki/SQL_Server_Locking_Mechanism

If you want more specific answers, watch your server during the population. Run an sp_who2, look at which queries are being blocked, and run a DBCC INPUTBUFFER(spid) command to find out what their T-SQL is. That way you can see exactly what types of queries are causing it. If you're sure it's using read uncommitted, upload a copy of your query execution plan, and we can help interpret it to find out what's going on.