[dba-SQLServer] What is going on

JWColby jwcolby at colbyconsulting.com
Tue Oct 31 23:27:14 CST 2006


Just an update, I started a new import wizard processing the exact same file
(a copy on the E: drive) into an empty table and it is running at the
expected 3000 records / second, so far.  So it appears to have something to
do with the number of records already in the main table as opposed to a
general SQL Server slow down, raid drive slowdown etc.

I am canceling the sloooow import.  The import into a clean table should be
finished within 15 minutes or so, at which point I will try an append from
the smaller table and see if that happens quickly.  

Something funky is definitely going on though.  The cancel takes a long
time, and while that is happening, the other data import has slowed to a
crawl.  This really sucks!


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 JWColby
Sent: Wednesday, November 01, 2006 12:12 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] What is going on

This afternoon, soon after posting how fast the system is, SQL Server
"locked up".  I had imported 18 files over the afternoon, and then suddenly
the three that were processing just froze.  In task manager I would see SQL
Server activity, the processors would alternate between no activity and 50%
utilization, and when that happened SQL Server was the task using the CPU
time.  However NO records were importing.  I decided to let it run, went
away for a meeting, returned several hours later to find NO cpu activity and
the exact same number of records imported (still frozen).  I aborted, then
restarted a single import process.  It progressed, but slowly.  The next
progressed but even more slowly.  The last file that needs to be imported is
just dragging.  As in will take 12 hours or more to do just the one file (20
minutes was the average before).
 
I stopped that last process, moved the file physically out onto the smaller
raid to test whether it was a comms issue between machines.  No help.  
 
My C: (system) drive has about 10g out of 80 used.  
My D: (SQL system databases there) has about 10g used out of 200 total.
My E: drive (the main database file for this database) has used 298 G with
901 gig free (one of two Raid 6 drives) .
My F: drive (the main database log file for this database) has used 45 Gb
with 330gb free (the second Raid 6 drive).
 
I have huge amounts of free space on the raid drives, well over 700 gb on
the one, and 300 gb on the other.  
 
The main db file is now up to about 280 mb but by my calcs that is about
normal (correct) given the amount of data input so far.
 
At this point I am on the last file to import, sitting at about 1 million
records imported out of 3 million to be imported.  It is importing them
about 1000 records every three seconds.  There are about 63 million records
in the table.  No indexes, no triggers, nothing like that.  Just raw data.
This thing should be flying.  Instead it is crawling.  CPU usage is running
12% to 25%, with SQL Server.exe and MMC.exe using that.
 
What the heck is going on?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list