JWColby
jwcolby at colbyconsulting.com
Wed Nov 1 20:09:26 CST 2006
At this point the import is finished. After a 12 hour!!! shrink operation the last file came in at the normal speed (25 minutes). I am now trying to add a PKID which is long etc. This looks like it is going to take another 12 hours. Dropping and adding indexes back is a grand idea, but on a 65 million record table when it takes 12 hours to build the index... Somehow I don't think I will drop them once built!!! The log file for this is now up to 118 gbytes and the main data file is back up to 221 gbytes after taking 12 hours to shrink it down to ~100 gbytes. TWELVE HOURS to shrink the damned file!!! Man oh man, I'm 52 years old. I could DIE before I get any real work done here. Once this is built, it is totally a read-only kind of operation. This is just a list of names / addresses and various info fields, which will be queried for counts of things like "count of names in a zip", "count of all people in an income bracket etc. The data still has to be cassed (address validation), ncoa'd, but after that the only thing that will be done is an occasional ncoa operation. I have to believe I am running into a SQL Server "setting" of some sort, where "X% of the data is trying to be cached or something. Since the table is so large it can't. Just a guess but jeeze, I could just about do this in a VBA program faster than SQL Server is handling this thing. There are indications that something isn't right. The page file is maxed out (I guess) at 1.84 gb, unchanging. I actually have 2 gb of RAM in the machine, and the page file is set to 4092 mb but no more or less is being used, it is a flat line at this point in the task manager. The CPU usage is sitting stable at 50% although the cpu usage history shows a varying graph. 8361 handles, 394 threads, 30 processes, all stable as a rock. It is chunking away, but I started this process at 2:30 this afternoon and it is now 8:55 so whatever it is doing it isn't doing very darned fast. Jim seems to think this is an OS issue, why I am not sure. If it ever comes back perhaps I'll go try to install Win 2003 Server again but I have my doubts. I have to admit it is nice knowing that the machine will be busy the whole time I'm asleep though. I didn't put all this money into this thing to see it sit around idle. ;-) John W. Colby Colby Consulting 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: Wednesday, November 01, 2006 8:06 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on Hi John, You are out of my league but... a couple of ideas come to mind. Set the database recovery Model to simple. Turn off Auto update and auto create statistics. Drop any/all indexes on the table. You can script the drop + create so you can add them back later. If you have calculated fields drop them. (add em back later) Set the database size to double what you think the largest it should be. Turn off auto grow. Shrink the log, it shouldn't grow too much on simple recovery... Check your event log, may be some info there. In short, try and stop as many extra processes during the import as possible... might help. http://www.sswug.org has good info and it own mailing lists. cheers Michael Maddison DDI Solutions Pty Ltd michael at ddisolutions.com.au Bus: 0260400620 Mob: 0412620497 www.ddisolutions.com.au -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Thursday, 2 November 2006 9:52 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on LOL, borderline insanity. I can assure you that I am FULLY insane. Bwaaa haaaa haaaa. I am using SP Pro. SQL Server ran just fine on 2K pro, XP Pro is the 2K Pro base code. Why would it not run on that? I keep hearing that it isn't supposed to, but it does. I can tell you I would be irritated beyond belief if I went to Win 2003 server (which I have but haven't a clue how to install, since this is a desktop machine in the end) and I still had this issue. I actually tried to install 2003 server (in fact I have a disk with the beginnings of the install) and got to questions that implied I was supposed to know what I was doing (and I don't) so how am I supposed to install 2003 so that I can test your theory? I am not a notwork / OS admin, I am a developer. MS themselves push SQL Server lite as a developer tool. If anyone wants to talk me through a 2003 server install I will do that but... John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, November 01, 2006 5:19 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on Hi John: I am trying to cover all the bases. The problems initially appears to be with the MS SQL sever setting as they run fairly indendendantly from an OS. If the problem is not in the MS SQL setup, accumulating non responsive results sort of negates the hardware being the issue, then it has to be in the process. If it is absolutely not in the process then it must be in the MS SQL settings. Please tell me you are using server OS as it would be border-line insanity to use a desktop type OS and then I would assure you, without hesitation, that that is the problem. HTH Jim