[dba-SQLServer] Optimizing nVLDB databases

Optimizing is one of the things I love most in Sql Server. :), You
don't HAVE to index a lot of the fields to get the COUNT to move
faster.  If you open up your Query Analyzer and run a simple select
such as

SELECT COUNT(*) FROM tblMyTable, run it and then go back into the
QUERY Menu and select Tunning wizard, it will offer some Indexing
suggestions where you can accpet and apply them.

Next... your T and F fields can be modified to appropriate data such
as a BIT where the data is either 1 or null, or tinyint where you can
get them to be 1 or 0.

You'd append a column like So:

ALTER TABLE tblAuthors ADD COLUMN columnTEST BIT

After you build your test sprocs, you can re run the index tunning
wizard along w/ the execution plan to find out what other fields
should also be indexed or converted from nvarchar to bit/tinyint




On Sun, 05 Sep 2004 11:12:49 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> OK it's time to talk about how to speed things up.  I now have a SQL Server
> machine with (4) 250g SATA data drives each containing one file from the
> database.  The four files together form a single database with a single
> table which as of this morning contains 27 million records.  Doing a count
> (*) from another machine took 18 minutes to count 24 million records.
> 
> What can I do to speed up this count function?  What can I do in general to
> speed up accessing the database?  I am going to need to do cross tab type
> queries across all 65 million records to see how many people do or use X
> thing.  I will also need to pull specific fields from all 65 million records
> WHERE some field ....
> 
> There are 6000 fields (so far) and I just can't see indexing all 600 fields
> although indexes on select fields will be a necessity.  Table scans on
> Boolean values is going to take FOREVER.  The "Boolean" fields are currently
> nvar(50) fields holding a Y or N.  Will it help to go through the database
> changing these fields to a different data type?
> 
> Please be as specific (including instructions on how if at all possible) as
> you can since a general "do Y" will take me HOURS of research.  I LOVE
> research, research is good for the soul, but I only have so many hours in
> the day and I'm trying to learn a lot in a short time.
> 

-- 
-Francisco