One of the first steps in preventing this is to have your report data and
OLTP data reside in seperate databases. This of course requires some level
of replication and isn't going to be the funnest approach to take.

The other option you have is to optimize you stored procedures to try to
alleviate as much locking, disk i/o, and CPU as possible.

--
Aaron Weiker
http://aaronweiker.com/

[quoted text, click to view]

[quoted text, click to view]

That is fine. How you have these implemented might make a difference, but
probably not.

[quoted text, click to view]

I assume you are referring to the computer SQL Server is installed on,
right?

[quoted text, click to view]

How long do these procedures take without the active OLTP users?

[quoted text, click to view]

Depends on your situation. How "real-time" do you need the data? If it
needs to be up to the minute data, then you are probably going to have to do
some hardcore tuning. There may be some blocking due to not completely
implemented indexes and constraints.

It would be best to have a copy of your data and do reporting there. This
will alleviate all data blocking, but it will not other types of blocking,
like I/O, CPU, etc.

Have you done any tuning in terms of looking at plans/perfmons, etc? A lot
depends on your situation in terms of I/O, locking, CPU etc.

[quoted text, click to view]

Process server? Do you mean reporting here, or something else.

[quoted text, click to view]

Not really. Strangely, in your case you would actually want to increase the
resource limits on the reporting process, since you want it to finish
locking stuff quicker. This assuming locks is your problem, and not just IO
or CPU.

--
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

[quoted text, click to view]

I have a problem with sqlserver. We user server as data / transaction /
report server .
Our clients (40-50?) enters transactions. We use client server model
generally.
We use sqlserver mainly as a data server.
But we have some report stored procedures, these procesdures makes some
calculations , reading from some tables with views,functions... And
execution time of this procedures goes to 5-10 minutes.
But in this period our transaction performance degraded 90% percent.
How can I solve this problem?
I guess , if I use sqlserver as process server, it degrades????
Or can I give some CPU or resource limits on some users.

Thanks,