Configuring Microsoft SQL Server

Perform the following tasks to ensure that SQL Server is compatible with Optimizer:

• If you are setting up SQL Server 2000, ensure the current user is a member of the sysadmin group.
• If you are setting up later versions of SQL Server, the current user must meet one of the following requirements:
• Be a member of sysadmin, or have the VIEW SERVER STATE permission enabled.
• Be a member of sysadmin, or have the SELECT permission enabled.

On SQL Server 2000 only:

You can enable profiling to capture more SQL by adding the following flag:

DBCC TRACEON(2861)

Trace flag 2861 instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction, and others). In other words, the number of objects in the procedure cache increases when trace flag 2861 is turned on because the additional objects are so small, there is a slight increase in memory that is taken up by the procedure cache.

Ensure you restart the server for your changes to take affect.

For tuning, you will need to be a member of sysadmin or have the SHOWPLAN permission enabled.