• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

Transaction/sec Issue

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

1 Solution
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?
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
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?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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 !!!

Ken SelviaRetiredCommented:
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?
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
PAQed, with points refunded (500)

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now