Transaction/sec Issue

Posted on 2004-09-19
Medium Priority
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


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?
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

by:Ken Selvia
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 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