January 4, 2006 at 11:01 pm
Hi Everyone
I need some assistance , I have a situation where i have 750 users on 3 local vlan's connecting to a sql 2000 box (recently upgraded as we thought that was the problem) SQL is now running on a HP Itanium Dual xeon processor with 4 gb Ram on a gig Backbone. The user machines access the Sql box through an Access front end using system DSN ODBC drivers ,as soon as more that 500 or so users log in the processor utilisation on the SQL box jumps up to 95% from about 28% and stays there , and there goes the performance
At the moment i am running 2 SQL servers to aleviate the problem, but i would really like to hear your suggestions , Is the Machine still under spec?
January 4, 2006 at 11:14 pm
Hmmm...
Could be any number of things. How busy are the disks and how are the database files laid out? It's possible that I/O is the bottleneck and redistributing the files across more spindles or separating log and data and indexes might improve things. Is the SQL Server really using all the 4 gig of memory? What edition of SQL Server are you using (standard, enterprise)? Are there particular queries which cause larger degradations than others? It might help to know what you may have already looked at so we won't cover ground you've already explored.
January 4, 2006 at 11:30 pm
Hi David
The disk activity was one of my first thoughts , i did put a performance counter on ' physical 'disk with read and write times on the box ,but it seems normal, the Data and log files are distributed over diffent disks ,Sql is using about 2,8 gb of the available 4GB Ram and Im using SQL Enterprise Edition
The Database is very simple, 12 tables ,its running in the call centre industry so basically it gets info like ,name , id , surname from several Different tables then populates the user's screen then only updates when the user has finished his telephone call
January 4, 2006 at 11:39 pm
Check things like blocking, also look at your queries to see how efficient their use of indices is...
January 5, 2006 at 4:05 pm
How big is the database?
How are connections handled? Are you using pooled connections from a web (or other) server or does each user make and terminate connections? There's a fair amount of overhead in establishing and terminating a connection, so you might want to look at some type of connection pooling if you're not using that now.
Are the users issuing dynamic SQL or invoking stored procs?
Are there temporary object that get created for these connections? If a large number of folks are using temp tables, I've seen some pretty severe performance degradation as the queue for updating the system tables for the creation and destruction of those temporary objects builds up.
January 5, 2006 at 11:00 pm
We had something similar, and for us it was really just an indexing issue. Identify your larger tables, find the queries that are being run on that table(s), and create 1 or more indexes to support those queries. Although multiple indexes on a table can hinder insert performance, depending on table size it may be neglible.
We had tables with multi-millions of records and this fixed our performance issues - we went from 90-100% CPU to consistantly less that 10%!!
Good luck - let us know what your fix was...
Ben
January 6, 2006 at 3:13 pm
I saw performance issues like that several times on the very good hardware (2 to 4 GB of RAM and very good multiple processors) with only a couple of users running either queries by third-party report tools towards other database servers or running data import applications. In almost each case the issues were created by big recordset sizes created by the applications.
Regards,Yelena Varsha
January 8, 2006 at 11:03 pm
H!!! , cpu moving towards 100% usage can happen in multiuser envirinment due to improperly maintaned databases .
1) you have to check weather the frequently accessed query'a are indexed properly or fragnmemted .
2) Check for memory leakage problems ,if possible apply sp4 with HOT FIX
regards,
Vinod (DBA)
HTC GLobal Services Chennai
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply