Databases Reference
In-Depth Information
Monitoring performance of TempDB
database
We know that the
TempDB
database is one of the system databases in SQL Server and
SQL Server heavily depends on
TempDB
for its normal functioning. Therefore, monitoring
performance-related statistics of
TempDB
database is very important. Quite a few times,
we see that people just don't care about
TempDB
database and tend to ignore looking after
its performance. This is not a good idea because the reasons for SQL Server's inefficient
performance may be hiding behind the suboptimal performance of
TempDB
database. That's
why you should consider monitoring
TempDB
database time-to-time.
You should know that SQL Server uses
TempDB
database while performing certain kinds of
operations on a large data. Some of them perform grouping or sorting operations in query,
cursor operations, version store operation, online index creations, and storing user objects,
such as local or global temporary tables and table variable data. As a DBA, you may need to
keep a watch on certain statistics for
TempDB
database to find out the usage pattern of the
TempDB
database to identify resource-consuming operations. You can get this information by
using database-related dynamic management views.
But, to work with most of the database-related dynamic management views, it's important
that you know the basic concept of how SQL Server internally organizes its data physically.
So, let's first do some groundwork by understanding the role of pages and extents.
As you may know, SQL Server stores its database primarily in two types of files. These are data
file (
.mdf
and
.ndf
file) and log file (
.ldf
file). Here, our discussion on pages and extents is
applicable to, and is in context of, data files only. So, do not get confused between pages and
extents, and log files, as they are not applicable to log files.
Data file is a type of database file where SQL Server stores its data for a database in the form
of database objects such as tables and indexes. This data file is composed of smaller storage
units called pages. A page is a block of 8 KB in size that actually stores the data.
On the other hand, extents are composed of pages. An extent is a series of 8 contiguous
pages. So, the size of an extent is 64 KB and there are 16 extents per MB.
Objects that contain data are allocated pages from these extents for data storage. There are
two types of extents and these are, uniform extent and mixed extent. A uniform extent is the
one whose data pages are dedicatedly allocated to a single object only, whereas a mixed
extent is the extent whose data pages can be allocated to up to 8 different objects. Mixed
extents are also called shared extents because they are shared amongst multiple objects.
Smaller tables are usually allocated to mixed extents initially and as soon as they get large,
they are allocated to their own uniform extents.
Search WWH ::
Custom Search