April 21, 2009 at 7:48 am
Hi, I have a package that reads a 60MB text file, do some minor converting and inserts in a Plain table in a SQL Server table.
This package, on my dev machine, runs in like 15-20 seconds.
Once I deployed it on the server for testing purposes, the same file takes about 8 MINUTES to run completely.. which is completely insane.
I thought the disk would be an issue, since the C Drive was only 20 GB, and 50% was empty, so I moved my database to the D Drive, which has plenty space. It did not help.
I thought maybe the memory, or the processors were not working right either... but the processor never gets past 10%, and memory stays at about 200MB for SQL Server, when there is 4GB available on the server..
So the package just seems not to be using the full server strength... What am I doing wrong?
I checked, and when the launching the job manually from the SQL Agent, the process gets launched with windows security, and the user is a sysAdmin, so that shouldn't be an issue either...
Anything else I could check, or maybe it's pretty basic and I can't see the solution,
Thanks,
Cheers,
J-F
April 21, 2009 at 9:00 am
I checked to see what was the bottleneck on this operation, and got the same time when trying to push from the dev environnement, and insert into the QA Server. So I'm pretty sure the SQL Server is slow on that server...
I ran this for testing the speed.
dbCC dropcleanbuffers
GO
dbcc FREEPROCCACHE
GO
set statistics IO on
set statistics time ON
select top 1000000 identity(int,1,1) as N into dbo.Tally
from master.sys.syscolumns sc1 cross join
master.sys.syscolumns sc2
set statistics time OFF
set statistics IO off
--select * from Tally
drop table TALLY
Simply creating a table with a million rows, on my dev environement, and on the QA Server.
Here are the results I got:
DEV:
Table 'syscolrdb'. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 115, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 2, logical reads 19, physical reads 1, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1172 ms, elapsed time = 1707 ms.
(1000000 row(s) affected)
QA Server :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'syscolrdb'. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 115, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 2, logical reads 11, physical reads 1, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 11727 ms.
(1000000 row(s) affected)
As you can see, the CPU time is the same, but elapsed time is 10 times the CPU time on the QA Server.
What can be causing this?
Thanks,
Cheers,
J-F
April 26, 2009 at 6:43 am
Are the servers all the same spec and config i.e W2K3? One thing that springs to mind is do you have AV software on all servers? It could be this scanning the text file or the folder/drive where the file is slowing the server down.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply