服务器的性能优化那些事
现在当凡做开发的都会碰到一点性能优化的事情，这里我将根据我优化的一些步骤来给大家说明下：
背景 ：在软件开发的前期，可能我们注重的是开发功能和灵活性。虽然有少部分经验丰富的人会在前期会注重，但是少之又少，尤其是做项目的情况，客户追，老大追，老板追。一般采取的方案就是先实现的功能再说啦！！但随着时间推移，系统将运行越来越慢。。。。。
碰到的现象:
服务器的内存居高不行，尝试重启数据库后，内存一下就降到很低，然后逐步增加，到后来与重启前一样。 有些页面查询时间超过5~6分钟，而且还常常报 timeout.但CPU也只在有限的时间的内会上升到60%左右的高度。
查找过程:
1：内存居高不下，肯定是跟数据有关系，系统已经运行3年了数据也有千万级，最开始1~2年还比较好，但从第3年开始就陆陆续续出现问题，所以我决定追查下，服务器的性能监控。使用的工具很简单，就是服务器上的Performance Monitor.监控的的数据包括: 内存，CPU,IO,网络.
Processor:
<Counter>\\DB SERVER\Processor Information(*)\Processor Frequency</Counter>
<Counter>\\DB SERVER\Processor Information(*)\% of Maximum Frequency</Counter>
<Counter>\Processor(_Total)\% Processor Time</Counter>
<CounterDisplayName>\\DB SERVER\Processor Information(*)\Processor Frequency</CounterDisplayName>
<CounterDisplayName>\\DB SERVER\Processor Information(*)\% of Maximum Frequency</CounterDisplayName>
<CounterDisplayName>\Processor(_Total)\% Processor Time</CounterDisplayName>
Memory:
<Counter>\Memory\Available MBytes</Counter>
<Counter>\Memory\Pages/sec</Counter>
<Counter>\Memory\Cache Bytes</Counter>
<Counter>\Cache\Dirty Pages</Counter>
<CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
<CounterDisplayName>\Memory\Pages/sec</CounterDisplayName>
<CounterDisplayName>\Memory\Cache Bytes</CounterDisplayName>
<CounterDisplayName>\Cache\Dirty Pages</CounterDisplayName>
PhysicalDisk:
<Counter>\PhysicalDisk(_Total)\Avg. Disk Bytes/Read</Counter>
<Counter>\PhysicalDisk(_Total)\Avg. Disk Bytes/Write</Counter>
<Counter>\LogicalDisk(_Total)\% Disk Time</Counter>
<Counter>\LogicalDisk(_Total)\Avg. Disk Write Queue Length</Counter>
<Counter>\LogicalDisk(_Total)\Disk Transfers/sec</Counter>
<Counter>\LogicalDisk(_Total)\Avg. Disk Bytes/Transfer</Counter>
<CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Bytes/Read</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Bytes/Write</CounterDisplayName>
<CounterDisplayName>\LogicalDisk(_Total)\% Disk Time</CounterDisplayName>
<CounterDisplayName>\LogicalDisk(_Total)\Avg. Disk Write Queue Length</CounterDisplayName>
<CounterDisplayName>\LogicalDisk(_Total)\Disk Transfers/sec</CounterDisplayName>
<CounterDisplayName>\LogicalDisk(_Total)\Avg. Disk Bytes/Transfer</CounterDisplayName>
MS SSQL:
<Counter>\SQLServer:Access Methods\FreeSpace Scans/sec</Counter>
<Counter>\SQLServer:Access Methods\Full Scans/sec</Counter>
<Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\SQLServer:Buffer Manager\Free pages</Counter>
<Counter>\SQLServer:General Statistics\User Connections</Counter>
<Counter>\SQLServer:Latches\Total Latch Wait Time (ms)</Counter>
<Counter>\SQLServer:Locks(_Total)\Lock Timeouts/sec</Counter>
<Counter>\SQLServer:Locks(_Total)\Lock Wait Time (ms)</Counter>
<Counter>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</Counter>
<Counter>\SQLServer:Memory Manager\Memory Grants Pending</Counter>
<Counter>\SQLServer:Memory Manager\Target Server Memory (KB)</Counter>
<Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>
<Counter>\SQLServer:SQL Statistics\Batch Requests/sec</Counter>
<Counter>\SQLServer:SQL Statistics\SQL Re-Compilations/sec</Counter>
<CounterDisplayName>\SQLServer:Access Methods\FreeSpace Scans/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Access Methods\Full Scans/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
<CounterDisplayName>\SQLServer:Buffer Manager\Free pages</CounterDisplayName>
<CounterDisplayName>\SQLServer:General Statistics\User Connections</CounterDisplayName>
<CounterDisplayName>\SQLServer:Latches\Total Latch Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Lock Timeouts/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Lock Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Memory Grants Pending</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\SQLServer:SQL Statistics\Batch Requests/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:SQL Statistics\SQL Re-Compilations/sec</CounterDisplayName>
<Counter>\SQLServer:Access Methods\FreeSpace Scans/sec</Counter>
<Counter>\SQLServer:Access Methods\Full Scans/sec</Counter>
<Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\SQLServer:Buffer Manager\Free pages</Counter>
<Counter>\SQLServer:General Statistics\User Connections</Counter>
<Counter>\SQLServer:Latches\Total Latch Wait Time (ms)</Counter>
<Counter>\SQLServer:Locks(_Total)\Lock Timeouts/sec</Counter>
<Counter>\SQLServer:Locks(_Total)\Lock Wait Time (ms)</Counter>
<Counter>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</Counter>
<Counter>\SQLServer:Memory Manager\Memory Grants Pending</Counter>
<Counter>\SQLServer:Memory Manager\Target Server Memory (KB)</Counter>
<Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>
<Counter>\SQLServer:SQL Statistics\Batch Requests/sec</Counter>
<Counter>\SQLServer:SQL Statistics\SQL Re-Compilations/sec</Counter>
<CounterDisplayName>\SQLServer:Access Methods\FreeSpace Scans/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Access Methods\Full Scans/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
<CounterDisplayName>\SQLServer:Buffer Manager\Free pages</CounterDisplayName>
<CounterDisplayName>\SQLServer:General Statistics\User Connections</CounterDisplayName>
<CounterDisplayName>\SQLServer:Latches\Total Latch Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Lock Timeouts/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Lock Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Memory Grants Pending</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\SQLServer:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\SQLServer:SQL Statistics\Batch Requests/sec</CounterDisplayName>
<CounterDisplayName>\SQLServer:SQL Statistics\SQL Re-Compilations/sec</CounterDisplayName>
NetWorkInterface:
<Counter>\Network Interface(*)\Bytes Sent/sec</Counter>
<Counter>\Network Interface(*)\Bytes Received/sec</Counter>
<Counter>\Network Interface(*)\Bytes Total/sec</Counter>
<CounterDisplayName>\Network Interface(*)\Bytes Sent/sec</CounterDisplayName>
<CounterDisplayName>\Network Interface(*)\Bytes Received/sec</CounterDisplayName>
<CounterDisplayName>\Network Interface(*)\Bytes Total/sec</CounterDisplayName>
设定以上参数通过2个星期的追踪发现，服务器的各项指标都比较正常，而内存占用比较高，但是速度不应该会慢的如此的程序。所以还需要继续追查是否有其他的原因导致。
2：由于看到数据库服务器的内存占用比较大，所以先从内存追查开始，在数据库服务器设置追踪SQL SERVER PROFILE工具追踪，是否有锁表或者死锁的情况。具体设置如下
果然追踪有收获，发现有很多情况死锁。进一步追踪死锁发生的原因主要是，大量的使用了事务，而在事务中有重复更新的情况。
如下
1：Update A set col1= 'a' where id=1
2： update B set col1 ='b' where id =2
3： update A set col1='b' where id=1
其实这种更新方式在单线程里面是没有问题的。但是在多线程中就有问题，原因在哪呢，举个例子:有线程 1，和线程2同时执行该过程。 线程1执行比较快一点，当执行到2步骤的时候，线程2才开始执行步骤1，这样就导致了资源互相抢占的情况。也就是线程1要先更新步骤2才能更新步骤3， 而线程2要先更新步骤1在更新步骤2. 而步骤1和步骤3都是同一笔数据。
知道这个问题候，将调整写法。可能有朋友会说，这个写法真奇怪，更新同一笔数据要分到不同地方去写。 其实原因呢，就是在逻辑写法上说是处理完一段逻辑就该下状态， 处理另外一段逻辑修改下这个状态值。所以逻辑上也是讲的通的。
经过处理后，发现死锁的情况很少了。但是服务器依然比较慢。而且在没有更新，没有事务的情况的情况下，也会有超时的现象。难道数据量很大？我继续追查原因，这次我该如何查呢，首先我要看看这个超时的现象有多严重，我后面发现大部分都超时。所以我想可能不是一两个语句造成的哦。看看影响查询的性能的问题有哪些？索引。索引我都加了哦。怎么还会慢呢？可能索引失效了，失效的原因可能是索引碎片了呢。所以我将查看服务器的索引碎片
3：检查服务器的索引碎片
查询的语句有两个
--查当前数据库索引碎片
select OBJECT_NAME(a.object_id) as objectid,a.index_id as indexid,b.name as indexName,a.partition_number as partitionnum,
a.avg_fragmentation_in_percent as frag
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') a
left join sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
where a.avg_fragmentation_in_percent >10.0 and a.index_id>0
--查索引碎片(较详细)
DBCC SHOWCONGTIG
通过查询发现数据库服务器上碎片非常严重，基本都是90%以上。其实索引碎片一般的情况下，我们都要做定期整理。尤其是不断的更新的表更应该如此。所以我花了一晚上的时间去重建索引。所用的命令很简单
DBCC DBREINDEX('TABLE NAME','',90)
这里要说下，其实ms sql提供两种方式的索引碎片整理。Rebuild：重建，这个是最有效的。他将会删除以前的索引信息，在重新写。当然他也为你提供一个填充因子，然后不要把空间占满了。为啥不占满呢。是因为他怕有相邻的索引数据过来时，还可以在这个页上插入。我这里是设置的是90.
reorgnize: ALTER INDEX ALL ON ‘TABLE NAME’ REORGANIZE。这个方式主要是将当前的索引重新组织，通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值
规则： 当对应索引的外部碎片值介于10-15之间，内部碎片值介于60-75之间时使用重组，其它情况就应该使用重建。
经过重建后，有个惊奇的发现，就是内存会加下一些了。以前达到90%，现在会下载到80%左右。看来重建索引还是有效的哦。现在查看超时的时间会比较慢了。实用sp_lock去查询，发现占用整个资源的情况也很少发生了。说明之前这几个动作是有效的。客户在使用的时候，也表示页面的速度比以前快了。不过有几个页面的速度还是慢。我想这是由于某几个的写法不规范造成了。
果然在后面的查询发现，有些写法会造成性能问题。
1：存储过程使用临时表，如果数据量比较大(何为大呢，我的定义是临时表的数据达到百万级)时，必须要加上主键。然后用主键再去跟其他表做联合。
2：全局的临时表和局部的临时表在性能上是有差别的。在存储过程中使用是最好用局部的临时表。至于原因我目前还不知道？希望有高人解答下。
