Database Reference
In-Depth Information
Analyzing the Connection Options Used by the Application
When making a connection to SQL Server, various options, such as
ANSI_NULL
or
CONCAT_NULL_YIELDS_NULL
, can
be set differently than the defaults for the server or the database. However, changing these settings per connection
can lead to recompiles of stored procedures, causing slower behavior. Also, some options, such as
ARITHABORT
, must
be set to
ON
when dealing with indexed views and certain other specialized indexes. If they are not, you can get poor
performance or even errors in the code. For example, setting
ANSI_WARNINGS
to
OFF
will cause the optimizer to ignore
indexed views and indexed computed columns when generating the execution plan. You can use the output from
Extended Events to see this information. The
options_text
column contains the settings used by the connection in
the
login
event and in the
existing_connection
event, as shown in Figure
25-2
.
Figure 25-2.
An existing connection showing the batch-level options
This column does more than display the batch-level options; it also lets you check the transaction isolation level.
You can also get these settings from the properties of the first operator in an execution plan.
I recommend using the ANSI standard settings, in which you set the following options to
ON
:
ANSI_NULLS
,
ANSI_NULL_DFLT_ON
,
ANSI_PADDING
,
ANSI_WARNINGS
,
CURS0R_CLOSE_ON_COMMIT
,
IMPLICIT_TRANSACTIONS
, and
QUOTED_IDENTIFIER
. You can use the single command
SET ANSI_DEFAULTS ON
to set them all to
ON
at the same time.
Analyzing the Effectiveness of Statistics
The statistics of the database objects referred to in the query are one of the key pieces of information that the query
optimizer uses to decide upon certain execution plans. As explained in Chapter 12, the optimizer generates the
execution plan for a query based on the statistics of the objects referred to in the query. The optimizer looks at the
statistics of the database objects referred to in the query and estimates the number of rows affected. In this way, it
determines the processing strategy for the query. If a database object's statistics are not accurate, then the optimizer
may generate an inefficient execution plan for the query.
