t-sql 更新大批量数据

需求:有两张很大的表,写了一个存储过程处理(加工数据然后插入到一张新表中),因为一次性处理数据量很大,会导致tempdb等资源被耗尽,为了解决这个问题,每次输入两个参数,每次根据两个id的范围处理程序
1、建一张日志表

CREATE TABLE [dbo].[data_log](
	[start_time] [datetime] NULL DEFAULT (getdate()),
	[min_id] [int] NULL,
	[max_id] [int] NULL,
	[end_time] [datetime] NULL DEFAULT (getdate()
) 

2、编写处理过程

CREATE PROCEDURE [dbo].[insert_more](@min_data int,@max_data int)
as
begin
--开始处理
insert into data_log values(getdate(),@min_data,@max_data,getdate())
--处理程序
INSERT INTO test_1 
 SELECT t1.name,
           t2.name,
           t1.id
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id
    AND t1.id<=@max_data
    AND t1.id>@min_data
--结束处理    
update data_log set end_time=getdate() where min_id=@min_data and max_id=@max_data
end

3、生成批量执行该存储过程语句

CREATE PROCEDURE [dbo].[get_list](@min_id int,@max_id int,@mode int)
AS
declare @i int
declare @max_m int
begin
set @i=0
while @i<=(@max_id-@min_id)/@mode
begin
set @max_m=@min_id+(@i+1)*@mode
if @max_m>@max_id  
  set	@max_m=@max_id
print 'EXEC [dbo].[insert_more] @min_data = '+CAST(@min_id+@i*@mode as VARCHAR(50))+',@max_data = '+CAST(@max_m as VARCHAR(50))+';'
set @i=@i+1
END
end

利用3的过程,输入最小id,最大id,取值间隔,生成需要执行2过程的sql语句,执行这些语句完成操作,通过日志监控操作情况

创建sql server分区表

1、创建数据库

USE Master 
GO
CREATE DATABASE Test_Partitioning
ON PRIMARY
(NAME='Partitioning_1',
FILENAME=
'E:\database\partitions\Partitioning_1.mdf',
SIZE=4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'Partitioning_2',
FILENAME =
'E:\database\partitions\Partitioning_2.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'Partitioning_3',
FILENAME =
'E:\database\partitions\Partitioning_3.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 )
GO

2、创建分区函数

Use test_Partitioning 
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')
GO

说明:
RIGHT:表示”=”在右边
LEFT:表示”=”在左边

3、创建分区方案

Use test_Partitioning 
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

4、使用分区创建表

Use test_Partitioning 
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

5、验证SQL语句
5.1)确定文件组的数量和数据库数据文件的数量

Use test_Partitioning 
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO 
-- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

5.2)验证分区表上的数据分布

Use test_Partitioning 
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO

sql server中查看对象

1、查询所有数据库
select * from sys.databases;

2、查询对象
select * from sysobjects;

3、复制表结构和数据
SELECT * INTO t_1 FROM master.dbo.spt_monitor;

4、查询视图
SELECT * FROM sys.views;

5、查询列
select * from sys.columns;

6、查询索引
select * from sys.indexes;

由于文件组 ‘DEFAULT’ 中的磁盘空间不足,无法为数据库 ‘TEMPDB’ 分配新页

今天接到一个朋友的问题,sql server报“由于文件组 ‘DEFAULT’ 中的磁盘空间不足,无法为数据库 ‘TEMPDB’ 分配新页”,按照错误提示应该是tempdb空间不足,让其查看tempdb对应数据库文件大小为不到4g,查看其对应文件为自增长,硬盘空间还有40g多点,那应该没有问题。后来她说该文件所处位置的盘是fat32的,那问题确定是fat32最大支持文件大小是4g。解决办法:
1、让tempdb中的文件恢复默认值,设置其最大值
1)重启sql server服务,tempdb一般会自动变为初始化大小
2)如果1)失败,使用下面语句
dbcc shrinkfile (tempdev, 10)
dbcc shrinkfile (templog, 10)

2)然后设置tempdev和templog的最大大小为4000m

2、添加数据文件和日志文件,并设置其最大值为4000m

3、修改程序,从根源上解决问题
分析产生该问题原因
询问朋友后,原来是她对一个6kw的表和1kw的表进行关联,然后取得需要的数据插入到另一张表中,因为涉及的数据量非常大,所有导致tempdb数据库飞速增大,超过系统文件大小限制。导致文章开始的错误,光添加文件,对于这么大数据量的程序来说还是不能解决问题。应该在程序上去优化,而不是靠添加tempdb中文件的大小和数量来解决问题。
添加程序的提交次数,而不是所有数据一次提交,放在一个事物中。
我根据程序需求模拟的写了两种处理过程的方法
模拟环境:

--创建模拟表
SELECT id,name INTO t_1 FROM sys.sysobjects;
SELECT object_id id,name INTO t_2 FROM sys.columns;
--创建跟踪表
CREATE TABLE t_s (id int DEFAULT 0,date datetime DEFAULT getdate());
INSERT INTO t_s  VALUES(0,getdate());
--创建需要插入数据表
    SELECT t1.name n1,
           t2.name n2,
           t1.id
           INTO test_1
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id 
    AND 1=2;

方法一:
特点:1、优点执行效率比较高,无需排序和插入跟踪表
2、缺点:不能查看程序执行进度,如果异常终止,必须重新执行

create PROCEDURE [dbo].[INSERT_d_1]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,
           t2.name,
           t1.id
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id
    ORDER BY
           t1.id
open c1                       
fetch next from c1 into @name1,@name2,@id 
WHILE @@fetch_status=0
BEGIN
	INSERT INTO test_1 VALUES(@name1,@name2,@id)
    FETCH next FROM c1 INTO @name1,@name2,@id
END
CLOSE c1                  
DEALLOCATE c1

方法二:
特点:1、可以跟踪程序的执行进度查询t_s表,如果异常终止可以写其他程序继续
2、程序使用二重游标,执行效率比较低

create PROCEDURE [dbo].[INSERT_d_2]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,          
           t1.id
    FROM   t_1 t1
    ORDER BY t1.id

open c1                       
fetch next from c1 into @name1,@id 
WHILE @@fetch_status=0
BEGIN
	--二重循环
	DECLARE c2 CURSOR FOR 
	SELECT t2.name FROM t_2 t2 WHERE t2.id=@id
	OPEN c2
	FETCH next FROM c2 INTO @name2
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO test_1 VALUES(@name1,@name2,@id)
		UPDATE t_s SET id=@id,date=getdate()
		FETCH next FROM c2 INTO @name2
	END
	CLOSE c2
	DEALLOCATE c2
    FETCH next FROM c1 INTO @name1,@id
END
CLOSE c1                  
DEALLOCATE c1

sql server 2005镜像配置

1、服务器ip地址和数据库版本
主机:192.168.1.1
备机:192.168.1.110
数据库:sql server 2005 sp4

2、主备实例互通
1)创建证书

--主机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_A_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

--备机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_B_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

2)创建连接的端点

--主机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_A_cert,encryption=supported,role=all);

--备机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_B_cert,encryption=supported,role=all);

3)备份证书以备建立互联

--主机执行
backup certificate HOST_A_cert to file='E:\database\key\HOST_A_cert.cer';
--备机执行
backup certificate HOST_B_cert to file='C:\database\mssql\key\HOST_B_cert.cer';

4)互换备份证书
拷贝证书文件到对象文件夹中

5)添加登录名、用户

--主机执行
create login HOST_B_login with password='20091125';
create user HOST_B_user for login HOST_B_login;
create certificate HOST_B_cert authorization HOST_B_user from file='C:\database\mssql\key\HOST_B_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_B_login];

--备机执行
create login HOST_A_login with password='20091125';
create user HOST_A_user for login HOST_A_login;
create certificate HOST_A_cert authorization HOST_A_user from file='E:\database\key\HOST_A_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_A_login];

3、建立镜像关系
1)备份还原数据库

/*尝试从刚刚使用的全备文件进行还原,在还原数据的时候需要使用”WITH NO RECOVERY“选项。*/
--主机执行
backup database test to disk='D:\sqlbackup\test091124full.bak'
backup log test to disk='D:\sqlbackup\test091124log.bak'

--备机执行
restore database acmr_10jqka from disk='D:\sqlbackup\test091124full.bak' with replace,norecovery
restore log acmr_10jqka from disk='D:\dbbackup\118_sql\acmr_10jqka091125log.bak' with norecovery

2)建立镜像

/*注意:先备机,后主机*/
--备机执行
alter database acmr_10jqka set partner='TCP://192.168.1.1:5022';
--主机执行
alter database test set partner='TCP://192.168.1.110:5022';

3)事务安全性设置

/*默认情况下,事务安全级别的设置为FULL,即“同步运行模式”,而且,SQL SERVER 2005标准版只支持同步模式。*/
/*如果关闭事务安全级别FULL模式,则可以切换到异步运行模式,该模式可使性能达到最佳。*/
--主机执行
use master
go
alter database test set partner safety full;   --事务安全,同步模式
alter database test set partner safety off;    --事务不安全,异步模式

4)切换

--主机执行下面命令后,即切换为备机角色
use master
go
alter database test set partner failover;
/*同理,也可以在备机(主体数据库)上执行上面命令,之后切换为备机(备数据库)*/