Re: So exactly what IS clustering?

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/11/04

  • Next message: Rob Oldfield: "Re: So exactly what IS clustering?" 
    Date: Sun, 11 Apr 2004 19:00:22 -0400

    I'd go with .server and .programming for this type of question.

    As for your app, the rule is - test, test, test. Generally, adding a few
    indexes doesn't hurt. Adding a lot does. Also, your app may not
    necessarily use the indexed views unless you are using the Enterprise
    Edition of SQL Server.

    Let us know if the new indexes made a difference. Also, if you are using
    clustered primary keys, the ITW isn't smart enough to consider changing a PK
    from clustered to nonclustered and then clustering on something else. You
    may want to experiment there. 

    -- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Rob Oldfield" <rob@oldfield100_wow_freeserve_yikes_co_incredible!_uk> wrote
in message news:OOR24eBIEHA.2376@TK2MSFTNGP12.phx.gbl...
Thanks for the info.  I've already saved the recommended script but haven't
run it yet.  In practical terms my problem is really whether I need to
change anything in my app in order to take advantage of the new indexes.
(And given that the ITW is reporting that it will give a 34% performance
increase it has to worth looking at).  Not being a major SQL server expert I
can't tell which newsgroup is best for telling the difference between
different types of view.  Any ideas on that one?
Thanks again.
  "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:Ot49L8AIEHA.3476@TK2MSFTNGP11.phx.gbl...
  There's clustering and then there's clustering.  ;-)
  The clustering to which this ng refers is server clustering, whereby one
server acts as the backup for another.  The backup automatically picks up
when the primary server fails.
  The clustering to which you refer is index clustering.  This is basically
the physical sort order of your table.  Thus, there can be - at most - only
one clustered index per table.  Also, in SQL Server 2000, you can have
indexed views.  Here, you create a unique clustered index on the view, which
materializes the view.  You can also create nonclustered indexes on:
  all tables
  indexed views which already have their clustered index built.
  The ITW will script the recommended indexes for you.  You should save
these to a file which you can then run at any time.
  -- 
     Tom
  ----------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON   Canada
  www.pinnaclepublishing.com/sql
  .
  "Rob Oldfield" <rob@oldfield100_wow_freeserve_yikes_co_incredible!_uk>
wrote in message news:%237$nwAAIEHA.2260@TK2MSFTNGP09.phx.gbl...
  Hi all,
  Some very basic questions here.  I have a VB.Net app that is using an SQL
2K
  server as it's data source.  Everything is working fine but it's a little
  slower than I would like.  So I've just run the index tuning wizard over
the
  SQL instance that I'm using.
  The result of that is a list of my tables specifying my existing indices
  (it's pretty straightforward stuff, so just the existing PKs), each of
which
  has a tick in the 'clustered' column.
  In addition, it's suggesting two new indexed views, one of which has a
tick
  in the 'clustered' column, the other doesn't.
  My questions:
  What is clustering?  I've looked through books on-line but can't find
  anything that explains it to me.  Can somebody explain it or have a
resource
  that does?
  Are my existing tables and views clustered?  Or is the index tuning wizard
  suggesting that I switch them (the tables) to be clustered?  I've just set
  them up in EM without doing anything clever.
  And one last question that doesn't really fit this newsgroup, but
hopefully,
  isn't too off topic: so far I haven't told the index tuning wizard to
  actually put its recommendations in place - will that just create the
  relevant views; is there a way of seeing what those views look like before
  taking this step; and, in general terms, is applying the results of the
  tuning wizard a safe activity, or do I really need to back everything up
  first?
  Thanks for any help.
  • Next message: Rob Oldfield: "Re: So exactly what IS clustering?"

    Relevant Pages

    • Re: Newb Active-Passive / Active-Active Single Instance Question
      ... The Disk space is SAN storage. ... node and one virtual server. ... cluster node as it is no longer like Windows Clustering is it that SQL 2005 ... Also, SQL Server ...
      (microsoft.public.sqlserver.clustering)
    • Re: Using sql server clustering
      ... Clustering will give that one high availability. ... other 4 can still handle the load while you fix the one that is down. ... add more server for incoming data wich is failover cluster. ... About the servers to pull data, can i use sql server clustering in order ...
      (microsoft.public.sqlserver.clustering)
    • Re: Memory Allocation - Win2k3 SP1 and SQL Server 2K SP4
      ... Microsoft SQL Server MVP ... MVP - Windows Server - Clustering ...
      (microsoft.public.sqlserver.clustering)
    • Re: So exactly what IS clustering?
      ... I'd go with .server and .programming for this type of question. ... Columnist, SQL Server Professional ... So I've just run the index tuning wizard over the ... What is clustering? ...
      (microsoft.public.sqlserver.clustering)
    • HA & Failover options
      ... instances for all the technology components (App Server, DB Server, ... Application Server 10g Release 3 and Oracle Database Release 2. ... Clustering features. ... how instantaneous is failover to the secondary site with data ...
      (comp.databases.oracle.server)