Re: 32 bit SQL vs 64 bit

Tech-Archive recommends: Fix windows errors by optimizing your registry

64-bit is most advantageous with larger memory. The plan cache growth problem is only on systems with large amounts of compled, non-parameterized or unique queries. Microsoft has added logic to deal with this in each service release for SQl 2005. I am still in favor of more user options to handle this problem, but that is neither here nor there. It is a relatively small impact and 64-bit SQL is definitely the way to go. Even if you don't have a lot of memory in the box now, 64-bit will enable you to use it effectively. It is also very cost effective when you use Standard Edition SQL.

--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP




"Chris Wood" <anonymous@xxxxxxxxxxxxx> wrote in message news:%23Fev$TFBIHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
I am trying to link 2 pieces of SQL2005 information together here.

There are changes to how SQL memory is used post SP2 with the algorithmn changing as to how much of SQL's memory is given to plan caching. The post SP2 version will allow more to be given to the databases pages as you have more memory. At the same time an article on 64 bit SQL mentions that 32 bit SQL using AWE only gives the higher amounts of memory to the data cache.

If I understand this correctly moving to 64 bit SQL is most beneficial if you have large amounts of memory, not servers in the 4 to 8 Gb range because the data cache could be starved by the plan cache.

Am I understanding this correctly?

Thanks

Chris
"Shailesh Khanal" <shailesh.khanal@xxxxxxxxxxxxxx> wrote in message news:%23Eha2gfAIHA.4592@xxxxxxxxxxxxxxxxxxxxxxx
As Geoff says the hardware upgrade is right time to move to 64 bit, most of the new servers come with x64 processors, there are no additional licensing fees for Windows as well as SQL Server 2005 moving from 32 to 64 bit.

"Geoff N. Hiten" <SQLCraftsman@xxxxxxxxx> wrote in message news:%23EhsX85$HHA.2268@xxxxxxxxxxxxxxxxxxxxxxx
The decision point varies for different users. A hardware refresh is a good time to consider 64-bit. Performance or scalability problems tracable to hardware limitations are also a good reason to consider an upgrade. The only reason I would see not to use 64-bit is if you need to access many non-SQL data sources. The supply of 64-bit ODBC drivers is a bit limited.

--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP




"Chris Wood" <anonymous@xxxxxxxxxxxxx> wrote in message news:uyU$ZI5$HHA.536@xxxxxxxxxxxxxxxxxxxxxxx
Geoff,

Is there anywhere that defines the moment that 64 bit should replace 32 bit rather than the hardware that you want to run on? We look like we should be moving up to 64 bit.

Thanks

Chris

Nice to meet you at PASS last week.

"Geoff N. Hiten" <SQLCraftsman@xxxxxxxxx> wrote in message news:eBVgWFv$HHA.5652@xxxxxxxxxxxxxxxxxxxxxxx
Unless you have a compelling reason such as external driver compatibility, I would definitely recommend 64-bit SQL for a highly scaled system.

--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP


"Gates" <gates@xxxxxxxxxxx> wrote in message news:O$nSKpu$HHA.4496@xxxxxxxxxxxxxxxxxxxxxxx
We are setting up a new beefy SQL database. I've got 32GB's of Ram. Everything I've been reading it looks like 64 bit is the way to go. Anyone have any thoughts on 32 vs 64?

Thanks,
Gates










.

Relevant Pages

  • Re: FTS Performance in SQL 2005
    ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... Can you post you query plans and the output of statistics IO ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SBS 2003 SP2 Memory
    ... MY PF is up to 4.75GB and free Physical memory is at 1.8GB. ... about the SQL instances and watching their individual need over time. ... part if your server rarely uses the pagefile. ... each of them based on usage so watch their RAM usage for a week or more and ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL .mdf privacy violation - huge file contains personal contents
    ... The .NET runtime handles memory management ... ... in memory, archived to disk, but never deleted. ... them into temp files, which again, would be a privacy violation. ... SQL Server pre-allocates space for transaction logs, fill-space, etc. ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Do I need to run SQL services
    ... Thanks for using the SBS newsgroup! ... high memory usage and especially the SQL service take up much memory. ... particular server installation. ...
    (microsoft.public.windows.server.sbs)
  • Re: AWE settings.
    ... dynamic AWE to work using SQL 2005, but don't quote me on that. ... Microsoft SQL Server MVP ... immediately allocates memory at system startup. ...
    (microsoft.public.sqlserver.setup)