DB: SQL Server


             Privilege Verification Query

             Profiler accesses
               master..sysprocesses p,
               master..sysdatabases d

Configuring Microsoft SQL Server, required permissions:
In all cases SYSADMIN is sufficient.
On 2008 and 2005 it is sufficient to have 'VIEW SERVER STATE' and  SELECT on any database or object which automatically give select on master.. objects) 
On 2000 you have to be SYSADMIN, no other options.

fn_get_sql - issues

Compatibility Levels
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
To set compatibility level you can use, for example

exec sp_dbcmptlevel MyOldDB, 90
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:


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.

SQL Server Hints

SQL Server Express - special install/setup notes

                  If you are using 64bit Windows check out this Microsoft link for login problems and solutions with JDBC and SQL Server.

SQL Server Database Info
        SQL Operations
        Diagnose Wait Locks
        Waits on SQL Server
Subpages (1): fn_get_sql