| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2008-08-19 : 14:15:11
|
| Is anyone aware of any SQL Server 2005 x64 issues to watch out for? In particular, any issues with the database engine or SSIS? Are there any issues with developing SSIS on a 32 bit platform and deploying on x64?I any considering using SQL Server 2005 x64 Enteprise Edition for a data warehouse application with a 3+TB database using SSIS for the ETL processes. SQL Server 2005 x64 seems to be ideal for this sort of application, but I want to make sure there are no known show stoppers.CODO ERGO SUM |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-19 : 14:19:25
|
| The only thing that I can think of is to make sure to grant the SQL Server service account the "lock pages in memory" permission. We've had massive performance issues without it on the x64 platform. We've made it a standard to have this on all of our x64 database servers as a result and per the recommendation of a MS engineer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-19 : 14:23:51
|
quote:
Originally posted by Michael Valentine Jones
Is anyone aware of any SQL Server 2005 x64 issues to watch out for? In particular, any issues with the database engine or SSIS? Are there any issues with developing SSIS on a 32 bit platform and deploying on x64?I any considering using SQL Server 2005 x64 Enteprise Edition for a data warehouse application with a 3+TB database using SSIS for the ETL processes. SQL Server 2005 x64 seems to be ideal for this sort of application, but I want to make sure there are no known show stoppers.[font=Courier New]CODO ERGO SUM[/font=Courier New]
The known issue I have seen is:1)Only DBmail works 2)More worse if you are migrating DTS packages with Activexscript task(COM objects) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-19 : 14:30:12
|
quote:
Originally posted by sodeep
quote:
Originally posted by Michael Valentine Jones
Is anyone aware of any SQL Server 2005 x64 issues to watch out for? In particular, any issues with the database engine or SSIS? Are there any issues with developing SSIS on a 32 bit platform and deploying on x64?I any considering using SQL Server 2005 x64 Enteprise Edition for a data warehouse application with a 3+TB database using SSIS for the ETL processes. SQL Server 2005 x64 seems to be ideal for this sort of application, but I want to make sure there are no known show stoppers.[font=Courier New]CODO ERGO SUM[/font=Courier New]
The known issue I have seen is:1)Only DBmail works 2)More worse if you are migrating DTS packages with Activexscript task(COM objects)
There will be no migration of DTS pachkages. All ETL will be developed in SSIS new for this application.DBMail is fine. I have never used anything but DBMail on SQL 2005.CODO ERGO SUM |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-19 : 14:33:44
|
quote:
Originally posted by tkizer
The only thing that I can think of is to make sure to grant the SQL Server service account the "lock pages in memory" permission. We've had massive performance issues without it on the x64 platform. We've made it a standard to have this on all of our x64 database servers as a result and per the recommendation of a MS engineer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Does the SQL Server install not grant the SQL Server service account the "lock pages in memory" permission? Do you have to do this before you install SQL Server?Did you do any benchmarks to compare the performance of 32 bit vs. 64 bit SQL Server? What kind of differences did you see?CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-19 : 14:41:05
|
quote:
Originally posted by Michael Valentine Jones
Does the SQL Server install not grant the SQL Server service account the "lock pages in memory" permission? Do you have to do this before you install SQL Server?
You have to do this manually on all database servers. It is especially important on 32-bit servers so that AWE works, but it has been found to be imperative on 64-bit servers as well.Here are the steps to grant the permission:To assign the Lock pages in memory user right, follow these steps: 1. Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy dialog box appears.2. Expand Computer Configuration, and then expand Windows Settings. 3. Expand Security Settings, and then expand Local Policies. 4. Click User Rights Assignment, and then double-click Lock pages in memory.5. In the Local Security Policy Setting dialog box, click Add User or Group. 6. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK. 7. Close the Group Policy dialog box.8. Restart the SQL Server service. quote:
Originally posted by Michael Valentine Jones
Did you do any benchmarks to compare the performance of 32 bit vs. 64 bit SQL Server? What kind of differences did you see?
We have not done any benchmarks, but 64-bit is much faster. We use 32-bit only on older servers.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-19 : 15:10:57
|
| Has anyone figured out where "lock pages in memory" is stored so that granting the permission can be scripted? I've googled and searched the registry but can't find it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-19 : 15:28:08
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by tkizer
The only thing that I can think of is to make sure to grant the SQL Server service account the "lock pages in memory" permission. We've had massive performance issues without it on the x64 platform. We've made it a standard to have this on all of our x64 database servers as a result and per the recommendation of a MS engineer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Does the SQL Server install not grant the SQL Server service account the "lock pages in memory" permission? Do you have to do this before you install SQL Server?Did you do any benchmarks to compare the performance of 32 bit vs. 64 bit SQL Server? What kind of differences did you see?CODO ERGO SUM
Good article:http://blogs.msdn.com/sqlprogrammability/archive/2007/04/30/will-64-bit-increase-the-performance-of-my-sql-server-application.aspx |
|
|
|
|
Any SQL Server 2005 x64 Gotchas?