473,574 Members | 2,309 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

performance and locking problems - urget

Dear Gurus,

A Client has the following problems/requests for their Production
databases, what is your professional/practical advises to tackle and
resolve these issues:

1)Number of Transactions per day – The current database can currently
handle about 5000 order transactions per day

2)Table locking – When doing row updates SQL Server will lock the
whole table. For example after a few instances of a driver updating an
order record it will lock the entire orders table

3)Reports – When a user runs a report involving big quantities of
order-related data other processes will suffer occasional timeouts

4)Archiving – There is no efficient way of archiving historical system
data
Jul 23 '05 #1
1 1203
David McGeorge (so**********@y ahoo.com) writes:
A Client has the following problems/requests for their Production
databases, what is your professional/practical advises to tackle and
resolve these issues:
I'm afraid that your questions are far too open-ended for it to be
possible to give an exhaustive answer in a newsgroup post. Had I been
a consultant, I would have been tempted to refer you the web site for
my business. Judging from your mail address you're a web programmer,
and from your questions it appears that you client is a need of someone
with SQL expertise.
1)Number of Transactions per day ? The current database can currently
handle about 5000 order transactions per day
What do you mean with "can"? Is 5000 transactions/day the actual load?
Or is the database at the maximum of its capacity with that rate? I
would assume the former, since 5000 transactions/day is a low number,
unless the transactions are extremely complex.
2)Table locking ? When doing row updates SQL Server will lock the
whole table. For example after a few instances of a driver updating an
order record it will lock the entire orders table
This sounds like the indexing of the database needs to be improved.
If you say:

UPDATE tbl
SET col = 23
WHERE othercol = 12

And there is no index on othercol, SQL Server will have no choice but
to lock the entire table.

One tool to improve indexing in the database is the Index Tuning Wizard.
You give it a day's workload, and it will suggest suitable indexes to add.
3)Reports ? When a user runs a report involving big quantities of
order-related data other processes will suffer occasional timeouts
Reports are often problem in OLTP databases, because they need to scan
lots of data. Indexing can help, but if user can build their own reports,
or there are report functions with lots of selection criteria, this
may not be feasible. Not the least, if the users can accept that an
odd report takes 20-30 minutes to run.

Therefore many sites sets up a report database, which is a copy of the
live database. Depending on the requirements, the report database can
be loaded from yesterday's backup, or you could keep it updated with
log shipping or replication. The latter is required if users must have
today's data in their reports, but it takes more effort to get there.
4)Archiving ? There is no efficient way of archiving historical system
data


This point is completely impossible to address without knowing the
business requirements.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1731
by: Steve_CA | last post by:
Hello all, I've been recruited to assist in diagnosing and fixing a performance problem on an application we have running on SQL Server 7. The application itself is third party software, so we can't get at the source code. It's a Client Management system, where consultants all over the country track their client meetings, results, action...
6
3289
by: George McLean | last post by:
Hello, I am trying to isolate some performance issues. The database is DB2 v8.1 running on a Win2000 server with 4 processors and 1gb of RAM. The application is a GIS application that uses ADO to access the database. It was not written specifically for DB2 and I think almost all queries are running as dynamic queries. The performance...
8
5742
by: lyn.duong | last post by:
Hi, I have a large table (about 50G) which stores data for over 7 years. I decided to split this table up into a yearly basis and in order to allow minimum changes to the applications which access this table, I created a union all view over the 7 yearly tables. What I have noticed is that queries against the union all view is considerably...
16
2673
by: John | last post by:
Hi All, I have two backend databases that area link to a frontend database where information is entered, retrieved and deleted. The information accessed from the frontend database is coming from both databases. Both backend databases are at least 225MB each, therefore accessing data is slow and running queries are slow and frustrating, this...
0
3161
by: Andrew Dowding | last post by:
Hi Everybody, I have been looking at problems with my Windows Forms C# application and it's little Jet 4 (Access) database for the last few days. The Windows Forms app implements a facade and implementation, data abstraction layer. But because each data adapter in the implementation layer has a connection object that opens and closes as...
17
2040
by: 57R4N63R | last post by:
I'm currently building a website for one of the client. There has been few errors here and there, but just recently the problem is getting worse. Basically the symptoms is that when the user try to access the page, it takes really long time to load. However, after up to 1 hour, the website will run fine again as normal. This issue has...
4
2676
by: Jim Devenish | last post by:
I have converted an Access back-end to SQL Server back-end but am having some problems. The Access to Access application has been running well for some years. I have successfully copied all the data to SQL Server and linked the tables to the front end .mdb (I am not using .adp). Some queries were performing poorly so I have converted...
3
1920
by: yonil | last post by:
Over the years of using C++ I've begun noticing that freestore management functions (malloc/free) become performance bottlenecks in complex object-oriented libraries. This is usually because these functions acquire a mutex lock on the heap. Since the software I'm writing is targetted for a number of embedded platforms as well as the PC, it's...
1
1279
by: robm | last post by:
Hi, We have recently upgraded an application from .NET 1.1 to .NET 2.0. It is a medium size enterprise application that makes quite heavy use of the ASP.NET caching API to improve performance. Since moving to .NET 2.0 we have observed a marked degradation in performance in certain areas of code. In particular one function which accesses...
0
7801
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8229
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7808
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6450
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5616
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5299
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2238
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1335
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1055
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.