Batch Size

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
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.
"Robert G." wrote:

> 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
|||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
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> 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.
> "Robert G." wrote:
|||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
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> 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.
> "Robert G." wrote:
|||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:
"John Kane" wrote:

> 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
> --
> SQL 2005 Full Text Search
> http://spaces.msn.com/members/jtkane/
>
> "Robert G." <RobertG@.discussions.microsoft.com> wrote in message
> news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...
>
>