Re: Clustered Indexes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/19/05 

Date: Sat, 19 Feb 2005 16:32:19 -0500

If MemberID is the column used most often to fetch and update the rows then
it makes a lot of sense to cluster on it. You can alleviate a bookmark
lookup with each call. 3000 updates a night is trivial and just make a
reasonable fill factor to account for some inserts and minor updates. Even
forgetting about all that a clustered index can actually increase
performance on inserts under the right conditions. And without a CI there
is no way to control fragmentation. 

-- 
Andrew J. Kelly  SQL MVP
"Jon A" <JonA@discussions.microsoft.com> wrote in message 
news:54770860-AA65-45A8-8C7D-F6169FD0AAFF@microsoft.com...
> "David Gugick" wrote:
>
>> I would leave the PK index as is right now and look into adding an
>> additional index to help the nightly updates and any other queries that
>> are run against the table that have no index to help them.
>>
> I tend to be in agreement with leaving this primary key non clustered, but 
> I
> am wondering since all work with the data is done on member id would that 
> be
> a good choice for a clustered index and let SQL add the row id so it is
> unique or just leave the table without a clustered index? Since the update
> program pulls data by member is it more efficient to have it sorted that 
> way.
> And just use a small fill factor along with periodically updating stats 
> and
> performing index maintainance.

Relevant Pages

  • Re: Clustered Indexes
    ... then inserts will probably not cause Page Splits. ... > Either way, updates, deletes will likely cause splits to occur. ... > defrag it because it is a Heap (No clustered Index) ... > You do NOT have to cluster on the Pkey. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server Cluster
    ... activity times to shift cluster resources. ... host nodes with different elevels of critical updates if you can't move ... I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.clustering)
  • Re: W2K3 Updates
    ... updates can be tested and worked out. ... MVP - Windows Server - Clustering ... > financial reasons) have a test cluster... ... > when I don't know if my installation will survive... ...
    (microsoft.public.windows.server.clustering)
  • Re: SQL Server Cluster
    ... > activity times to shift cluster resources. ... > host nodes with different elevels of critical updates if you can't move ... > I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL Server 2 Node Cluster Administration
    ... critical security updates and Service Pack you would still use ... Windows NT/2000/2003 Cluster Technologies ...
    (microsoft.public.windows.server.clustering)