 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
SQL Server 64-bit applications and performance drawbacks
Serdar Yegulalp, Contributor
11.19.2007
Rating: -3.67- (out of 5)
|
There's no question that the commodity computing world is broadly transitioning from 32-bit to 64-bit architectures, especially on the server side. Conventional wisdom would have it that 64-bit is better, period: You can deal with a larger, less bounded memory space and do more with the processor itself. Why, then, would there be any incentive to continue to use a 32-bit architecture -- especially for something like SQL Server?
As it turns out, this isn't a cut –and-dried situation. On the desktop, 64-bitness isn't always the best thing, and it's not in SQL Server, either. I'd suspected something like this was the case, but I didn't come across specific reasons until I bumped into this post by the Microsoft SQL Programmability and API Development Team. They detail some of the reasons why 64-bit might not be an improvement compared to 32-bit SQL Server.
- The older your 64-bit processor, the less of a performance boon you'll get. Mainly, if you're using an earlier iteration of 64-bit processor that doesn't have a lot of Level 2 cache, there will be little or no performance improvement. Performance may in fact be markedly worse. Most of this is because 64-bit processing requires more memory -- when compared to 32-bit -- to accomplish many of the same things. So the same amount of processor cache will not go as far.
- An existing application that isn't suffering from memory constraints (what Microsoft calls "memory pressure") will probably not gain any inherent boost in performance from being ported to a 64-bit platform.
- Some unexpected behaviors occur when you move 32-bit applications to 64-bit. The linked article above has some more specific examples of this, which are not common but worth noting. Non-parameterized SQL statements are actually something I've run into in other contexts as well. Parameterized SQL is just that much more "professional" a way to handle things, aside from whatever impact it has on the procedure cache. In my opinion, the best long-term solution, although not always the most flexible one depending on the environment you're developing in, is stored procedures. But if most of the query programming is coming from the application side rather than the database side and there's nothing to be done about that (yet), then use parameterized SQL whenever possible.
One other thing noted by the engineers is that in the long run, 64-bit will almost certainly benefit you. Here's one example: Even if it doesn't benefit
you in the scope of one particular application to go 64-bit, it becomes possible to consolidate more of these applications into the same machine. This is something that isn't as feasible or economical in the 32-bit world.
The final piece of advice is the most perpetually relevant: Use live statistics to determine the real performance impact of moving to a 64-bit architecture. Don't use guesswork or, worse, 32-bit performance as any kind of index for how things will run in 64-bit space. If you experience performance issues, having live stats will help you determine if those issues are related to 32-/64-bit transitions or are just artifacts of a new, untuned installation.
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a frequent contributor to various sections of TechTarget as well as other publications. He hosts the Web site Windows Insight, where he posts regularly about Windows and has an ongoing feature guide to Windows Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
|
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
