Re: Clustered Indexes
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 02/20/05
- Next message: David Gugick: "Re: VSS with SQL server"
- Previous message: D: "what is the best way to store this data"
- In reply to: Jon A: "Re: Clustered Indexes"
- Next in thread: Jon A: "Re: Clustered Indexes"
- Reply: Jon A: "Re: Clustered Indexes"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 20 Feb 2005 12:40:16 -0500
Jon A wrote:
> memberid int 4,
> serialnum char(17),
> stocknum char(20)
> This is the primary key and defines uniqueness in the table. A member
> can have multiple items and the same item may have multiple stock
> numbers.(please note I didn't design this I am just trying to solve
> there issues of performance. They do not want to ovewrhaul the
> design.) The primary key is now non clustered. All lookups for
> updates or insert are done by searching on these fields and ordering
> the results on these fields. The only thing is they process a member
> at a time. The performance problem they have is as the tables have
> grown, after one night of uploads they need to stop SQL server and
> restart. Or the next nights run goes from 4 hours to 12 hours. The
> memory in SQL grows until there is little left for the Web Server. I
> believe the memory problem is due to the fragmentation of the tables
> due to being heap tables. The machine is 2 processor P4 with 2 gig
> ram and a SCSI 3 disk raid 5 with 32 meg ram. The batch program runs
> on the server and is single connection,single threaded, and nothing
> else is running(no lock problems). Would it help the fragmentation to
> Cluster on memberid because as the batch runs it goes a member at a
> time?
First I would get rid of any sorting operations against this table.
ORDER BY clauses are rarely required by production applications and add
unnecessary overhead.
If you are processing a member id at a time (how are you doing this?),
then it does make some sense to use a clustered index. If you use a
set-based operation on the ID, having a clustered index will help a lot.
If you are accessing IDs one at a time, then it may not help much over a
non-clustered index as other processes may cause the disk heads to move
somewhere else between ID queries. At the very least, you'll get rid of
the bookmark lookup operation which can be responsible for 50% of a
query's overhead.
But the clustered index design does pose some problems. On one hand,
clustering the entire PK should help your queries and enforce the PK
constraint. On the other hand, large clustered keys cause non-clustered
indexes to grow (since the clustered key is part of the non-clustered
index). But since you have no non-clustered indexes right now, I would
cluster on the PK.
The memory issue is a configuration problem at your end. SQL Server uses
memory as it reads data, and generally does not release memory back into
the system unless specifically asked to do so by the OS. You are running
a web server on the same box. When running multiple applications on the
same box, it's imperative to set a maximum memory limit for SQL Server,
or you'll likely run into the situation you already see. That is, SQL
Server uses so much memory that other services are left memory-starved.
One reason SQL Server can chew up memory fast is when it runs many
unoptimized queries. As the data is read, it is loaded into memory.
Memory is not released, but the pages in memory are likely being swapped
as others are read. So check for queries that require tuning (like the
nightly upoptimized updates).
But the best way to avoid SQL Server using too much memory is to set a
memory limit.
-- David Gugick Imceda Software www.imceda.com
- Next message: David Gugick: "Re: VSS with SQL server"
- Previous message: D: "what is the best way to store this data"
- In reply to: Jon A: "Re: Clustered Indexes"
- Next in thread: Jon A: "Re: Clustered Indexes"
- Reply: Jon A: "Re: Clustered Indexes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
