performance and locking problems - urget

[David McGeorge]

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

[Erland Sommarskog]

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