Havy load with lot users
-
Hi
Our machin has the values:
Pentium 4 2.4GHz Single processor,2GB RAM
SQL Server 2000, 6GB DB-Space 90% for a statistics table
By a fulltext search with a stored procedure you got the answer back between 3s and 10s, what is quite fast. The site is very public so if a lot of users using the site its becomming slow, the cpu is allways around 100%. This happens between 10am and 4pm.
So where is the bottleneck?
Bevor the search there will be an insert into the statistics table. But this is fast, it has an index.
a) Tries the SQL Server to page data from the disc into the memory, are 2GB not enough?
b) Sould the machine be a dual core?
c) Does the statistics table has an influence?
Thanks for a tip.
Jan
-
I would say it could be all of above.
(1) Bump up the memory
(2) Yes dual core would help.
(3) An immediate improvement can be seen if you move the table to a different file group.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Before you go throwing hardware at it find out where your performance bottleneck is. Use the builtin performance counters to try and isolate the component that is not performing as well and address it. Microsof thas some good stuff on SQL and perfmon counters, and there is plenty of other things you can google for.
Also run profiler to see if there is any queries or result sets that are consistently slow. ( anything > 2s is slow, 10s all the time would get me fired:crazy
----
if youre going to upgrade to dual core then you should also configure your physical disks in the best manner possible.
(3) An immediate improvement can be seen if you move the table to a different file group.
only if to a different physical disk/array (if you can, and on raid other than 5)
-- when you say lots of users, its a very difficult thing to quantify. It only takes one naughty user can block your whole system.
-
If cpu >80% then you have problems - the quickest upgrade is to make memory > = database size , you may have thread starvation which would be right considering load, check your context switches.
full text is I believe notorious for resource so I'd say get a dual dual core box with more memory.
as suggested using perfmon would be good to establsih where your problem lies, but it sounds as if you need an upgrade.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply