如何在SQLSERVER中快速有条件删除海量数据

最近有个朋友问我，他说他在SQLSERVER删除几百万到几千万数据是显的很慢，帮他分析了一下，提了一些以下意见，或许对很多人有用，再者也好长没写过BLOG了，一起探讨一下
如果你的硬盘空间小，并且不想设置数据库的日志为最小(因为希望其他正常的日志希望仍然记录)，而且对速度要求比较高，并清除所有的数据建议你用turncate table1,因为truncate 是DDL操作，不产生rollback，不写日志速度快一些，然后如果有自增的话，恢复到1开始，而delete会产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments，同时还要记录下G级别的日志 ；当然如果有条件删除比如where time<'2006-3-10' 怎么办，能不能不记录日志用delete，回答是不行的，SQL Server 引擎在设计上就会对 Delete 操作进行日志记录。至今没有办法强制制定某一些语句不记录到日志中，如果在执行 Delete Table1 where Time < '2006-3-10' 由于涉及的记录比较多,所以日志记录也相应很大(3-4G),如果可行,我建议用以下方式：
选出您所需要保留的记录到新的表。如果您使用 Full Recovery Mode
根据SELECT INTO的记录数,日志可能会比较大
Select * into Table2 From Table1 Where Time > = '2006-03-10'
然后直接Truncate Table1。无论何种恢复模式都不会进行日志记录
Truncate table Table1
最后对Table2进行改名为Table1
EC sp_rename 'Table2', 'Table1'
出处：BLOG:domino的专栏

有时候程序处理的数据量比较小时，四平八稳，一切安然无恙，但数据量一大，原先潜伏的问题就暴露无遗了。
原访问数据库的代码为：
1SqlConnection conn = new SqlConnection(strConn);
2conn.Open();
3SqlTransaction trans = conn.BeginTransaction();
4try
5{
6 CEngine.ExecuteNonQuery(trans,CommandType.Text,sql);
7 trans.Commit();
8}
9catch(SqlException ex)
10{
11 trans.Rollback();
12 ErrorCode = ex.Number;
13 Info = "数据操作失败：" ex.Message;
14}
15finally
16{
17 trans.Dispose();
18 conn.Close();
19}
20
21
22
运行时，一旦出现数据量过大或者处理时间较长，则系统会提示出错。错误提示为“SqlTransaction已经用完；它再也不能使用。”
开始时，我怀疑是跟内存有关。因为系统需要做好事务回滚的准备，每执行一条插入或修改的SQL，都要有一定的开销，数据量一大，恐怕就吃不消了。不过我查了一下SQL SERVER的资料，未见提到内存的问题。
后来想到，数据库连接SqlTransaction有个时间问题。默认是15秒。数据量大的时候，这个时间很可能就不够了。于是改为：
1SqlConnection conn = new SqlConnection(strConn);
2conn.Open();
3SqlTransaction trans = conn.BeginTransaction();
4try
5{
6 SqlCommand cmd = new SqlCommand();
7 cmd.CommandType = CommandType.Text;
8 //连接时限改为300秒
9 cmd.CommandTimeout = 300;
10 cmd.CommandText = sql;
11 cmd.Connection = conn;
12 cmd.Transaction = trans;
13 cmd.ExecuteNonQuery();
14 trans.Commit();
15}
16catch(SqlException ex)
17{
18 trans.Rollback();
19 ErrorCode = ex.Number;
20 Info = "数据操作失败：" ex.Message;
21}
22finally
23{
24 trans.Dispose();
25 conn.Close();
26}
修改后在测试，问题解决：）

　　当您怀疑计算机硬件是影响SQL Server运行性能的主要原因时，可以通过SQL Server Performance Monitor监视相应硬件的负载，以证实您的猜测并找出系统瓶颈。下文将介绍一些常用的分析对象及其参数。
　
　　Memory: Page Faults / sec
　　如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。
　　Process: Working Set
　　SQL Server的该参数应该非常接近分配给SQL Server的内存值。在SQL Server设定中，如果将"set working set size"置为0, 则Windows NT会决定SQL Server的工作集的大小。如果将"set working set size"置为1，则强制工作集大小为SQLServer的分配内存大小。一般情况下，最好不要改变"set working set size"的缺省值。
　　Process:%Processor Time
　　如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。
　　Processor:%Privileged Time
　　如果该参数值和"Physical Disk"参数值一直很高，表明I/O有问题。可考虑更换更快的硬盘系统。另外设置Tempdb in RAM，减低"max async IO"，"max lazy writer IO"等措施都会降低该值。
　　Processor:%User Time
　　表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。
　　Physical Disk:Avg.Disk Queue Length
　　该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。
　　注意:一个Raid Disk实际有多个磁盘。
　　SQLServer:Cache Hit Ratio
　　该值越高越好。如果持续低于80%，应考虑增加内存。
　注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI（美国国家标准协会）的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle, Sybase, Microsoft SQL Server, Access等都采用了SQL语言标准。虽然很多数据库都对SQL语句进行了再开发和扩展，但是包括Select, Insert, Update, Delete, Create, 以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作。下面，我们就来详细介绍一下SQL语言的基本知识。
数据库表格
　　一个典型的关系型数据库通常由一个或多个被称作表格的对象组成。数据库中的所有数据或信息都被保存在这些数据库表格中。数据库中的每一个表格都具有自己唯一的表格名称，都是由行和列组成，其中每一列包括了该列名称，数据类型，以及列的其它属性等信息，而行则具体包含某一列的记录或数据。以下，是一个名为天气的数据库表格的实例。
城市 最高气温 最低气温
北京 10 5
上海 15 8
天津 8 2
重庆 20 13
该表格中“城市”， “最高气温”和“最低气温”就是三个不同的列，而表格中的每一行则包含了具体的表格数据。
数据查询
　　在众多的SQL命令中，select语句应该算是使用最频繁的。Select语句主要被用来对数据库进行查询并返回符合用户查询标准的结果数据。Select语句的语法格式如下：
select column1 [, column2,etc] from tablename
[where condition];
（[] 表示可选项）
　　select语句中位于select关键词之后的列名用来决定那些列将作为查询结果返回。用户可以按照自己的需要选择任意列，还可以使用通配符“*”来设定返回表格中的所有列。
select语句中位于from关键词之后的表格名称用来决定将要进行查询操作的目标表格。
Select语句中的where可选从句用来规定哪些数据值或哪些行将被作为查询结果返回或显示。
在where条件从句中可以使用以下一些运算符来设定查询标准：
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于
　　除了上面所提到的运算符外，LIKE运算符在where条件从句中也非常重要。LIKE运算符的功能非常强大，通过使用LIKE运算符可以设定只选择与用户规定格式相同的记录。此外，我们还可以使用通配符“%”用来代替任何字符串。举例如下：
select firstname, lastname, city
from employee
where firstname LIKE 'E%’;
（注意，字符串必须被包含在单括号内）
上述SQL语句将会查询所有名称以E开头的姓名。或者，通过如下语句：
select * from employee
where firstname = 'May’；
查询所有名称为May的行。

我们知道在使用SQL语句编写查询时会用到SELECT语句。它的基本结构如下：
SELECT ... ... FROM ... ... WHERE ... ... ORDER BY ... ...
在使用WHERE条件子句时我们知道可以通过LIKE关键字进行模糊查询，而且我们也知道可以使用通配符实现这个。我们通常知道的通配符有下划线_和百分号%。其实我们还有其它的查询匹配可用，只是我们不经常使用而忽略了它们。被我们忽略的就是匹配特定范围[]和匹配特定范围之外的[^]两个。
下面大家先看看在Microsoft SQL Server中定义的通配符：
通配符 说明

_（下划线） 与任意单字符匹配

% 与包含一个或多个字符的字符串匹配

[ ] 与特定范围（例如，[a-f]）或特定集（例如，[abcdef]）中的任意单字符匹配。

[^] 与特定范围（例如，[^a-f]）或特定集（例如，[^abcdef]）之外的任意单字符匹配。

下面我们列出一些事例去说明这些通配符的使用。
•
WHERE FirstName LIKE '_im' 可以找到所有三个字母的、以 im 结尾的名字（例如，Jim、Tim）。
　

•
WHERE LastName LIKE '%stein' 可以找到姓以 stein 结尾的所有员工。
　

•
WHERE LastName LIKE '%stein%' 可以找到姓中任意位置包括 stein 的所有员工。
　

•
WHERE FirstName LIKE '[JT]im' 可以找到三个字母的、以 im 结尾并以 J 或 T 开始的名字（即仅有 Jim 和 Tim）

•
WHERE LastName LIKE 'm[^c]%' 可以找到以 m 开始的、后面的（第二个）字母不为 c 的所有姓。