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.