This section discusses several SQL Server tools you can use to tune Transact-SQL statement.
SQL Trace is a graphical utility that monitors and records SQL Server database activity. It can display all server activity in real time or create filters that focus on the actions of particular users, applications, or host servers. It can display all SQL statements and remote procedure calls sent to SQL Server.
SQL Trace also records connections and disconnection's made to and from the server. All activity can be saved as a Transact-SQL script or as an activity log.
All events can be annotated with SQL Server login name, Windows NT domain\username, application name, and host name. The statistics start time, end time, duration, CPU usage, reads, and synchronous writes are available for all events. For language and RPC events, the statistics refer to the individual events. For events such as disconnection, the statistics refer to the complete session. Complete session statistics can be used for auditing.
SQL Trace provides a graphical user interface to the extended stored procedure xp_sqltrace. Many of the features provided by SQL Trace can also be achieved programmatically by using xp_sqltrace directly. For example, by creating an auto-start stored procedure that calls xp_sqltrace, it is possible to automatically start SQL Server auditing.
For more information about SQL Trace, see Microsoft SQL Server What's New in
SQL Server 6.5.
The SET statement can also be used to influence the optimizer. The SET statement sets SQL Server query-processing options for the duration of your work session, or for the duration of a running trigger or a stored procedure.
The SET FORCEPLAN ON statement forces the optimizer to process joins in the same order as the tables appear in the FROM clause. This hint is similar to the ORDERED hint used with the Oracle optimizer.
The SHOWPLAN option generates a description of the processing plan for the query and immediately processes it unless NOEXEC is set. This option can indicate whether an index is being used to retrieve query results. The SHOWPLAN option provides similar results to the Oracle EXPLAIN PLAN tool.
Transact-SQL statement processing consists of two phases, compilation and execution. The NOEXEC option compiles each query but does not execute it. After NOEXEC is turned on, no subsequent statements are executed (including other SET statements) until NOEXEC is turned off.
SET SHOWPLAN ON
SET NOEXEC ON
go
SELECT * FROM DEPT_ADMIN.DEPT,
STUDENT_ADMIN.STUDENT
WHERE MAJOR = DEPT
go
STEP 1
The type of query is SETON
STEP 1
The type of query is SETON
STEP 1
The type of query is SELECT
FROM TABLE
DEPT_ADMIN.DEPT
Nested iteration
Table Scan
FROM TABLE
STUDENT_ADMIN.STUDENT
Nested iteration
Table Scan
The Oracle DBMS allows you to use hints to influence the operation and performance of its cost-based optimizer. The SQL Server cost-based optimizer works extremely well and does not usually require the use of hints to assist in its query evaluation process. They are offered, however, as some situations do warrant their use.
The INDEX = {index_name | index_id} hint specifies that the index name or ID to use for that table. An index_id of 0 forces a table scan, while an index_id of 1 forces the use of a clustered index if it exists. This is similar to the index hints used with Oracle.
The FASTFIRSTROW hint directs the optimizer to use a nonclustered index if its column order matches the ORDER BY clause. This hint operates in a similar fashion to the Oracle FIRST_ROWS hint.