Poor SQL Server performance on 64-Bit operating systems

Follow this procedure to correct poor SQL Server performance on 64-Bit operating systems.

About this task

Note: If you are using Discover version 8.8 or later, SQL Server 2005 is no longer supported. For more information, see "Supported SQL Server Versions" in the Unica Discover Databases Guide.

Discover databases are designed to take advantage of 64-bit operating system capabilities. However, because of a Microsoft™ issue, performance issues may arrive when running SQL Server 2005 on a 64-bit version of Windows™. These issues can include the following:

  • The performance of SQL Server 2005 decreases suddenly.
  • SQL Server 2005 stops responding for a short time.
  • A timeout occurs for applications that connect to SQL Server 2005.
  • Problems occur when you run even simple commands or use applications on the system.

The issue may be that 64-bit Windows™ operating system is paging out the working set of the SQL Server process. When the SQL Server process reaches 50 percent of the memory that is allocated for the process on SQL Server 2005 SP2 or later, error messages may begin appearing in the SQL Server error log.

To fix the issue:

Procedure

  1. Review the troubleshooting steps on the Microsoft Support site for information about "How to reduce paging of buffer pool memory in the 64-bit version of SQL Server".
  2. If the problem persists, you can prevent Windows™ from paging out the buffer pool memory of the SQL Server process. You can lock the memory by assigning the Lock pages in memory user right to the user account that is the startup account for the SQL Server service.

    This step prevents the operating system from paging out memory and instead allows SQL Server to do the paging. See the Microsoft Support site for information about the "Enable the Lock Pages in Memory Option (Windows)"

  3. Restart the SQL Server.