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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
burny1Author Commented:
Seems that the VGA Driver was causing the sluggishness of the system.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.