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


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?
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 42
Trying to get a Linked Server to Oracle DB working 21 60
SqlServer no dupes 25 35
SQL View nearest date 5 36
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

777 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