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

husthxd

很有研究精神！

lfree

以orderid为聚集索引,增加计算列字段:

ALTER TABLE dbo.Orders ADD
            btbt  AS CONVERT(nvarchar(100),shipname+shipaddress+shipcity+shipregion+'1234567890123456789012345678901234567890' )
commit

SET STATISTICS IO ON

SELECT *  FROM orders(index(0))
SELECT orderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
        ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
  FROM orders(index(0))
SELECT btbt from orders

统计结果:
表 'Orders'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。
表 'Orders'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。
表 'Orders'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。

取消聚集索引,结果如下:

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


如图(S9):你可以发现计算列通过"compute scalar"获得,并不占用空间.


下面建立btbt的索引,恢复以orderid为聚集索引.


SET STATISTICS IO ON

select btbt from orders
select btbt from orders(index(btbt))
select btbt from orders where btbt ='laji'
commit

IO统计结果:

表 'Orders'。扫描计数 1，逻辑读 21 次，物理读 0 次，预读 0 次。
表 'Orders'。扫描计数 1，逻辑读 10 次，物理读 0 次，预读 0 次。
表 'Orders'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。

如图S10:可以发现使用第2条sql语句从btbt索引取出结果直接显示,第3条sql
从btbt索引获得'laji'的关键值,经过bookmark lookup,再次显示btbt的值,
是再次经过"compute scalar"获得的.

lfree

原帖由 husthxd 于 2009-6-12 13:55 发表

很有研究精神！

可惜没人看.

lfree

我看ms sql server 2000 (10) --- 存储过程优化

我开始优化市医保的项目是2004年的10月开始,到2005年3月底结束,主要工作集中在2004.11-2005.1之间.当时软件已经使用了2年半时间了.

在上项目不久,我就使用"事件查看器"发现存储过程执行有问题,当时执行时间大约2-3秒(有时候更长到5秒),逻辑读大约在2万-3万上下,为了这事,我还跟开发商提过,因为开发商存储过程是加密的,加上我自己不熟悉ms sql server以及别的工作,优化的事情一直没做.

2004年10月,我利用国庆放假这段时间,看了许多ms sql server优化方面的书,以及上网找一些优化ms sql server工具(主要原因是我是那种不熟悉命令的技术人员),最终我发现许多工具都不是免费的或者不合适,最终我放弃寻找.最后基本使用ms自带的工具.

当时我最需要的一个工具就是格式化sql的工具,因为通过"事件查看器"采集的sql都没有格式化,要看懂这些sql很费时间,本来我想通过toad for oracle来格式化,但是一些命令语法不一样,在里面格式化会报错!不过最终我通过vim的一些插件解决了这个问题.

回过头来讲存储过程,实际上那是我优化的重点,解密存储过程还是很简单的,网上有现成的工具,在测试环境很快获得存储过程的源码.解开后我大概看了一下,发现执行过程很复杂,存储过程又调用另外的存储过程,而且里面还有循环等,第一感觉执行这点时间很正常的.在oracle8i下我估计也许也要这个时间.

当时我做优化有几种选择,现在想想我选择的有些不对.
1.自己通读源码,这个太费时间,一开始就放弃,实际上自己也看了一些.
2.在生产系统解密存储过程,然后使用事件查看器收集有问题的sql.现在想想这个应该是最好的,因为当时我不敢乱动生产系统,也就是没有采用这个方式!
3.在查询分析器中看执行计划,这个也许我当时采用的!

没有想到这个才是恶梦的开始!在查询分析器中展开执行计划,我发现看到大部分的cost都是0%,我仅仅需要确定那些大于1%的sql语句就可以了(实际上有问题都是4%),我发现看扫描完整的存储过程,必须差不多从上班的8点到12点上下,而且如果鼠标不小心很容易错过一些重要细节.

我这个过程大约花了1个星期,里面一些简单的sql很容易优化,实际上存在问题仅仅4处,就是隐式转换的问题.
我必须修改几个存储过程,将对应的sql修改的谓词条件修改为convert(varchar(12), @xxx).

修改是很简单的,我在测试环境测试N次,先解密->修改存储语句->加密回去,为了保险,我特意叫前台停止业务10分钟.修改完成后,再次重复扫描存储过程的步骤(这个过程又花了一个星期),这回我遇到一个很奇怪的问题,在测试环境我没有看到任何一个大于4%的,而在生产环境可以看到两处大于4%的cost.

为了更好的比较,我决定解密生产系统的对应有问题的sql,结果解密后,问题竟然莫名奇妙的消失了,当时真的把我吓出冷汗!
现在想想我当时没有注意看细节(应该看看figure1里面的执行细节),我猜测我可能遇到一些BUG.如图S11


最后优化完成后,我发现ms sql server执行存储过程确实很快,上面的最后仅仅需要1XX-2XXms.至少在当时比我们的8i下执行的快.这个改变了我对ms sql server的一些看法!

lfree

我看ms sql server 2000 (11) --- 事务与一致性读取：

关于这个方面，ms sql server是很难处理的，可以看出ms sql server在这个问题方面，要求事务尽可能快的提交。

例子：
   在查询分析器打开implicit transaction，插入后不提交：
   set implicit_transactions on
   insert into customers ( customerid,companyname  ) values ('ALFKJ','ZZZ') ;

    打开另外的窗口，只要执行如下：

    set implicit_transactions on
    select * from customers ;
        或者
    select * from customers where customerid='ALFKJ';
        或者
        select * from customers where customerid like 'ALFK%' and companyname='Alfreds Futterkiste'


    系统就挂起，等待窗口1的提交或者回滚。执行如下是正常的：
    select * from customers where customerid='ALFKI';
   
    而oracle绝对不会存在这个问题的，因为oracle通过多版本，实现一致性读取。

    如果ms sql server要不阻塞，仅仅写成如下：
    select * from customers (nolock) where customerid='ALFKJ';

    这个是“可怕的事情”，因为事务可能并不提交！如果把这个用在与钱有关的系统，一定要处理好这些事务关系。这个在oracle下另外的会话不可能读取还没有提交的事务的，这个就是所谓的脏读。在sql server下，如果查询的条件包含未提交的记录符合条件，这个语句就要等待未提交的事务提交，才能正常执行，除非你使用nolock。

        
     在oracle下，写入不会阻塞读取，读取也不会阻塞写入的。而在ms sql server下很难解决这个问题，这个要求ms sql server 架构师要更好的设计数据结构以及执行sql。从根本讲我认为oracle的一致性读取是一个很优秀的设计.ms sql server的超越oracle,必须很好的实现一致性读取,解决好这个问题.实际上ms sql server大多数生产系统都会遇到问题(至少我使用的,特别是用户并发数量很多的系统.)

lfree

我看ms sql server 2000 (12) --- 事务与一致性读取：

如果要使用上面的语句
        select * from customers where customerid like 'ALFK%' and companyname='Alfreds Futterkiste'
在那样的情况下能正常执行.可行吗?

实际上我在生产系统中就遇到这个类似的问题,我们与中心上传数据的时候就遇到这个问题,一上传整个前台的收费业务全部挂起,无法进行正常的工作,当时在医保的人看来如果不能解决这个问题,我的优化实际上是失败的.

我仔细跟踪了上传的sql,我发现它把整个上传当作一个完整的事务,在当时我认为最好的方法就是修改程序,将事务分成若干的小事务,这样前台的工作能够正常进行.

这个问题我思考了很久,把执行的sql仔细看,我特地建立一个BT的索引(包含10个字段),结果当时导致一些执行计划发生变化,还没有执行上传,整个系统就hang住了.

很偶然的机会(纯粹是灵机一动)发现这样我改变一个表的聚集索引,整个问题就迎刃而解.

以上面的例子来说明:

如果建立聚集索引在companyname上真个问题就解决了.

PromisingChina

没都看完，几点探讨，仅供参考：
1）一般来说，通常数据库可能会有多级缓存，在读数据库时，第一遍可能有物理读，以后不应该有。同时这种测试通常要有大数据量，多线程测试才能找到瓶颈。同时，内存量，CPU数，数据分布，线程数，数据量，每条数据的字节数，读写比率都有关系。通常会有批处理程序，有多个客户端来加压，而且要有预热过程（5分钟）后，等到TPS（transaction per second)稳定后，才取样。最后根据个数据点，做出各种曲线，分析。
2）这种方式对于测试数据非常敏感，最好的方式，是复制历史数据，再排列组合。否者，有时系统上线后实测结果会完全不同。
3）这种Profile粒度的分析，更多用于对应用程序的某种算法，如MS SQL的存储过程调优时使用，主要是找到瓶颈。否则，系统其他的耗时和这种成本不是一个量级的。最大的成本实际上是网络的延迟，比如每一个SQL都要有身份认证，实际上在TCP/IP上要走几个来回，每个来回都是在TCP七层中通讯中好几层来回，所以成本远超过几个物理读，逻辑读。
4) 就软件算法而言，通常固定的Cost，都不是最大因素；该关心的是复杂度, 就是常说的O（fun(n)）, 如O（n^2), O(n*Log(n)), 等，不变的常数都会忽略不计。
5）其实，系统最危险的是应用的设计，如死锁;其次，最大的问题是程序开发员没有真正的用关系数据库（SET，数据集）的思维，比如用Loop、If Else、滥用temp table等等；最后读写比率(read\write ratio)需要严格关注，监控，如需要要读写分流等等。

从逻辑层面看，好的框架、数据库设计，在MS SQL和Oracle上应该是非常接近的效果。更多的差别是在易用性，易管理性上。这一点上看，每个好的DBA，无论是哪个数据库版本的，都是非常有价值，非常抢手的。这点在中国非常紧缺，因为各公司通常都是采取垂直开发模式，就是每个人负责一个功能，从界面到数据库都包了；最典型的就是PB模式，更没有专人负责数据库，因此通才总不能什么都精通，而每个公司所有开发人员也不可能都成为数据库高手。

PromisingChina

有好几年没具体写SQL，还真有点落伍了，几点建议，估计会有错，希望有一条两条对你有启发，就不要拿板砖砸我。不行我就只要换马甲了。


SELECT LEFT(pres_date, 6) as name, sum(cast(substring(anal_result, 1, 1) as int)) as pwjj, sum(cast(substring(anal_result, 2, 1) as int)) as xhzy,
       sum(cast(substring(anal_result, 4, 1) as int)) as cfyy, sum(cast(substring(anal_result, 5, 1) as int)) as kss, sum(cast(substring(anal_result, 7, 1) as int)) as qtwt,
       sum(cast(substring(anal_result, 8, 1) as int)) as yywt, sum(cast(substring(anal_result, 1, 1) as int)) +sum(cast(substring(anal_result, 2, 1) as int))
       +sum(cast(substring(anal_result, 4, 1) as int)) +sum(cast(substring(anal_result, 5, 1) as int)) +sum(cast(substring(anal_result, 7, 1) as int))
       +sum(cast(substring(anal_result, 8, 1) as int)) as count
  FROM t_pres_info
WHERE (LEFT(pres_date, 8)          >= '20090610'
   AND LEFT(pres_date, 8)           <  '20090612'
   AND len(rtrim(ltrim(pres_date))) =  14)
group by LEFT(pres_date, 6)
order by LEFT(pres_date, 6)


在这里，WHERE 中有任何计算都是非常费事的，而且千万条数据每个算一遍，又丢掉，太浪费了。
1）pres_date 是否该用日期，而不用字符串？比较日期数据，一个CPU运算，字符串至少要8次，还要加上LEFT。
2）非要用字符串，可否对原表Table做一个View，然后建一个index,在MS SQL 2005以后，就叫Persistant View, 就是系统给你算一遍后，会存在物理内存里，就好像你有个真的Table，你做1000次 SELECT，LEFT运算只算一次。
3）如果经常计算，可否为统计性的数据做一个其他的查询库，和你的生产库分离？同时可以用分区方式减少单元数据条数，比如2009年一个，2008年一个，2007年一个。
4）(这个方法有点不确定），LEFT(pres_date, 8)          >= '20090610' 能不能改成 pres_date          >= '20090610000000'？也许会快点。
5）（这个更不确定），如果你用个嵌套SQL，先将所有想要的表列原样查询到一个变量，再在结果中运算，会不会有帮助？(这里不确定的是，如果这在存储过程中，也许编译器已经优化成这样了）
SELECT LEFT(pres_date, 6) as name, sum(cast(substring(anal_result, 1, 1) as int)) as pwjj, sum(cast(substring(anal_result, 2, 1) as int)) as xhzy,
       sum(cast(substring(anal_result, 4, 1) as int)) as cfyy, sum(cast(substring(anal_result, 5, 1) as int)) as kss, sum(cast(substring(anal_result, 7, 1) as int)) as qtwt,
       sum(cast(substring(anal_result, 8, 1) as int)) as yywt, sum(cast(substring(anal_result, 1, 1) as int)) +sum(cast(substring(anal_result, 2, 1) as int))
       +sum(cast(substring(anal_result, 4, 1) as int)) +sum(cast(substring(anal_result, 5, 1) as int)) +sum(cast(substring(anal_result, 7, 1) as int))
       +sum(cast(substring(anal_result, 8, 1) as int)) as count
  FROM
{
   select anal_result from　t_pres_info
        WHERE (LEFT(pres_date, 8)          >= '20090610'
           AND LEFT(pres_date, 8)           <  '20090612'
                  AND len(rtrim(ltrim(pres_date))) =  14)
｝As MySmallerTable
group by LEFT(pres_date, 6)
order by LEFT(pres_date, 6)
这里有几个好处：
a) 你尽快的查完了t_pres_info表，这样尽量不影响其他查询。
b) 你做的许多工作其实都只用到anal_result，MySamllertable只要这一列，同时只有两天的数据，这样临时变量表会不会小点？


总体觉得，你如果能把上述所有的字符串运算（LEFT，Cast, Substring, ltrim, rtrim) 都剪掉，性能能至少能提高10倍.

[ 本帖最后由 PromisingChina 于 2009-6-12 19:54 编辑 ]

PromisingChina

对15楼问题：
以前没玩过Oracle，还真不知道这些分别。
但是，对于Oracel事务的设计，我有点难以接受。事务按定义不就是要保证我若干运算能同进同退吗？如果是我改别人也能改，理论上是不能滚回的。
这里，如果Oracle默认是能读，我可以接受，如果默认使能写，有点问题，绝对逻辑问题。
同时，按照LZ的观察：MS SQL已经能做到Row Level Lock，因为
    select * from customers where customerid='ALFKJ'; 有问题
    select * from customers where customerid='ALFKI'; 没问题
这已经非常不容易了。
但是如果不锁定，一定会有逻辑错误。比如，我做以下几件事：
1）对某人病历增加一个处方
2）对某人病历增加一个检验
3）对某人病历增加一个检查
4）对某人病历增加一个收费
假设这4个是不可分的步骤，假设一定要事务，就是要保证要么四个都更新，要么四个都不更新。如果不锁住，一定有可能在某一瞬间，Oracle可能仅让你看到其中3个，或2个，或1个。这样的问题，根据你的需求，可能是错误的，甚至是致命的。而且可能1亿分之一的概率，出了错，你一辈子查不出来。

所以，这种一致性读取，是不是个非常优秀的设计，实在是仁者见仁，智者见智了。我会非常小心。

所以，你的问题也许应该是，上传SQL该不该用事务。这完全根据你的业务，和系统需求。在许多大规模数据的网站上，会有严格的控制，通常不会用简单的上传，甚至要读取目标SQL服务器上的CPU承载量，等待线程等决定，比如，一旦SQL服务器CPU超过50%，上传暂停等等。

你可以试试，建个特别的SWAP表单（最好不是Temp table)，每次先上传到这个表里，再从这个表上传到真的表，也许可以大大减少影响。在高级一点，可以用镜像的技术，自动同步。
再不行，至少先将数据文件考到SQL服务器，然后在本机上传，这样大大减少每个数据传输的时间，减少整个流程锁定的时间。

PromisingChina

=======
回过头来讲存储过程,实际上那是我优化的重点,解密存储过程还是很简单的,网上有现成的工具,在测试环境很快获得存储过程的源码.解开后我大概看了一下,发现执行过程很复杂,存储过程又调用另外的存储过程,而且里面还有循环等,第一感觉执行这点时间很正常的.在oracle8i下我估计也许也要这个时间.
=======
这里的循环也许印证了我所体会的：DBA的数据集思维（Data Set)和普通程序型编程思维(Procedural Programming)的不同。通常SQL中绝大多数的循环，都是能改为不用循环的SQL，而且性能不可同日而语。

我初学的时候，曾在工作中处理每天10G的数据，当时我写了一个SQL，一个多小时无法完成，常常超时。在请教一个高手是，那个高手直接告诉我，你在写C，不是写SQL。后来我改过来，全部用关系，马上减到4分钟多，最后调优到1分钟内。从那以后才对SQL有点感觉。