Re: Performance Improvements: Hardware vs. DB Design
From: David G. (david_nospam_at_nospam.com)
Date: 09/20/04
- Next message: Phillip Windell: "Re: Use datasource behind a dmz?"
- Previous message: Mike Epprecht (SQL MVP): "Re: Setting up Log shipping."
- In reply to: Sean: "Performance Improvements: Hardware vs. DB Design"
- Next in thread: Adrian Edwards: "Re: Performance Improvements: Hardware vs. DB Design"
- Reply: Adrian Edwards: "Re: Performance Improvements: Hardware vs. DB Design"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 13:00:55 -0400
Sean wrote:
> Hi
>
> I recently designed & developed a data warehouse for a client of ours.
> The project has been quite a success, but they have since loaded the
> database with a substantial amount of (historic) data, and have given
> access to the query & reporting system (Business Objects) to many more
> users. As a result, the query time on the reports is beginning to
> slow.
>
> I need to advise the client on what should be done to improve
> performance again.
>
> I'm quite familiar with what can be done on the design side of the
> database (i.e. indices, aggregate tables, etc.), but I am not very
> sure about the cost/benefits from a hardware perspective. As a
> consultancy, the cost of our services (to build aggregate tables, etc)
> would be quite expensive. It might be a better option for the client
> to improve the spec of the server instead, but I am reluctant to
> recommend this option without knowing that there would be guaranteed
> benefits. In other words, if they were to double the processing power,
> would the query time effectively halve? What about memory - i.e. would
> more memory help with multiple-users, but not affect the speed of the
> reports? Is 4GB the max memory that Windows 2000 can have?
> [I can't remember the exact spec, but the server's got something like
> 2 Xeon dual-processors, 4GB RAM & Win2k.]
>
> Can anyone recommend a study or white-paper on the performance
> improvements in relation to hardware upgrades, or something similar?
>
> Otherwise, what other options do I have? Am I overlooking something
> like two load-balanced servers?
>
> Thanks for any help / recommendations!
> Sean W.
Unfortunately, I think many companies are inclined to add more server
without first trying to tune the database. You've gone ahead and
designed a database for the customer and added the necessary RI and
indexes. But now that you see the SQL executed, someone needs to tune
the queries, revise the indexes if necessary, and verify the reporting
system is executing SQL in an efficient way: For example, is it using
stored procedures? Do users have Ad Hoc access to the database to query
whatever they want? Does the reporting tool bring back large result sets
and filter on the client PC? Do the queries use a READ UNCOMMITTED or
NOLOCK query option to keep locks to a minimum?
My point is that without performance tuning the queries, it's really
impossible to know whether adding more hardware will give you anything
but a temporary reprieve of poor performance.
When I hear about problems like this, I like to remember an old client
that had a couple hundred people hitting a SQL Server database which ran
on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
I hear about performance problems on quad-Xeon systems with 3GB RAM (as
an example), I get concerned that a company is considering throwing
another $150K at a new server.
Ad Hoc reporting systems are notoriously bad on performance, especially
if users don't run canned reports and can throw what SQL the end-user
reporting tool can generate at the server. The amount of data queried
can easily move old data out of cache and force the next query to read
that data from disk, which is really slow. Adding more memory is an
option if plan reuse is the issue. And since memory is cheap, it can't
hurt to add all you can. Adding more hardware will help, but may not
really be a long term solution.
However, nothing beats being able to aptly use an index to generate a
result set. Table/Clustered Index Scans are a killer on big tables.
You may want to consider placing query limits on ad hoc queries using
"SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
those queries cosuming excessive CPU and see if the database or the
queries can be tuned.
-- David Gugick Imceda Software www.imceda.com
- Next message: Phillip Windell: "Re: Use datasource behind a dmz?"
- Previous message: Mike Epprecht (SQL MVP): "Re: Setting up Log shipping."
- In reply to: Sean: "Performance Improvements: Hardware vs. DB Design"
- Next in thread: Adrian Edwards: "Re: Performance Improvements: Hardware vs. DB Design"
- Reply: Adrian Edwards: "Re: Performance Improvements: Hardware vs. DB Design"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
