MS sql server 2012 file import very slow

Hi,

We have been having an issue with one of our super micro servers. Runs windows server 2012 datacentre R2 and sql server 2012 sp1. When trying to import a flat file it takes really long, in the region of 4 days for 6 millions lines. This never used to be the case and we have not done or installed anything on the machine. To ensure everything is in order, i reloaded the server and updated the drivers. what is interesting is that the whole system effectively becomes non responsive. The moment you kill the process everything returns back to normal. Is there anything that i can run or set to see what it is that is causing this lag? Just to mention, when you look at the processes running, CPU is on 2% and memory on 10% with virtually no HDD activity. The HDD is set up in RAID5 with 3 x 1TB enterprise WD drives.

Any suggestions?

Regards
Bernard
burny1Asked:
Who is Participating?
 
burny1Author Commented:
Seems that the VGA Driver was causing the sluggishness of the system.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Potentially silly answers>
Does the destination table have any indexes, triggers, or constraints?  If yes, remove them all.
Does the destination table have any foreign keys with referential integrity?  If yes, consider importing to a stand-alone 'staging' table with no constraints at all.  Then once the load is complete you can run scripts to validate data and insert into the final destination table.
Is there any implicit conversion going on, such as the source has a char column that is all numbers, and the destination is a numeric column?  If yes, better to make sure the data types are the same.
Does this ETL require any kind of sort?  If yes, remove it.
When does this job execute?  Better to schedule it so it starts at oh-dark-thirty where there are no other blocking processes going on.
How wide are the rows, meaning what's the # when you add up all of the memory sizes of every column?  If it's greater than 10000, then it's a real good idea to increase the Default Max Buffer Rows (assuming SSIS) to handle it.  I once had a Salesforce.com source that had a wompload of nvarchar(1000)'s, and this was necessary.
Are any of these columns nvarchar/nchar's, but there's no characters that would warrant using anything higher than varchar/char?  Unicode 'n' data types are double the column with, and a case can be made that they should be avoided.
(CPU is on 2% and memory on 10% with virtually no HDD activity)Is the package running single threaded or multi-threaded?  Multi-threaded will max the processing and increase speed, at the risk of blocking any other activity.
0
 
ste5anSenior DeveloperCommented:
hmm..

crystal-ball-small.jpg
after some serious resarch: have yo checked for blocking processes ( run EXEC sp_who2 ).
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
One more thing to verify.
Check if the Anti virus is On. If is On and don't have the SQL exceptions then add the SQL exceptions or it can produce the behavior that you are getting.
0
 
ste5anSenior DeveloperCommented:
imho an AV should not run on a SQL Server box.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
should not run on a SQL Server box
I can't agree with this statement. I always had AV in SQL Server boxes. You just need to create SQL Exceptions (files and processes) so AV wouldn't verify those but the rest.
0
 
ste5anSenior DeveloperCommented:
AV puts additional load on CPU and RAM. This is imho not a good idea for boxes with permanent load.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Everything that is security have a downside that usually is performance. Are you comfortable enough to answer to your senior managers and directors to justify a security break in the future?
I'm working for years in bank environment where security is the number #1 focus. Anyway, until now I couldn't find a poor query performance because of AV unless those cases that didn't have SQL Server exceptions enabled.
0
 
ste5anSenior DeveloperCommented:
Well, when someone is capable of compromissing your SQL Server box to drop files on them, then the attacker will imho have no problem with an AV.

Just for curiosity: How often and how do you scan the entire disks of such a SQL Server box? And how long does such a scan take?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In terms of security we can't forget nothing even when the chances to happen something are very low.
AV is a demand from the Security team not from the DBA's. DBA's are responsible to set the exceptions.

Disk scanning are make during monthly or quarter maintenance window depending on the system. Also the duration vary but can run for hours (6 to 12 in larger databases).
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can you give us some more details on how you found this out, and how you chose the VBA driver you did?  
Curious for my own feeds..
0
 
burny1Author Commented:
After reloading the system and installing a different VGA Driver, problem seems to have disappeared.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.