刚刚在客户优化监控系统中，通过DMVs发现一条（通过HQL语句生成）sql语句，执行时间要7205ms，语句如下:
select
top
30
this_.id
as
id10_0_, this_.objid
as
objid10_0_, this_.objname
as objname10_0_, this_.mid as mid10_0_, this_.logtype as logtype10_0_, this_.logdesc as logdesc10_0_, this_.submitor as submitor10_0_, this_.submitdate as submitdate10_0_,
this_.submittime as submittime10_0_, this_.submitip as submitip10_0_, this_.col1 as col11_10_0_,this_.col2 as col12_10_0_, this_.col3 as col13_10_0_ from log this_ where not exists ( select ' X ' from Delobj del where del.objid = this_.id and del.objtable = ' Log ' ) order by this_.submitdate desc , this_.submittime desc
as objname10_0_, this_.mid as mid10_0_, this_.logtype as logtype10_0_, this_.logdesc as logdesc10_0_, this_.submitor as submitor10_0_, this_.submitdate as submitdate10_0_,
this_.submittime as submittime10_0_, this_.submitip as submitip10_0_, this_.col1 as col11_10_0_,this_.col2 as col12_10_0_, this_.col3 as col13_10_0_ from log this_ where not exists ( select ' X ' from Delobj del where del.objid = this_.id and del.objtable = ' Log ' ) order by this_.submitdate desc , this_.submittime desc
执行sql语句的执行计划:
分析：这里发现整个sql语句最占用资源的地方就是排序了，占了90%，而且表log中大约有超过46万行数据。排序的成本很高，我们发现是通过：
[
dbo
]
.
[
log
]
.submitdate 降序,
[
dbo
]
.
[
log
]
.submittime 降序
排序是在submitdate和submittime的排序。由于我们在log表中列id建立了聚集索引，这时由于排序是用到了时间列，所以成本很高，这时我们需要重新重构聚集索引。
是必须在（submitdate，submittime）建立聚集索引，由于前期在设计表时的不合理，造成了时间字段教长，有18个字节，大大超过了8个字节的datetime类型，不太适合建立聚集索引。
这时为保证查询性能，在submitdate字段建立聚集索引，而不是建立（submitdate，submittime）复合聚集索引，适当提高速度。
总结：
1，字段类型和长短的是很重要，不然在后续修改和优化是很困难。
2，对与聚集索引选择一般在需要排序，进行范围选择和group by等字段上建立