SQLServer的系统数据库

2023-02-20 00:00:00 数据 数据库 文件 系统 数据库中

 首先要明确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

            相关文章