Install the latest service pack and cumulative updates for SQL Server Install the latest service and the latest cumulative updates for SQL Server 2005 and SQL Server 2008 as well as the latest .NET Framework service packs. Install SQL Service on both BizTalk Server and SQL Server When installing service packs for SQL Server, also install the service pack on the BizTalk Server computer. BizTalk Server uses SQL Client components that are updated by the SQL Server service packs. Consider implementing the SQL Server 2008 Data Collector and Management Data Warehouse SQL Server 2008 accommodates the use of the new Data Collector and Management Data Warehouse to collect environment/database performance related data for test and trend analysis. The Data Collector persists all collected data to the specified Management Data Warehouse. While this is not a performance optimization, this will be useful for analysis of any performance issues. Grant the account which is used for SQL Server the Windows Lock Pages In Memory privilege Grant the Windows Pages in Memory” privilege to the SQL Server service account. This “Lock should be done to prevent the Windows operating system from paging out the buffer memory of the SQL Server process by locking memory that is allocated for the buffer pool in physical memory. For more information, see Microsoft Knowledge Base article 914483 to reduce “How paging of buffer pool memory in the 64-bit version of SQL Server 2005” at . the SE_MANAGE_VOLUME_NAME right to the SQL Server Service account Ensure the account running the SQL Server service has the Volume Maintenance “Perform Tasks” Windows privilege or ensure it belongs to a security group which does. This will allow instant file initialization ensuring optimum performance if a database has to auto-grow.
Install the latest service pack and cumulative updates for SQL Server Install the latest service and the latest cumulative updates for SQL Server 2005 and SQL Server 2008 as well as the latest .NET Framework service packs. Install SQL Service on both BizTalk Server and SQL Server When installing service packs for SQL Server, also install the service pack on the BizTalk Server computer. BizTalk Server uses SQL Client components that are updated by the SQL Server service packs. Consider implementing the SQL Server 2008 Data Collector and Management Data Warehouse SQL Server 2008 accommodates the use of the new Data Collector and Management Data Warehouse to collect environment/database performance related data for test and trend analysis. The Data Collector persists all collected data to the specified Management Data Warehouse. While this is not a performance optimization, this will be useful for analysis of any performance issues. Grant the account which is used for SQL Server the Windows Lock Pages In Memory privilege Grant the Windows “Lock Pages in Memory” privilege to the SQL Server service account. This should be done to prevent the Windows operating system from paging out the buffer memory of the SQL Server process by locking memory that is allocated for the buffer pool in physical memory. For more information, see Microsoft Knowledge Base article 914483 “How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005” at . the SE_MANAGE_VOLUME_NAME right to the SQL Server Service account Ensure the account running the SQL Server service has the “Perform Volume Maintenance Tasks” Windows privilege or ensure it belongs to a security group which does. This will allow instant file initialization ensuring optimum performance if a database has to auto-grow.
BTS09HyperV.docx