January 24, 2008 at 6:18 am
Hi there.
We are in the process of migrating one of our SQL server 2000 (32bit) databases to SQL 2005 x64.
we appear to be experiencing performance issues on 2005x64.
perhaps someone could push us in the right direction.
Table has excess of 300million records. (unpartitioned).
datafile and log files have been separated according to best practices and this table currently resides in its own filegroup.
compared to its SQL 2000(x32) instance, the SQL2005(x64) server appears to insert records alot slower than its counterpart.
Without the clustered index on 2005 the inserts appear to load comparably, after creating the SQL 2005 load is alot slower.
any guidance appreciated.
thx in advance
January 24, 2008 at 12:41 pm
I can't say as I've noted any issues with inserts - however, if your inserts are being put up and down the table and not on top , as per a heap, then page splits may slow things down. The choice of clustered index may be critical here. As to why it seems slower - is it identical hardware ?
there are some known config points with x64, mainly the lock pages in memory. I've gnerally found 2005 to be very quick, but I've not been able to do alongside testing on identical hardware.
I'm sure you'll get a load of posts about stats and stuff - I assume you've updated stats, run dbcc updateusage, maybe checked for fragmentation - compared query plans. If it's x64 you MUST set the max memory for sql server. I don't see any issues with table size, I've worked tables of 400 million rows and more - in fact I've blogged about index tuning and clustered index selection http://sqlblogcasts.com/blogs/grumpyolddba/ there's also some stuff about x64 which may help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 24, 2008 at 11:48 pm
Hi Colin
Thanks for your reply - the hardware is not identical, 2005 running on superior hardware and being x64 can address up to available 8gb memory on this host compared to older 32bit server which couldnt.
figured the same regarding pagesplits and setting higher fill factor as the data being inserted is not necessarily sorted and page splits would by default occur. - doesnt seem to make too much difference tho.
thx
January 25, 2008 at 2:14 am
Hi Ebrahim,
Just to confirm...when you migrated did you do all the usual for SQL2000 to SQL2005 migration, ie index rebuilds, update of stats, etc...
BTW, you can access 8GB of RAM for Win Adv Svr 2000/Win 2003 Server 32-bit and SQL2000 Ent using PAE and AWE.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply