运维小记

删除大量数据

在单个事务中删除大量的数据有几个缺点。DELETE语句被完整地记录到日志，操作过程中日志不能够截断，要求在事务日志中有足够的空间以完成整个事务。而且，如果该事务因某种原因被中途中断，这之前发生的所有操作都将被回滚，而这也会花费一些时间。最后，当删除许多行时，数据库可能会把被删除行上的单一锁提升为排它表锁，以阻止DELETE完成之前对目标表的读写操作（在Profiler中监视Lock Escalation事件可以很容易地验证选择的行数是否会导致锁提升）。 

日常删数代码为：

DELETE FROMf_cntr WHERE prov_branch_code = ?

可修改为：

WHILE 1 = 1
BEGIN
  DELETE TOP(5000) FROM f_cntr  WHEREprov_branch_code = ?
  IF @@rowcount < 5000 BREAK;
END
GO

以上讨论对更新操作同样适用。

找出未建索引的大表

在数据仓库中记录数大于1000万的事实表太正常了，记录数大于100万的维表也很多。在数据库建设过程中，由于各种原因可能没有在这些大表上创建索引，上线以后会带来很大的性能问题。可以通过以下语句查找出记录数大于100万的未建立非聚集索引的表。

select object_name(i.id),i.rows
from sysindexesi,sysobjectso
where i.id=o.idand o.type='U'
and  i.indid=0
and i.idnot in(selectid from sysindexes where indid<>0 andname not like '%_WA_Sys%')
and i.rows>1000000
order by i.rows desc

列名	数据类型	说明
id	int	索引所属表的 ID。
status	int	系统状态信息。 标识为仅供参考。不提供支持。不保证以后的兼容性。
first	binary(6)	指向第一页或根页的指针。 当 indid = 0 时不使用。 NULL = 当 indid > 1 时，对索引进行分区。 NULL = 当 indid 为 0 或 1 时，对表进行分区。
indid	smallint	索引 ID： 0 = 堆 1 = 聚集索引 > 1 = 非聚集索引
root	binary(6)	如果 indid >= 1，root 是指向根页的指针。 当 indid = 0 时不使用。 NULL = 当 indid > 1 时，对索引进行分区。 NULL = 当 indid 为 0 或 1 时，对表进行分区。
minlen	smallint	行的最小大小。
keycnt	smallint	键数。
groupid	smallint	在其上创建对象的文件组 ID。 NULL = 当 indid > 1 时，对索引进行分区。 NULL = 当 indid 为 0 或 1 时，对表进行分区。
dpages	int	如果 indid = 0 或 indid = 1，dpages 是已用数据页的计数。 如果 indid > 1，dpages 是已用索引页的计数。 0 = 当 indid > 1 时，对索引进行分区。 0 = 当 indid 为 0 或 1 时，对表进行分区。 如果发生行溢出，则不会得出准确的结果。
reserved	int	如果 indid = 0 或 indid = 1，reserved 是分配给所有索引和表数据的页计数。 如果 indid > 1，reserved 是分配给索引的页计数。 0 = 当 indid > 1 时，对索引进行分区。 0 = 当 indid 为 0 或 1 时，对表进行分区。 如果发生行溢出，则不会得出准确的结果。
used	int	如果 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。 如果 indid > 1，used 是用于索引的页计数。 0 = 当 indid > 1 时，对索引进行分区。 0 = 当 indid 为 0 或 1 时，对表进行分区。 如果发生行溢出，则不会得出准确的结果。
rowcnt	bigint	基于 indid = 0 和 indid = 1 的数据级行计数。 0 = 当 indid > 1 时，对索引进行分区。 0 = 当 indid 为 0 或 1 时，对表进行分区。
rowmodctr	int	对自上次更新表的统计信息后插入、删除或更新行的总数进行计数。 0 = 当 indid > 1 时，对索引进行分区。 0 = 当 indid 为 0 或 1 时，对表进行分区。 在 SQL Server 2005 及更高版本中，rowmodctr 与早期版本不完全兼容。 有关详细信息，请参阅“备注”。
reserved3	int	返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
reserved4	int	返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
xmaxlen	smallint	最大行大小
maxirow	smallint	最大非叶索引行大小。 在 SQL Server 2005 及更高版本中，maxirow 与早期版本不完全兼容。
OrigFillFactor	tinyint	创建索引时使用的初始填充因子值。 不保留该值；但如果需要重新创建索引但不记得当初使用的填充因子，则该值可能很有帮助。
StatVersion	tinyint	返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
reserved2	int	返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
FirstIAM	binary(6)	NULL = 索引已分区。 标识为仅供参考。不提供支持。不保证以后的兼容性。
impid	smallint	索引实现标志。 返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
lockflags	smallint	用于约束经过考虑的索引锁粒度。 例如，对于本质上是只读的查找表，可以将其设置为仅进行表级锁定以最大限度地降低锁定成本。
pgmodctr	int	返回 0。 标识为仅供参考。不提供支持。不保证以后的兼容性。
keys	varbinary(816)	组成索引键的列 ID 列表。 返回 NULL。 若要显示索引键列，请使用 sys.sysindexkeys。
name	sysname	索引或统计信息的名称。 当 indid = 0 时返回 NULL。 修改应用程序以查找 NULL 堆名。
statblob	image	统计信息二进制大型对象 (BLOB)。 返回 NULL。
maxlen	int	标识为仅供参考。不提供支持。不保证以后的兼容性。
rows	int	基于 indid = 0 和indid = 1 的数据级行计数，如果indid >1，则该值包含重复的计数。

小表（字典表）常驻内存

DBCC PINTABLE

将表标记为驻留，这表示 SQL Server不从内存中刷新表页。

语法

DBCCPINTABLE ( database_id , table_id )

参数

database_id

是要驻留的表的数据库标识 (ID) 号。若要确定该数据库 ID，请使用DB_ID 函数。

table_id

是要驻留的表的对象标识号。若要确定表 ID，请使用OBJECT_ID 函数。

注释

DBCCPINTABLE 不会导致将表读入到内存中。当表中的页由普通的 Transact-SQL 语句读入到高速缓存中时，这些页将标记为内存驻留页。当 SQL Server 需要空间以读入新页时，不会清空内存驻留页。SQL Server 仍然记录对页的更新，并且如有必要，将更新的页写回到磁盘。然而，在使用 DBCCUNPINTABLE 语句使该表不驻留之前，SQL Server 在高速缓存中一直保存可用页的复本。

DBCCPINTABLE 最适用于将小的、经常引用的表保存在内存中。将小表的页一次性读入到内存中，将来对其数据的所有引用都不需要从磁盘读入。

注意  DBCCPINTABLE 可以提供性能改进，但是使用时务必小心。如果驻留大表，则该表在开始时会使用一大部分高速缓存，而不为系统中的其它表保留足够的高速缓存。如果所驻留的表比高速缓存大，则该表会填满整个高速缓存。sysadmin固定服务器角色的某个成员必须关闭而后重新启动 SQL Server，然后使表不驻留。驻留太多的表和驻留比高速缓存大的表会产生同样的问题。

结果集

下面是结果集：

Warning:Pinning tables should be carefully considered. If a pinned table is larger, orgrows larger, than the available data cache, the server may need to berestarted and the table unpinned.

DBCCexecution completed. If DBCC printed error messages, contact your system administrator.

权限

DBCCPINTABLE 权限默认授予 sysadmin 固定服务器角色的成员且不可转让。

示例

下例驻留pubs 数据库中的 authors 表。

DECLARE @db_id int,@tbl_id int
USE pubs
SET @db_id= DB_ID('pubs')
SET @tbl_id= OBJECT_ID('pubs..authors')
DBCC PINTABLE(@db_id,@tbl_id)

TRUNCATE与DELETE

1. DELETE会完整滴记录到日志，而TRUNCATE按照最简单方式记录到日志；
2. TRUNCATE TABLE不会引发触发器记录（很可能引发增量层问题）；
3. TRUNCATE TABLE会将IDENTITY重置为种子，而DELETE并不会重置IDENTITY列；