performance problem...

hello ng
i've got a problem with my fulltext-index, or with the performance of the
sql-server.
in one database i've installed a fulltext-index. this index based on a often
scheduled job, because it's nessessary to find the entrys directly after the
insert. the schedule has a frequence of 1 minute...
on the same sql-server i've got an other database. in this db i like to
insert many entrys on the same time.
the index works and the other db works too. but the inserts are very slow
when i start the index-job. the sql-server runs on a 4 processor system with
2gb of memory, and the processor has more capacity than i use (max 20%). the
harddisk is also very fast (scsi).
inserts per second: up to 300
inserts per secon with index running: up to 40
what can i do to run both things on same time?
do i have to change some settings for the index-job?
thanx. simon
Simon,
Could you post the full output of the below SQL code as this information is
very helpful in troubleshooting SQL FTS issues?
use <your_database_name>
select @.@.version
select @.@.language
select count(*) from your_FT-enabled_table_name
If you're are using SQL Server 2000, does your FT-enable table have "Change
Tracking" with "Update Index in Background" enabled or are you using a
frequently scheduled Incremental Population? Depending upon the size of your
table and if you're using SQL 2000, it might be best to use CT with UIiB.
Also, could you provide information on where your FT Catalog folder is (what
drive and type of drive, i.e., RAID level) relative to your database files
(*.mdf & *.ldf)?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon Steiner" <anybody@.anonymous.com> wrote in message
news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
> hello ng
> i've got a problem with my fulltext-index, or with the performance of the
> sql-server.
> in one database i've installed a fulltext-index. this index based on a
often
> scheduled job, because it's nessessary to find the entrys directly after
the
> insert. the schedule has a frequence of 1 minute...
> on the same sql-server i've got an other database. in this db i like to
> insert many entrys on the same time.
> the index works and the other db works too. but the inserts are very slow
> when i start the index-job. the sql-server runs on a 4 processor system
with
> 2gb of memory, and the processor has more capacity than i use (max 20%).
the
> harddisk is also very fast (scsi).
> inserts per second: up to 300
> inserts per secon with index running: up to 40
> what can i do to run both things on same time?
> do i have to change some settings for the index-job?
> thanx. simon
>
|||Here's the Output from SQL:
version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition
on Windows NT 5.2 (Build 3790: )
language: us_english
row_count: 10137
The catalog is placed on "D:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA" on the same disk like the Server is running...
The size of the catalog is 6MB
I'm using a frequently scheduled Incremental Population. Could that be a
reason to switch to "Update Index in Background"?
Simon
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> Simon,
> Could you post the full output of the below SQL code as this information
is
> very helpful in troubleshooting SQL FTS issues?
> use <your_database_name>
> select @.@.version
> select @.@.language
> select count(*) from your_FT-enabled_table_name
> If you're are using SQL Server 2000, does your FT-enable table have
"Change
> Tracking" with "Update Index in Background" enabled or are you using a
> frequently scheduled Incremental Population? Depending upon the size of
your
> table and if you're using SQL 2000, it might be best to use CT with UIiB.
> Also, could you provide information on where your FT Catalog folder is
(what[vbcol=seagreen]
> drive and type of drive, i.e., RAID level) relative to your database files
> (*.mdf & *.ldf)?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon Steiner" <anybody@.anonymous.com> wrote in message
> news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
> often
> the
slow
> with
> the
>
|||uups.
The Size of the *.ldf-file ist: 235MB and the *.mdf has 27.9MB
The HD-Type is a seagate scsi drive and there is a RAID0 installed... so
there are 2 disks.
simon
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> Simon,
> Could you post the full output of the below SQL code as this information
is
> very helpful in troubleshooting SQL FTS issues?
> use <your_database_name>
> select @.@.version
> select @.@.language
> select count(*) from your_FT-enabled_table_name
> If you're are using SQL Server 2000, does your FT-enable table have
"Change
> Tracking" with "Update Index in Background" enabled or are you using a
> frequently scheduled Incremental Population? Depending upon the size of
your
> table and if you're using SQL 2000, it might be best to use CT with UIiB.
> Also, could you provide information on where your FT Catalog folder is
(what[vbcol=seagreen]
> drive and type of drive, i.e., RAID level) relative to your database files
> (*.mdf & *.ldf)?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon Steiner" <anybody@.anonymous.com> wrote in message
> news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
> often
> the
slow
> with
> the
>
|||Thanks, Simon,
Q. Could that be a reason to switch to "Update Index in Background"?
A. Yes! As you're using SQL 2000 SP3 on Win2003, you can enable CT with UIiB
and disenable or delete your frequently running Incremental Population job
and I'd highly recommend this.
Additionally, you should drop and re-create the FT Catalog and re-create it
on your server's non-system disk drive (usually c:\) and separate from your
database files. So, if you have another drive, say the E:\ and your
databases are on the D:\ (where SQL Server is installed), I'd put the FT
Catalog on the E:\ drive assuming it is the RAID0 drive. The primary reason
for this is to avoid the read/write I/O contention between the FT Catalog
and the database files during FT Indexing.
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon Steiner" <anybody@.anonymous.com> wrote in message
news:OI9NUqdFFHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> uups.
> The Size of the *.ldf-file ist: 235MB and the *.mdf has 27.9MB
> The HD-Type is a seagate scsi drive and there is a RAID0 installed... so
> there are 2 disks.
> simon
>
> "John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
> news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> is
> "Change
> your
UIiB.[vbcol=seagreen]
> (what
files[vbcol=seagreen]
> the
after[vbcol=seagreen]
to[vbcol=seagreen]
> slow
system[vbcol=seagreen]
20%).
>