Solved

Transaction/sec Issue

Posted on 2004-09-19
9
801 Views
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

0
Comment
Question by:HERAINFORMATICA
[X]
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
9 Comments
 
LVL 6

Expert Comment

by:peyox
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?
0
 
LVL 8

Expert Comment

by:plq
ID: 12094664
Get to the root of the problem

- Is it network
- CPU
- 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
0
 
LVL 10

Expert Comment

by:AaronAbend
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?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:HERAINFORMATICA
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

for AARONABEND :

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.

ANOTHER IMPORTANT INFORMATION :

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



0
 
LVL 12

Expert Comment

by:kselvia
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?
0
 

Author Comment

by:HERAINFORMATICA
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
0
 

Accepted Solution

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

CetusMOD
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Only Wanting One Record 8 55
SQL Percentage Formula 7 28
Need more granular date groupings 4 41
VM SQL server license. 1 50
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

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