SQLServer的系统数据库

 首先要明确SQLServer的系统数据库一共有5个：Master、Model、Msdb、Tempdb、Resource。

1、Master数据库

　　（1）master数据库记录了所有系统级别的信息，包括元数据（登录账户）、端点、链接服务器和系统的配置设置。需要注意的是系统对象不在master库中，而在resource库中；

　　（2）mater数据库还记录了所有其他数据库的存在、数据库的文件的位置；

　　（3）master数据库不可用，则SQLServer实例就无法启动；

　　（4）不能给master数据库添加文件或文件组，不能更改master数据库的排序规则，默认是实例的排序规则；

　　（5）不能删除master数据库，不能删除guest用户;

　　（6）不能创建触发器，不能启动CDC（数据变更捕获），不能参与数据库镜像，不能设置为只读，不能设置为OFFLINE(脱机);

2、Model数据库

　　（1）model数据库是一个模板数据库，在create database时，将通过复制model数据库中的内容来创建数据库的部分，然后用空页填充新数据库的剩余部分；

　　（2）model数据库必须存在，model数据库的全部内容都会被复制到新数据库中，例如设置的权限、数据库选项、表、函数、存储过程等等;

　　（3）model数据库的数据和日志文件默认初始大小为8MB;

　　（4）每次系统重启创建的tempdb数据库的模板都来自于model；

3、Msdb数据库

　　（1）msdb用来保存SQLServer代理作业和执行计划的配置信息；

　　（2）默认情况下msdb使用简单模式；

4、Tempdb数据库

　　（1）tempdb数据库保存系统运行过程中产生的临时表、存储过程、全局或局部临时表及索引、表变量等等；

　　（2）tempdb数据库是一个全局资源，任何连接到系统的用户都可以在该数据库中产生临时表和存储过程；

　　（3）每次重启SQLServer的时候。都会清空该数据库中的内容，所以每次系统重启后，tempdb总是一个干净的数据库副本（模板来自model数据库）；

　　（4）tempdb中的操作是小日志记录操作，以便回滚事务；

　　（5）不允许对tempdb数据库做备份还原操作；

　　（6）tempdb数据库的大小和物理位置可能影响系统的性能。例如定义的tempdb大小过小，那么每次重启SQLServer实例时，都可能会占用部分系统处理负载，让tempdb自动增长到工作所需的大小（如果正常工作需要的tempdb的大小比较大的话）；

　　（7）每个文件组中的数据文件大小应一致，SQLServer可以使用比例填充算法增加可用空间，进行文件分配，将tempdb将分割成大小相等的多个数据文件，这样可以为使用tempdb的操作提供更高的并行效率；

　　（8）SQLServer2016开始，对tempdb的性能进行了优化，具体如下：

　　　　* 临时表和表变量可以缓存。缓存允许删除和创建临时对象的操作非常快速的执行，减少了物理上页分配的争用问题；

　　　　* 改进了分配页闩锁协议，减少了所用UP（更新）闩锁的数量；

　　　　* 减少了tempdb的日志记录开销，从而减少了tempdb日志文件的磁盘I/O带宽消耗；

　　　　* 新安装的SQL实例，tempdb会添加多个tempdb数据文件；

　　（9）查看当前tempdb的大小和增长参数

　　命令：

  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

       （10）验证tempdb是否经过了内存优化（内存优化表）

　命令： 

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

 5、Resource数据库

　　（1）Resource数据库是一个只读的数据库，里面包含了SQLServer中所有的系统对象，默认是隐藏的，在SSMS里看不到这个数据库；
　　（2）Resource数据库数据文件和日志文件的存储路径为C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn下，文件名叫mssqlsystemresource.mdf和mssqlsystemresource.ldf,位置不应该移动；

　　（3）SQLServer系统对象在物理上上存储在Resourece数据库中，但在逻辑上却显示在每个数据库的sys架构中。Resource数据库不包含用户数据和用户元数据；
　　（4）每个SQLServer实例都有且只有一个关联的mssqlsystemresource.mdf文件，多个实例间不能共享此文件；
　　（5）SQLServer不能备份Resource备份；
　　（6）Resource数据库的ID始终是32767，与Resource数据库关联的其他重要值是版本号和数据库上次更新的时间；　　

　　（7）查看resource数据库的版本号

　　命令：

select serverproperty('resourceVersion')

 　　（8）查看resource数据库上次升级的时间

　　命令：

select serverproperty('ResourceLastUpdateDateTime')

 　　（9）查看系统对象（sys.objects）的定义

　　命令：

select object_definition(object_id('sys.objects'))

本文来源https://www.modb.pro/db/75154