SQL Server 2008 64-Bit vs 32-Bit Performance

I’ve got an x64 machine I do most of my “hard core” development work on, that I’m very happy with. I’m running Windows 7 Ultimate x64 and have had few problems. The box only has 4GB RAM, but I almost never hit the ceiling with that, no matter what the heck I do.

But recently, we needed to do some work on a database that has some tables with close to 6 million rows, and I needed to build FullText catalogs for some of them.

That’s where SQL Server 2008 x64 crapped out. I had memory consumption issues that caused me to have to hold the power button down for 4 seconds just to be able to “get out of Dodge” if you will -- several times too. Mouse didn’t work, machine was unresponsive, etc. – just building a FullText catalog on this big table. Now I don’t care about all the KB’s and suggested “Fixes” and all that. I haven’t got the time to futz with this crap.

So I said, OK, let’s get rid of this sucker and see if the x86 version of SQL Server 2008 does better. So I did, and guess what? I still have memory consumption issues, but everything is faster and it never gets to the point where I lose control of the machine. The whole process of switching only took a little over an hour. Incidentally, on an x64 machine SQL Server 2008 “tries” to install the x64 version by default. You need to go into the x86 folder and run the “LandingPage.exe” file and it will happily install the x86 version on your x64 box!

Another issue is that I have never been able to install SP1 on either the x64 or the x86 versions because of a crapped-out installation of SQL Books Online that I cannot find the original installation MSI for. That really sucks bigtime, IMHO. The next step is that I will try to make a slipstream installation to fix this.

UPDATE: I figured out a much easier way to get SP1 installed if you have a failed installation of Books Online that is blocking it:  Search the Registry for instances of the value “SQL_Server_Books_Online_Redist”. You should find three keys with this value. Remove them, and SP1 will be happy!

One other little tidbit I discovered. Don’t just “Create indexes” because you “think” they’ll be important. Instead, develop a series of workload queries in a .SQL file, and use the Database Engine Tuning Advisor to run these and make suggestions. The SQL to create the recommended indexes will be right there at the right hand side of the report and you can copy the SQL to the clipboard.

This is important because index creation should be based on how you query your database, not “what you think”. Extra indexes that don’t necessarily contribute to performance with real life queries only create a drag on the server. Here’s what the experts have to say about indexes:

  • Before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them.
  • Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
  • Unused indexes slow data modifications, cause unnecessary I/O reads when reading pages, waste space in your database, and increase the amount of time it takes to backup and restore databases.

You may be surprised what the Tuning Wizard recommends – it could be quite different from “what you thought” should be done! And, even “good” indexes become fragmented over time and need to be rebuilt.

What have your experiences been with SQL Server 2008 64-bit?

Go figure.

Comments

  1. Mark Kadlec11:59 AM

    Interesting observation Peter. I have SQL Server x64 working here with some tables/queries dealing with 50+ million rows and it is working fine. Mind you, those boxes are very beefy with multi processors, and 5 times as much RAM as you mentioned.

    I have the developer edition on my box and it is also running fine for queries of 1 million rows, BUT, I am running 8 Gig ram.

    I did find issues with running certain SSIS packages, but when I turned off 64 bit in the Project settings, they worked fine.

    ReplyDelete
  2. peterbromberg1:22 PM

    @Mark, the post focuses around issues with FullText and the building of catalogs for same. I never ran into the mentioned issues before beginning to index a large table with a fulltext index.
    Thanks for the comment.

    ReplyDelete
  3. Unknown4:39 PM

    64-bit version will try to leech out as much RAM as it can from the OS. Have you tried to reduce the Max memory to < 4GB? By default, the installation sets it to as much memory as you have.

    My experiences:
    http://www.shinylight.com/2009/09/18/upgrading-from-sql-server-2000-to-2008/

    http://www.shinylight.com/2010/01/28/sql-server-2008-installation-errors/

    ReplyDelete
  4. peterbromberg5:35 PM

    @Dan, yes i did set max SQL Server memory to only 2GB. It had no effect. As mentioned, this problem with the x64 version only occured when I added a FullText Catalog to a very large (6 million rows) table, on one varchar column.

    ReplyDelete
  5. Anonymous8:10 PM

    Peter,

    Try this page "Performance Tuning and Optimization of Full-Text Indexes" ( http://msdn.microsoft.com/en-us/library/ms142560.aspx )

    There are best practices for memory, cpu, etc., in particular covering "Troubleshooting Full Population Performance Issues" on x86 (with and without AWE) and x64 and IA64. The SQL Server 2008 Storage Engine can be limited on memory as Dan had mentioned, but fdhost.exe also needs to be managed.

    And 6 million rows is big? That's 4 hours of data in one of my apps :)

    Hope that link helps.

    ReplyDelete
  6. Anonymous2:00 PM

    Thank you for this. You saved me a lot of time.

    ReplyDelete
  7. Sandeep9:44 AM

    You are a life saver.
    Java Interview Questions for Database

    ReplyDelete
  8. Ginni Kakkar5:36 AM

    I had a bit problem, after up gradation to 64-bit server, some of my crystal reports began to run slow. I am unable to find the fix. Can u pl help??

    ReplyDelete
  9. Ginni Kakkar5:38 AM

    Hi,

    After up gradation to 64-bit server, some of my crystal reports began to run slow. They run fast some times and some time very slow. Can you pl help??

    ReplyDelete

Post a Comment

Popular posts from this blog

FIREFOX / IE Word-Wrap, Word-Break, TABLES FIX

One of the most annoying things a developer (who would normally delight in writing code, not futzing with markup) can have is getting rendering issues between different browsers. The two biggest players are of course Internet Exploder and Firefart (as I lovingly like to refer to each). In most cases that's going to take care of 98 percent of your total site traffic. IE has had a proprietary "word-break" style attribute for a long time, and this made it into the CSS 3.0 spec. But that doesn't necessarily help you with Firefox right now. Firefox literally - (and I consider this completely idiotic, since it's been in their bug database for FIVE YEARS) does not have a reliable CSS style element to force table cell content to break in the middle of a word in order to stop the content from expanding your table / div off the page or over other content on the page. Here's a partial fix, which will work for most tables and browsers. The style declaration (I call it m
Read more

Some observations on Script Callbacks, "AJAX", "ATLAS" "AHAB" and where it's all going.

I've taken a more than cursory interest in the whole Remote Scripting (.NET species) vs. AJAX and now ATLAS discussion, mostly because I started using Remote Scripting since Microsoft first released it, and because I continued to refine it after seeing Brent Ashley's excellent work with JSRS, which was one of the first "real" cross-browser solutions back in 2000 (that's the turn of the Century for you history buffs). Now Bertrand Leroy, a Microsoft guy whose work I like , has authored some very interesting ASP.NET 2.0 script callback stuff that he points to on his blog, and he is obviously (at least, to me he is) involved quite heavily in the development of this new ATLAS infrastructure that they'll preview at PDC. Leroy's most recent post brings to the surface what I agree are the major differences between the "AJAX a - la .NET" approach as popularized by Michael Swartz with his AJAX.NET library, and Leroy's RefreshPanel, the ASP.NET 2.0 b
Read more

IE7 - Vista: "Internet Explorer has stopped Working"

Image
This one was a bitch. All of a sudden for no reason at all, out of the blue, I get this dialog "internet Explorer has stopped working". I didn't install any new software, crap- I didn't do anything! So here I am using FIREFART to go on the internet and find out what to do! Damn! Good thing I've got the sucker on the same machine (I'm not "anti-Firefox", i just don't use it that much except to check my page renderings). The Fix The fix (at least for me): 1) Go into Control Panel, and choose "Internet Options". 2) Under the "Advanced" tab, press the "RESET" button at the lower right: Don't ask me why this happens, or why the fix works. That's a BUG, D00D - I don't care how you slice it!
Read more