ISNULL(ISNULL(sum(case   when   

方法1,直接修改Partition Function,通过拆分末端Partition来增加分区

    在完整恢复模式或大容量日志恢复模式下,如果自上一次备份后生成检查点,则在日志备份后进行截断(除非是仅复制日志备份)。

 (SELECT sum(CASE WHEN isnullable=0 then 1 end) FROM syscolumns 

ALTER PARTITION FUNCTION partition_function_name()
{ 
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value ) 
} [ ; ]

   

  from   syscolumns   where   id=object_id( ‘EmpInfo’)

在关系型 DB中,分区表经常使用DateKey(int 数据类型)作为Partition
Column,每个月的数据填充到同一个Partition中,由于在Fore-End呈现的报表大多数是基于Month的查询,按照Month分区的设计能够提高查询性能,但是,如果,前任DBA没有创建Schedule来维护Partition
Function,不能增加新的Partition,所有新增加的数据都会插入到最后一个Partition中,导致最后一个Partition填充的数据量十分大,降低了分区表的查询性能。

事务日志有啥功能呢?关于事务日志的功能,详细具体内容可以参考官方文档事务日志 (SQL
Server),里面已经详细介绍了事务日志的几个功能,在此不做展开。

 WHERE id=object_id(‘EmpInfo’))

3,在执行Patition Split 操作时,必须存在一个FileGroup被指定为Next
Used,否则,Split 操作失败

 

(

CREATE PARTITION FUNCTION [funcPartition_DateKey](int) 
AS RANGE RIGHT 
FOR VALUES (20100701, 20100801, <.....> , 20150501, 20150601)
GO

CREATE PARTITION SCHEME [schePartition_DataKey] 
AS PARTITION [funcPartition_DateKey] 
TO ([PRIMARY], <....>, [PRIMARY])
GO

 

SELECT 

If you create all the partitions in the
same filegroup, that filegroup is initially assigned to be the NEXT USED
filegroup automatically. However, after a split operation is performed,
there is no longer a designated NEXT USED filegroup. You must explicitly
assign the filegroup to be the NEXT USED filegroup by using ALTER
PARITION SCHEME or a subsequent split operation will fail.

GO

)as ‘空字段总数’

ALTER PARTITION SCHEME
(Transact-SQL).aspx)

 

–空字段总数

If [PRIMARY] is specified, the
partition is stored on the primary filegroup. If ALL is specified, only
one file_group_name can be specified. Partitions are assigned to
filegroups, starting with partition 1, in the order in which the
filegroups are listed in [,*…n]. The same file_group_name
can be specified more than one time in [,
…n*].

 

 (SELECT COUNT(*) FROM syscolumns WHERE id=object_id(‘表名’))-

Script2,逐个更新Table的Patition Schema

 

select   count(*)   ‘总字段数’,

Script1,创建新的Partition设计

事务日志支持以下操作:

   isnullable=0   then   1  end),null),null) as ‘非空字段数’  

--rebuild table
create unique clustered index [PK__SchemaName_TableName_KeyColumn]
on SchemaName.TableName([KeyColumn],[CreatedDateKey])
with(data_compression=page,drop_existing=on)
on [schePartition_new_DataKey]([CreatedDateKey]);

--rebuild columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_ColumnStore_SchemaName_TableName] 
ON [SchemaName].[TableName]
(
    column list....
)

 

参考doc:

上面关于事务日志的虚拟日志循环覆盖使用是否有点眼熟的感觉,这个跟ORACLE下REDO
LOG的循环覆盖使用的理念是一模一样的。只不过是不同的概念和不同的实现方式。

三,在同一个文件组中创建分区

 

1,创建Patition Schema时,使用 ALL
关键字指定只能指定一个FileGroup,所有的Partition
都创建在同一个FileGroup上;在Patition
Schema创建成功之后,默认会将该FileGroup标记为Next Used

澳门微尼斯人手机版 1

ALL
Specifies that all partitions map to the filegroup provided in
file_group_name, or to the primary filegroup if [PRIMARY]
is specified. If ALL is specified, only one file_group_name can be
specified.

 

 

 

Rebuilding Existing Partitioned Tables to a New
Partition Scheme

DELETE
FROM dbo.TEST WHERE ID=101;

一,最佳实践(Best
Practices )

    
但WAL不仅仅是保证了原子性和持久性。还会提高性能.

显式将一个FileGroup 指定为 Next Used

 

创建新的Partition function 和 Partition Schema,逐个Table修改其Partition
Schema,这个方法(Workaround),虽然实现过程比较繁琐,但是对系统性能的副作用最小,将影响控制在当前操作的Target
Table。

备份事务日志后,你会发现FileId=3的日志文件对应的虚拟日志(VLF)的Status变为了0,那么此时就可以移除事务日志文件了。

如果FileGroup被指定为Next
Used,意味着分区表的下一个分区将会创建在该FileGroup上。在创建Patition
Schema时,指定
ALL关键字,不仅指定将表的所有分区都创建在同一个FileGroup上,而且,还将该FileGroup指定为Next
Used。

 

微软建议,避免对已填充的分区执行split或merge操作。在分区表的两端都保持空的分区(Empty
Partition),第一个分区和最后一个分区是Empty
Partition。通过对尾端的Empty
Partition进行Split操作,就能在尾端增加分区,并且不会产生数据移动;当将数据从前端的第二个分区中归档后,第一个分区和第二个分区都是Empty
Partition,Empty Partition进行Merge操作,不会产生数据的移动。

 

2,在Patition schema中,只有一个FileGroup会被指定(Mark)为Next Used

 

使用Alter Partition Function
命令执行拆分或合并分区的操作,每次操作,只能拆分一个,或合并一个分区:

本文是对SQL
Server事务日志的总结,文章有一些内容和知识来源于官方文档或一些技术博客,本文对引用部分的出处都有标注。

Always keep empty partitions at both ends
of the partition range to guarantee that the partition split (before
loading new data) and partition merge (after unloading old data) do not
incur any data movement. Avoid splitting or merging populated
partitions. This can be extremely inefficient, as this may cause as much
as four times more log generation, and may also cause severe
locking.

 

在一个文件组中创建表的所有分区,每个分区在物理上都是独立的存储对象,只不过这些独立的存储对象位于同一个FileGroup。

1:
将当前数据库脱机;

因此,受此Best
Practices的影响,DB开发人员在创建分区表时,一般的做法是只创建特定数量的分区,并且只保持两端是Empty
Partition,例如:

 

If ALL is specified, the
sole file_group_name maintains its NEXT USED property for
this partition_function_name. The NEXT USED filegroup will receive
an additional partition if one is created in an ALTER PARTITION FUNCTION
statement. 

 

--create Partition function
declare @DateKeyList varchar(max)
declare @DateKey int 
--set initial DateKey
set @DateKey=20140701;

while @DateKey<20200101
begin 
    set @DateKeyList=coalesce(@DateKeyList+','+cast(@DateKey as varchar(8)),cast(@DateKey as varchar(8)))
    --Increase iterator
    set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int);
end
--print DateKey List
--select @DateKeyList
declare @sql nvarchar(max)
set @sql=N'
CREATE PARTITION FUNCTION [funcPartition_new_DateKey](int) 
AS RANGE RIGHT 
FOR VALUES ('+@DateKeyList+N');'

EXEC sys.sp_executesql @sql
GO
--create partition schema
CREATE PARTITION SCHEME [schePartition_new_DataKey] 
AS PARTITION [funcPartition_new_DateKey] 
all TO ([PRIMARY]);
GO

 

ALTER PARTITION FUNCTION
(Transact-SQL).aspx)

 

由于很多 Big Table 使用相同的Partition
Schema进行分区,简单地从末端Partition为起点,逐个增加分区,在短时间内会产生海量的Disk
IO操作,对系统产生极大的影响,例如

 

ALTER PARTITION SCHEME partition_scheme_name 
NEXT USED [ filegroup_name ] [ ; ]

 

declare @DateKey int 
set @DateKey=20150701

while @DateKey<20200101
begin
  alter partition scheme [schePartition_DataKey]
  Next Used [primary];

  alter partition function [funcPartition_DateKey]()
  split range(@DateKey);

  set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int);
end 

 

如果,必须要对已填充的分区表,增加分区,要怎么做?

 

Appendix

澳门微尼斯人手机版 2

ALTER PARTITION FUNCTION repartitions any
tables and indexes that use the function in a single atomic operation.
However, this operation occurs offline, and depending on the extent of
repartitioning, may be resource-intensive.

 

在指定Next Used时,要注意:

 

 

USE TEST;

方法2,更改分区表的Partition Schema

如果是生产环境或者在上述备份事务日志后,对应日志文件的VLF的状态仍然为2,那么可以用收缩日志文件和备份事务日志循环处理,直至对应日志文件下所有的VLF状态全部为0,就可以删除事务日志文件。

由于Patition Table中,可能存在不止一个Index,在rebuild table时,使用
drop_existing=on 能够减少分区表上nonclustered
indexes的重建时间。

 

改进建议:在SQL Server 2012中,在一个Table或索引上,最多能够创建15
000个分区(In SQL Server 2012, a table or index can have a maximum of
15,000
partitions),未雨绸缪,我们可以按照月份,一次性创建360个分区,每月一个分区,每年12个分区,服务30年,一劳永逸,当然,也可以创建Schedule,前提是,你必须保证Schedule按时执行成功。

Write-Ahead
Logging的核心思想是:在数据写入到数据库之前,先写入到日志.

  • 在创建Partition
    Scheme时,指定ALL关键字,只会将一个FileGroup指定为Next Used;
  • 可以多次指定Next
    Used,最后一次指定的FileGroup是Next Used;
  • 一个FileGroup可以被多次指定为Next
    Used;即使该File Group已经用于存储(Hold)分区的数据; A filegroup
    that already holds partitions can be designated to hold additional
    partitions. 
  • 在完成一次Partition
    Split操作之后,之前的Next Used 已被使用,当前不存在被指定为Next
    Used的FileGroup,必须显式指定一个FileGroup作为新的Next
    Used,才能继续执行Partition Split操作;

 

 

·        
将标记检查点结束的记录写入日志文件。

澳门微尼斯人手机版 3

§  回滚与未完成的事务(如没有 COMMIT 或 ROLLBACK
日志记录的事务)相关联的所有修改。

GO

 

 

ALTER
DATABASE TEST REMOVE FILE TEST_log2

 

 

澳门微尼斯人手机版 4

 

 

 

 

BACKUP
LOG TEST TO DISK = ‘D:\SQL_LOG\澳门微尼斯人手机版,Test.Trn’

下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。

 

 

 

SELECT  f.database_id    AS database_id  ,

        d.name           AS database_name,

        f.type_desc      AS type_desc    ,

        COUNT(*)         AS log_count

FROM    sys.master_files  f

INNER  JOIN sys.databases d ON f.database_id = d.database_id

WHERE   type = 1

GROUP BY f.database_id ,

         f.type_desc,

         d.name

HAVING  COUNT(*) >= 2;

 

 

 

事务日志收缩

 

 

1:查看对应数据库事务日志的逻辑名称(name),后续操作需要用到。

 

USE
YourSQLDba;

 

GO

 

 

·        
已经使用 ALTER DATABASE
添加或删除了数据库文件。

·        
将为检查点记录的信息存储在检查点日志记录链内。

GO

SELECT
* FROM dbo.TEST;

 

 

 

 

CREATE
TABLE dbo.TEST(ID  INT);

什么是预写式日志呢?
其实其核心思想就是在变化的数据写入到数据库之前,将相关日志记录信息先写入到日志. SQL
Server的预写式日志(Write-Ahead
Logging)机制保证修改的描述(例如日志记录)会在数据本身修改写入数据文件前写入,会写入磁盘上的事务日志文件。它是SQL
Server保证事务持久性(Durability)的基本机制。一个日志记录会包含已提交事务或未提交事务的详细信息,在数据被事务修改的不同情况下,可能已经写入数据文件或还没来得及写入数据文件,这取决于检查点是否已发生。

 

NAME
= N’TEST_log’

 

 

2:
修改数据库的事务日志位置

 

 

Msg
5020, Level 16, State 1, Line 35

Log_reuse_wait 值

Log_reuse_wait_desc 值

说明

0

NOTHING

当前有一个或多个可重复使用的虚拟日志文件。

1

CHECKPOINT

自上次日志截断之后,尚未生成检查点,或者日志头尚未跨一个虚拟日志文件移动。 (所有恢复模式)

这是日志截断延迟的常见原因。 有关详细信息,请参阅数据库检查点 (SQL Server)。

2

LOG_BACKUP

在截断事务日志前,需要进行日志备份。 (仅限完整恢复模式或大容量日志恢复模式)

完成下一个日志备份后,一些日志空间可能变为可重复使用。

3

ACTIVE_BACKUP_OR_RESTORE

数据备份或还原正在进行(所有恢复模式)。

如果数据备份阻止了日志截断,则取消备份操作可能有助于解决备份直接导致的此问题。

4

ACTIVE_TRANSACTION

事务处于活动状态(所有恢复模式):

一个长时间运行的事务可能存在于日志备份的开头。 在这种情况下,可能需要进行另一个日志备份才能释放空间。 请注意,长时间运行的事务将阻止所有恢复模式下的日志截断,包括简单恢复模式,在该模式下事务日志一般在每个自动检查点截断。

延迟事务。 “延迟的事务 ”是有效的活动事务,因为某些资源不可用,其回滚受阻。 有关导致事务延迟的原因以及如何使它们摆脱延迟状态的信息,请参阅延迟的事务 (SQL Server)。

长时间运行的事务也可能会填满 tempdb 的事务日志。 Tempdb 由用户事务隐式用于内部对象,例如用于排序的工作表、用于哈希的工作文件、游标工作表,以及行版本控制。 即使用户事务只包括读取数据(SELECT 查询),也可能会以用户事务的名义创建和使用内部对象, 然后就会填充 tempdb 事务日志。

5

DATABASE_MIRRORING

数据库镜像暂停,或者在高性能模式下,镜像数据库明显滞后于主体数据库。 (仅限完整恢复模式)

有关详细信息,请参阅数据库镜像 (SQL Server)。

6

REPLICATION

在事务复制过程中,与发布相关的事务仍未传递到分发数据库。 (仅限完整恢复模式)

有关事务复制的信息,请参阅 SQL Server Replication。

7

DATABASE_SNAPSHOT_CREATION

正在创建数据库快照。 (所有恢复模式)

这是日志截断延迟的常见原因,通常也是主要原因。

8

LOG_SCAN

发生日志扫描。 (所有恢复模式)

这是日志截断延迟的常见原因,通常也是主要原因。

9

AVAILABILITY_REPLICA

可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 (完整恢复模式)

有关详细信息,请参阅:AlwaysOn 可用性组概述 (SQL Server)。

10

仅供内部使用

11

仅供内部使用

12

仅供内部使用

13

OLDEST_PAGE

如果将数据库配置为使用间接检查点,数据库中最早的页可能比检查点 LSN 早。 在这种情况下,最早的页可以延迟日志截断。 (所有恢复模式)

有关间接检查点的信息,请参阅数据库检查点 (SQL Server)。

14

OTHER_TRANSIENT

当前未使用此值。

SQL
Server数据库中的事务日志可以有一个或多个事务日志文件,但是即使有多个事务日志文件,也不能并行写入多个事务日志文件,数据库引擎还是会串行使用多个事务日志文件。也就是说大多数场景,多个事务日志文件其实并没有什意义,那么它存在的意义是什么呢?例如,当你当前磁盘告警,事务日志无法继续增长,你需要在其他磁盘新增一个事务日志文件,让数据库继续顺畅运行。个人觉得多个事务日志文件确实是一个很鸡肋的东西。Paul S.
Randal在“了解SQL
Server的日志记录和恢复”中明确指出:不要创建多个的日志文件,因为它不会导致性能增益。

·        
通过 SHUTDOWN
语句或通过停止
SQL Server (MSSQLSERVER) 服务停止了 SQL Server 实例。 任一操作都会在 SQL Server
实例的每个数据库中生成一个检查点。

 

 

 

 

 

 

     如果对应数据库的Log Space
Used(%)的值较小,那么就可以收缩事务日志。

注意,此时DBCC LOGINFO显示FileId=3的日志文件对应的虚拟日志(VLF)的Status为2,此时删除事务日志文件会提示文件无法删除,因为Status=2意味着VLF不能被覆盖和重用。

 

Status
= 2 means that VLF can’t be reused (overwritten) at this time and it
doesn’t necessarily mean that VLF is still active and writing
transactions to that VLF. As Jonathan already mentioned, it means that
the VLF is waiting for backup/REPL/Mirroring etc..

 

 

导致检查点的活动

·        
CheckPoint的开始LSN

2:
使用DBCC
SQLPERF查看事务日志空间使用情况统计信息:

 

参考资料:

UPDATE
dbo.TEST SET ID=101;

GO

MODIFY
FILE

 

浅谈SQL
Server中的事务日志(二)—-事务日志在修改数据时的角色
这篇博客有深入浅出的介绍(如下所示):

USE master;

GO

CREATE DATABASE [TEST]

 CONTAINMENT = NONE

 ON  PRIMARY 

( NAME = N'TEST', FILENAME = N'D:\SQL_DATA\TEST.mdf' , SIZE = 100MB , MAXSIZE = 40GB, FILEGROWTH = 64MB )

 LOG ON 

( NAME = N'TEST_log' , FILENAME = N'D:\SQL_LOG\TEST_LOG_1.ldf' , SIZE = 20MB , MAXSIZE = 40MB , FILEGROWTH = 10MB),

( NAME = N'TEST_log2', FILENAME = N'D:\SQL_LOG\TEST_LOG_2.ldf' , SIZE = 20MB , MAXSIZE = 20GB , FILEGROWTH = 10MB)

GO

 

BACKUP DATABASE [TEST] TO  DISK = N'D:\DB_BACKUP\Test.bak' 

        WITH NOFORMAT, NOINIT,  

        NAME = N'TEST-Full Database Backup',

        SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

GO

 

 

USE TEST;

GO

SELECT * INTO mytest FROM sys.objects;

GO

INSERT INTO mytest

SELECT * FROM mytest

GO 12

 

DBCC SQLPERF(LOGSPACE)

 

DBCC LOGINFO('TEST')

 

 

 

·        
尚未传递给分发数据库的最早的复制事务起点的 LSN.

 

 

 

 

 

 

GO

 

 

 

实际上,日志截断会由于多种原因发生延迟。 查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列,了解哪些因素(如果存在)阻止日志截断。 下表对这些列的值进行了说明:

 

 

 

 

 

 

 

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注