Tempdb Database
The tempdb system database is a global resource
that is available to all users connected to the instance of SQL Server or
connected to SQL Database. Tempdb is used to hold:
Temporary user objects that are explicitly created, such as:
global or local temporary tables and indexes, temporary stored procedures,
table variables, Tables returned in table-valued functions, or cursors.
Internal objects that are created by the database engine. These include:
o Work tables to store intermediate results for
spools, cursors, sorts, and temporary large object (LOB) storage.
o Work files for hash join or hash aggregate
operations.
o Intermediate sort results for operations such as
creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain
GROUP BY, ORDER BY, or UNION queries.
Note
Each internal object uses a minimum of nine pages; an IAM page and
an eight-page extent. For more information about pages and extents.
Version stores,
which are a collection of data pages that hold the data rows that are required
to support the features that use row versioning. There are two version stores:
a common version store and an online-index-build version store. The version
stores contain:
Row versions that are generated by data modification transactions
in a database that uses read-committed using row versioning isolation or
snapshot isolation transactions.
Row versions that are generated by data modification transactions
for features, such as: online index operations, Multiple Active Result Sets
(MARS), and AFTER triggers.
Operations within tempdb are minimally logged so
that transactions can be rolled back. tempdb is re-created
every time SQL Server is started so that the system always starts with a clean
copy of the database. Temporary tables and stored procedures are dropped
automatically on disconnect, and no connections are active when the system is
shut down. Therefore, there is never anything in tempdb to be
saved from one session of SQL Server to another. Backup and restore operations
are not allowed on tempdb.
Physical Properties of
tempdb in SQL Server
The following table
lists the initial configuration values of the tempdb data and
log files in SQL Server, which are based on the defaults for the Model
database. The sizes of these files may vary slightly for different editions of
SQL Server.
File
|
Logical name
|
Physical name
|
Initial size
|
File growth
|
Primary
data
|
tempdev
|
tempdb.mdf
|
8
megabytes
|
Autogrow
by 64 MB until the disk is full
|
Secondary
data files*
|
temp#
|
tempdb_mssql_
#.ndf
|
8
megabytes
|
Autogrow
by 64 MB until the disk is full
|
Log
|
templog
|
templog.ldf
|
8
megabytes
|
Autogrow
by 64 megabytes to a maximum of 2 terabytes
|
* The number of files depends on the number of (logical)
processors on the machine. As a general rule, if the number of logical
processors is less than or equal to eight, use the same number of data files as
logical processors. If the number of logical processors is greater than eight,
use eight data files and then if contention continues, increase the number of
data files by multiples of 4 until the contention is reduced to acceptable
levels or make changes to the workload/code.
Restrictions
The following operations
cannot be performed on the tempdb database:
Adding filegroups
Backing up or restoring
the database
Changing collation. The
default collation is the server collation
Changing the database
owner. tempdb is owned by sa
Creating a database
snapshot
Dropping the database
Dropping the guest user
from the database
Enabling change data
capture
Participating in
database mirroring
Removing the primary
filegroup, primary data file, or log file
Renaming the database or
primary filegroup
Running DBCC CHECKALLOC
Running DBCC
CHECKCATALOG
Setting the database to
OFFLINE
Setting the database or
primary filegroup to READ_ONLY
Permissions
Any user can create temporary objects in tempdb. Users can only
access their own objects, unless they receive additional permissions. It is
possible to revoke the connect permission to tempdb to prevent a user from
using tempdb, but is not recommended as some routine operations require the use
of tempdb.
Optimizing tempdb
performance in SQL Server
§The size and physical placement of the tempdb
database can affect the performance of a system.
§For example, if the size that is defined for
tempdb is too small, part of the system-processing load may be taken up with
auto growing tempdb to the size required to support the workload every time you
restart the instance of SQL Server.
§
If possible, use database instant file initialization to
improve the performance of data file grow operations.
§
Always give the tempdb a
sufficient capacity. Leave it to grow on automatic.
§
Set the recovery mode to
a Simple. Not everything is recorded in the transaction log, means when a
transaction completed it is removed from the transaction log. Therefore
the log file does not continue to grow unnecessarily.
§
The creation of
additional data files can optimize to get access to the data carrier, as this
storage conflicts can be avoided. There is a pi * thumb rule: number of CPUs
equals the number of files. This improves the access to the data carrier.
§ Preallocate space for all tempdb files by
setting the file size to a value large enough to accommodate the typical
workload in the environment. Preallocation prevents tempdb from expanding too
frequently, which affects performance. The tempdb database should be set to
autogrow, but this should be used to increase disk space for unplanned
exceptions.
§ Data files should be of equal size within
each filegroup, as SQL Server uses a proportional-fill
algorithm that favors allocations in files with more free space. Dividing
tempdb into multiple data files of equal size provides a high degree of
parallel efficiency in operations that use tempdb.
§ Set the file growth increment to a reasonable
size to avoid the tempdb database files from growing by too small a value. If
the file growth is too small, compared to the amount of data that is being
written to tempdb, tempdb may have to constantly expand and affect performance.
To check current tempdb size and growth parameters, use the
following query:
SELECT name AS FileName,
size*1.0/128 AS FileSizeInMB,
CASE max_size
WHEN 0 THEN 'Autogrowth
is off.'
WHEN -1 THEN 'Autogrowth
is on.'
ELSE 'Log
file grows to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size
is fixed.'
WHEN growth
> 0 AND is_percent_growth = 0
THEN 'Growth
value is in 8-KB pages.'
ELSE 'Growth
value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
Put the tempdb database on a fast I/O subsystem. Use disk striping
if there are many directly attached disks. Individual or groups of tempdb data
files do not necessarily need to be on different disks or spindles unless you
are also encountering I/O bottlenecks.
Put the tempdb database
on disks that differ from those that are used by user databases.
Performance improvements
in tempdb for SQL Server
Starting with SQL Server
2016 (13.x), tempdb performance is further optimized in the
following ways:
§ Temporary tables and table variables are cached.
Caching allows operations that drop and create the temporary objects to execute
very quickly and reduces page allocation contention.
§ Allocation page latching protocol is improved to
reduce the number of UP (update) latches that are used.
§ Logging overhead for tempdb is
reduced to reduce disk I/O bandwidth consumption on the tempdb log
file.
§ Setup adds multiple tempdb data files during a
new instance installation. This task can be accomplished with the new UI input
control on the Database Engine Configuration section and a
command-line parameter /SQLTEMPDBFILECOUNT. By default, setup adds as many
tempdb data files as the logical processor count or eight, whichever is lower.
§ When there are multiple tempdb data
files, all files autogrow at same time and by the same amount depending on
growth settings. Trace flag 1117 is no longer required.
§
For the primary
filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot
be modified.
Troubleshooting
Insufficient Disk Space in tempdb
The tempdb system
database is a global resource that is available to all users that are connected
to an instance of SQL Server. The tempdb database is used to store
the following objects: user objects, internal objects, and version stores.
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk
space used by the user objects, internal objects, and version stores in
the tempdb files. Additionally, to monitor the page allocation or
deallocation activity in tempdb at the session or task level, you can
use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be
used to identify large queries, temporary tables, or table variables that are
using a large amount of tempdb disk space.
The following table
lists error messages that indicate insufficient disk space in the tempdb database.
These errors can be found in the SQL Server error log, and may also be returned
to any running application.
Error
|
Is raised when
|
1101 or 1105
|
Any session must allocate space in tempdb.
|
3959
|
The version store is full. This error usually appears after a
1105 or 1101 error in the log.
|
3967
|
The version store is forced to shrink because tempdb is
full.
|
3958 or 3966
|
A transaction cannot find the required version record in tempdb.
|
tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.
The following examples
show how to determine the amount of space available in tempdb, and the
space used by the version store and internal and user objects.
Determining the Amount of Free Space in tempdb
The following query
returns the total number of free pages and total free space in megabytes (MB)
available in all files in tempdb.
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM
sys.dm_db_file_space_usage;
One of the Resolution for TempDB issue:
I gone into an
interesting situation today where the tempdb was full and only had 10 MB left
on the respective drive. Even though there was more than 90% space
available, I was not able to reduce the size by running the shrink command.
Then the below steps I tried and each of them ran successfully but
couldn’t really shrink the database.
Here are the serial
lists of things performed. Please use them under circumstantial
situations as this is not a solution that may be applicable each time.
When DBCC ShrinkDB or
shrinkFile doesn’t work.. Try these steps.
1.CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
2.DBCC
FREEPROCCACHE
Go
3.DBCC
FREESYSTEMCACHE('ALL')
GO
4.DBCC
FREESESSIONCACHE;
GO
5.DBCC
SHRINKFILE (N'datafilename' , 0)
GO
do ShrinkDB or
shrinkFile .
If you still see issues,
increase the size of tempdb by 1MB from its existing size. For example, if the
current tempdb size is 10000 MB, you increase it to 10001MB
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10001MB )
GO
After the above command,
Try to rerun the steps 1 till 5 again and shrink the DB.
No comments:
Post a Comment