[dba-SQLServer] FYI - nVLDB performance

Francisco Tapia fhtapia at gmail.com
Tue Sep 14 22:46:11 CDT 2004


*GROAN* 

John, 
  I know I would hate to wait SOOOO long for anytype of data.  The
time bother's me but what really bothers me is the idea of waiting
that long only to find out you wanted either another field or some
alternate criteria :(

please check out www.sqlservercentral.com and check their forums, I
beleive you will find some very USEFUL tips and help w/ this
optimizing this sort of thing... :(




On Tue, 14 Sep 2004 16:38:54 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> Another set of numbers.
> 
> I built two queries to pull subsets.
> 
> One pulls every 100th record, JUST the PK column and builds a table from the
> results.  This takes about 45 seconds to complete.
> The other pulls every 100th record, all the data and builds a table from the
> results. This takes 34 MINUTES.
> 
> 
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
> Tapia
> Sent: Tuesday, September 14, 2004 1:24 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] FYI - nVLDB performance
> 
> John,
>   Thanks for keeping us updated... It seems logical to me since you are
> running backups of your data that it may be wise to switch from FULL
> loggging to SIMPLE logging now that you're doing Indexes.  This way you
> don't log every event.  Additionally restriciting the size of the log to a
> specific size will also help speed things along, this way SQL Server is not
> tied up w/ useless things such as creating virtual page files.  In general
> this is good practice, but in your case it
> will be very much more needed.   I've posted as has Eric and I beleive
> others, on how to do this, if you don't have the email, let me know and I'll
> post again.
> 
> as far as trying to use the 4gb, you may want to take a look at this... this
> will obviously help speed up performance, because up until now you've only
> been using 2gb, and yes Windows 2000 natively supports 4gb, while XP is
> restricted to 2gb... why? I dunno. (but that's one more reason for me to
> hold on to 2000 a little longer)
> 
> http://www.sql-server-performance.com/awe_memory.asp
> 
> On Tue, 14 Sep 2004 12:45:32 -0400, John W. Colby
> <jwcolby at colbyconsulting.com> wrote:
> > Just to let you know some numbers on the database as it currently
> > stands.
> >
> > First, I have two almost identical machines which I built to hold and
> > manipulate the database.  Both machines use an MSI K8N Neo motherboard
> > with built in 4 port SATA and dual channel IDE, gbit LAN.  The
> > processor is the 754 pin Athlon64 at 3ghz.  The server currently has
> > Windows 2K and 3g RAM installed.  Apparently Win2K can use up to 4g
> > ram whereas XP is limited to 2g.  Unfortunately I cannot persuade SQL
> > Server to use more than 2g RAM so I am not sure that more memory than
> > that is really useful in this context.
> >
> > The server then has (4) 250g Maxtor SATA drives and (1) 250g Maxtor
> > IDE drive holding the data files and the log file respectively.  The
> > second machine is currently running XP Pro.  Since the two new
> > machines have gbit nics built into the motherboard I bought an 8 port
> > gbit switch so they could talk at full speed.  In general I have found
> > that I can run operations from the second machine to the server over
> > gbit LAN at close to full speed, i.e. the LAN is not a severe
> > bottleneck anymore (it definitely was at 100mbit).
> 
> --
> -Francisco
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



-- 
-Francisco



More information about the dba-SQLServer mailing list