SQL Server

From DB Optimizer
Jump to: navigation, search

DB: SQL Server


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
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:
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
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
Waits on SQL Server