Nineteen@newsmth

@Wendytt
因为只有出现特定问题的时候才需要加option(recompile)。在线上系统中，这东西不加为妙。高负载系统手工调节比较好。

@zhouwb

得看什么样的查询。有时候为了利用索引，还会增加子查询。

@liuzhiliang
首先要确认这个异常是不是一个critical的异常。critical是说，出现这种异常的时候，整个事务是否需要被终止并回滚。如果是，那就rollback。如果不是，应该把可能出现这种异常的语句放在一个Supressed的TransactionScope里（如果使用的是TransactionScope的话），并且cache掉这个异常，并继续处理事务的其他部分。

从你的应用来看，是对同一张表进行操作，但是非成功的操作如果是因为unique constraint则忽略掉，并继续其他处理；如果是其他原因造成的非成功操作，则回滚事务。这个东东如果用TransactionScope是无法做到的。建议你在SQL里面做。

Oracle偶不清楚，理论上应该有类似SQLServer @@Error或者TRY CATCH的机制。换个存储过程搞吧。

@liuzhiliang

这个ORA-00001是个啥异常？没见过。你程序中如何处理这个异常的？仅仅是忽略掉继续？

对于没有使用索引的情况，我瞎猜一把。发到服务器端的SQL语句类型不匹配？

@shuiniu
谢谢.

这个"ad hoc"只是一个名字,那篇文章里面不是还有说"SQL Server requires exact text match for two ad-hoc queries. The text match is both case- and space-sensitive.".实际上就是拼出来的完整的SQL语句,偶说的直白了一些:)

这个blog说是blog,其实还不如说是八月份比较空闲,整理整理连续的开发过程中的心得,写的乱七八糟的,各位见笑了:)

你的语句性能差差在SQL根本无法估算选择性大小,原因在于你在where条件中对列执行了运算,所以执行的是table scan.一般情况下,逻辑IO如此巨大,都需要彻底改进.

至于你提到的两种方式执行效率不同,根本原因在于where条件中类型不匹配.

你犯的两个错误在我的blog中都有提到:)

补丁打了没有?没打补丁见:http://www.cnblogs.com/cn_wpf/archive/2007/08/06/844766.html.

btw:你得说说错误是什么大家才能帮助你定位问题

俺从头到尾就一直认为LINQ是垃圾。实际项目中是打死也不用的，呵呵。

顶蛤蟆的东东～～

你不需要enable TIP事务和XA事务

@coollzh
本地事务能保证多个instance数据一致性？不能吧
TS里面是不是要保持连接，这个我们没办法控制，它一定会保持连接。

所以用不用TS要看对数据一致性的需求

"清晰的认识自己所开发系统的重点在哪里，审视自己以及组织的重点是否在偏离这个方向，是很重要的。而事实上也很容易发生偏移。"这句话大赞。

@progame
OMG....I said:本子待机了一次，然后再开机的时候
我没办法重现SQLServer
~~~~~
自己选择NestedLoop总是比MergeJoin的cpu占用时间短了

@Justin Shen
@progame
从分析结果上看，对NestedLoop，SQL用于评估执行计划的算法非常优秀，它尽可能的不去做IO。但是实际执行并不是这样，它该IO还是IO。
这并不另人迷惑，因为事实就是那样。稍微有点猥琐的是MS为什么不用评估执行计划的算法去执行实际操作。呵呵

本子待机了一次，然后再开机的时候我没办法重现SQLServer自己选择NestedLoop总是比MergeJoin的cpu占用时间短了。现在的情况是：SQLServer每次都错误的选择了NestedLoop，导致的结果是IO相差20 ～ 30倍，执行时间多了百分之50。

至于选择这么简单的情境做个例子，就是因为它说起来比较方便：）^_^

如此简单的特例，如果SQLServer处理的有问题，那么复杂情况下实在是令人担心——让人迷惑的是，一般情况下它工作的挺好……

TO ： @Pragame，你得到了它。

http://www.cnblogs.com/cn_wpf/archive/2007/08/06/844766.html

使用TransactionScope必须安装的补丁，这厮的缺点毛病，外加应该用什么替代都有提到。

另外还提到了为什么在TransactionScope中操作相同的库也会把事务升级成分布式事务。

@progame
标记了，谢谢。

@progame
汗，这个很麻烦了。

vb6不是很了解，不知道它有没有一个AddParameter的过程。如果有的话，倒是可以在这时候依据传入的Parameter类型推测DB那头需要啥，比方一个int转成DB的int一般不会错，int64转成bigint一般也不会错。

如果是一个string，传入的时候把参数都设置成nvarchar(maxvalue)对执行计划缓存来说应该是比较好的。但是2k里面，如果表中的列定义为varchar，那会把本该seek算法搞成scan（我没实际测试过，据说如此）——原因是SQLServer不像我们想像的那么聪明，它不是先把传入参数转成目标类型再进行seek，而是每次都把目标类型转成传入类型，从而不得不scan。

所以长痛不如短痛，还是都给它改了吧

@progame
这个……你说的让我有点迷糊。

你提到“对原来程序出来的SQL进行后期参数化处理”，这个“程序出来的SQL”是指应用程序发送到SQLServer真正的语句吗？“后期参数化处理”是指？

没有程序源码也没有DB结构？如果有后者，应该能拿到真正的类型，再加上前者，改成强匹配类型应该不麻烦啊

@progame
2是有一些区别的，因为SQLServer现在还稍微有点傻，并不是先Convert，至少不是所有类型都会帮你Convert。所以还是得具体问题具体研究:)尽量保持类型完全匹配是个好习惯。

3可以参考偶的做法，用xml传一堆东西进来。然后通过nodes函数和value函数给它映射成表。详情见MSDN。

它会消耗一些cpu。

@progame
1、到现在一直避免使用触发器，所以对这个东东没啥经验，我实验一二之后看看不能不能解释，sorry:)
2、这得看目标数据类型和NVARCHAR能否隐式转换。这得具体问题具体分析了。
3、不固定集合的查询，我们项目中推荐的做法是在应用程序侧拼成xml，然后作为参数传入存储过程，在存储过程中解析它，就像处理一张表一样。如果一定要用参数化查询，那就得在查询中解析它，写起来稍微麻烦一点。

在解析它的时候，可以指定类型。

@corvallis
实际上plan cache大多数都在buffer pool中，数据缓存都在buffer pool中。
buffer pool空间有限，plan cache多了，数据缓存就得少。

要保持较高的数据缓存命中率，可能数据缓存需要足够的空间（假设10G），如果服务器内存足够大，就算是plan cache占了100G，只要数据缓存有10G就OK；反之DB整体性能就会受到影响。

就像你提到的，是偶没说清楚：）非常感谢你提到这个问题:)
偶补充一点点：看plan cache的大小和命中率，可以观察计数器：SQLServer:Plan Cache下的东东。

@IT清爽狐狸
LINQ这个东西，说它是语法糖，又不够甜，如：带来的所谓的Extention Method，怎么看怎么像毒瘤；说它是ORM，偏偏又缺少一些必要的机制，如Identity Map；性能，由于缺少索引类似的机制，着实的不敢恭维……

将来没看到它更明确的优点之前，偶是绝对禁止使用它的。

@RicCC
从存储过程sys.sp_procedure_params_managed的返回结果集来看，确实是有return value.

刚才终于找到了传说中的SqlHelper，里面有这么一段代码：
private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(spName,cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(cmd);

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];;

cmd.Parameters.CopyTo(discoveredParameters, 0);

return discoveredParameters;
}
}

看上去好像它做了Remove：）你看看现在的EL代码，它是不是也做了？

@RicCC
这东西是这样：
create proc foo
(
@var varchar(32)
)
as
insert into yourtable (@var)
======
这样一个存储过程，它会自己给你加一个return value作为第0个parameter.这个用起来不太方便，一般偶都把它给Remove了

@金色海洋（jyk）
你说的是在存储过程里面通过sp_executesql来执行拼出来的sql文本吧？

站在DB的角度看，它和应用程序拼SQL产生查询计划的方式相同，即只要拼的SQL语句有任何不同，都会生成不同的查询计划。所以事情没有变得更糟。

@凉面

谢谢，我那句话说的确实不严谨：）存储过程是否被动的被SQL引擎重新编译，确实受很多因素的影响，如：是不是缓存过期、depends的表被更改、统计信息更新、带DML触发器的表变化太大（如果自动更新统计信息选项被设置为ON）等等。
存储过程主动重新编译，一般是我们认为输入参数带来的选择性变化剧烈，为了获取最快的查询计划，我们要求存储过程重新编译（或者指定其中的某一句要重新编译，sql2k5）。

但是，貌似存储过程的执行计划同一个时间只有一份，这个“很多的缓存计划”是？可能我记错了或者理解错了，可否指点一二？

btw：除了网络传输，存储过程另外一个优势是，它只有一个源，它的语句是不变的，SQLServer可以优化查找相应查询计划的算法

@RicCC

你说的是SqlCommandBuilder.DeriveParameters方法吗？它在内部调用了存储过程sys.sp_procedure_params_managed，所以它给SqlCommand添加的Parameter index = 0的总是一个return value，一般还得手工给它RemoveAt掉（如果不需要返回值的话）。

印象中，古老的Enterprise Library最早的SqlHelper里面好像用到了它。

@金色海洋（jyk）
@OOP
拼SQL性能上和使用存储过程，执行本身效率差别并没有这么明显。只是拼SQL会占用大量的BufferPool空间，会导致缓存命中率下降得很厉害（当然，因为不同的系统行为，可能后果不是那么严重）。缓存命中率下降会带来更多的物理读，咱都知道，物理读和逻辑读相比，系统消耗完全不是一个数量级。

另外一个缺点就是，如果没有限定SQLServer可以占用的最大内存，那么SQLServer会一直试图吃内存。一般情况下，DB服务器上除了Sqlservr.exe之外，还有很多的辅助进程，当系统内存降到100M以下，这些进程会受到影响。

最后，我很同意当项目运行发现性能问题之后再解决它而不是在开发过程中就去追求完美。当然，这得有个度的把握:)也不能完全不管不顾的开发。

@赵宝民
项目规模够大的时候，迁移数据库八成是行政原因，不是技术原因～如果在某个时间点预见由于性能、管理等技术原因需要迁移数据库的话，从俺的经历来看，八成是自己写程序迁，相关存储过程基本上会手工重写——当然这首先得对应用程序透明。如果应用程序也得改，任务不太紧张的情况下，基本上DB Imp整个重来了。相对整个项目，DBImp规模一般都不大。

@RicCC
对。

我再补点东西到文中。

@leaf
谢谢。能否提示一二？

其实偶在文中已经表达过，高层的模块实现，不应该依赖几乎处于最底层的DB实现。也就是说，那地方至少有个Proxy或者Adapter。

真要迁移，增加个DB实现好了。

这相对于存储过程带来的优点，个人认为迁移的麻烦微不足道。当然，这得看项目规模。

首先要鄙视那些说这个简单那个简单的人。

其次，对楼主的需求来说，应该首先看看是不是需要缓存。DB服务器是撑不住了？一般情况下，这种情况不太可能存在，类似楼主需求的查询，加点内存一般就OK了。另外，要知道SQLServer缓存机制是非常彪悍的，在应用程序上做缓存，命中率比SQLServer高吗？

然后，应该根据用例分析要缓存哪些数据。看楼主的文章，缓存的貌似都是静态数据，如果只是为了提供查询功能，还不如加一台DB服务器，专门存储这些数据，命中率肯定比在应用程序服务器上自己做缓存强。就算是没有机器，直接在应用程序服务器上建个实例，没准也比自己搞强。

解决方案其实很简单，完全不用写程序的说。呵呵

苏州果然漂亮～

@Justin Shen
获取自增之后的值而产生deadlock，这个也许可以通过output关键字避免:)

@Justin Shen
赞～～

偶说的适用于heap的情况，在我曾经看到的是这样：
一是查询非常多、几乎不删除、非键列更新，并且，查询结果基本都是单行，而查询条件几乎平均分布到三个以上索引的表。
二是一个每天产生kw级日志的日志表，查询基本上是统计每天产生的日志量、查询指定用户产生的日志（结果集和总行数比，基本趋近于0）。

如果出现了您评论中描述的行为，用堆基本上属于自杀行为:)。

堆有存在的价值，也有使用的条件，偶之所以强调不是所有的表都需要建聚集索引，是因为不少人都忽略了堆的价值:)

这篇跟纯粹的数据库关系不大，但是跟数据库应用有些关系，不知道发在DB团队里是不是合适：）

顶.

转载可以,怎么可以无耻的不保留原文出处呢

@Reeezak
从我个人的经历来看,前期开发一般都会"臆测"系统行为,并由此多少建一些索引.臆测系统行为时,很少花费太多的精力,并且开发人员也不是太在乎它:),毕竟后面依据实际系统行为调整索引才是重点.

至于在后面你提到的where col1 - col2 > 1的问题(我想你把它写成"where col1 - 2 > 1"是个笔误),这个不论建什么样的索引,都没有算法可以让SQL直接评估出col1 - col2 > 1的选择度.就算是我们自己去想,也想不出个所以然来.
俺偷偷的说,让我解释没地方下嘴,所以就偷懒不说了:)

@roy

好吧，我假设你没有看到我一再强调的“系统行为决定一切”，也假设你没有看到我一再说明的“在特定条件下”，所以我最终假设你把我认为的“森林中每棵树都有不同，应该根据这些不同进行分别处理”当作“只见树木不见森林”的观点是正确的……

至于统计，真没见过“转换成功的”行需要在原数据中删除……不保留原始数据，这个实在太强。

退一万步，就算我真要这么转，难道不能:
TransactionScope sp = new TransactionScope;
delete from yourLog output deleted.* where LogTime < XXXX;
WriteToOLAPDB();
sp.Complete();

（伪码）。

@RicCC
谢谢～

所谓系统行为决定一切，如果系统的行为表明使用堆更好，就应该使用堆。这个倒是和认真对待clustered index的建立没什么冲突：）

我碰到的真实场景是：有一张表，读的量相对写非常巨大。写操作绝大部分是insert 和更新非键列的值，只有极少量的delete。分析这些读操作，发现在4个非聚集索引上几乎都是index seek。这种情况，使用堆的确是更好的选择。

请允许偶再重复一次“系统行为决定一切”：）就像你提到的找8.1-8.9的log，如果这种查询在OLTP上大量存在，在时间字段上建聚集索引就是不二选择。:)
=====

就index来说，对clustered index seek，它的行为本来就具有你提到的特点：找到8.1的记录，然后往后遍历就OK了；对nonclustered index seek来说，如果是涵盖查询或者include查询，其行为和clustered index seek没啥不同，如果不是涵盖查询或include查询，SQL会根据选择度来判断是做clustered index scan还是nonclustered index seek。

但是偶没注意到过unique index seek，能否稍微解释一下？谢谢：）

@RicCC
赞～～

首先偶只是说：特定情况下需要使用堆表。特定情况，不是多数情况。:)文中偶举的例子说的就是index seek。

其次，偶一再强调需要依据系统行为来调整索引。如果系统行为有太多的scan，太多的写操作，用堆显然不合适。低并发量的插入、非键字段更新，大量的index seek，堆是更合适的结构。

@dx

聚集索引和唯一索引是俩个概念，之间完全没有必然关系。

@shen126

谢谢。这个问题是这样，如果更新的是聚集索引键值，那毫无疑问，所有的非聚集索引都得跟着更新。如果更新的只是非索引字段，那聚集索引、非聚集索引都不更新。
所以更新并不构成不使用堆的理由：）

再者，俗话说“存在即合理”，堆既然存在，自然有它存在的理由：）

@roy
God....
俺还的的确确没见过统计系统需要更新、删除日志的。
用一个被改变过的数据源怎么能拿到原始统计信息？这样的统计信息有什么意义吗？

另外，统计这种摆明了OLAP操作，怎么能在OLTP上做呢？

日志难道不给客服用？难道不用来排查系统bug?用处貌似很多吧

@金色海洋（jyk）

就像我举到的例子中提到的日志表。对日志表不进行更新或删除在我来看一项基本原则。
对于分页，难道不能按时间排序进行排序、分页吗？

另：偶文中说，在时间字段上创建聚集索引。：）

@Justin Shen

呵呵，请注意使用堆而不使用聚集表条件。偶并没有说使用聚集表不对，只是说某些个别的情况下，应该使用堆。
在我遇见过的问题中，确确实实存在应该使用堆的情况。
这个问题俺小心的向GTech的大师求证过，经过仔细的讨论，最终的结论应该不会错。：）

使用聚集索引和RID查找数据效率上确实没啥差别，但是谁也不会也无法通过RID索引表。就像我文中提到的，你的系统行为决定你有一种以上的查询需要更快速的支持，一张表上你又无法创建一个以上的聚集索引，这时候，堆，就是更好的选择。

另外，文中偶一再提到按照系统行为调整索引，所以如果到最后，你的查询还是没有被cover，那是索引调整的问题。

最后，本文摘要中说，sql2k5，呵呵。
------------------------
偶在sql2k5里面尚未见到过clustered index seek + key lookup;只见过nonclustered index seek + key lookup.您的笔误？

@rosanshao
没有看到你的留言，也没有看到你的邮件回复

@zhou
汗，我倒是有这些hotfix，但是若MS没有发布，我拿出来可能会吃不了兜着走……

仔细找找，真的没有吗？我曾经拿到一个公共链接，可以直接下936983的说。

抱歉，各位