Batch Size

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert G. - 27 Oct 2005 23:08 GMT

Hi

I'm trying to improve a crawl's performance. Online books refers to
changing the default batch size from 1600 to a number associated with the
number of processors on my server.

For the life of me, I have no idea how to go about changing the batch size
for full-text search. Any and all pointers apprciated.

Rob

Reply to this Message

Robert G. - 28 Oct 2005 01:53 GMT

Adding to my post. I thought this was a 2005 specific newsgroup - sorry.

I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
hyperthreading. fast XEON procesors (don't exactly remember how fast).

Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
populate. I've done the other hardware things suggested by books online.

Thanks.

> Hi
>
[quoted text clipped - 6 lines]
>
> Rob

Reply to this Message

John Kane - 28 Oct 2005 05:28 GMT

Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry

You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm

Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.

Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?

FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."

Thanks,
John

Signature

SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/

> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
>
[quoted text clipped - 17 lines]
>>
>> Rob

Reply to this Message

John Kane - 28 Oct 2005 06:45 GMT

Signature

SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/

> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
>
[quoted text clipped - 17 lines]
>>
>> Rob

Reply to this Message

Robert G. - 28 Oct 2005 16:39 GMT

Hi

Your assumption is correct. Clustered index, files spread over 3 logical
disks (and two channels, for good measure).

Low is 1. According to that article, I should be seeing batches in the 4 -
8 range (maybe 16, since SQL believes I have 8 processors (hyperthreading)).

Low CPU is 0 - 5%, sometimes peaking at 30%, but rarely.

The article states "if the number of batches is low ... Increase full-text
batch size". That's what I'm trying to figure out.

They even give a suggested range - "default is 1600 rows per batch. For an
8-way computer 700Mhz CPU, the batch size recommend is 5000 rows."

Sounds like a great configuration change, if I could figure out how to
change the configuration!

Thanks again.

Rob

to quote the article:

> Robert,
> Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
[quoted text clipped - 45 lines]
> >>
> >> Rob

Reply to this Message