默认情况下,每个SQLServer实例都带有四个系统数据库——Master、MSDB、Model和TempDB。TempDB数据库是一个全局数据库,几乎所有用户和进程都使用它来实现各种功能,例如存储临时对象、内部对象、某些操作的中间排序结果(GroupBY、OrderBY、重建索引)。
那么,SQLServerTempDB和Model数据库之间有什么关系呢?下面,我们将验证两者之间的关系。
TempDB是Model数据库的副本吗?
SQLServer中的Model数据库充当SQLServer中所有新创建的数据库的模板。如果对此数据库进行任何更改,所有新数据库都会获得这些更改。现在,大多数DBA都知道SQLServer会在服务重新启动时重新创建TempDB。因此,不要使用TempDB将对象存储在TempDB数据库中。
因此,SQLServer也使用Model数据库来创建TempDB数据库。然而,它不是一个精确的副本。
恢复模式差异
查看两者的回复模式
Select[name]asDatabasename,recovery_model_descasDBReocoveryModelfromsys.databaseswherenamein(TempDB,Model)
让我们尝试重新启动SQLServer并重新运行上述查询。你得到相同的结果。TempDB始终保留在简单恢复模式,因为它是一个日志记录最少的数据库。你不能将恢复模式修改为完整或大容量日志。否则,它会给出以下错误消息。
USE[master];ALTERDATABASE[TempDB]SETRECOVERYFULL;
结论:TempDB数据库始终保持在简单的恢复模式。Model数据库恢复模型不会影响它。
TempDB数据文件和日志文件
默认情况下,每个新数据库都会获得一个主数据文件(*.mdf)和事务日志文件(*.ldf)。根据最佳实践,DBA根据逻辑处理器的数量配置多个TempDB数据库文件以减少争用。
逻辑处理器数小于等于8,按逻辑处理器创建数据文件
逻辑处理器数量大于八个,创建八个数据文件
监控TempDB争用,并在需要时将数据文件增加四的倍数
假设你为SQL实例创建了四个TempDB数据文件。我们知道,默认情况下,Model数据库只有一个数据文件。所以想几个问题:
每次重新启动SQLServer时都需要重新创建TempDB文件吗?
TempDB文件数是否等于Model数据库数据文件?
在我的演示环境中,我有一个TempDB数据文件。因此,我使用以下查询添加了另外三个文件:
USE[master]GOALTERDATABASE[tempdb]ADDFILE(NAME=Ntempdev2,FILENAME=ND:\ProgramFiles\MicrosoftSQLServer\MSSQL14.MSSQL\MSSQL\DATA\tempdev2.ndf,SIZE=KB,FILEGROWTH=KB)GOALTERDATABASE[tempdb]ADDFILE(NAME=Ntempdev3,FILENAME=ND:\ProgramFiles\MicrosoftSQLServer\MSSQL14.MSSQL\MSSQL\DATA\tempdev3.ndf,SIZE=KB,FILEGROWTH=KB)GOALTERDATABASE[tempdb]ADDFILE(NAME=Ntempdev4,FILENAME=ND:\ProgramFiles\MicrosoftSQLServer\MSSQL14.MSSQL\MSSQL\DATA\tempdev4.ndf,SIZE=KB,FILEGROWTH=KB)GO
如下图,TempDB数据库有四个数据文件,而Model数据库有一个数据文件。
selectname,state_descfromtempdb.sys.database_fileswheretype=0selectname,state_descfrommodel.sys.database_fileswheretype=0
现在,使用SQLServer配置管理器重新启动SQL服务并验证TempDB数据文件的数量。你将获得在SQL服务重新启动之前配置的四个TempDB数据文件。
SQLServer将数据文件和日志文件的数量存储在其内部表中。例如,以下查询从[sys].[master_files]中检索TempDB配置的数据。
selectdb_name(database_id)as[DatabaseName],type_desc,name,physical_name,size,growthfrom[Master].[sys].[master_files]wheredb_name(database_id)=TempDB
结论:SQLServer会在SQL服务重新启动后保留TempDB数据文件的数量。
TempDB文件的自动增长配置
我们将文件增长设置为16MB.
USE[master]ALTERDATABASE[tempdb]MODIFYFILE(NAME=Ntempdev,SIZE=KB)ALTERDATABASE[tempdb]MODIFYFILE(NAME=Ntempdev2,SIZE=KB)ALTERDATABASE[tempdb]MODIFYFILE(NAME=Ntempdev3,SIZE=KB)ALTERDATABASE[tempdb]MODIFYFILE(NAME=Ntempdev4,SIZE=KB)
重启SQLServer服务,当tempdb重建后,文件数及增长大小并没有改变。
结论:TempDB没有从模型数据库中获取自动增长配置。
Model和TempDB数据库中的数据库对象
众所周知,Model数据库是新创建的数据库的模板。因此,在你希望所有新数据库都拥有这些对象的副本之前,不应在其中创建任何对象。假设你在Model数据库中创建了一个审计表。
TempDB是否从Model数据库中获取对象?
为了回答上述问题,让我们使用以下脚本在Model数据库中创建一个对象。
UseModelgoCreateTableAuditLogs(IDint,Auditdatavarchar(50))
现在,重新启动SQLServer服务并验证[AuditLogs]表是否存在于TempDB数据库中。该表存在如下所示。
select*fromtempdb.sys.tables
结论:如果你在Model数据库中创建任何对象,它会在TempDB数据库中创建,服务重启的行为类似于新的常规用户数据库
哪个数据库先启动——Model还是TempDB?
SQL服务启动后,它会按照特定顺序将所有数据库置于联机状态。它将一个条目记录到启动数据库及其恢复过程的错误日志中。
要获取数据库启动顺序,请执行以下查询:
DECLARE
DBSTABLE(LogDateDATETIME,ProcessInfoVARCHAR(10),LogTextVARCHAR(50))INSERTINTODBS(LogDate,ProcessInfo,LogText)EXECsys.sp_readerrorlog0,1,StartingupdatabaseSELECTdbs.LogDateLogDateTime,dbs.ProcessInfo,dbs.LogTextFROMDBSASdbsORDERBYdbs.LogDateASC如上图所示,SQLServer首先启动Master数据库,然后是资源数据库(mssqlsystemresource)。TempDB仅在Model数据库之后启动。因此,Model数据库对于SQL服务启动是必不可少的。如果它不可访问,SQL服务将不会启动。
如果你专门为TempDB过滤错误日志,你将获得以下条目。在TempDB启动期间,它还会在错误日志中记录TempDB文件的数量。
结论:Model数据库对于服务启动期间的TempDB是必不可少的。你还可以从错误日志中找出TempDB文件的数量
TempDB数据库备份的工作方式是否类似于Model数据库
Model数据库备份取决于恢复模型。如果DB处于默认(完整)恢复模式,你可以进行类似于常规事务数据库的完整、差异、日志备份。
TempDB数据库始终保持在简单恢复模式中。但是,你不能对TempDB进行任何类型的备份。在SSMSGUI中,你看不到TempDB备份选项。
如果你尝试使用T-SQL进行数据库备份,则会收到以下错误消息。它终止备份,因为它在TempDB数据库上是不允许的。
Model和TempDB数据库中的事务日志记录
SQLServer对TempDB数据库使用最少的事务日志记录。因此,如果你在常规用户数据库或TempDB上运行工作负载,则性能会有所不同。如果我们重新启动SQL服务,我们会得到一个干净的TempDB副本。因此,TempDB仅记录事务的某些日志记录。
另一方面,Model数据库是模板数据库;因此,即使在执行回滚的简单恢复模式中,SQLServer也能确保记录事务。
为了检查事务性能,我们将在Model和TempDB数据库上启动相同的事务。
注意:不要使用Model数据库来执行查询。在本文中,我们使用它来执行Model和TempDB关系的演示
以下查询在TempDB数据库中创建[DemoTable]并向其中插入几条记录。
UseTempDBGoCREATETABLEDemoTable(IDINTIDENTITY,[Date]DATETIMEDEFAULTGETDATE(),[Text]varchar()defaultABC);SETNOCOUNTON;declare
iint=0whileibeginINSERTINTOdbo.DemoTableDEFAULTVALUES;seti=i+1endGO如下图,TempDB使用了7秒。同样在Model数据库上执行同样的操作,耗时45秒。这是由于TempDB中的事务日志记录最少。
本文探讨了SQLServerModel和TempDB数据库之间的关系。通常,DBA不会考虑Model数据库的重要性,因为它只是新数据库的模板。它在SQLServer数据库创建中起着至关重要的作用。TempDB数据库也是它的一个副本,它继承了一些属性,例如文件数量、自动增长、来自主数据库表的恢复模式。
预览时标签不可点收录于话题#个上一篇下一篇