【案例讨论】从案例引发的对SQL Server性能调优的思考

yanyangtian4502

那我说说索引的问题吧
这个问题 应该大家比较关心的

谈到索引，主要就是以下几个方面：
1.        索引碎片分析
2.        索引统计信息分析
3.        缺失索引分析
4.        无用索引分析
5.        高维护索引分析
6.        最常用索引分析
7.        索引使用资源分析（内存，磁盘，CPU）
8.        索引编辑（增删改查）

yanyangtian4502

对于，索引碎片，主要就是在有索引的表上面不断的进行数据操作（增，删，改）
从而导致索引页上面出现很多的碎片空间，这一点和我们磁盘的碎片产生的道理类似，
索引碎片 找起来也简单，我也收集了一些查询，可以很快的找出碎片问题：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempFragmentation
SELECT TOP 20
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC'
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
DROP TABLE #TempFragmentation

看到的效果图如下：

1.      开始重建碎片率大于80%的索引，语句如下：

ALTER INDEX 索引名字 ON 表名 REBUILD

注意：

索引名字：就是需要重建的索引的名字

表名：就是索引所在的表

例如，ALTER INDEX IX_keywordinfo_Type ON KeyWordInfo REBUILD

2.      按照上面的方法一个个的将索引重建，注意：建议在晚上或者周末的时候重建，否则，将会导致用户无法使用数据库。也可以在建立的时候采用online的方式，这种方式会异步的重建。

hwtong

yanyangtian4502 发表于 2012-6-6 09:54
对于，索引碎片，主要就是在有索引的表上面不断的进行数据操作（增，删，改）
从而导致索引页上面出现很多 ...

如果是Enterprise版本,可以加上online参数,就会好点

hyrongg

Fragmentation 超过30%我就rebuild 了，表数据超过3千万，rebuild 一个index需要2-3 分钟，基本上需要downtime才能进行，
顺便问一下高人,在运行sys.dm_db_index_physical_stats很容易遇到内存问题，100GB的db,list dirty indexes :
SELECT object_name(ps.OBJECT_ID) table_name,b.name index_name,ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
     and avg_fragmentation_in_percent>30
     and name is not null
ORDER BY avg_fragmentation_in_percent desc

Page life expectancy 可以从几万一下子掉到几十，online根本不敢做index clean.

CareySon

hyrongg 发表于 2012-6-6 12:23
Fragmentation 超过30%我就rebuild 了，表数据超过3千万，rebuild 一个index需要2-3 分钟，基本上需要downt ...

这种东西不都是半夜黑灯下火的时候扔给agent做嘛，干嘛online去做呢

hyrongg

CareySon 发表于 2012-6-6 16:16
这种东西不都是半夜黑灯下火的时候扔给agent做嘛，干嘛online去做呢

系统7*24小时都很忙，越是黑灯瞎火的时候越忙。尝试早晨7点多用Agent,deadlock 一片啊

CareySon

hyrongg 发表于 2012-6-6 16:24
系统7*24小时都很忙，越是黑灯瞎火的时候越忙。尝试早晨7点多用Agent,deadlock 一片啊

那应该刚开始的时候更好的设计数据库来减少碎片...减少维护次数..

我们这块的程序有个功能，deadlock作为牺牲品的语句会被记录下来，然后re-try就行了。

我见过最恶心的是用GUID做主键...那碎片的百分比增长的快啊。。

yanyangtian4502

hyrongg 发表于 2012-6-6 12:23
Fragmentation 超过30%我就rebuild 了，表数据超过3千万，rebuild 一个index需要2-3 分钟，基本上需要downt ...

你说的很对！你们只有一个数据库，有没有搞均衡 或者备用的？
在一台上面搞，确实容易出问题

Obuntu

句句都是精华呀。看来楼主已经是得心应手了。
关于IO，之前搞过两篇文章：
http://topic.csdn.net/u/20101116 ... 9425bdeb.html?45133
http://topic.csdn.net/u/20101116 ... ede436505.html?7433

hwtong

Obuntu 发表于 2012-6-6 20:22
句句都是精华呀。看来楼主已经是得心应手了。
关于IO，之前搞过两篇文章：
http://topic.csdn.net/u/ ...

多谢Obuntu兄的支持