none
Difference between SQL 2012 and SQL 2008 R2

    Question

  • Hi everyone

    I am doing batch inserts in a table that has a index on it and after running approx 9 batches, on a machine that has better hardware but has SQL 2008 R2 the process takes 59 minutes to run, say that is Machine A. On the other machine (Machine B) that has SQL 2012 running the exact same proc takes 28 minutes to run.

    The only thing that is running on Machine A is replication, Machine B does not have that.

    Please explain how come with much much better hardware why would Machine A not show better performace timings.

    If it helps I did the execution plan, Machine B showed this meesage on sort and Hash commands "Ooperator uses tempdb to sill data during executon  spill level 1" Hash and Sort steps took less time on Machine B though, but the over all cost of steps for each insert batch is teh same on both the machines, that is 9 %

    Thanks

    -Sarah

    Tuesday, May 01, 2012 10:50 PM

Answers

All replies

  • Anyone pls... Need help with this ASAP..

    Thanks

    -Sarah

    Wednesday, May 02, 2012 7:52 AM
  • What type of replication are you using?

    If Machine A is a transactional publication database then there could be some contention on the log by the log reader agent(s) which can affect performance.

    If Machine A is a merge publication or subscription database then the merge triggers can definitely affect performance.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by Peja Tao Thursday, May 03, 2012 2:58 AM
    Wednesday, May 02, 2012 8:07 AM
  • Hi everyone

    I am doing batch inserts in a table that has a index on it and after running approx 9 batches, on a machine that has better hardware but has SQL 2008 R2 the process takes 59 minutes to run, say that is Machine A. On the other machine (Machine B) that has SQL 2012 running the exact same proc takes 28 minutes to run.

    The only thing that is running on Machine A is replication, Machine B does not have that.

    Please explain how come with much much better hardware why would Machine A not show better performace timings.

    If it helps I did the execution plan, Machine B showed this meesage on sort and Hash commands "Ooperator uses tempdb to sill data during executon  spill level 1" Hash and Sort steps took less time on Machine B though, but the over all cost of steps for each insert batch is teh same on both the machines, that is 9 %

    Thanks

    -Sarah

    You did not mention whether or not the log reader agent was set to 'continuous'.  Your [machineA] may be trying to replicate each command as its issued, instead of batching them up for distribution on some other periodicity (like 15 mins).  Another thing to check is to be sure your distribution database has enough space (is its log full?  is its data file full?, etc.)

    Also, the symptom could be unrelated to replication at all; if the database / xact log in question on [machineA] is full, or out of drive space.  It could be [machineA] is busy with other processes (though the implication is that *only* your process is running on both machines, I'm not sure that's what you meant).

    • Proposed as answer by Peja Tao Thursday, May 03, 2012 2:58 AM
    Wednesday, May 02, 2012 12:35 PM
  • @Sarah

    Could you please post the execution plan on both Machines?
    In addition, Batch inserts will use the space in tempdb and consume the memory in Stolen memory.You could run DBCC FREEPROCCACHE to clear the cache to ensure Stolen memory is enough for SQL Server.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, May 03, 2012 3:07 AM
  • Sure, I have the execution plans in files, how can I post them here, is there a way that I can attach them? Thanks very much for your help on this..
    Thursday, May 03, 2012 3:34 AM
  • @Sarah

    You could upload execution plans to some shared network folder like 'Skydrive'.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, May 03, 2012 4:23 AM
  • Ok it is on SkyDive documents, how can I share it with you to see. Thanks much for your help on this.

    Let me know pls.

    Thanks

    Thursday, May 03, 2012 5:13 AM
  • @Sarah,

    You could use a shared link in Skydrive and post here.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, May 03, 2012 6:00 AM
  • ok Lets try this

    https://skydrive.live.com/redir.aspx?cid=2bae9ae84d317730&resid=2BAE9AE84D317730!128&parid=2BAE9AE84D317730!127&authkey=!ADWnnTNcScfSLeg

    Please try this and let me know. It has the documents for execution plans for both the proc runs on two different machines,

    Thanks again, let me know pls

    -Sarah

    Thursday, May 03, 2012 7:29 AM
  • Hi Sarah,

    After looking through your execution plans, the total cost for those two is not much different. SQL Server DB engine will decide the cost percentage for the main steps 'Hash match','Sort' and 'Cluster index insert'. If all the schema setting is the same on those two DBs, you may take a look at the IO system of SQL Server (location and speed of Disk) ,the recovery mode(bulk copy or full) and the temp DB configuration.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, May 08, 2012 9:28 AM
  • Thanks much Peja

    Can you pls elaborate a bit more on

    "you may take a look at the IO system of SQL Server (location and speed of Disk) ,the recovery mode(bulk copy or full) and the temp DB configuration."

    How can check the above settings, like IO system of SQL Server (location and speed of Disk) and temp DB configuration

    Recovery mode of both the dbs is simple

    Thursday, May 10, 2012 9:11 PM
  • Hi Sarah,

    Please refer to these online articles:

    Working with tempdb in SQL Server 2005

    SQL Server Best Practices Article


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Peja Tao Thursday, May 17, 2012 2:04 AM
    Friday, May 11, 2012 2:12 AM
  • Thanks much for your help on this, yes it looks like the issue is because these two servers are running on two different machines with different harrdwares. Believe the difference iss due to that.

    Thanks again

    Tuesday, May 29, 2012 7:42 PM