Database Reference
In-Depth Information
Database Options
Every database should have the
Auto Shrink
option disabled. As we have already discussed, the Auto Shrink
periodically triggers the database shrink process, which introduces unnecessary I/O load and heavy index
fragmentation. Moreover, this operation is practically useless because further data modifications and index
maintenance make database files grow yet again.
The Auto Close option forces SQL Server to remove any database-related objects from memory when the
database does not have any connected users. As you can guess, it leads to extra physical I/O and query compilations
as users reconnect to the database afterwards. With the rare exception of very infrequently accessed databases, the
Auto Close setting should be disabled.
It is better to have multiple data files in filegroups with volatile data. This helps avoid allocation map contention,
similar to what happens in the case of
tempdb
. We will discuss the symptoms of such contention later in this chapter.
SQL Server Execution Model
From a high level, the architecture of SQL Server includes five different components, as shown in Figure
27-1
.
Figure 27-1.
High-Level SQL Server Architecture
The
Protocol layer
handles communications between SQL Server and the client applications. The data
is transmitted in an internal format called
Tabular Data Stream (TDS)
using one of the standard network
communication protocols, such as TCP/IP or Name Pipes. Another communication protocol, called
Shared Memory
,
can be used when both SQL Server and client applications run locally on the same server. The shared memory
protocol does not utilize the network and is more efficient than the others.
■
Different editions of SQl Server have different protocols enabled after installation. For example, the SQl Server
express edition has all network protocols disabled by default, and it would not be able to serve network requests until you
enable them. You can enable and disable protocols in the SQl Server Configuration manager utility.
Tip
The
Query Processor
layer is responsible for query optimization and execution. We have already discussed
various aspects of its behavior in previous chapters.
