John W. Colby
jwcolby at colbyconsulting.com
Sat Sep 11 11:17:56 CDT 2004
Thanks for the references. I'm not sure that swapping is something I can control. My mem is just about maxed out and the database is so huge that no matter what operation you try it won't fit in memory. I need to get some indexes on fields like zip or state so that I can break the record sets down into smaller chunks and do things on smaller pieces. Unfortunately the data is padded with spaces, no idea why. But I have to figure out how to get rid of the trailing spaces economically. I have processor time, I can set up a job to strip spaces off of a field by state and go to work, do the next one that evening etc. It's simply silly that it is taking so long that I can't get anything at all done in less than 24 hours. In the end though, operations like adding indexes are by definition going to happen on the entire record set. Nothing to do there but bite the bullet, start it and pray it will complete sometime before Christmas. 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 Eric Barro Sent: Saturday, September 11, 2004 11:57 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Server is hopelessly slow John, Here are two links you might want to check out... http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20841609.ht ml http://www.sql-server-performance.com/sql_server_configuration_settings.asp To summarize some of the points in those two links... -Make sure your tempdb and logfiles are on different drives than the data--that will make a HUGE difference. -Put the database into single-user mode if possible to do the update.... (1) Swapping is probably a serious issue here, as SQL Server will attempt to keep all of the records affected in memory as much as possible. (2) TempDB usage is also large here, as that will be used for temporary storage of the records to be affected. (3) The combined disk swapping will slow your updates down dramatically (4) If all processing can be done without TempDB and Disk Swapping, process speed can be increased 100-fold or more (I have seen this in a query on my own server box), especially if TempDB and the system swap file are on the same drive (and even more so if the database is on the same drive). --- 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 John W. Colby Sent: Saturday, September 11, 2004 8:31 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Server is hopelessly slow >add it as the last column, otherwise EM will drop and rebuild the whole table Ahh, the price of ignorance. Thanks, that could be one of my problems. I just pushed all the fields down and added the field at the top (using EM table design view). 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 Michael Maddison Sent: Saturday, September 11, 2004 11:21 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Server is hopelessly slow Sometimes it pays do do things 1 at a time in EM. I don't know what you've done but... try adding the field - add it as the last column, otherwise EM will drop and rebuild the whole table, save. then change the column to increment - save. Actually I've got a nagging suspicion that EM wont let you change the field once its saved... I can't test it here at home... see if that helps. cheers Michael M -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Sunday, 12 September 2004 12:28 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Server is hopelessly slow I am not running Server 2003 YET. I have it, but I have to get the drivers for my motherboard loaded. The CD that comes with the MB does a system check at install and prevents loading unless the OS is in a list of supported OSs and Server 2003 is not in that list. Tech support for the MB company says the drivers should work so... I ended up loading XP Pro just to get up and running. I wonder if I could do an OS upgrade to Server 2003 over the top of XP Pro. Since the drivers are loaded, perhaps I could get it installed that way. I can certainly appreciate "a lot going on" but for example I tried to add an identifier field (auto increment long) to the table. AFAICT There just isn't any way to do that before the load so I have to do it when I am done. I started it running and THREE DAYS LATER my machine is still locked up. With no feedback from EM I have no idea if it will be finished in an hour or it is only on the 3 millionth row with 160 million rows to go? A few hours left or 3 years? This is no way to run a company! I re-imported a single set of 3 million records and am about to try setting up the identifier field on that subset and time how long it takes. However my first machine is still locked up trying to roll back the previous attempt on the entire database. Now I start this on my remaining fast machine. What if it locks that up for days on end? This is simply silly. There must be a way for SQL Server to write a status to a log file or SOMETHING. I just can't believe that this superpowerful whizbang database engine won't tell me whether it is doing something or simply on lunch break. John W. Colby www.ColbyConsulting.com _______________________________________________ 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