我看ms sql server 2000 (1) - 开头

lfree

set statistics io on
set implicit_transactions on
Declare @vid  numeric

select @vid=1
select * from t where id= @vid

如图(S16):

注意看细节,numeric比int更高的数据类型,convert发生在那一边!发生在convert(id),这个也许可以用来解析14楼的如图问题.
因为当时过程是加密的,现在我使用的版本没有打补丁,如果当时解密后问题就消失!

如果那位打了补丁测试看看,也许看到的执行计划会不一样了.

lfree

在oracle下,许多人都知道高水位标志.

建立一张表
create table test select * from dba_objects ;
select * from test ;
delete from test ;
commit;
select * from test ;

第2次执行的select * from test ;执行计划的全表扫描,发现逻辑读并没有降低!
要降低必须先truncate,move table,shrink space等操作才能降低高水位标志.

再来看看ms sql server,建立测试表orderstest并且聚集索引orderid:
select * into orderstest from orders
commit ;

还是以orderstest表(聚集索引建立orderid上):
set statistics io on
set implicit_transactions on
select * from orderstest
select min(orderid),max(orderid) from orderstest
或者orderid 的最大与最小值.
min=10248 max=11077

再执行
set statistics io on
set implicit_transactions on
select * from orderstest
delete from orderstest where orderid not in (10248,11077)
select * from orderstest
delete from orderstest where orderid =11077
select * from orderstest
rollback


看看IO 的统计:
(所影响的行数为 830 行）
表 'orderstest'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。
--（所影响的行数为 828 行）
--表 'orderstest'。扫描计数 2，逻辑读 3205 次，物理读 0 次，预读 0 次。
（所影响的行数为 2 行）
表 'orderstest'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。
--（所影响的行数为 1 行）
--表 'orderstest'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
（所影响的行数为 1 行）
表 'orderstest'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。

可以发现ms sql server与oracle一样存在高水位的问题.除非重新组织表.

[ 本帖最后由 lfree 于 2009-6-15 11:38 编辑 ]

lfree

我曾经就这个问题问过一个ms sql server的程序员,我仅仅记得他告诉我,ms sql server下你不需要优化,现在想想不知者无畏! 做一个例子测试看看,举一个极端的例子:
create table t1 ( id int ,a varchar(4),b varchar(2000))
commit ;

[注意:我使用char类型,好象即使是NULL,也占用空间]

每个字段分别建立索引,名字为pk_t1_id,ix_t1_a,其中聚集索引字段id.ix_t1_a的索引字段为a.

set statistics io off
set implicit_transactions on
declare @vid int
Select @vid = 1
While @vid <=16
Begin
        insert into t1 values(@vid , convert(varchar(4),@vid) , NULL)
        Select @vid = @vid + 1
end       
set statistics io on
select * from t1
commit


IO统计:
表 't1'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
可以发现数据在一个块里面.

set statistics io on
set implicit_transactions on
select * from t1 where a='5'
IO统计:
表 't1'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。


set statistics io off
set implicit_transactions on
declare @vid int
Select @vid = 1
While @vid <=8
Begin
        set statistics io off
        update t1 set b='1'+space(1998)+'1' where id = @vid *2
        commit
    Select @vid = @vid + 1
        set statistics io on
        select * from t1
        set statistics io off
end       
set statistics io on
select * from t1
commit


IO统计:
表 't1'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 4 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 4 次，物理读 0 次，预读 0 次。

表 't1'。扫描计数 1，逻辑读 4 次，物理读 0 次，预读 0 次。
可以发现T1表变化了.


set statistics io off
set implicit_transactions on
declare @vid int
Select @vid = 1
While @vid <=8
Begin
        set statistics io off
        update t1 set b='1'+space(1998)+'1' where id = @vid *2 -1
        commit
    Select @vid = @vid + 1
        set statistics io on
        select * from t1
        set statistics io off
end       
set statistics io on
select * from t1
commit


表 't1'。扫描计数 1，逻辑读 5 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 5 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 5 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 5 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 6 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 6 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 6 次，物理读 0 次，预读 0 次。
表 't1'。扫描计数 1，逻辑读 6 次，物理读 0 次，预读 0 次。

表 't1'。扫描计数 1，逻辑读 6 次，物理读 0 次，预读 0 次。

可以发现,合理的设置还是很重要的,实际上这个与oracle的pctfree一样,必须可以的设置.

再次查询,发现以下逻辑读是否变化,发现没有.

set statistics io on
set implicit_transactions on
select * from t1 where a='5'
IO统计:
表 't1'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。

我的猜测如果块分裂很厉害,或许逻辑读 会有变化,这个主要问题还是集中在非聚集索引保存聚集索引键值外+还包括什么?
也许最好的方法就是dump 块.

lfree

我看ms sql server 2000 (20)- 锁转换与提升.

http://www.diybl.com/course/7_databases/sybase/2007616/60886.html

锁的力度
SQL Server有两级锁：页锁和表锁。通常页锁比表锁的限制更少（或更小）。页锁对本页的所有行进行锁定，而表锁则锁定整个表。
为了减小用户间的数据争用和改进并发性，SQL Server试图尽可能地使用页锁。
当SQL Server决定一个语句将访问整个表或表的大多数页时，它用表锁来提供更有效的锁定。锁定策略直接受查询方案约束，如果
update或delete语句没有可用的索引，它就执行表扫描或请求一个表锁定。如果update或delete语句使用了索引，它就通过请求页
锁来开始，如果影响到大多数行，它就要请求表锁。一旦一个语句积累的页锁超过锁提升阈值，SQL Server就设法给该对象分配一
个表锁。如果成功了，页锁就不再必要了，因此被释放。表锁也在页层提供避免锁冲突的方法。对于有些命令SQL Server自动使用表锁。

以上这节一篇<Sybase数据库死锁对策>的文章(ms sql server 应该也差不多),按照这篇文章的介绍,如果update或者delete没有索引可用,
它就执行表扫描或请求一个表锁定,结果就是一个锁转换与提升.

检查表orders,发现shipcity没有索引.

set statistics io on
set implicit_transactions on
select count(*),shipcity from orders group by shipcity  order by 1
34 Rio de Janeiro
33 London
....
2  Caracas


打开一个窗口,执行如下:
set statistics io on
set implicit_transactions on
select * from orders  where shipcity ='Caracas'
update orders set shipvia=1 where shipcity not in ('Caracas')
--rollback


再打开一个窗口,执行如下:
set statistics io on
set implicit_transactions on
select * from orders where shipcity ='Caracas'

系统挂起! 因为前面的操作系统lock了页.可以从企业管理器里面查看.

查询如下语句是可以通过的.
select * from orders where orderid=10268 and shipcity ='Caracas'

感觉ms sql server实现锁的机制存在问题!

lfree

34楼的例子有点问题,不知道如何实现表锁功能.

lfree

我看ms sql server 2000- 总结

给出一个链接作为总结:
http://www.yafla.com/papers/SQL_ ... ql_server_pt2_1.htm
摘录上面的最后一段:
Summary

    * Keep your rows as small as possible to maximize real data density.
    * All tables should have a clustered index except in rare exception situations. Normally this will be a single field primary key.
    * Small clustered indexes keep non-clustered indexes small, increasing real data density.
    * Clustered indexes help make other indexes covering indexes.
    * Queries serviced by covering indexes are extremely efficient.
    * Avoid hiding criteria fields in functions – indexes will not be used for them.
    * Consider indexed computed columns where appropriate
    * If you shelled out the cash for Enterprise Edition, seriously evaluate how indexed views fit in your solutions
    * Index, index, index! Only in extremely rare cases are the additional update and insertion costs associated with maintaining indexes heavier than the benefits.
    * Understand execution plans. Evaluate them regularly.

我的看法:
ms sql server下建立选择聚集索引很重要,选择聚集索引字段多或者字段长度很大,聚集索引变大的同时,非聚集索引也跟者变大.我个人的感觉就是如果你经常执行
范围查询多某个表(比如某个日期范围),或者一次查询返回较多的,可以在该字段上选择聚集.同时要兼顾非聚集索引的大小,同时非聚集索引的选择性最好要好一些,
不然bookmark lookup很很大,效率也不高.个人认为ms sql server作为第3方做优化远比oracle困难的多.

至于其它,我不多说,我个人的观点ms sql server要超越oracle难度很大. 还可以写很多,还是不写了.

PromisingChina

DING

lfree

我看ms sql server 2000 (21)- char 与 NULL ?

建立堆表t3
create table t3 ( id int ,a char(2000))
commit ;

insert into t3 values(1,NULL)
insert into t3 values(2,NULL)
insert into t3 values(3,NULL)
commit ;

set statistics io on
set implicit_transactions on
select * from t3 ;

insert into t3 values(3,NULL)
commit ;
select * from t3 ;

IO 统计结果:

（所影响的行数为 3 行）
表 't3'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。
（所影响的行数为 4 行）
表 't3'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。
可以发现一个插入第4条记录后,逻辑读变成了2次.
从这里可以说明,ms下,char类型好象先占好位置的即使是NULL.

从我个人考虑,在数据库类型里面最好不要使用char类型的变量,即使是正好填满的类型.

youthspace

顶    楼主加油

ccwlm741212

这个我曾经在ms sql 板块做过优化的详细的描述