August 29, 2013 at 2:21 pm
Our Server1 Db is running on SQL server 2008, and the server has 2 processors, 2.67 GHz , 60 GB memory, 64 bit operating System, Windows server 2008 R2, Enterprise.
The Server2 server Db is running SQL server 2012, and the server is 2 GHz 2 processors, 128 DB memory, 64 bit Operating System, Windows server 2008 R2, Enterprise.They are both on the same network and use SAN drives with similar performance capacity.
The CPU_Count used on Server2 is 32, while it is 24 on production.
They both have the same database, as we restored the db on server2 from server1 db.
We have very large difference in the time taken to run an ETL on the two servers. It takes 6-7 hrs on server1, while it takes 24-28 hrs on server2
Both are running the same code, and processing the same amount of data. We have run several rounds of this ETL on both servers, but the timing is still the same.
We were expecting it to take much less on the more robust server2 with latest version SQL.
What can we look at to improve performance on server2.
August 29, 2013 at 2:27 pm
Did you update the statistics on the DB after you restored it to the new server?
August 29, 2013 at 2:35 pm
deep3.kaur 98681 (8/29/2013)
Our Server1 Db is running on SQL server 2008, and the server has 2 processors, 2.67 GHz , 60 GB memory, 64 bit operating System, Windows server 2008 R2, Enterprise.The Server2 server Db is running SQL server 2012, and the server is 2 GHz 2 processors, 128 DB memory, 64 bit Operating System, Windows server 2008 R2, Enterprise.They are both on the same network and use SAN drives with similar performance capacity.
The CPU_Count used on Server2 is 32, while it is 24 on production.
They both have the same database, as we restored the db on server2 from server1 db.
We have very large difference in the time taken to run an ETL on the two servers. It takes 6-7 hrs on server1, while it takes 24-28 hrs on server2
Both are running the same code, and processing the same amount of data. We have run several rounds of this ETL on both servers, but the timing is still the same.
We were expecting it to take much less on the more robust server2 with latest version SQL.
What can we look at to improve performance on server2.
TON of things could be at fault here.
Have you done any during-ETL fileIO stall and waitstats analyses?
Also run sp_whoisactive during the run.
How are server configurations?
Any virtualization in play here?
Patch levels on SQL 2012? there is a VERY nasty memory bug related to NUMA stuff that was not fixed until SQL 2012 SP1 CU4. If you aren't there that is the first thing I would do assuming your hardware is physical and NUMA (which almost all servers are these days). I have had several clients and several more forum posters caught by that bug.
Since you are doing ETL I might also check network stuff if you are doing stuff from off-server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2013 at 3:37 pm
Have you run a trace to see whether it is an overall slowdown, or whether it is some particular statement(s) that get hung?
I realise that this will take some time given your outrageous execution times, but this analysis is absolutely required to give you more knowledge.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 30, 2013 at 9:56 am
Auto Create Statistics and Auto Update Statistics is set to true for all the Dbs that this ETL touches. On your suggestion, i also updated the statistics for all DBs manually before running the ETL. Waiting to see if it helped.
The sql command Select @@version shows that SP1 is installed:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Nov 11 2012 23:13:51
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
How do i check if it is SP1 CU4?
Thanks.
August 30, 2013 at 10:54 am
They post all the updates on http://blogs.msdn.com/b/sqlreleaseservices
but its the build number you need to look at. The blog has a link to the MS KB article on the update and you can find the build number there (for SP1 CU4 its 11.0.3368.0) and there is a CU 5 out now.
August 30, 2013 at 1:39 pm
It is often appropriate in DW loading processes to DISABLE autostats stuff before load, then manually do stats after the load. you really don't want them firing multiple times during the build process, which can easily happen.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 30, 2013 at 1:44 pm
we are dropping indexes before inserts and re-build after the inserts are complete.
My main issue is why the ETL is taking more time on Sql 2012 then it is on SQL 2008. The code and the data is the same on both servers.
August 30, 2013 at 3:46 pm
deep3.kaur 98681 (8/30/2013)
we are dropping indexes before inserts and re-build after the inserts are complete.My main issue is why the ETL is taking more time on Sql 2012 then it is on SQL 2008. The code and the data is the same on both servers.
Yes, we have heard that now. Have you made any effort to analyse whether the factor in speed decrease is the same across the board, or if only particular parts of your operation?
That is the first question to answer, because that determines where you start looking.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 30, 2013 at 4:30 pm
I am working on this analysis now. Will post it as soon as I get to know. After the update stats, a step that used to take 12 hrs, finished in 7 hrs, which is good, but still not as good as on the other server with sql 2008, where it takes 4 hrs.
Also, why did stats update help, why was auto stats update not working. I don't understand that.
Thanks.
August 31, 2013 at 3:01 am
deep3.kaur 98681 (8/30/2013)
Also, why did stats update help, why was auto stats update not working. I don't understand that.
Hard to say when we don't know the exact steps you did. For instance, when you update statistics manually, did you use FULLSCAN?
Note also that when you rebuild an index, the statistcs for that index is updated (with fullscan).
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply