Poor Performance 64-Bit vs. 32-Bit, or 1 CPU vs. 2 CPU
-
You would think that throwing more memory, processors, and bits at SQL Server 2005 (Dev Ed.) would increase overall perfornce of SQL Server. I'm finding the opposite, however.
I have two machines I use for testing. My desktop machine is an HP xw8600, dual XEON quad-cores (16-cores total), 8GB RAM, with 15K SAS drives. The OS & page file are on a single SATA (7600 rpm) drive, with MDF files on two (RAID-0) SAS 15K drives, and the LDF files on two (RAID-0) SAS 15K drives. I'm running XP Pro 64-bit, and SQL Server 2005 Developer Edition 64-bit (9.0.3042). I run a query that I've been trying to tune on this machine, and it takes about 9-minutes to run.
My second machine is an HP laptop with a single, dual-core proc, 2GB RAM, and a single 5400 RPM drive. It's running XP Pro 32-bit, and SQL Server Standard Ed. 32-bit. The exact same query runs in 3-minutes on the laptop!
I've read about Processor Affinity, I/O Affinity, and Max Degree of Parallelism. I have set Max Degree of Parallelism to "1" using sp_configure:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
That made no difference at all.
I set Processor Affinity, and I/O Affinity in SSMS, and it still didn't make a difference.
Has anyone experienced anything like this? I specifically requested this monster machine so that I could run custom client upgrade scripts at the speed of light. As is, I'm better off on my puny little laptop.
Any help would be *greatly* appreciated!
Linque
-
First thing when investigating issues like this is to look at the execution plans. Are they any different ?
Do they make use of paralleism at all?
I have no experienc with quad-core machines, but in the past I've seen cases there execution plans using parallelism performed much worse that without and adding the MAXDOP=1 ndex hint improved performace. Also make sure all the indexes are the same and they are not too fragmented.
[font="Verdana"]Markus Bohse[/font]
-
Once you've set the Mac degree of Parallelism, you might care to recompile your query and/or clear the proc cache, just to be double-sure that it reevaluates the execution plan.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I have the laptop and the workstation side by side. I rebooted the workstation just to make sure all settings took place, and buffers were flushed. The execution plans are the exact same, and I didn't see any mention of parallelism. I'm truly baffled. I'm about to install SQL Server 32-bit in a VM on this machine and see if it runs faster there.
If anyone else has seen this before, and has found a fix for it, please share the wealth...
-
Interesting article about 64-bit SQL Server.
So, I've installed SQL Server Standard Ed. SP2 (32-bit) in a VM on my dual Xeon workstation. The same query that took 3-minutes to run on the laptop, and 9-minutes to run on my workstation only took 2-minutes to run in a VM (1-proc w/2GB RAM).
I'm going to uninstall SQL Server 64-bit, and install 32-bit instead. I'll leave the 64-bit OS, and see what happens.
Wish me luck.
Linque
-
What are the differences in your CPU time, Disk Idle %, and hard & soft pages faults?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Also, turn CPU & IO statistics on in Query analyzer and let us no what the comparative numbers are, especially logical & physical IO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
The 32-bit version on a 64-bit OS did not help. My next step is to grab another disk, install Windows 2003 Server 32-bit, SQL Server 32-bit, and give it another go. I have already lost a full day to this, so I won't be able to do more testing until next week.
As a final test before leaving work last night, I reinstalled 64-bit SQL Server again, and ran a script that took 18-hours to run on my old, 32-bit, single-proc, dual-core machine with 2GB of RAM. I'm hoping that previously long running scripts will benefit from the additional memory, 64-bit SQL, and Xeon quad-cores. I'll post the results later this morning.
-
WOW! So I came in this morning expecting the script to still be running, and was pleasantly surprised to see that a script that took 17 hours and 32 minutes on my old workstation only took 3 hours and 18 minutes to run on my new one. This script is the first of six or so upgrade scripts that will be sent to clients. Its task is to get the client database aligned with the new schema. It renames any table that needs to be modified to "tmp_TableName", creates a new modified table, inserts the data from the temp table back into the new table, then drops the temp table. At the end of the script all foreign keys are created. In some cases there are 8 to 15 million rows in tables that are 213 column wide (don't ask -- before my time). Needless to say, I'm very pleased with these results.
The script I referenced in my original post is part of the third script. It's a procedure that uses a cursor to populate one table using data from another table. So I guess I need to revise my original complaint, and say that *cursors* (at least in my case) run better on my laptop than my new workstation. I'm somewhat relieved that I've narrowed it down to this. At least I can focus my energy on cursor performance now. Maybe I can figure out a way to not use a cursor...
-
Dumb question, but why are you using a cursor to move data from one table to another instead of a set-based query?
😎
-
I think that's a valid question. I'm relatively new to the company I work for, and some of the queries are inherited. I need to spend some time (when I have some) going over some of these. Procedures aren't my strongest point, so I'm sure I'll be spending hours on this site alone researching it.
The table that records are being inserted into is capturing historical data, so it's important to go line by line and compare with what has already been inserted. But you're right, there is probably a better way to do this.
Do you have any good links on cursors vs. set-based queries?
-
well - you're here already - so you're most of the way there already:). Actually - there's a ton of stuff on here about set-based vs. cursors. Just type the word cursor in the google box on this page, and you should get more than enough....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
But that would require me doing the research. 😉 All kidding aside, SQLServerCentral.com has been a life saver. If I can't Google something quickly, then I head straight here.
I just typed in "cursors" and the first hit was "Eliminating Cursors". Seems like a good place to start.
-
Well, the Cursors are probably killing any ability to parallelize so your 2x4 core desktop is probably using the same number of cores as you dual-core laptop: 1.
And cursors tend to dump stuff in the TempDB, which could be making your disks thrash if your TempDB is on the same volume as your MDF.
These two things alone could explain your performance problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
I will add that I hope you undid your affinity settings too and put them back to default. I have yet to see anyone less than a TRUE expert do anything with those settings that wasn't completely hosed (except for one that just fiddled with them iteratively and finally stumbled across a set that made things measurably faster).
Unless you are advised by PSS or CAT, you probably should leave those settings alone. 🙂
I agree with others - probably some form of looping going on (either explicit or implicit through the use of functions et al), or incredibly complex calculations that can't be parallelized.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply