Performance Considerations of Data Types
本文大意：
主要介绍数据类型的选择，选择尽量小的数据类型，减少表宽就以为这减少磁盘占用空间，以为这减少读入内存后占用的内存，就以为这减少不必要的cpu来读入数据，处理数据，char和varchar最佳实践小于5使用char大于10使用varchar。关于聚集索引的选择选用窄的，静态的（不容易被修改），唯一的，自增的列为聚集索引键（持有保留）
本文大意：
1.数据库文件和数据库日志设置：日志和数据文件是否要分开存放，自动增长是否配置正确，快速文件初始化是否设置，自动收缩是否禁用
2.索引碎片：碎片分为2类1内部碎片，2外部碎片（这个就是我们通常说的索引碎片），内部碎片由分页，或者delete造成，页密度下降，导致空间浪费，通常处理办法是索引重建，如果是分页引起也可以使用填充因子设置来调整
3.统计信息：统计信息会造成优化器对数据的误判导致生产的执行计划效率低下
4.错误页处理：定期的使用dbcc checkdb（建议一周，小弟还没开始使用这个）
5.数据库备份：这个自然是重中之重
本文大意：
sql server 2005的分区处理存在以下问题：
1.如分区比cpu少，那么并不是所有的cpu都参与，只参与了分区个数的cpu
2.分区中数据不均衡，导致某个线程承担了最大的io
sql server 2008 做了调整：所有的cpu先访问一个分区，然后逐个向下访问，
但是这中方法还是有一些限制在以下条件下是不会采用上述方式运行的：
1.数据不再内存并且
2.数据不是均衡存放在可用磁盘中
因为如果数据都放在一个磁盘下，那么所有的cpu都启动一个线程扫描一个磁盘，反而会减少吞吐量
本文大意：
多余不太使用的索引应该删除：
1.若有插入，那么就会插入到这个索引，导致没有必要的io，log，分页
2.有记录删除，那么就会删除这个索引，导致没有必要的io，log
3.若有聚集索引键被修改，那么索引也会被修改
4.聚集索引被重建，那么该索引也会被重建
通过查看sys.dm_db_index_usage_Stats的seek，scan，lookup,update来确定索引是否在使用
本文大意：
多核cpu和单核cpu最主要的区别就在于cpu的cache：单核cpu，2个核之间要协同处理需要通过总线来走，但是多核可能只需要通过cpu本身就可以了
Intel处理器：一个双核cpu有各自的一个L1和一个共享的L2，当需要协同时，只需要把L2的数据共享到L1就可以了
AMD处理器：一个双核处理器各自有一个L1和L2，共享L3，当需要协同时，L2之间类似链接的关系，当需要的时候时候直接把L2复制给另外一个L2
如果是独立完成的负荷，那么多核cpu之间L2，或者L3参合在一起肯定没有单核来的好
但是如果是需要协作完成的负荷，那么多核cpu就比单核cpu有优势，因为不需要走总线
本文大意：
主体和镜像之间的性能很大一部分都是因为日志的问题，所以2008加入的日志压缩然后在传送当然这样会增加cpu的使用，如果已经到cpu瓶颈的服务器，并且有独立的镜像nic，那么可以
使用1642跟踪标记开关闭日志压缩
本文大意：
在镜像中做索引维护为产生大量的日志（因为日志操作都是完全模式）
1.在同步模式下，索引维护的日志会变成一个大的问题，导致网络拥塞，降低吞吐量
2.在异步模式下，虽然生产的日志会充满整个网络，但是是异步提交，但是SEND队列会变得很大，导致如果出现故障转移后会丢失很多东西
既然怎么严重作者就提了一下几个观点：
1.使用一个产生日志小的，比如索引重组，索引重建不管碎片多不多都会产生一样多的日志，但是索引重组只会对有索引碎片的部分处理，日志生成也相对减小
2.不关心索引的碎片率 一心只用整理或者重建，也不查看碎片是否清楚，查看
Microsoft SQL Server 2000 Index Defragmentation Best Practices
3.对于分区索引在维护的时候可以指定某一个分区进行详细看alter index
本文大意：
索引碎片：逻辑顺序和物理顺序不匹配。碎片会影响io的性能，但不会影响内存数据的性能。
在确定是否是索引碎片引起的性能问题前先要排除一下可能：
1.系统资源问题（io，内存，cpu）
2.是否存在系统级碎片
3.性能差的查询
因为是2000，作者使用dbcc showcontig，并对这个命令主要的指标进行了说明：
1.碎片对索引的影响，当索引越大，那么影响也就越大
2.高索引碎片会造成索引扫描性能下降
3.低的页密度说明需要需要读入更多的页
在olap和oltp下性能有很大差异：oltp受索引碎片影响较小，olap受索引影响较大，这个和工作方式有关oltp主要是高选择度的查询，但是olap是以扫描为主
1.索引重组在静态情况下对索引的评估比较到位，但是在可修改状况下就不是很到位，是否有问题可以使用命令查看碎片率
2.在做索引重建的时候，在原索引没有删除前，sql server会分配一个新的空间让新的索引使用，导致离内圈较近导致io吞吐量小量下降
3.索引重建，当没有足够的连续空间时会使用不连续的空间，导致小的碎片率
一个查询语句中和io有关的主要是1.物理io，2.预读
预读会在sql server 读取数据的时候预先读取可能会用到的数据，如果碎片少就会带入大块数据，如果碎片大会带入小块数据，这样就会比碎片少是花更多的cpu和吞吐量来读入相同的数据
重建比重组的有2个优点：1.会重新生成统计信息，2.可以利用多个可用的cpu参与创建
重组不是一个事务，而是多个小事务组成，因此可以随时截断日志，并且不会有数据丢失。重组有2个阶段：1.靠近填充因子，2.引动碎片，让物理顺序和逻辑顺序一致，因为是多个小事务因此对性能影响较小。重组有个缺点：调整后索引区并不是连续的，尽管有这个缺点但是并不会对性能有很大的影响。
因为重建会使用所有可用的cpu，但是重组的本意就是不影响生产环境，因此重建的速度比重组快。重组的速度取决于当前服务器负荷。
在完全模式下，重建的日志生产比重组大很多，重建的日志量=页数*8KB，但是在bulk logged模式下，重建的日志就会有很大的变化，比完全模式少很多，所以建议在比较大表上重建索引切换到bulk logged下
本文大意：
选择正确的数据类型主要是尽量选择小的数据类型，这样可以减少磁盘，内存的空间，同时也减少io的时候，以及参与io的cpu。对于char和varchar选择有个最佳实践，如果长度小于5那么使用char若大于10并且变动很大，那么使用varchar
宽行：1.一页中的数据变小，2.读入cpu时需要的cpu缓存，所消耗的cpu也更大
窄行：1.页中可以保存更多的数据，2.整个索引变小，3.需要的io也变的很小
本文大意：
微软说不能超过10个，只是在32位的系统下的用户的一个最佳点的假设，其实应该关注一下几点：
1.主体和镜像之间的cpu
2.主体和镜像的内存
3.2边的io能力
4.每个数据库生成的日志
5.主体和镜像之间的可用带宽为多少
最后2条最重要，如果带宽不够就无法承受生成的日志，那么主体性能会降低，如果镜像服务器性能低，会造成数据库镜像负荷过大
本文大意：
自动收缩要关闭的几个原因：
1.生成碎片
2.收缩需要大量的io和cpu，并需要buf，把hotpage切换出去，可能会导致磁盘队列过长甚至超时
3.收缩造成系统级碎片
4.在收缩时，造成资源的巨大浪费
5.自增的时候因为没有设置快速初始化，导致需要填0，造成暂时的offline
本文大意：
作者对备份压缩和非备份压缩的做了测试，并也对比了还原的速度结论如下：
压缩备份和恢复会有小量的cpu消耗，但是备份和恢复时间都有大幅度减少
本文大意：
一般是不需要多个文件对于多个cpu，除非像tempdb一样出现等待（pfs，gam，sgam）的情况
本文大意：
作者测试了一下2008的FILESTREAM的性能：
1.blob保存filestream是通过win32的api来访问
2.blob保存filestream通过tsql处理
3.blob保存在varbinary中
结论文件越小使用sql操作性能越好，一般是
本文大意：
选择合适的索引在单个sql下没啥难度，当时当sql多的时候就要恼人了
当查询复杂时，索引键的选择也会变得复杂，如何选择key，如何组合，频率等都需要考虑
里面还指出，如果条件是用and连接的那么索引可以的顺序可以无所谓，并不一定要和谓词中的一致，sql server会现在一个较合适的索引
本文大意：
GUID key 会造成碎片并使查询性能降低
guid的生产是随机的所以插入也是随机的，这样会造成大量的分页和碎片
聚集聚集索引key也会被当成键，跟随在其他键之后，那么guid也会被当成key来处理，如果非聚集所以索引键存在一样的值，那么就有可能出现碎片问题
关于非聚集索引的记录可是可以看笔者的另外一篇博文：
SQL Server页中行物理存储
本文大意：
1.是否要使用FILESTREAM存放：<256KB,存放在数据库中，若大于1MB存放在文件系统中，小数据块存放在FILESTREAM并没有很好的性能
2.选择合适的raid，如果是写密集型就不要选raid5
3.选择合适的磁盘SCSI或SATA
4.确定SATA开启了NCQ,SCSI开启了CTQ，NCQ，CTQ是磁盘读取技术，最小化寻道时间，主要是通过对指令顺序重排达到
5.日志文件和数据库文件分开
6.如果有必要可以先运行一下碎片整理
7.关闭8.3命名规则，8.3命令规则会减少插入和修改大小，可以使用fsutil
8.使用fsutil关闭，对最后一次访问时间的跟踪
9.若大于1mb，那么把分配单元设置成64KB
10.把修改先写入到一个新的文件，之后把多个更新合并到一起，再更新
11.如果数据需要传到客户端，把SMB的buf设置为60KB，这样不会被tcp协议拆包，tcp为64KB，可以查看
http://support.microsoft.com/kb/223140设置SMB buf size
本文大意：
因为溢出页而导致查询扫描的性能问题：当行>8060时就有可能溢出，溢出时把可变变量溢出到溢出页中，但是不能保证所有的溢出都是同一个列。这样溢出就会带来几个问题：1.查询会多一次io，2.如果有多个可变列，那么就无法预期有那些列是溢出的。
镜像自动切换，碰到不必要切换问题：1.删除仲裁服务，2.设置超时值，（作者提到这个是可能是分钟，但是经过我查看了联机文档都是秒，不知道是否我理解错误了）
差异备份很大的问题：当一个extent被修改了，就会在differential map中做标记。diff map每4gb一个。当差异备份的时候，所有diff maps被扫描，复制所有的extents，但是并不重置diff map，只有到全备的时候才被重置
2个数据库服务器，每个服务器一个实例，一个实例有一个库，2个数据库互作故障，并且按最大内存设置为了50%的物理内存，但是因为负荷增加而引起内存不足的问题：以前在2000的时候，数据库不会对系统的内存压力做出反应，因此只有设置50%保证内存平衡使用，但是到了2005之后可以把最好内存设置为40%，当一个实例出现故障转移，因为2005会做系统的内存压力做出反应，然后2个实例之间会趋于一个平衡
本文大意：
是一个系列里面有2篇文章：
Performance impact: a large number of virtual log files – Part I ：作者以测试的方式得出结论VLFS变大会对性能有影响
Performance impact: a large number of virtual log files – Part II：同样作者以证明的方式得出，OLTP负荷下并不会对性能基本没有影响
本文大意：
为什么log-shpping的恢复日志备份会怎么慢，原因：
1.logshipping，使用的是with standby，既会执行redo和undo，并把undo的细节保存到一个文件中.
2.当恢复一个备份是，先要把上次的undo再做一次undo，然后再恢复日志
这样就会花费2部分时间，如果是并发量比较大的log备份，那么就有可能时间会变得很长
本文大意：
作者从2方面考虑数据库物理层的设计：
1.io：若你只有一个数据库文件，那么就会在数据库设计上面有争议，因为多个数据库文件的反而造成，磁头在不同文件之间的跳转，如果你有一个存储，那么就可以考虑的更宽一点。
2性能，可恢复性，可管理性：把数据库文件或者文件组分散到独立的存储中，这样可以减少磁头的冲突，增强写入的性能，特别是checkpoint，也可以减少pfs，GAM，SGAM的冲突。还有一个是为了实现段落还原，还有就是把io量比较大的表放到其他独立磁盘或者存储单元中减少对其他表的io影响
本文大意：
本文提到一个关于添加列之后null map被增加的误区，所有的表都会有null map 除非这个表都为 not null 或者都为稀疏列
创建新列并且为可为null，并且以null初始化，一开始并不会，出现在记录中只有当某些原因下才会被更新（比如表中某字段被更新）
新列可以为null，但是有非null的默认值，所有记录都更新了nullmap（非null的默认值到底指的是什么，因为我alter table 加了列定义了默认值，但是没出现作者说的状况）
当新列为not null强制更新nullmap
本文大意：
使用sys.dm_tran_database_transactions查看生产的日志，作者通过测试发现insert造成分页的log是不分页的5.5倍，当然这个随着行越小，发生分页的这个比例也就越大，
分页不单单造成io问题和索引碎片也会造成大量的log
本文大意：
这边文章主要说了关于分区对齐的事情
在2003之前分区的偏移是31.5kb（
63 x 512B
）但是raid 的条带大小往往是64kb，这样就造成了分区不对齐，但是在server 2008 之后就不存在这个问题，2008 默认分区保留是1024kb，具体可以查看
Disk Partition Alignment Best Practices for SQL Server
Disk Performance Hands On Series系列文章中对分区偏移，raid条带大小，分配单元做了测试，推荐使用64的条带大小，64的分配单元，并且分区对齐
关于分区对齐，给出了2个公式1：分区偏移/raid条带大小要是整数，条带大小/分配单元要是整数
本文大意：
作者认为是非聚集索引的好原因如下：
1.若为聚集索引key就会变成16字节，并影响聚集索引的大小，但是会影响非聚集索引的大小
2.随机guid比较容易产生碎片。一个页内可以保留的记录变少，分页加大，碎片变大
3.随机guid 会导致分页频繁，导致不必要的io和log，cpu，guid。在非聚集索引中影响不会很大，因为一个页中保存guid较多并且可以使用填充因子调整
本文大意：
1.io问题：写密集的workload是否使用raid5，磁盘分区是否对齐，是否存在io队列过长，是否出现tempdb的pfd，gam，sgam冲突。
2.cpu,内存问题：1.单cpu使用上升是否是负荷过大，2.是否是并发查询导致cpu上升，内存过少导致，内存中的页生命周期过短，并且tempdb上有大量的spool data。确保numa没有夸节点使用内存（这个有点麻烦）
3.虚拟机问题：当使用软件来虚拟化io时，性能极具萎缩，最好不要再生产上使用 hyper-v解决了这个问题和vmware esx 一样直接使用物理资源
4.数据库物理设计：是否要使用文件日志隔离，是否要设计多个文件，当一个很大的数据文件显然在大负荷的情况下性能没有很好，但是可以放到raid10 甚至SAN上面可以 查看文章
Physical database layout vs. database size
5.表设计：表设计以尽量减少表所占用的空间为主，可以参考2篇文章1，
Performance Considerations of Data Types 2，
Importance of choosing the right LOB storage technique
6.聚集索引表和堆表比有一下几点好处：
1.性能普遍比堆表好，但是插入的性能没有堆表好
2.聚集索引的控件比堆表少
3.聚集索引表上删除的控件更容易释放控件，聚集索引是会通过ghost clear 回收但是堆只能手动创建
7.非聚集索引策略：(笔者:很有难度，作者也说这个是一门艺术)，1.尽量使用覆盖索引，（困难点，负荷比较复杂的时候基本上没办法用覆盖索引），2.窄索引（当你在日常调优的时候发现一条sql有问题，2个办法1.创建一个索引，2.修改原来的索引往往是添加key或者include，这样索引只可能越来越大，或者索引越来越多，索引越多造成的问题是写入性能下降，索引变宽造成1.分页，2.索引变肥，3.和索引变肥带来的一些问题如磁盘空间，内存空间等）
8.统计信息问题：统计信息过期会导致优化器出现错误的判断，给出了一个认为好的，但是很差的执行计划
9.应用程序设计问题：作者指出很多应用程序的开发并不考虑sql server 的运行，导致性能很差(笔者也认为这里面还有很多优化可以做)
10.数据维护问题：根据作者引用的文章主要有5点：1.数据库文件（物理设计）2.索引碎片3.统计信息4.一致性问题5.数据库备份
本文大意：
sql server 在 count(1) 没有其他select list 的时候会直接用io最小的索引
本文大意：
这个是一系列文章，作者优化生产1TB表的过程，1TB表通过模拟用户行为插入数据分析，分为(16, 32, 64, 128, 256)几个连接，每个连接插入134217728 /连接数个记录
第一篇是一个基线用于比对之后是否有性能的提示
第二篇是通过调整一个事务包含的insert数量来调整，其中出现当insert 为100每个事务的时候并比每个事务10个insert完成时间断。这个是因为当事务提交的是时候，事务产生的日志就要被写入磁盘，但是并不是单个日志记录，日志io的单元是日志块，vlf划分成动态的日志块（大小从512b到60kb不等）。
单个事务要产生4952B，刷入需要512的倍数所以是5120B
如果是10个记录产生的日志是48688B，刷新到日志文件是49152B，比上面的*10 要小所以速度快
当记录为100是产生日志489628B比60KB大所以会被拆分，当拆分后，反而增加了io
第三篇是通过日志文件和数据文件分离，性能提升明显
第四篇是通过优化网络，因为在第三篇的时候出现了网络瓶颈，使用多种方式其中使用负载分离提升最明显，即单独用一张网卡连接到数据文件的ISCSI的网络中，另外一张网卡负载网络活动和SATA的RAID
第五篇是网络的再次优化，1.增加传输的帧大小，2.把其中2张型号厂商相同的网卡连接到数据文件所在的存储，并修改传输算法为轮替，性能有显著的提升
本文大意：
作者使用不同的文件个数放到同一个raid里面做测试发现，文件个数为8的性能最好。8个文件分散在一个8*300G的raid10的性能 比 8个文件分散在2个4*300G的raid10性能稍差一些
本文大意：
1.磁盘队列过长：作者表示使用队列长度并不能看出是io性能问题，原因是sql server 为了榨干io使用了异步io的方式，这样就可能造成队列长度比较高的情况，应该要借助 avg.disk sec/read,avg.disk sec/write 2个指标如果超出常规值（5ms,12ms）就表示io出现瓶颈。（我认为在同一个时间点下，使用对垒长度和上面的2个指标是一样的），若sys.dm_io_virtual_file_stats中没有明显的问题，那么可能是其他程序造成的，那么就最好迁移到专用的io中。若能看到指定文件中io有问题那么可以考虑是否是以下问题引起：1.比较2的执行计划，2.和其他的文件io 分开，3.增加内存，避免读io
2.如何合理的配置一个log文件大小（笔者还没有考虑过这个问题，也无从下手）需要一下信息：1.最大的dml事务的负荷,2.最大的bulk操作,3.最大的索引重建
如果产生了100mb的日志，当然也要产生100mb的空间用于回滚。
如果log异常的大，可以考虑一下问题：
1.数据库在full或者bulk_logged没有执行过日志备份
2.长事务导致了所有事务日志记录不能被截断
3.数据库镜像环境下，事务日志记录不能从主体复制到镜像
4.事务日志复制 原理同上
3.最小日志问题：最小日志记录，只记录页的分配和释放log其他都不分配，只有在tempdb的行版本中不会生产日志，因为不需要回滚行版本。sql server肯定会记录log的，若sql server 不记录日志，那么sql server 没有办法做回滚，当出现错误页，空间不够，那么数据库就变得不可用而停止。
本文大意：
若执行计划有问题从以下几个方面考虑：
1.实际执行行数和预计的不同，2个部分 要不是统计信息，要不就是存储过程中参数的数据不均衡造成
2.order by 可能是有些索引miss造成
3.join通常是不被理解的，但是没有选择好也会造成很多问题
4.扫描的出现并不一定是最不理想的，1.如索引没有提供全部数据导致需要去表中取数据反而加大了开销，所以sql server选择扫描2.选择度可能太低，所以sql server 使用扫描
5.因为隐式数据库类型转化导致的扫描
6.key/rid lookup,使用非聚集索引没有值的时候需要lookup
7.花费比较大的操作通常是一个比较好的切入点，可以通过修改代码，统计信息，索引修改
8.并发查询并不是都不好，比如在报表系统中，如果在oltp中就可能是一个问题
本文大意：
内存不足的问题：
1.读io，因为内存不足
2.写io，造成lazywrite
3.发生Resource_semaphore因为查询需要内存
4.plancache 没内存导致查询编译
页密度低再次内存浪费：1.行太宽，如5000B一行那么每页就有3000B被浪费2.分页3.因为行删除
数据密度低造成的问题：1.需要更多的空间存数据2.更多的io读数据3.更多的buffer存数据
低页密度的解决办法：1.垂直分表，减少列宽2.修改索引键列 把非顺序的改成顺序的，减少分页3.放大fillfactor减少分页4.重建有问题的索引5.考虑压缩
作者在文中给出了一个脚本查看内存的浪费
本文大意：
页生命周期设置为300是有问题的，根据
Finding what queries in the plan cache use a specific index证明应该使用datacache/4gb*300
现在的系统基本上都是使用NUMA，每个都有自己的节点，lazy write线程，buffer free list，那么 buffer manager:page life expectancy这个计数器就可能变得不科学，PLE是每个小的buffer pool相加的平均值，那么问题也就是如果一个小的buffer pool发生的内存问题，但是看ple 就有可能没有什么问题。因此就会出现lazy write100+但是ple基本没有变化的情况，在numa的机器上，
应该看 buffernode:page life expectancy而不是buffer manager里面的
本文大意：
过多索引会造成的一些问题：1.要修改buffer pool中的非聚集索引2.索引修改搞成更多的脏数据3.索引修改造成的日志增长
4.还有一下一些操作会因为日志变大影响：cdc日志读取，复制日志读取，日志备份，数据库镜像，磁盘空间，日志传送
5.锁变多6.分页变严重7.一致性检查时间变长8.碎片检查9.统计信息更新10.备份文件的大小
解决办法：
1.通过sys.dm_db_index_usage_Stats确定哪些是要被删除的
2.删除重复的非聚集索引
3.看有没有索引合并的可能
本文大意：
若聚集索引键没有选好可能会造成大量的浪费，那么如果聚集索引：小，静态，顺序，唯一有什么好处呢？
以下是小的聚集索引带来的好处：
1.减少磁盘空间使用，和备份文件的大小
2.备份恢复变快
3.非聚集索引变小，dml操作和索引碎片移除变得很快
4.一致性检查变快
5.页密度增加，占用内存变小，索引处理变快
6.减少log的生产，日志备份，日志传送，复制，数据库镜像速度加快
“CSelCalcCombineFilters_ExponentialBackoff” Calculator
本文大意：
通篇讲，在2014之前的版本中，评估行数and=rows*col1的selective*col2的selective，而在2014中是=rows*col1的selective*sqrt(col2的selective)
并且多了query_optimizer_estimate_cardinality 事件可以用来查看如何参数的评估行数
本文大意：
作者使用2个多列统计信息来说明，老的CE和新的CE的区别。
一种比较重要的一句话：In SQL Server 2014, regardless of multi-column statistics, the estimate for multiple conjunctive (AND) predicates is calculated using exponential back-off.
意思是说，在sql server 2014中，是不管多列统计信息的，评估用and连接的谓词都是通过指数回退的方式，也就是说 p1*sqlit(p2)*密度。所以在例子中照成老的ce表新的ce准确。
本文转自 Fanr_Zh 博客园博客，原文链接：http://www.cnblogs.com/Amaranthus/archive/2013/05/18/3085965.html
，如需转载请自行联系原作者
