Databases Reference
In-Depth Information
operations, and the operations use the following connection strings, respectively, three pools are created, and hence
three distinct database connections are established in SQL Database on the same database:
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;PWD=123456;UID=hroggero
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456;Application Name=MyApp
To ensure that the same database connection is used, you must ensure that the three operations use the exact
same connection string:
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
To measure the number of database connections open on a given database, you can use the
sys.dm_exec_
connections
management view. A row is returned for each distinct database connection; the fewer rows, the better!
Execution Plans with SSMS
Sometimes you need to dig deeper and understand how SQL Database fetches data, and then use that information
to improve performance. In SQL Server, you can also use execution plans to observe the impact of changes to the
underlying hardware, such as changing memory configuration. Although you have no control over configuration
settings with SQL Database, execution plans can still be very useful to see the impact of your indexes and to view
which physical operators are being used.
Whereas logical operators are used in a SQL statement, such as
LEFT JOIN
,
physical operators
tell you which
technique SQL Database is using to solve a given logical operation or to fetch additional data. The most common
physical operators SQL Database uses to represent
JOIN
operations are listed in Table
11-4
.
Table 11-4.
Physical
JOIN
operators
Operator
SSMS Symbol
Comment
Nested loop
A loop is performed in SQL Database to retrieve data. For each
record in Table 1 matching the
WHERE
clause, find the matching
records in Table 2. On large recordsets, loops can be costly.
Hash match
A hash is calculated for each record in each table participating
in a
JOIN
, and the hashes are compared for equality.
Merge
Merge operators are usually the fastest operators because
they perform a single pass of the tables involved by taking
advantage of the order in which the data is stored or retrieved.
You can give SQL Database certain hints to use a specific physical operator, but using them isn't generally
recommended. You have three proper ways to influence SQL Database to select an effective physical operator:
•
Review your
WHERE
clause.
This is perhaps the most overlooked aspect of performance tuning.
When you have the choice, applying the
WHERE
clause on the tables that have the most rows
gives you new opportunities for indexing.
•
Optimize your database design.
Highly normalized databases force you to create more
JOIN
statements. And of course, the more
JOIN
statements, the more tuning you need to do. While
you shouldn't necessarily plan to have tables at first normal form, denormalizing certain
tables can offer performance benefits.
