mssql sqlserver tempdb设置大小的建议


摘要:
在以前的temp简介中,我们讲述了tempdb简介及适用场景及和其它数据库的对比,下文我们主要讲述tempdb数据库的数据分配方法和分配大小的建议。


当我们执行下面的sql脚本时,我们可以查看当前tempdb数据库所占用的数据库文件大小

  use tempdb;
  go
  sp_Helpfile;
  go

sp_helpfile_tempdb查看

sp_helpfile_tempdb查看


从图片中我们可以看出主数据库文件名称为’tempdev’。
这个名字一直回到最早的SQL Server版本。
在SQL Server 7之前,数据库的空间是以“设备”来定义的,这些设备实际上只是大型文件,个别数据库可以使用这些大文件来使用空间。
CREATE DATABASE命令没有引用磁盘上的特定文件位置,而是指定从先前创建的设备中使用的指定空间。对于tempdb,系统创建的设备名称为tempdev,tempdb的所有空间都来自该设备。
从旧版升级到SQL Server 7时,设备名称成为文件的逻辑名称。我们仍然使用这个名字,tempdev,即使现在在SQL Server 2017中也是如此。但是,这只是默认名称,
可以通过SQL Server Management Studio中数据库属性表中的文件页面或通过使用ALTER DATABASE tempdb MODIFY FILE命令进行更改。
在SQL Server 2016中微软引入了一些对tempdb文件管理的更改,这些更改主要与SQL Server为数据库分配空间的方式有关。

空间分配

用于存储表和索引的数据库中的空间以称为扩展数的单位进行管理。一个范围由八个逻辑上连续的页面(或64KB的空间)组成。为了使空间分配更有效,在大多数数据库中,SQL Server不会将整个扩展数据块分配给包含少量数据的表。SQL Server有两种类型的范围。
统一扩展盘区由单个对象拥有,并且盘区中的所有八个页面只能由拥有对象使用
混合盘区共享最多八个对象
在早于SQL Server 2016的版本中,SQL Server为混合盘区中的新表或索引分配页面。当表或索引增长到八页时,所有未来的分配使用统一的范围。
当表或索引需要增长时,SQL Server需要查找可用空间进行分配。如果表或索引总共少于八页,则SQL Server必须至少找到一个可用页面的混合范围。如果表或索引是八页或更大,SQL Server必须找到一个免费的统一范围。
SQL Server使用两种特殊类型的页面来记录哪些范围已被分配以及哪种类型的使用(混合或统一)范围可用。有两种特殊类型的分配页面,它们跟踪哪些区段已被使用,既可以是统一的区段,也可以是混合的区段。全局分配映射(GAM)页面记录哪些范围已被分配用于任何类型的用途。共享全局分配映射(SGAM)页面记录了哪些扩展盘区被用作混合盘区。GAM始终是任何数据文件的第2页,而SGAM是第3页。每4GB有额外的GAM和SGAM页面。

在创建大量表的数据库中,会有很多混合的盘区分配,因此SGAM页面上会有大量活动。当然,在tempdb中可能会创建大量表。Page Free Space或PFS页面是另一种类型的分配页面,当大量表格被创建时,这种分配页面被大量使用。每个文件都是第1页,每8,088页有一个额外的PFS页面。

查看SQL Server阻止的进程信息可以告诉您是否必须在混合盘区中查找可用页面导致问题。DMV sys.dm_os_waiting_tasks工具可以显示正在等待的资源。如果它是数据库2(tempdb)中的第1页或第3页,我们通常称之为“分配争用”。在SQL Server 2016之前,有两种方法可能会缓解分配争用问题。由于每个文件都有自己的GAM,SGAM和PFS页面,因此创建更多的文件将允许您使用不同的SGAM或PFS页面跟踪所需的页面,从而分散工作。当分配分散在多个文件上时,在相同的SGAM或PFS页面上出现冲突的可能性较小。建议创建一个tempdb文件,用于SQL Server使用的每个逻辑CPU,除非您拥有八个以上的文件。如果您有八个以上的CPU,则可以从八个文件开始,并且只有在您继续看到分配争用时才添加更多。

另一个解决方案是完全消除混合程度。跟踪标志1118会关闭整个SQL Server实例中的混合范围使用情况。

截至SQL Server 2016的新选项
在SQL Server 2016中,微软使我们更容易一些。首先,安装过程不是为默认创建的tempdb创建一个文件,而是使用刚描述的算法假定您将拥有多个tempdb文件。数据库引擎配置屏幕中有一个选项卡,如下所示,它允许您配置文件。下面的屏幕是我的新实例在具有四个逻辑处理器的计算机上的默认值。我运行SQL Server 2017的屏幕捕获和我的结果将使用Microsoft显示® SQL Server Management Studio中17.5。

您可以看到,默认情况下,它会创建四个数据文件,但我可以在安装期间更改该数字。默认目录显示为SQL Server自己的DATA目录,但也可以更改。如果需要,我还可以添加其他目录以将文件分散到多个磁盘上。我可以选择初始大小和增长增量。在安装SQL Server之后,总是可以配置tempdb以在多个位置拥有多个文件,并且具有不同的大小和增长增量,但将这些选项作为安装的一部分使得新DBA更有可能关注这些选项。具有多个文件的默认值与以前的版本不同。

SQL Server 2016中的一个全新功能是一个数据库选项,它允许我们取消跟踪标志2018.该选项称为MIXED_PAGE_ALLOCATION,并且当为数据库设置为OFF时,SQL Server将永远不会为您的表或索引分配混合盘区。对于除tempdb以外的每个数据库,该值默认为ON 。Tempdb将此值设置为OFF,并且无法更改。要查看每个数据库的值,可以运行以下查询。

SELECT name, is_mixed_page_allocation_on
FROM sys.databases;
GO
---值1表示设置为ON,而0表示关闭。


调整tempdb

没有一个确定的算法,来确定tempdb应该占用的大小,我们通常使用 使用中各种会话 存储 内部对象 使用的MB,估算出tempdb平均合理的使用大小,
然后设定tempdb的大小。

下文列出 用户表,内部对象和版本存储。最后一个查询显示所有正在运行的任务的所有内部对象正在使用多少tempdb空间。

-- 获取temp可用空间

SELECT SUM(unallocated_extent_page_count) AS [空闲页],
(SUM(unallocated_extent_page_count)*1.0/128) AS [空闲页MB]
FROM tempdb.sys.dm_db_file_space_usage;

--tempdb 已用页数

SELECT SUM(user_object_reserved_page_count) AS [已用页数],
(SUM(user_object_reserved_page_count)*1.0/128) AS [已用MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- tempdb内部对象使用数

SELECT SUM(internal_object_reserved_page_count) AS [内部对象使用],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [内部对象使用MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- tempdb内部存储使用数

SELECT SUM(version_store_reserved_page_count) AS [内部存储使用数],
(SUM(version_store_reserved_page_count)*1.0/128) AS [内部存储使用数MB]
FROM tempdb.sys.dm_db_file_space_usage;

--每个会话所占用的内存空间数

SELECT session_id,
SUM(internal_objects_alloc_page_count) AS [已分配的会话占用],
SUM(internal_objects_dealloc_page_count) AS [未分配的会话占用]
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

----通过以上的代码,我们可以估算出tempdb所需的数据空间大小