DB: SQL Server

Profiler

Privilege Verification Query

Profiler accesses

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

To set compatibility level you can use, for example

exec sp_dbcmptlevel MyOldDB, 90

see link http://social.msdn.microsoft.com/

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

Login

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

SQL Server Internals

SQL Server Waits with XEVENTS

Take the guesswork out of performance Tuning Part 1

Take the guesswork out of performance Tuning Part_2