SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update is Slow in SQL server 2014


Update is Slow in SQL server 2014

Author
Message
yuvipoy
yuvipoy
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1386
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15020 Visits: 18596
Impossible to say without more information. Could you post the table structure, the update code, execution plans etc., the more the merrier.
Cool
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39584 Visits: 32638
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12343 Visits: 8547
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 on googles mail service
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32168 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12171 Visits: 10645
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15020 Visits: 18596
Quick question, do you need assistance providing the necessary information.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search