master..sysprocesses p,
               master..sysdatabases d

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 or 2005 ( 8 or 9) ensure the current user is a member of sysadmin.
• 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.

fn_get_sql - issues

Compatibility Levels

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

^{THe compatibility level has to be set to the same level as the actual version of the databse.}

For example, SQL Server 2005 is level 90, but if the compatibility is change to 80 the equivalent of windows 2000, then DB Optimizer won't work.

>> select name,compatibility_level from sys.databases

if the compatibility level is different than the default, one issue that happens is an error running the function fn_get_sql which is required by DB Optimizer

• 80 = SQL 2000
• 90 = SQL 2005
• 100 = SQL 2008

SQL Server 2000 only
             This flag will allow profiler to capture more SQL.  If a session/sql is not showing up or the sql text is missing then turn this flag on:

DBCC TRACEON(2861)

then restart  the server.

Using Trace flag 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). 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, you will see a small increase in memory, which is taken up by the procedure cache.

Tuner - 

SQL Server Hints

SQL Server Express - special install/setup notes