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
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
Jim Horn
<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.
hmm..
after some serious resarch: have yo checked for blocking processes ( run EXEC sp_who2 ).
after some serious resarch: have yo checked for blocking processes ( run EXEC sp_who2 ).
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.
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.
imho an AV should not run on a SQL Server box.
should not run on a SQL Server boxI 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.
AV puts additional load on CPU and RAM. This is imho not a good idea for boxes with permanent load.
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.
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.
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?
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?
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
Curious for my own feeds..
ASKER
After reloading the system and installing a different VGA Driver, problem seems to have disappeared.