December 21, 2005 at 4:43 am
Hi all,
I have a SQL Server 2000 with SP3 loaded onto Windows 2003 R1
regarding Hardware, its a P4 HT Tech. with dual processor support. RAM is 1GB, has two 40 GB and 80 GB Seagate Hard Disks.
I have enabled Windows NT Boost Fibers option and dedicate only second Processor to SQL Server process.
ours is a VB based client for Data Uploading purposes.
but still since we have a lots of updates going on during Data Uploading task, it slows down the Server Machine as a whole.
What can the problem be if I have optimised all the options, including truncating logs afte the backups at the end of the day and also index defragment using DBCC INDEXDEFRAG command.
Please help.
Thanks in advance.
Avinash Barnwal
+9194155 01787
December 21, 2005 at 5:51 am
You may want to check and see what the RAM usage is set to for SQL Server. In addition, 1GB is probably not enough RAM for a SQL Server INstallation on a Windows 2003 server. To check this you may want to run perfmon and track available memory.
December 22, 2005 at 6:49 am
Agree with above regarding memory. MS advised me not to use Boost Fibers and not to limit processor usage, Windows 2003 and SQL work closely enough not to require too much intervention.
December 22, 2005 at 11:55 am
You might also want to check the average disk queue length of the data raid/disk. If it is higher than 2/spindle for an extended period you will experience slowdowns.
Comparision: Restoring 30gb database
2*7200 sata raid 1 ->3 hours
4*15k scsi raid 10-> 30 min
December 28, 2005 at 9:59 am
Consider removing the Windows NT Boost Fibers option & verify the indexes, fillfactor to improve data loading
January 5, 2006 at 5:08 am
Now what the hell is this fillfactor!
Somebody plz. explain me of this!
I tried disabling Boost Windows NT Fibers option but in vain! It aggravated the problem which i already had.
I think the culprit is the poorly designed VB Client. Can it be?
I have used Update queries instead of ADO Recordset operations!
Can it slow down the Server to a large extent?
Please help
Avinash Barnwal
+91 94155 01787
January 5, 2006 at 6:12 am
Fillfactor=when creating the index
example index node can point to 1000 records before a new node needs to be constructed
fillfactor 100%=all pointers point to actual data, if a new record is to be added, a new node (and possible a new page of 8kb,disk access, "slow")is constructed and pointers divided between the nodes
fillfactor 50%=half the pointers point to actual data. If a new record is to be added there is no need so create a new node since it hasn't been filled up yet.
Update queries should perform better than ado updates (since Ado uses cursors). Are your update queries the actual update .... or a call to an update stored procedure like execute usp_updatemyrecord.
Can you post your actual update logic ( or your previous ado code)?
January 5, 2006 at 11:10 pm
Look Pattyn,
I have a scenaria where writes /read ratio is much higher as to the tune of 90/10.
I can't post the update query because it includes many fields. But I can surely tell you that my updates are purely T-SQL updates. And these T-SQL updates have been executed using Connection.Execute Method call.
I have not used ADO Recordsets in any of the codes. But I plan to use them to try whether they can relieve my Server of some burden!. I hope it could help me!
Thanks.
Avinash Barnwal
SCPL Lucknow
+91 94155 01787.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply