August 1, 2014 at 12:49 am
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.
August 1, 2014 at 1:28 am
Impossible to say without more information. Could you post the table structure, the update code, execution plans etc., the more the merrier.
😎
August 1, 2014 at 8:45 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 1, 2014 at 9:23 am
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
August 1, 2014 at 9:34 am
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[/url]
Learn Extended Events
August 1, 2014 at 9:57 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 1, 2014 at 10:21 am
Quick question, do you need assistance providing the necessary information.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply