Re: Issues with CHANGE_TRACKING AUTO after initial index population
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Tue, 23 May 2006 21:16:38 -0400
There's a problem with 64 bit SQL 2005 server. Basically set min and max
memory to 3 Gigs, enable AWE, reboot. You should get better performance now.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lubdha" <lubdha@xxxxxxxxx> wrote in message
news:1148408695.063349.145710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We tried to create a full-text index on one of our DB tables, and have
run into a number of performance issues with CHANGE_TRACKING AUTO that
forced us to turn off the FT indexing. Here's a detailed description
of our configuration, steps taken, issues and questions.
Any help in this regard will be greatly appreciated!
What we have:
1. We have a website that runs off a SQL 2005 RTM x64 (9.0.1399) on
Windows 2003 x64, running on 4GB memory. SQL is using 1 processor in a
dual processor system at 3.6ghz
2. Our database data file, transaction log and the full-text index are
all on separate physical disks (each of them being a single 146GB, 15K,
RAID pairs). The system databases (including tempdb) are on a separate
array from the data and log file for our database.
3. The FT index has its own filegroup
4. The table we're trying to index has a Primary Key that is a
bigint. The 3 columns we're trying to FT index are nvarchar(254),
nvarchar(max), nvarchar(254)
5. The table has about 22,000,000 rows and is queried/updated very
often
6. The database is in Full recovery model, with t-log backups every 15
minutes
What happened:
This is what actually happened in our production environment. We
started the FT initial index population when our site activity is low.
During the initial FT index population:
1. The initial population took 5.25 hours
2. CPU maxed out, at 100%. Website perf slightly slower, but acceptable
3. High activity on the data file. Activity on the log file
commensurate with the number of writes happening. Intervals of high and
medium activity on the FT index
After the initial index population:
1. The CPU remained at 100%
2. The data file remained at very high activity
3. User transactions started to time out. At one point we had 1000+
timeouts in 1 hour
4. Website performance became unacceptable
5. Running DBCC FREEPROCCACHE successively 2 times helped the response
time a bit, but still not to an acceptable level
6. In general, the performance grew worse every hour, but the most
significant jump in performance degradation came AFTER the initial
population was complete. This is not necessarily directly proportional
to site activity.
7. Turning off auto change tracking and dropping the catalog produced
another 6GB of transaction log data in less than an hour after it was
dropped. The DB performance went back to normal, and website is now
operating properly.
The questions we have at this point:
1. Is it realistically possible to accomplish what we're trying to
do: index/maintain a very dynamic table with 22,000,000 rows, and
growing.
2. We suspect it might have been the AUTO change tracking that killed
us (with SQL and the FD both trying to read/write the change list
internal table and not able to keep up with the number of changes). We
are planning another run at it, with CHANGE_TRACKING MANUAL and
updating the FT index every 5 minutes or so. Would this give us better
results?
3. Would adding extra memory/disk/CPU give us better results?
Any help in this regard will be greatly appreciated!
Thanks,
Lubdha
.
- Follow-Ups:
- References:
- Prev by Date: Re: Return a random 5 records from full text index
- Next by Date: Re: how does contains sort returned rows?
- Previous by thread: Issues with CHANGE_TRACKING AUTO after initial index population
- Next by thread: Re: Issues with CHANGE_TRACKING AUTO after initial index population
- Index(es):
Relevant Pages
|
