tempdb数据库清理_tempdb数据库，简介和建议

tempdb数据库清理

介绍 ( Introduction )

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

人们常常将注意力集中在用户数据库上以提高性能，但有时问题不在于用户数据库本身。 有时问题出在tempdb。

In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

在本文中，我们将简要介绍tempdb数据库，展示如何在其中创建一些临时对象，并展示如何进行改进和监视。

要求 ( Requirements )

• SQL Server 2005 or later (in this example, we are using SQL Server 2014)
  SQL Server 2005或更高版本（在此示例中，我们使用的是SQL Server 2014）

入门 ( Getting started )

The temdb is a special system database used to store temporary objects and data like tables, views table variables, tables returned in functions, temporary objects and indexes.

temdb是一个特殊的系统数据库，用于存储临时对象和数据，例如表，视图表变量，函数中返回的表，临时对象和索引。

The temdb can also be used for internal operations like rebuilding indexes (when the SORT_IN_TEMPDB is ON), queries using UNION, DBCC checks, GROUP BY, ORDER BY. Hash join and Hash aggregate operations.

temdb还可以用于内部操作，例如重建索引（当SORT_IN_TEMPDB为ON时），使用UNION，DBCC检查，GROUP BY，ORDER BY的查询。 哈希联接和哈希聚合操作。

The tempdb is in simple recovery model because the information stored is temporary.

tempdb采用简单的恢复模型，因为存储的信息是临时的。

You cannot backup the tempdb database because the data is temporary.

您不能备份tempdb数据库，因为数据是临时的。

Let’s start with a brief introduction creating temporary tables. If you already know how to create temporary tables, you can jump to the tempdb recommendations.

让我们从创建临时表的简短介绍开始。 如果您已经知道如何创建临时表，则可以跳转到tempdb建议。

创建临时对象 ( Creating temporary objects )

There are several temporary objects in SQL Server. Let’s start with the local temporary table.

SQL Server中有几个临时对象。 让我们从本地临时表开始。

本地临时表 ( Local temporary tables )

The following example creates a temporary local table in the tempdb database with the information of the Person.Address table.

以下示例使用Person.Address表的信息在tempdb数据库中创建一个临时本地表。

 
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
	  into #address
  FROM [Person].[Address]
 

If you check the tempdb, you will find the table in the temporary table folder:

如果检查tempdb，则会在临时表文件夹中找到该表：

The prefix # is used to indicate that it is a local temporary table. You can access to the temporary tables from any database. These tables or objects created are visible only in the session where they were created. They are very secure. As you can see in the image above, there is an internal left-padded numeric suffix in the table. This is used to differentiate from other local tables created by different procedures or processes.

前缀＃表示它是本地临时表。 您可以从任何数据库访问临时表。 创建的这些表或对象仅在创建它们的会话中可见。 它们非常安全。 如上图所示，表中有一个内部左填充的数字后缀。 这用于与由不同过程或过程创建的其他本地表区分开。

If you try to create a view, a function or a trigger based on a local temporary table you will receive an error message.

如果您尝试基于本地临时表创建视图，函数或触发器，则会收到错误消息。

See the following example:

请参见以下示例：

 
create view v_address
as
select addressid
from #address
 

The error message displayed would be the following:

显示的错误消息如下：

Msg 4508, Level 16, State 1, Procedure v_address, Line 22
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

消息4508，级别16，状态1，过程v_address，第22行
临时表上不允许使用视图或函数。 以“＃”开头的表名称表示临时表。

全局临时表 ( Global temporary tables )

These tables are global and can be accessed from other sessions. This option is not secure and consumes many resources. It is not recommended to use this option, but it is good to know it in order to correct it if someone is using it.

这些表是全局的，可以从其他会话访问。 此选项不安全，并且消耗许多资源。 不建议使用此选项，但是最好知道它以便在有人使用时对其进行更正。

The syntax is similar than the local temporary tables, but with double ##:

语法与本地临时表相似，但带有双##：

 
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
	  into ##address
  FROM [Person].[Address]
 

The global tables do not require suffixes.

全局表不需要后缀。

表变量 ( The table variables )

The table variables can be used instead of the global and local temporary tables. They are easier to handle and to dispose the resources, which makes the use more efficient. The following example will show how to create the table variable, how to insert data on it and how to show the variable values. The use is similar to a simple table. The following example shows how to declare a table variable, how to insert data and how to do a select:

可以使用表变量代替全局和局部临时表。 它们更易于处理和配置资源，这使使用效率更高。 以下示例将说明如何创建表变量，如何在表变量上插入数据以及如何显示变量值。 用法类似于简单表。 以下示例说明如何声明表变量，如何插入数据以及如何执行选择：

 
DECLARE @myTableVariable TABLE (id INT, col1 varchar(20))
insert into @myTableVariable values(1,'First value'),(2,'Second value')
select * from @myTableVariable 
 

As you can see, it is very easy to use them like any other variable. In general, the local temporary tables are more efficient. The table variables are also stored in the tempdb. However, depending on the scenarios you can only use table variables or local temporary tables. For example, if you need indexes, you can only do it with a local temporary table. By the other hand, in a function, you can only use a table variable. Note that the statistics are not maintained in table variables. In routines, table variables require fewer compiles.

如您所见，像其他变量一样使用它们非常容易。 通常，本地临时表效率更高。 表变量也存储在tempdb中。 但是，根据方案，您只能使用表变量或本地临时表。 例如，如果需要索引，则只能使用本地临时表来执行。 另一方面，在函数中，只能使用表变量。 请注意，统计信息不保留在表变量中。 在例程中，表变量需要较少的编译。

A special type of table variable is the Table-value parameter. This is very useful for client applications.

表变量是表变量的一种特殊类型。 这对于客户端应用程序非常有用。

The following example shows how to create and use Table-value parameters:

以下示例显示如何创建和使用表值参数：

 
CREATE TYPE Table_value AS TABLE 
(id int
, name varchar(30) );
GO
 

We will also create a simple table to fill with the Table-value parameter in a stored procedure:

我们还将创建一个简单的表，以在存储过程中填充Table-value参数：

 
CREATE table product
(id int
, productname varchar(30))
 

The stored procedure will load the data from the Table-value parameter into the product table created.

该存储过程会将表值参数中的数据加载到创建的产品表中。

 
CREATE PROCEDURE insertdata
    @myTable table_value READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO product
           ([id]
           ,productname)
        SELECT id,name
        FROM  @myTable;
        GO
 

Now, we are going to declare the Table-value parameter insert data there and call the stored procedure and do a select in the table to make sure that the data was inserted:

现在，我们将在其中声明Table-value参数插入数据，并调用存储过程并在表中进行选择以确保已插入数据：

 
DECLARE @TVP 
AS Table_value;
 
INSERT INTO @TVP (id, name) values
   (1,'Camera'),
   (2,'iPad');
 
EXEC insertdata @TVP;
GO
 
select * from product
 

If everything is OK, you should be able to see the new data inserted in the products table using the stored procedure:

如果一切正常，则应该可以使用存储过程查看在products表中插入的新数据：

Tempdb建议 ( Tempdb recommendations )

By default, the tempdb size is 8 MB approximately. If your databases handle a big amount of data, you may need a bigger Tempdb database. It is not a good idea to have a small size if the tempdb database has to growth very often.

默认情况下，tempdb的大小约为8 MB。 如果您的数据库处理大量数据，则可能需要更大的Tempdb数据库。 如果tempdb数据库必须非常频繁地增长，则减小其大小不是一个好主意。

To check the tempdb size, in the SQL Server Management Studio (SSMS), go to Databases>System Databases and right click on the tempdb database. Click on the files page:

要检查tempdb的大小，请在SQL Server Management Studio（SSMS）中，转到“数据库”>“系统数据库”，然后右键单击tempdb数据库。 单击文件页面：

If possible, move your tempdb to different disks than the user databases. That will increase the performance.

如果可能，请将您的tempdb移到与用户数据库不同的磁盘上。 这将提高性能。

If possible, use RAID 0 to improve the performance.

如果可能，请使用RAID 0来提高性能。

Make sure that the autogrowth is enabled (by default it is in 10%, which is usually OK).

确保已启用自动增长（默认情况下为10％，通常可以）。

Monitor the tempdb size with the Performance Monitor or other tools of your preference. To do this open the performance monitor in your Windows Operative System:

使用性能监视器或您喜欢的其他工具来监视tempdb的大小。 为此，请在Windows Operative系统中打开性能监视器：

In the system monitory, to the SQLServer:Databases>Data File(s) Size and select the tempdb database.

在系统监视中，选择“ SQLServer：数据库>数据文件的大小”，然后选择tempdb数据库。

In addition, it is important to check the SQL Error Log in SSMS:

另外，检查SSMS中SQL错误日志也很重要：

Common errors in the SQL error Log file related to the tempdb space are the errors 1101, 1105, 3959, 3967, 3966, 3958.

SQL错误日志文件中与tempdb空间相关的常见错误是错误1101、1105、3959、3967、3966、3958。

Also, make sure to maintain your indexes in your user databases. Rebuild or Reorganize your indexes in frequency to reduce the tempdb use.

另外，请确保在用户数据库中维护索引。 重建或重新组织索引的频率以减少tempdb的使用。

结论 ( Conclusion )

In this chapter, we introduce the tempdb and explained how to create local, global and table variables in the tempdb.

在本章中，我们介绍了tempdb，并说明了如何在tempdb中创建局部变量，全局变量和表变量。

We also introduced some recommendations to monitor the tempdb and improve the performance.

我们还引入了一些建议来监视tempdb并提高性能。

翻译自: https://www.sqlshack/the-tempdb-database-introduction-and-recommendations/

tempdb数据库清理