[dba-SQLServer] What is going on

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    




More information about the dba-SQLServer mailing list