Re: Executing SP freezes other clients
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/04/05
- Next message: Nigel Rivett: "Re: Executing SP freezes other clients"
- Previous message: Adam Machanic: "Re: Syntax to calculate hierarchy (ParentID)"
- In reply to: ATS67: "Executing SP freezes other clients"
- Next in thread: Nigel Rivett: "Re: Executing SP freezes other clients"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 3 Jan 2005 19:16:59 -0500
ATS67 wrote:
> In my accounting system which uses SQL Server with ODBC when the
> manager computer executes a report which takes about 1-2 minutes to
> complete the other clients' PCs who are using POS Screen almost
> freeze.
>
> The server is HP370 G3 w/2GB RAM, RAID 5 HD, DB Size: 3.5 GB
> Why this happen, is there a tweak or something to do to make the other
> clients (POS System users) make there computer take advantage over the
> manager computer in favor of SQL utilization.
>
> Thanks in advance
You need to understand locking on SQL Server. Reporting applications are
notorious for causing all sorts of production availability issues.
That's why many companies run large reports off hours or use a read-only
copy of the database. When the data is requested, SQL Server requests
shared locks on the pages while the data is accessed. If a writer has a
page locked, the reporting query is temporarily blocked and waits for
the resource. If a writer needs to write to a page with a shared lock by
the reporting query, it waits until the shared lock is released. Large
reporting queries that run for minutes can cause all sorts of blocking
issues.
If you have the requirement to run reports during production hours,
there are some things you can do to mitigate problems:
1- Make sure DBAs approve the SQL that creates all reports. That is,
don't let an end-user reporting tool be used without verification that
the SQL executed is optimized by SQL Server. Reporting is a very
different beast than OLTP, so the requirements (indexing mostly) are
very different.
Examine the SQL, check the execution plan, make sure the proper indexes
are in place. Some reporting tools I've seen in the past do a lot of
filtering on the client. This means that a lot of data needs to be
created, which is time consuming. Best to filter on the serverr.
2- If at all possible, design stored procedures to do the data access
for the reports. That gives you control over the SQL. Even better would
be to revoke all rights to the production tables from users and give
them access only to the stored procedures they require. That way, no one
can fire up Access and run an obnoxious query on production data.
3- Use the READ UNCOMMITTED isolation level (same as WITH (NOLOCK) in
queries). This prevents any SELECT statements from respecting and taking
shared locks on data. The queries run faster and don't block any writers
on the data. You can read dirty data, but many reports can handle small
inconsistencies in the rare case an uncommited transaction is rolled
back during the report generation.
4- Use a copy of the production database or copies of the production
tables. This database could be updated using log shipping or nightly
batch routines. Tables can be easily updated at night (assuming there is
available bandwidth and server load is low). Does the data need to be
current? Or is a 24 hour window sufficient?
5- Limit query execution time. Set a query limit of 5 or 10 seconds.
That way, if the query is long, the SQL is cancelled.
6- Set the reporting query as the preferred deadlock victim if a
deadlock should occur. You can use SET DEADLOCK_PRIORITY LOW to do this.
This is just some insurance, but by no means a solution.
-- David Gugick Imceda Software www.imceda.com
- Next message: Nigel Rivett: "Re: Executing SP freezes other clients"
- Previous message: Adam Machanic: "Re: Syntax to calculate hierarchy (ParentID)"
- In reply to: ATS67: "Executing SP freezes other clients"
- Next in thread: Nigel Rivett: "Re: Executing SP freezes other clients"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
