Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update is Slow in SQL server 2014 Expand / Collapse
Author
Message
Posted Friday, August 1, 2014 12:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Hi,
Updating a statement takes 120 millisecond in SQL server 2000
Updating the same statement takes 660 millisecond in SQL server 2012.
2000
Os: win server 2003 32 bit
2012
Os: win 7 64 bit
in order to check the performance RAM allocation for both is 2GB.
i have checked this for 5000 cycle , all the cycle having same performance impact
for 2000 it ranges from 80-120(min- max)
for 2012 it ranges from 600-660(min- max)
both the databases are fresh only some 100 rows in each table.
sp_dbcmptlevel are 80 for 2000 and 110 for 2012
Why SQL server 2012 is taking more time.
Post #1598590
Posted Friday, August 1, 2014 1:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 1,908, Visits: 5,251
Impossible to say without more information. Could you post the table structure, the update code, execution plans etc., the more the merrier.
Post #1598597
Posted Friday, August 1, 2014 8:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 13,752, Visits: 28,148
2gb is a very small environment. 2012 has a lot more functionality built in than 2000 does. It's entirely possible that 2000 runs faster in such a tiny environment as compared to 2012.

But that's a guess. It could be differences in the execution plans caused by differences in statistics or just in differences in the optimizer and how it resolves your queries. It could be different levels of blocking or contention for resources on the servers. It could be a whole slew of other things. I'd lean towards issues with memory or, stats or the optimizer.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1598720
Posted Friday, August 1, 2014 9:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 4,359, Visits: 6,195
I also note you are comparing a desktop OS to a server OS. And 32 bit vs 64 bit. Virtualized too by any chance? Did you update all stats with a full scan after upgrading the database?

As others have said, there are about a bajillion things that could normally cause this type of behavior, and you have compounded that with at least the items above.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1598734
Posted Friday, August 1, 2014 9:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,609, Visits: 15,468
Running 2012 on a desktop os that is probably running a bunch of other things at the same time?

Probably have AV enabled on the desktop too?

Disks are configured differently?

Faster disk on the 2000 box than on your desktop (common thing there because it is server vs. desktop)?

Numerous things.

How does the database perform when installing SQL 2000 on the desktop and doing a more apples to apples comparison?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1598739
Posted Friday, August 1, 2014 9:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 1,660, Visits: 4,749
The first thing to do is compare how the execution plan and number of page read/writes differ between the two databases. You can then focus on why. This can be done using "STATISTICS IO" and "SHOWPLAN_TEXT" settings.
Post #1598752
Posted Friday, August 1, 2014 10:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 1,908, Visits: 5,251
Quick question, do you need assistance providing the necessary information.
Post #1598761
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse