Transaction/sec Issue

Posted on 2004-09-19
Last Modified: 2008-02-07
I have a big problem :

Since 3 days ago the transaction/sec on the database we used are grown up to 300/400 per seconds and the client application hang up

The database running on sql server 2000 standard edition sp3a, on a server with 2 gb of ram a nd 2 Xeon processor.

There are 4 tables on this database with about 65.000 records each one, with a primary key.
There are store procedure to run the queries

The application on client side is written in visual basic for applicatin on a access 2003 front end. The sql table are connected via ODBC connection

The client are 20 on a XP Porfessions  sp1 computers.

When they start to work, after 3 minuts, the transaction/sec ride 400/sec and remain established until we shutdown the access appliction on client side.

What's happened? do you have any ideas ?

This architecture is running since 1 month with 20 users and all it's ok. COuld be a index problem or a database problem?

Thnk's in advance for your help

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12094553
What kind of queries are these? Mostly selects or inserts?
What about indexing, does db has proper indexes?
20 users generates 400 transactions/sec? What are the doing?

Expert Comment

ID: 12094664
Get to the root of the problem

- Is it network
- Disk size / log full
- Are server response times fast and client response times slow

Use perfmon to monitor cpu, disk activity and detailed sql behaviour

Task manager also gives cpu usage and network bandwidth in use

If its a sql problem the application developer should help you determine which queries are running slow and why

Have you been dumping the log ?

Is there some other thread running at 100% cpu
LVL 10

Expert Comment

ID: 12095259
Please clarify - things were running fine until 3 days ago with the same conditions? Or conditions changed and now things are hanging?

Presuming the first situation, I would look for a network issue or disk space (as plq suggests).  

If data entry conditions have not changed a log problem is unlikely unless you recently changed your recovery mode (?)

Check for locks when it "hangs"

Did you recently add CPU?

One more question: when you say transactions per second increases to 400/sec, what tool are you using to determine this?
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Author Comment

ID: 12096686
thank's for your fast reply !!!!

more information to you :

- until 2 weeks ago we were 10 users and 50.000 record to manage. From last Monday the users has been increase to 20 unit, the records grown up to 65.000 more or less.  all works fine under this condition until Thursday afternoon when the transaction/sec perfomnce monitor (COUNTER SQL/DATABASE) counter increased up to 400/sec and the client application stop to responding or, wait 4 o5 minutes toreceive the new record on the screen.

My client is a call center and the users use this application for a teleselling activity. They call a customers, read the information on the display (name,surname,address, ask some information about the selling and so on, about 25 field per record). At the end of interview the client update the record and store the information in the database for statistics. When th application close the curent record and run the store procedure to execute the query for capture the new record the transaction /sec grown to 400 ore more. When the hangs occour, the CPU grown to 60-80% and doesn't go down until we shutdown the applcation on the client pc.

The disk space avaliable is 35 GB on the partion fo SQLDATA and ,8 GB for the C:\ partition
The server borns with 2 CPU and 2 GB of RAM


what does means "If data entry conditions have not changed a log problem is unlikely unless you recently changed your recovery mode (?)" : how can i change the recover mode ?
When the application hang i found more and more LOCK present on the database.

Tomorrow I will post the store procedure statements and the profiler trace that i registered during the hang of application.


if i try to lunch the maintenance plan on the database the task terminate with an error but i don't know why . Where i can look for to understand which kind of error i received ?

I'm in trouble so much because my client lose much money if the teleselling activity doesn't reach the goal at the end of September and he can lose the commitment too.

Thn'ks again !!!

LVL 12

Expert Comment

ID: 12099536
Also post any triggers on the tables you are updating.  Have you run SQL profiler to capture and identify high IO generating queries or sp executions?

Author Comment

ID: 12109350
Hi everybody,

seems that the bug is a store procedure . Yesterday , during an hangs test we discovery a store procedure locked on sql server. We made some changes on this store procdure and, for the last work hour, 20 users works fine.

Today we will try 40 users together.

i wll  post here the store procedure statments for more investigation. This nght, after the test, i will post the result

Thank' again for your support

Accepted Solution

CetusMOD earned 0 total points
ID: 12438848
PAQed, with points refunded (500)

Community Support Moderator

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question