Eric Barro
ebarro at afsweb.com
Sun Sep 5 11:04:27 CDT 2004
John, In terms of the hardware side you will want read/writes to the log file and read/writes to the database to go to separate hardware controllers and disk sub-systems. SQL server keeps track of every transaction in the transaction log file so it makes sense to have that write to its own controller/drive sub-system. A second processor and lots of RAM (max out your RAM if you can) will help a lot. But of course we all know that a poorly designed db/application can bring any high end system to its knees. I've seen it happen too many times... --- Eric Barro Senior Systems Analyst Advanced Field Services (208) 772-7060 http://www.afsweb.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Francisco Tapia Sent: Sunday, September 05, 2004 8:44 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com