您的位置:新葡亰496net > 网络数据库 > 二零零七中的文件和文件组,数据库优化施行

二零零七中的文件和文件组,数据库优化施行

发布时间:2019-07-13 21:58编辑:网络数据库浏览(141)

    数据库是数据的仓库,用于存储数据,而存储数据需要媒介,现在的存储媒介,最常用的是硬盘,土豪一点的服务器使用固态硬盘(SSD),特殊用途的服务器使用内存。数据库最常用的存储文件是数据文件和日志文件,数据文件用于存储数据,由一个主数据文件(.mdf)和若干个辅助数据文件(.ndf)构成;日志文件用于存储事物日志,由.ldf文件构成。不同的文件可以存分布到不同的物理硬盘上,这样便于分散硬盘IO,提高数据的读取速度。

     

    SQL Server的逻辑存储结构为文件组(file group)、区(extent)、数据页(data page)。
      SQL Server 将数据库映射为一组操作系统文件。数据和日志信息绝不混合在同一个文件中,而且一个文件只由一个数据库使用。文件组是文件的命名集合,用于简化数据存放和管理任务(例如,备份和还原操作)。

    优化技巧主要是面向DBA的,但我认为即使是开发人员也应该掌握这些技巧,因为不是每个开发团队都配有专门的DBA的。

    数据库使用数据文件(扩展名是mdf 或 ndf)来存储数据,使用日志文件(扩展名是ldf)来存储事务日志,通常情况下,数据文件会持续增长,不会自动释放空闲空间,这样会导致硬盘空间耗尽。如果一个数据库的文件有很多空闲空间,收缩数据库文件是一种解决硬盘空间紧张的直接方式。在SQL Server中,我们可以使用 DBCC ShrinkFile命令收缩数据文件,该命令首先将文件尾部的区(extent)移动到文件的开头,文件结尾的空闲的硬盘空间被释放给操作系统,这种操作就像截断将文件的尾部一样,这种方式不需要消耗很多IO就能释放空间;但是,如果空闲部分不在文件末尾时,收缩操作必须扫描数据文件,并对正在读取的页面加锁,把文件末尾的区移动到文件开头,这是一个IO密集型的操作,影响数据库的性能,但是,收缩操作不是一个独占行为,在做文件收缩时,其他用户仍然可以对数据库进行读写操作。在任意一个时间点停止dbcc shrinkfile命令,任何已经完成的工作都将保留。

    数据文件的组合,称作文件组(File Group),数据库不能直接设置存储数据的数据文件,而是通过文件组来指定。

    1、文件和文件组的含义与关系

    数据库文件

    SQL Server 数据库具有三种类型的文件:

    • 主数据文件
      主数据文件是数据库的起点。除了存储系统以及用户数据以外,主数据文件还存储了数据库中的所有辅助数据文件以及重做日志文件的路径、名称、大小等信息。SQL Server通过读取主数据文件得到其他数据文件及重做日志文件的信息,这个功能与Oracle控制文件相似。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是 .mdf。
    • 次要数据文件
      除主数据文件以外的所有其他数据文件都是次要数据文件,次数据文件一般只存储用户数据。某些数据库可能不含有任何次要数据文件,而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
    • 日志文件
      日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件,当然也可以有多个。日志文件的推荐文件扩展名是 .ldf。

    SQL Server 不强制使用 .mdf、.ndf 和 .ldf 文件扩展名,但使用它们有助于标识文件的各种类型和用途。
      在 SQL Server 中,数据库中所有文件的位置都记录在数据库的主文件和 master 数据库中。大多数情况下,SQL Server 数据库引擎使用 master 数据库中的文件位置信息。但是,在下列情况下,数据库引擎使用主文件的文件位置信息初始化 master 数据库中的文件位置项:

    • 使用带有 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 选项的 CREATE DATABASE 语句来附加数据库时。
    • 从 SQL Server 2000 版或 7.0 版升级时。
    • 还原 master 数据库时。

      第九步:合理组织数据库文件组和文件

    一,收缩文件命令

    一,文件组和文件的作用

    SQL Server的数据存储在文件中,文件是实际存储数据的物理实体,文件组是逻辑对象,SQL Server通过文件组来管理文件。

    新葡亰496net 1

     

    一个数据库有一个或多个文件组,主文件组(Primary File Group)是系统自动创建的,用户可以根据需要添加文件组。每一个文件组管理一个或多个文件,其中主文件组中包含主数据文件(master data file),扩展名是.mdf,这个文件是系统默认生成的,并且在数据库中是唯一的;辅助文件的扩展名是.ndf,是用户根据需要添加的。主文件组中也可以包含辅助文件,除了主文件组之外,其他文件组只能包含辅助文件。

    例如,查看示例数据库的文件组,Primary 是住文件组,勾选Default表示住文件组是默认的文件组,这意味着,如果在create table和create index中没有指定FileGroup选项,那么SQL Server将使用默认的文件组来存储数据。

    新葡亰496net 2

    文件组是一个逻辑实体,实际上,数据存储在文件中(.mdf和.ndf)中,每一个文件组中都包含文件,因此,在create table和create index命令中指定文件组,那么数据就会存储到文件组包含的文件中。

    新葡亰496net 3

    从上图中可以看到,数据库文件的元数据:

    • Logical Name是数据文件的逻辑名称,用于数据压缩 DBCC ShrinkFile等;
    • 数据库文件有两种类型:Rows Data(存储数据)和Log(存储日志),
    • Initial Size是文件的初始大小,
    • Autogrowth表示文件自动增加的大小,Maxsize是文件大小的最大值,
    • Path参数表示文件的路径,
    • File Name是文件的物理名称,逻辑名称和物理名可以是不同的。

    用户也可以使用sys.database_filessys.filegroups 查看数据的文件和文件组的元数据。

    每个数据库有一个主数据文件.和若干个从文件。文件是数据库的物理体现。 文件组可以包括分布在多个逻辑分区的文件,实现负载平衡。文件组允许对文件进行分组,以便于管理和数据的分配/放置。例如,可以分别在三个硬盘驱动器上创建三个文件(Data1.ndf、Data2.ndf 和 Data3.ndf),并将这三个文件指派到文件组 fgroup1 中。然后,可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上,因而性能得以提高。在 RAID(磁盘冗余阵列)条带集上创建单个文件也可以获得相同的性能改善。然而,文件和文件组使您得以在新磁盘上轻易地添加新文件。另外,如果数据库超过单个 Microsoft Windows 文件的最大大小,则可以使用次要数据文件允许数据库继续增长。

    数据库文件组

    为便于分配和管理,可以将数据库对象和文件一起分成文件组。SQL Server的文件组由若干个数据文件组成。
      SQL Server的文件组分为primary文件组和用户文件组,分别对应Oracle数据库中的system表空间和用户表空间。

    • primary文件组
      主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。与Oracle数据库的system表空间相似,primary文件组不能删除,其名称primary也是固定不能修改的。
    • 用户定义文件组
      用户定义文件组是通过在 CREATE DATABASE 或 ALTER DATABASE 语句中使用 FILEGROUP 关键字指定的任何文件组。

    日志文件不包括在文件组内。日志空间与数据空间分开管理。
      SQL Server数据库中没有对应于Oracle临时表空间的文件组,SQL Server的多版本数据(undo)以及排序或散列操作所产生的临时数据都存储于tempdb系统数据库中,多个数据库共用tempdb数据库。

    一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联。在这种情况下,它们的所有页将被分配到该文件组,或者对表和索引进行分区。已分区表和索引的数据被分割为单元,每个单元可以放置在数据库中的单独文件组中。
      在 SQL Server数据库中,不允许删除包含表或索引的文件组,这与Oracle不同,在Oracle中,如果表空间中包含数据,使用drop tablespace删除表空间时,可以附加including contents子句。
      每个数据库中均有一个文件组被指定为默认文件组。如果创建表或索引时未指定文件组,则将假定所有页都从默认文件组分配。一次只能有一个文件组作为默认文件组。如果没有指定默认文件组,则将主文件组作为默认文件组。db_owner 固定数据库角色成员可以将默认文件组从一个文件组切换到另一个。

    文件和文件组的设计规则
    下列规则适用于文件和文件组:

    • 一个文件或文件组不能由多个数据库使用。例如,任何其他数据库都不能使用包含 sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。
    • 一个文件只能是一个文件组的成员。
    • 事务日志文件不能属于任何文件组。

      创建SQL Server数据库时,数据库服务器会自动在文件系统上创建一系列的文件,之后创建的每一个数据库对象实际上都是存储在这些文件中的。SQL Server有下面三种文件:

    收缩文件(DBCC SHRINKFILE)命令做的收缩都是区一级的动作,它会把使用过的区向文件前面移动,把没有使用的区从文件中移除。但是,它不会把一个区里面的空闲页面(Empty Page)移除、也不会把页面中的空闲空间移除。因此,如果一个数据库文件中含有很多只使用了一两个页面的区,收缩操作的效果会不明显。收缩文件不会丢失数据,如果数据最少占用1GB空间,那么收缩操作不会把文件收缩到少于1GB。

    二,使用文件组的优势

    在实际开发数据库的过程中,通常情况下,用户需要关注文件组,而不用关心文件的物理存储,即使DBA改变文件的物理存储,用户也不会察觉到,也不会影响数据库去执行查询。除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是:

    • 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么SQL Server能同时从不同的物理硬盘上读写数据,把IO负载分散到不同的硬盘上。
    • 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,SQL Server在读写某一个分组的数据时,能够调用不同的硬盘IO。

    这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高IO性能。

    创建分区表时,不同的分区可以使用相同的文件组,也可以使用不同的文件组。因此,在设计文件组时,应尽量把包含的文件包含在不同的硬盘上,以实现物理IO的最大分散化。

    在创建文件时,服务器CPU核的数量,决定最大的并发IO度,应该根据CPU 核的数量创建多个文件。通常情况下,文件的数量和CPU核的数量一致,是最优化的设计。

    还有,应该根据硬盘的性能来创建文件组,日志文件存储到性能最好的硬盘上,而查询延迟要求高的数据,也需要存储到性能最好的硬盘上。

    不是所有的数据都是同等重要的,应该根据业务需求和查询延迟,对数据分级,因此,在设计文件组时,应该把级别高的数据分散,而把那些基本用不到的数据存储到性能差的,用于存储归档数据的硬盘上,以实现服务器性能的合理配置。

    2、文件、文件组在实践应用中常见的问题

    区(extent)

    extent是给表或索引分配存储空间的单位,也是管理空间的基本单位。
      在SQL Server中,extent的大小是固定的8个连续的数据页,64KB,这意味着 SQL Server 数据库中每 MB 有 16 个区。在创建文件组时,不能指定类似Oracle中的autoallocate或uniform size子句定义extent的大小,在这方面,SQL Server的灵活性稍差一些。

    新葡亰496net 4

      SQL Server对表的分配extent的方式与Oracle不同。为了使空间分配有效,SQL Server 不会将所有区分配给包含少量数据的表,所以SQL Server不会对空表分配extent,extend的分配会延迟到对表添加记录时。
      SQL Server 有两种类型的区:

    • 混合区(mixed extent):混合区由多个表或索引共用,最多可由八个对象共享。 区中八页的每页可由不同的对象所有。
    • 统一区(uniform extent):统一区由由单个对象所有。区中的所有 8 页只能由一个表或索引专用。

    通常对表或索引分配的前8个数据页会在混合区内分配,以后的数据页则在统一区内分配,这种方式与Oracle不同,Oracle的一个区只能分配给一个表或索引,不能多个对象共用,或者也可以说,Oracle只有SQL Server中的统一区一种类型。

      1).mdf文件

    收缩文件命令的语法:

    三,指定文件组

    在创建表时,在on子句中指定文件组,那么数据将存储在该文件组包含的文件中:

    CREATE TABLE [dbo].[student](
    ...
    ) ON [PRIMARY]
    

    在创建索引时,在on子句指定文件组选项,那么该表的索引结构将存储在文件组包含的文件中:

    CREATE NONCLUSTERED INDEX [idx_student_stuname] 
    ON [dbo].[student]( [stuname] ASC ) 
    ON [PRIMARY]
    

    在创建分区时,在TO自居中指定文件组,每一个分区将存储到文件组中:

    REATE PARTITION SCHEME scheme_name
    AS PARTITION function_name 
    TO ([fg_name1], <....>, [fp_nameN])
    

    通常情况下我们构造的数据库都只有两个文件,mdf文件和ldf文件.但是这样有两个缺点:

    页(data page)

    SQL Server 中数据存储的基本单位是页。 为数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。 磁盘 I/O 操作在页级执行。 也就是说,页也是也是读写数据的单位。
      页是区段的分配单元。每一个区段包含8个页,每个页的大小固定为8KB,不能修改,这与Oracle数据库在创建表空间时可以指定数据库大小不同。

    新葡亰496net 5

      上图展示了数据是如何存放在页中的。对于插入的每一行,为了表明特定行的数据开始于页中的何处,每一页的末尾都用一小块空间记录的每一行相对于页头位置的偏移量。
      SQL Server 数据文件中的页按顺序编号,文件的首页以 0 开始。数据库中的每个文件都有一个唯一的文件 ID 号。若要唯一标识数据库中的页,需要同时使用文件 ID 和页码。

      这是最主要的数据文件,每个数据库只能有一个主数据文件,所有系统对象都存储在主数据文件中,如果不创建次要数据文件,所有用户对象(用户创建的数据库对象)也都存储在主数据文件中。

    DBCC SHRINKFILE 
    (
        { file_name | file_id } 
        { [ , EMPTYFILE ] 
        | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
        }
    )
    

    四,数据文件大小增长导致的问题

    当数据文件爆满,没有空间存储数据时,此时执行insert命令,这会导致数据文件的增长。如果filegrowth选项设置的过大,会导致SQL Server耗费较长时间来实现文件的增长,在数据文件增长时,该文件是不能访问的,因此,即使用户仅插入一条数据,也要等待很长时间才能完成查询,对用户来说,体验不友好。

    数据文件增长是非常耗费系统资源和影响性能,如果设置SQL Server 自动增长,可能会导致系统性能不够稳定,所以,应该预测可能的空间使用需求,并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成,及时满足客户端应用的需求。

    (一)容易导致文件过大

    管理SQL Server文件组及文件组

    ALTER DATABASE database_name   
    {  
        <add_or_modify_files>  
      | <add_or_modify_filegroups>  
    }  
    [;]  
    
    <add_or_modify_files>::=  
    {  
        ADD FILE <filespec> [ ,...n ]   
            [ TO FILEGROUP { filegroup_name } ]  
      | ADD LOG FILE <filespec> [ ,...n ]   
      | REMOVE FILE logical_file_name   
      | MODIFY FILE <filespec>  
    }  
    
    <filespec>::=   
    (  
      NAME = logical_file_name
      [ , NEWNAME = new_logical_name ]
      [ , FILENAME ={'os_file_name'|'filestream_path'|'memory_optimized_data_path'}]
      [ , SIZE = size [ KB | MB | GB | TB ] ]
      [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
      [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
      [ , OFFLINE ]
    )
    
    <add_or_modify_filegroups>::=  
    {  
        | ADD FILEGROUP filegroup_name   
            [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
        | REMOVE FILEGROUP filegroup_name   
        | MODIFY FILEGROUP filegroup_name  
            { <filegroup_updatability_option>  
            | DEFAULT  
            | NAME = new_filegroup_name   
            | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
            }  
    }  
    <filegroup_updatability_option>::=  
    {  
        { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }  
    }  
    
    • <add_or_modify_files>::=</add_or_modify_files>:指定要添加、删除或修改的文件。
    • database_name:要修改的数据库的名称。
    • ADD FILE:向数据库中添加文件。
    • TO FILEGROUP { filegroup_name }:指定要将指定文件添加到的文件组。
    • ADD LOG FILE:将要添加的日志文件添加到指定的数据库。
    • REMOVE FILE logical_file_name:从 SQL Server 的实例中删除逻辑文件说明并删除物理文件。 除非文件为空,否则无法删除文件。
    • logical_file_name:在 SQL Server 中引用文件时所用的逻辑名称。
    • MODIFY FILE:指定应修改的文件。 如果指定了 SIZE,那么新大小必须比文件当前大小要大。
      若要修改数据文件或日志文件的逻辑名称,请在 NAME 子句中指定要重命名的逻辑文件名称,并在 NEWNAME 子句中指定文件的新逻辑名称。 例如:
    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
    

    若要将数据文件或日志文件移至新位置,请在 NAME 子句中指定当前的逻辑文件名称,并在 FILENAME 子句中指定新路径和操作系统(物理)文件名称。 例如:

    MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ')
    
    • { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'}
    • os_file_name:对于标准 (ROWS) 文件组,这是在创建文件时操作系统所使用的路径和文件名。
    • ' filestream_path ':对于 FILESTREAM 文件组,FILENAME 指向将存储 FILESTREAM 数据的路径。
    • memory_optimized_data_path:对于内存优化文件组,FILENAME 会引用将存储内存优化数据的路径。SIZE、MAXSIZE 和 FILEGROWTH 属性不适用于内存优化文件组。
    • FILEGROWTH:用于指定每次文件增长大小,如果未指定确定的值,则默认为1MB,如果指定为0,则数据文件不能自动增长。可以使用MB、KB、GB、TB或百分比(%)为单位,默认值为MB。如果指定%,则增量大小为发生增长时文件大小的指定百分比。指定的大小舍入为最接近64KB的倍数。
    • OFFLINE:将文件设置为脱机并使文件组中的所有对象都不可访问。
    • <add_or_modify_filegroups>::=</add_or_modify_filegroups>:在数据库中添加、修改或删除文件组。
    • CONTAINS FILESTREAM:指定文件组在文件系统中存储 FILESTREAM 二进制大型对象 (BLOB)。
    • CONTAINS MEMORY_OPTIMIZED_DATA:指定文件组在文件系统中存储内存优化数据。每个数据库只能有一个 MEMORY_OPTIMIZED_DATA 文件组。 在创建内存优化表时,文件组不能为空,其中必须至少包含一个文件。
    • REMOVE FILEGROUP filegroup_name:删除文件组filegroup_name从数据库中删除文件组。 除非文件组为空,否则无法将其删除。 首先从文件组中删除所有文件。
    • MODIFY FILEGROUP filegroup_name:修改文件组。
    • DEFAULT:更改默认的数据库文件组到filegroup_name。 数据库中只能有一个文件组作为默认文件组。
    • AUTOGROW_SINGLE_FILE:在文件组中的文件符合自动增长阈值时,仅该文件是增长。 这是默认设置。
    • AUTOGROW_ALL_FILES:如果文件组中的文件达到了自动增长阈值,文件组中的所有文件都增长。
    • <filegroup_updatability_option>:对文件组设置只读或读/写属性。
    • READ_ONLY | READONLY:指定文件组为只读。 不允许更新其中的对象。 主文件组不能设置为只读。 若要更改此状态,您必须对数据库有独占访问权限。
    • 因为只读数据库不允许数据修改,所以将发生以下情况:
      系统启动时,将跳过自动恢复。
      不能收缩数据库。
      在只读数据库中不会进行锁定。 这可以加快查询速度。

    【示例】

    A. 向数据库中添加由两个文件组成的文件组
      以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个 5 MB 的文件添加到该文件组。

    USE master  
    ALTER DATABASE AdventureWorks2012  
    ADD FILEGROUP Test1FG1;  
    GO  
    ALTER DATABASE AdventureWorks2012   
    ADD FILE   
    (  
        NAME = test1dat3,  
        FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat3.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    ),  
    (  
        NAME = test1dat4,  
        FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat4.ndf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )  
    TO FILEGROUP Test1FG1;  
    GO  
    

    B.向数据库中添加两个日志文件

    USE master;  
    ALTER DATABASE AdventureWorks2012   
    ADD LOG FILE   
    (  
        NAME = test1log2,  
        FILENAME = 'D:Microsoft SQL ServerMSSQLDATAtest2log.ldf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    ),  
    (  
        NAME = test1log3,  
        FILENAME = 'D:Microsoft SQL ServerDATAtest3log.ldf',  
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    );  
    GO  
    

    C.从数据库中删除文件

    USE master;  
    ALTER DATABASE AdventureWorks2012  
    REMOVE FILE test1dat4;  
    GO  
    

    D.修改文件
      以下示例添加的一个文件的大小。ALTER DATABASE MODIFY FILE 命令与可以使文件大小更大,因此如果你需要使文件大小更小你需要使用 DBCC SHRINKFILE。

    USE master;  
    ALTER DATABASE AdventureWorks2012   
    MODIFY FILE  
    (NAME = test1dat3,  
    SIZE = 200MB);  
    GO  
    

    此示例中收缩数据文件的大小为 100 MB,然后指定在该数量的大小。

    USE AdventureWorks2012;
    DBCC SHRINKFILE (AdventureWorks2012_data, 100);
    GO
    
    USE master;  
    ALTER DATABASE AdventureWorks2012   
    MODIFY FILE  
    (NAME = test1dat3,  
    SIZE = 200MB);  
    GO
    

    E.将文件移至新位置
      下面以把AdventureWorks数据中的数据文件E:t1dat2.ndf移动到C:t1dat2.ndf为例,说明移动数据文件的过程。
    首先把数据库脱机:

    alter database AdventureWorks set offline
    

    在操作系统中把E:t1dat2.ndf移动到C:t1dat2.ndf:

    !! move E:t1dat2.ndf C:t1dat2.ndf
    

    修改数据库中对此文件路径的记载:

    ALTER DATABASE AdventureWorks 
    MODIFY FILE  
    (  
        NAME = Test1dat2,  
        FILENAME = N'C:t1dat2.ndf'  
    );  
    GO  
    

    最后再把数据库重新联机:

    alter database AdventureWorks set online
    

    然后查询t1dat2的物理文件路径:

    select name,physical_name from sys.database_files where name ='C:t1dat2.ndf'
    

    F.使文件组成为默认文件组
      下面的示例使Test1FG1成为默认文件组。 然后,默认文件组被重置为 PRIMARY 文件组。 请注意,必须使用括号或引号分隔 PRIMARY。

    USE master;  
    GO  
    ALTER DATABASE AdventureWorks2012   
    MODIFY FILEGROUP Test1FG1 DEFAULT;  
    GO  
    ALTER DATABASE AdventureWorks2012   
    MODIFY FILEGROUP [PRIMARY] DEFAULT;  
    GO  
    

      2).ndf文件

    收缩文件是一个函数,其参数的含义是:

    五,仅使用MDF和LDF文件还原数据库

    在测试服务器上,有一个DB无法访问,原因是服务器上安装的是SQL Server的评估版本,在试用期过期之后,SQL Server实例无法打开,导致DB无法访问。该数据,只有MDF和LDF文件,没有做数据库的还原和分离操作,要还原数据库必须使用特殊的方法:重建一个名字相同DB,然后替换数据库的文件。

    • Step1,新建一个同名的数据库,该DB和过期的DB有相同文件;
    • Step2,将新DB脱机,删除其MDF和LDF文件;
    • Step3,将过期DB的MDF和LDF文件复制到相应的路径下;
    • Step4,将新DB联机,查看DB的数据,及时备份和还原。

     

     

    推荐文档:

    文件自动增长和自动收缩sql server

    我们知道,mdf文件是数据库文件,这样的话也就意味着随着数据库的增大mdf就会相应的增大,显然在现在的应用中数据膨胀是太常见的事情了,当你的应用变大后,mdf文件也会变大,然而windows对文件的大小是有要求的,这样的话很容易导致mdf文件达到windows所允许的文件大小的界限(于是数据库就崩溃了)。

    查询指定表被分配的extent信息

    在SQL Server可以使用dbcc extentinfo命令查询表被分配的extent信息。

    dbcc extentinfo(数据库名,表名)
    

      这些都是次要数据文件,它们是可选的,它们存储的都是用户创建的对象。

    • target_size :单位是MB,收缩操作的目标是把文件收缩到target_size指定的大小。收缩操作是把文件末尾的区向文件开头移动,这意味着,文件末尾的已使用的区会被移动到文件的前面,先重新分配可用的硬盘空间,在移动数据之后,释放数据原来占用的空间。
    • EMPTYFILE  :清空文件,把数据从当前文件迁移到同一个文件组中的他文件上,同时禁止数据存储到空文件中,被清空的文件被移除。
    • NOTRUNCATE :不截断选项,该选项的作用是把已分配(allocated)的区从文件末尾移动到文件前面的未分配的(unallocated )区中,由于文件末尾未的空闲空间并没有释放到操作系统,因此文件的大小不变。
    • TRUNCATEONLY :仅截断选项,该选项的作用是把文件末尾的空闲空间释放给操作系统,由于该选项不会做任何区的移动操作,因此,该选项不会消耗硬盘IO,操作的结果是数据文件的大小减少。在截断日志文件时,该选项不移动任何日志记录,仅仅从日志文件的末尾移除不活动的虚拟日志文件(VLF)。

    (二)没有利用到磁盘阵列

      3).ldf文件

    二,收缩文件的步骤

    大型的服务器好多都有磁盘阵列,你可以把磁盘阵列简单的假象成n个一块转动的磁盘,磁盘阵列的设计是希望通过多个磁盘的串联来得到更大的读写效率.但是如果你的数据库只有一个mdf文件(ldf文件暂时不考虑),那么你总是只能够利用这个磁盘阵列里面的一个磁盘而已.那样的话昂贵的磁盘阵列的效率就由并联变成串联了.试想如果我们能够让mdf分散成多个文件,比如说磁盘阵列上的每个磁盘中都分配一个文件,然后把mdf中的数据分散到各个文件中,我在读取的时候就是串联的读取了,这样就充分的利用了磁盘阵的存取效能.

      这些是事务日志文件,数量从一到几个不等,它里面存储的是事务日志。

    通常情况下,收缩数据库的数据文件和日志文件, 释放文件中的空闲空间,需要两个步骤:先移动,后释放

    这两个问题平常我们没有遇到过(条件不具备),但是做大型的服务开发的时候这几乎是致命的.

      默认情况下,创建SQL Server数据库时会自动创建主数据文件和事务日志文件,当然也可以修改这两个文件的属性,如保存路径。

    Step1,将文件的后部分中正在使用的区向前移动,移动到文件的前部分:

    3、查询文件组和文件语句

      文件组

    dbcc shrinkfile('filename',0,notruncate)
    

    这在2005下,运行

    二零零七中的文件和文件组,数据库优化施行。  为了便于管理和获得更好的性能,数据文件通常都进行了合理的分组,创建一个新的SQL Server数据库时,会自动创建主文件组,主数据文件就包含在主文件组中,主文件组也被设为默认组,因此所有新创建的用户对象都自动存储在主文件组中(具体说就是存储在主数据文件中)。

    Step2,将文件末尾未被使用的区释放掉,归还给操作系统

    SELECT df.[name],
    df.physical_二零零七中的文件和文件组,数据库优化施行。name,
    df.[size],
    df.growth,
    f.[name][filegroup],
    f.is_default
    FROM sys.database_files df JOIN sys.filegroups f
    ON df.data_space_id = f.data_space_id

      如果你想将你的用户对象(表、视图、存储过程和函数等)存储在次要数据文件中,那需要:

    dbcc shrinkfile('filename',target_size_MB,truncateonly)
    

    4、MSDN官方解释

      1)创建一个新的文件组,并将其设为默认文件组;

    清空文件,是为了把空文件从数据库中移除:

    了解文件和文件组

      2)创建一个新的数据文件(.ndf),将其归于第一步创建的新文件组中。

    -- Empty the data file.
    DBCC SHRINKFILE (Test1data, EMPTYFILE);
    GO
    -- Remove the data file from the database.
    ALTER DATABASE AdventureWorks2012
    REMOVE FILE Test1data;
    GO
    

    每个 SQL Server 数据库至少具有两个操作系统文件:一个数据文件和一个日志文件。数据文件包含数据和对象,例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理,可以将数据文件集合起来,放到文件组中。

      以后创建的对象就会全部存储在次要文件组中了。

    三,日志文件的收缩**

    à数据库文件
    SQL Server 数据库具有三种类型的文件,如下所示

      注意:事务日志文件不属于任何文件组。

    事务日志文件不停增长的原因有很多,比如

    主要數據文件

      文件/文件组组织最佳实践

    • 1,Recovery Mode 不是simple,或者说不处于auto-truncate 模式下,
    • 2,有一个很大的事务在运行,
    • 3,有一个运行很长时间的事务没有提交

    主要数据文件包含数据库的启动信息,并指向数据库中的其他文件。用户数据和对象可存储在此文件中,也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf.
    次要数据文件

      如果你的数据库不大,那么默认的文件/文件组应该就能满足你的需要,但如果你的数据库变得很大时(假设有1000MB),你可以(应该)对文件/文件组进行调整以获得更好的性能,调整文件/文件组的最佳实践内容如下:

    都会导致事务日志文件不停增长,此时执行dbcc shrinkflie 命令是不能把日志文件shrink的,必须针对特定的情况,采取对应的对策,一般来说,

    是可选的,由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上,次要文件可用于将数据分散到多个磁盘上。另外,如果数据库超过了单个 Windows 文件的最大大小,可以使用次要数据文件,这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。
    事务日志文件
    事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf.

      1)主文件组必须完全独立,它里面应该只存储系统对象,所有的用户对象都不应该放在主文件组中。主文件组也不应该设为默认组,将系统对象和用户对象分开可以获得更好的性能;

    • 对于1,必须安排backup log的计划,使事务日志文件截断和重用 ,
    • 对于2,优化语句的业务逻辑,避免出现大的事务,
    • 对于3,优化application,及时commit 或 rollback 事务。

    à文件组
    每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组,用于将数据文件集合起来,以便于管理、数据分配和放置。

      2)如果有多块硬盘,可以将每个文件组中的每个文件分配到每块硬盘上,这样可以实现分布式磁盘I/O,大大提高数据读写速度;

    有时,会遇到日志文件无法清空的异常:

    例如,可以分别在三个磁盘驱动器上创建三个文件 Data1.ndf、Data2.ndf 和 Data3.ndf,然后将它们分配给文件组 fgroup1。然后,可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上,从而提高了性能。通过使用在 RAID(独立磁盘冗余阵列)条带集上创建的单个文件也能获得同样的性能提高。但是,文件和文件组使您能够轻松地在新磁盘上添加新文件。

      3)将访问频繁的表及其索引放到一个单独的文件组中,这样读取表数据和索引都会更快;

     

    以下列出了存储在文件组中的所有数据文件。

      4)将访问频繁的包含Text和Image数据类型的列的表放到一个单独的文件组中,最好将其中的Text和Image列数据放在一个独立的硬盘中,这样检索该表的非Text和Image列时速度就不会受Text和Image列的影响;

    dbcc shrinkfile('db_log',emptyfile)
    

    主文件组
    主要包含主要文件的文件组。所有系统表都被分配到主要文件组中。
    用户定义文件組

      5)将事务日志文件放在一个独立的硬盘上,千万不要和数据文件共用一块硬盘,日志操作属于写密集型操作,因此保证日志写入具有良好的I/O性能非常重要;

     

    用户首次创建数据库或以后修改数据库时明确创建的任何文件组

      6)将“只读”表单独放到一个独立的文件组中,同样,将“只写”表单独放到一个文件组中,这样只读表的检索速度会更快,只写表的更新速度也会更快;

     

    默认文件组
    如果在数据库中创建对象时没有指定对象所属的文件组,对象将被分配给默认文件组。不管何时,只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大,能够容纳未分配给其他文件组的所有新对象。

      7)不要过度使用SQL Server的“自动增长”特性,因为自动增长的成本其实是很高的,设置“自动增长”值为一个合适的值,如一周,同样,也不要过度频繁地使用“自动收缩”特性,最好禁用掉自动收缩,改为手工收缩数据库大小,或使用调度操作,设置一个合理的时间间隔,如一个月。

    四,收缩操作的副作用

    PRIMARY 文件组是默认文件组,除非使用 ALTER DATABASE 语句进行了更改。但系统对象和表仍然分配给 PRIMARY 文件组,而不是新的默认文件组。

    第十步:在大表上应用分区

    收缩数据文件是IO密集型的操作,需要IO,CPU和内存资源,并会产生大量的事务日志,并会导致索引碎片,因此,尽量避免收缩数据文件,只有当迫不得已时,才适当收缩数据文件。由于数据文件的增长也是非常耗时的,所以,不要把数据文件收缩得太小,要预留足够的空间,避免数据文件出现增长的情况。

    5、操作實例

      什么是表分区?

    1,影响Range操作的查询性能

    --(5.1)创建数据库
    --切换到 master 数据库
    USE master
    GO
    create database db_Study
    on primary --主文件组和主要数据文件
    (name='db_study_Primary',
    filename='D:studydb_新葡亰496net,study_pri.mdf'),
    filegroup db_Study_filegroup1 --用户定义文件组1
    (name='db_study_fg_data1',
    filename='D:studydb_study_fg_data1_1.ndf'),--次要数据文件1
    (name='db_study_fg_data2' ,
    filename='D:studydb_study_fg_data2_2.ndf'),--次要数据文件2
    filegroup db_Study_filegroup2
    (name='db_study_fg_data3',
    filename='D:studydb_study_fg_data3_1.ndf')
    log on
    (name='db_study_log',
    filename='D:studydb_study.ldf')
    go
    --5.2查询文件组和文件
    SELECT df.[name],
    df.physical_name,
    df.[size],
    df.growth,
    f.[name][filegroup],
    f.is_default
    FROM sys.database_files df JOIN sys.filegroups f
    ON df.data_space_id = f.data_space_id
    /*
    name physical_name size growth filegroup is_default
    db_study_Primary D:studydb_study_pri.mdf 280 128 PRIMARY 1
    db_study_fg_data1 D:studydb_study_fg_data1_1.ndf 128 128 db_Study_filegroup1 0
    db_study_fg_data2 D:studydb_study_fg_data2_2.ndf 128 128 db_Study_filegroup1 0
    db_study_fg_data3 D:studydb_study_fg_data3_1.ndf 128 128 db_Study_filegroup2 0
    */

      表分区就是将大表拆分成多个小表,以免检索数据时扫描的数据太多,这个思想参考了“分而治之”的理论。

    Shrink file 有可能产生大量的碎片,这是因为shrink file 存在Extent的移动,逻辑上连续的extent,在移动之后,可能会导致物理上不再连续,影响Range 操作的性能。

    --5.3修改默认数据文件组
    alter database db_study
    modify filegroup db_study_filegroup1 default
    --5.4
    --在默认文件组db_study_filegroup1创建表,
    --并且指定图像数据保存在用户定义文件组db_study_filegroup1
    create table my_test
    (
    ID int primary key,
    [Name] varchar(10),
    PIC image
    )textimage_on db_study_filegroup2
    --在用户定义文件组db_study_filegroup2上创建索引
    create index ix_my_test on my_test(ID) on db_study_filegroup2
    GO
    --5.5将要删除数据文件db_study_fg_data1的数据转移到其他数据文件中,
    --并且清空数据文件db_study_fg_data1
    DBCC SHRINKFILE(db_study_fg_data1,EMPTYFILE)
    go
    --删除数据文件db_study_fg_data1
    ALTER DATABASE DB_study
    REMOVE FILE db_study_fg_data1
    go

      当你的数据库中有一个大表(假设有上百万行记录),如果其它优化技巧都用上了,但查询速度仍然非常慢时,你就应该考虑对这个表进行分区了。首先来看一下分区的类型:

    2,消耗IO和CPU资源

    作者:wufeng4552 博客:

      水平分区:假设有一个表包括千万行记录,为了便于理解,假设表有一个自动增长的主键字段(如id),我们可以将表拆分成10个独立的分区表,每个分区包含100万行记录,分区就要依据id字段的值实施,即第一个分区包含id值从1-1000000的记录,第二个分区包含1000001-2000000的记录,以此类推。这种以水平方向分割表的方式就叫做水平分区。

    如果SQL Server在shrink data file时不移动数据,那么shrink 就不会产生碎片,对现有数据不会有影响。唯一不影响现有物理数据的情况是在执行DBCC Shrinkfile 命令时指定TruncateOnly选项,DBCC Shrink命令只将文件末尾的剩余空间释放,归还给OS。

      垂直分区:假设有一个表的列数和行数都非常多,其中某些列被经常访问,其余的列不是经常访问。由于表非常大,所有检索操作都很慢,因此需要基于频繁访问的列进行分区,这样我们可以将这个大表拆分成多个小表,每个小表由大表的一部分列组成,这种垂直拆分表的方法就叫做垂直分区。

    Shrinkfile 是个非常消耗IO资源的操作,Shrinkfile的过程需要移动大量的数据,消耗大量的IO;Shrinkfile的过程会被记录到日志,造成日志暴涨;还会消耗大量的CPU资源。

      另一个垂直分区的原则是按有索引的列无索引列进行拆分,但这种分区法需要小心,因为如果任何查询都涉及到检索这两个分区,SQL引擎不得不连接这两个分区,那样的话性能反而会低。

    3,产生大量的日志,影响依赖日志的Application的性能

      本文主要对水平分区做一介绍。

    收缩产生的大量日志会被事务日志传送,镜像,复制等操作重复执行。

      分区最佳实践

    4,ShrinkFile 不能将Data File 收缩的太小

      1)将大表分区后,将每个分区放在一个独立的文件中,并将这个文件存放在独立的硬盘上,这样数据库引擎可以同时并行检索多块硬盘上的不同数据文件,提高并发读写速度;

    当插入新的数据时,如果Data File空间不够,那么SQL Server需要重新申请 Disk Space,增加 Data file Size,在此过程中,SQL Server需要对新分配的Disk Space填0初始化,除非你开启的是不用填零初始化的选项,不用填零初始化有泄露信息的风险。在全部空间初始化完成之前,新分配的Data File是不能使用的。这个填0初始化,是个非常耗费IO资源的操作,如果一次增长的空间过大,SQL Server需要很长时间对新分配的Disk Space进行初始化,这可能会导致Application timeout,使事务回滚,影响Application的性能。

      2)对于历史数据,可以考虑基于历史数据的“年龄”进行分区,例如,假设表中存储的是订单数据,可以使用订单日期列作为分区的依据,如将每年的订单数据做成一个分区。

     

      如何分区?

    参考文档:

      假设Order表中包含了四年(1999-2002)的订单数据,有上百万的记录,那如果要对这个表进行分区,采取的步骤如下:

    DBCC SHRINKFILE (Transact-SQL).aspx)

      1)添加文件组

    Why you want to be restrictive with shrink of database files 

      使用下面的命令创建一个文件组:

      ALTER DATABASE OrderDB ADD FILEGROUP [1999]

      ALTER DATABASE OrderDB ADD FILE (NAME = N'1999', FILENAME

      = N'C:OrderDB1999.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO

      FILEGROUP [1999]

      通过上面的语句我们添加了一个文件组1999,然后增加了一个次要数据文件“C:OrderDB1999.ndf”到这个文件组中。

      使用上面的命令再创建三个文件组2000,2001和2002,每个文件组存储一年的销售数据。

      2)创建分区函数

      分区函数是定义分界点的一个对象,使用下面的命令创建分区函数:

      CREATE PARTITION FUNCTION FNOrderDateRange (DateTime) AS

      RANGE LEFT FOR VALUES ('19991231', '20001231', '20011231')

      上面的分区函数指定:

      DateTime<=1999/12/31的记录进入第一个分区;

      DateTime > 1999/12/31 且 <= 2000/12/31的记录进入第二个分区;

      DateTime > 2000/12/31 且 <= 2001/12/31的记录进入第三个分区;

      DateTime > 2001/12/31的记录进入第四个分区。

      RANGE LEFT指定应该进入左边分区的边界值,例如小于或等于1999/12/31的值都应该进入第一个分区,下一个值就应该进入第二个分区了。如果使用RANGE RIGHT,边界值以及大于边界值的值都应该进入右边的分区,因此在这个例子中,边界值2000/12/31就应该进入第二个分区,小于这个边界值的值就应该进入第一个分区。

      3)创建分区方案

      通过分区方案在表/索引的分区和存储它们的文件组之间建立映射关系。创建分区方案的命令如下:

      CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION FNOrderDateRange

      TO ([1999], [2000], [2001], [2002])

      在上面的命令中,我们指定了:

      第一个分区应该进入1999文件组;

      第二个分区就进入2000文件组;

      第三个分区进入2001文件组;

      第四个分区进入2002文件组。

      4)在表上应用分区

      至此,我们定义了必要的分区原则,现在需要做的就是给表分区了。首先使用DROP INDEX命令删除表上现有的聚集索引,通常主键上有聚集索引,如果是删除主键上的索引,还可以通过DROP CONSTRAINT删除主键来间接删除主键上的索引,如下面的命令删除PK_Orders主键:

      ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

      在分区方案上重新创建聚集索引,命令如下:

      CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderDate) ON

      OrderDatePScheme (OrderDate)

      假设OrderDate列的数据在表中是唯一的,表将基于分区方案OrderDatePScheme被分区,最终被分成四个小的部分,存放在四个文件组中。如果你对如何分区还有不清楚的地方,建议你去看看微软的官方文章“SQL Server 2005中的分区表和索引”(地址:

    第十一步:使用TSQL模板更好地管理DBMS对象(额外的一步)

      为了更好地管理DBMS对象(存储过程,函数,视图,触发器等),需要遵循一致的结构,但由于某些原因(主要是时间限制),我们未能维护一个一致的结构,因此后来遇到性能问题或其它原因需要重新调试这些代码时,那感觉就像是做噩梦。

      为了帮助大家更好地管理DBMS对象,我创建了一些TSQL模板,利用这些模板你可以快速地开发出结构一致的DBMS对象。

      如果你的团队有人专门负责检查团队成员编写的TSQL代码,在这些模板中专门有一个“审查”段落用来描写审查意见。

      我提交几个常见的DBMS对象模板,它们是:

       Template_StoredProcedure.txt:存储过程模板()

       Template_View.txt:视图模板()

       Template_Trigger.txt:触发器模板()

       Template_ScalarFunction.txt:标量函数模板()

       emplate_TableValuedFunction.txt:表值函数模板()

      1)如何创建模板?

       首先下载前面给出的模板代码,然打开SQL Server管理控制台,点击“查看”*“模板浏览器”;

       点击“存储过程”节点,点击右键,在弹出的菜单中选择“新建”*“模板”,为模板取一个易懂的名字;

       在新创建的模板上点击右键,选择“编辑”,在弹出的窗口中输入身份验证信息,点击“连接”;

       连接成功后,在编辑器中打开下载的Template_StoredProcedure.txt,拷贝文件中的内容粘贴到新建的模板中,然后点击“保存”。

      上面是创建一个存储过程模板的过程,创建其它DBMS对象过程类似。

      2)如何使用模板?

      创建好模板后,下面就演示如何使用模板了。

       首先在模板浏览器中,双击刚刚创建的存储过程模板,弹出身份验证对话框,输入对应的身份信息,点击“连接”;

       连接成功后,模板将会在编辑器中打开,变量将会赋上适当的值;

       按Ctrl Shift M为模板指定值,如下图所示;

    新葡亰496net 6

      图 1 为模板参数指定值

       点击“OK”,然后在SQL Server管理控制台中选择目标数据库,然后点击“执行”按钮;

      如果一切顺利,存储过程就创建成功了。你可以根据上面的步骤创建其它DBMS对象。

      小结

      优化讲究的是一种“心态”,在优化数据库性能时,首先要相信性能问题总是可以解决的,然后就是结合经验和最佳实践努力进行优化,最重要的是要尽量预防性能问题的发生,在开发和部署期间,要利用一切可利用的技术和经验进行提前评估,千万不要等问题出现了才去想办法解决,在开发期间多花一个小时实施最佳实践,最后可能会给你节约上百小时的故障诊断和排除时间,要学会聪明地工作,而不是辛苦地工作!

    本文由新葡亰496net发布于网络数据库,转载请注明出处:二零零七中的文件和文件组,数据库优化施行

    关键词:

上一篇:新葡亰496net:死锁监控,扩展事件

下一篇:没有了