一般而言,在诊断数据库性能问题的时候,我们会在早期做一些快速的分析,例如查看等待信息,这里,会通过sys.dm_os_wait_stats这个DMV来快速的一窥情况,通过这个DMV来查看数据库中有哪些资源处于等待,因为很多的时候,数据库的性能变差,问题就出现在等待上面。分析等待,是个不错的切入点。可以让我们开始“顺藤摸瓜”


下面就举个例子,例如,如果发现PAGEIOLATCH_SH这个等待很多,那么可能就说明很多的回话都在获取缓存页的时候有延迟了。这个情况的发生,可以是因为有很多的回话,或者某一个回话请求可过多的数据页,但是此时这些需要的数据页并没有加载在数据缓冲区中。SQL Server要为每一个数据页去分配缓冲页,同时,也会在数据页上面放置一个锁。这里可能的瓶颈问题就是磁盘I/O。可能是磁盘子系统无法快速的返回数据页来响应这个多会话对,导致了这个等待的产生。


到这里,可能有人就认为:是不是磁盘子系统太慢了,要去购买更好的磁盘。


注意:分析到这里为止,这是说明:存在磁盘I/O问题,是因为磁盘子系统响应过慢,但是不说明磁盘子系统不满足现在的I/O。


那么这个时候,我们要进一步的分析,此时,因为我们随着I/O这条线往下面走,确认:到底是不是I/O产生了问题。


此时,我们可以进一步的查看sys.dm_io_virtual_file_stats,来证明是不是因为文件的读写问题产生了性能问题。我们也可以通过这个DMV来查看数据库中的每个文件的I/O的读写情况。

另外,作为补充的和确认的步骤,也要查看Physical Disk\Avg. Disk Reads/sec,Physical Disk\Avg. Disk Writes/sec性能计数器。】


如果这些值都很高,那么,我们基本可以估计:是I/O问题了。



确定了基本的问题的方向之后,那么我们就进一步的看看,这个问题产生的“罪魁祸首”。

此时,我们可以查看sys.dm_exec_query_stats来查看计划缓存,主要查看那最多物理读取的计划,然后查看这个计划对应的查询语句,看看这个查询是否因为“缺失索引”产生的问题。


1、不要数据库文件,例如日志,数据文件放在系统盘中。

2、日志,数据文件要分开,最好将系统的数据库与我们的数据库分开放在不同的磁盘中

3、建议将tempdb放在单独的磁盘。因为日志文件是按顺序写的,而数据文件是随机读写的,所以,放    在一起,势必使得磁盘的磁头来回的寻道。

4、在选择磁盘的时候,不要一味的考虑容量,而不考虑吞度量,例如要购买一个1TB的磁盘,最好是    买几个小的磁盘,例如300GB的,将之拼接为1TB。主要是为了使得磁盘读取数据的并行度加大



另外,还需要注意数据库内部的一些信息:

1.数据页每个大小事8K,而数据库在分配的时候,每次不是按照页来分配的,而是按照块,即,每次分配64K的空间,也就是8个页,那么我们在为每一个磁盘分区分卷的时候,最好将之定位64K,毕竟磁盘过多的分页,这个道理和毕竟过多的数据库分页一样的。

2. 要检查磁盘的扇区是否对齐,这个问题发生在win2003以及之前的版本,win2008不用管了。


关于磁盘扇区对齐,这个问题搞定之后,可以将磁盘的I/O行提升20%~30%。

可以采用diskpart命令来搞定。方法如下(注意,在磁盘扇区对齐的时候,要将分区里面的文件备份到其他地方):

1.“开始”->"运行",输入“diskpart”

2、输入“list disk”,这里将会列出所有的磁盘,注意:是物理的磁盘,如果是采用的阵列,那么这就会列出所有的磁盘。

3、select disk XXX”,其中XXX就是磁盘的编号,例如0,1,2等

4、然后查询这个磁盘的分区,运行“list partition”,将分区全部列出来

5、对齐扇区,运行“create partition primary algin=64”


不管数据库大小,一般是设置成按多少MB增长,而不是百分比.

适当时候,可以关闭数据库自动自动增长,手动设置数据库增长大小,自动增长不合适会产生磁盘碎片



如何查看等待的问题,因为有很多的等待是系统的等待,不是我们要研究,下面的脚本非常实用:

SELECT TOP 10

wait_type ,

max_wait_time_ms wait_time_ms ,

signal_wait_time_ms ,

wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

AS percent_total_waits ,

100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

AS percent_total_signal_waits ,

100.0 * ( wait_time_ms - signal_wait_time_ms )

/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0 -- 移除为0的wait_time

AND wait_type NOT IN -- 将不相关的等待移除

( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE' )

ORDER BY wait_time_ms DESC




磁盘整列

RAID02块磁盘就可以,读写性能佳,使用率100%,不容错

RAID12块磁盘就可以,读写性能一般,使用率50%,容错(存放单个的日志文件,日志文件的按顺序读写)

RAID53块磁盘就可以,读性能佳,写一般(10:1),使用率 磁盘容量为(N-1)*磁盘容量,容错

RAID104块磁盘就可以,读写性能佳,使用率50%,不容错


--查询每一个数据库占用的内存

SET TRAN ISOLATION LEVEL READ UNCOMMITTED

SELECT

ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName

, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))

AS [Size (MB)]

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

ORDER BY DatabaseName


--查询表或者索引占用的内存

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

OBJECT_NAME(p.[object_id]) AS [TableName]

, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]

, ISNULL(i.name, '-- HEAP --') AS ObjectName

, COUNT(*) AS NumberOf8KPages

FROM sys.allocation_units AS a

INNER JOIN sys.dm_os_buffer_descriptors AS b

ON a.allocation_unit_id = b.allocation_unit_id

INNER JOIN sys.partitions AS p

INNER JOIN sys.indexes i ON p.index_id = i.index_id

AND p.[object_id] = i.[object_id]

ON a.container_id = p.hobt_id

WHERE b.database_id = DB_ID()

AND p.[object_id] > 100

GROUP BY p.[object_id], i.name

ORDER BY NumberOf8KPages DESC


--性能监视器--计数器

SQL Server:Buffer Manager\Buffer Cache Hit Ratio

SQL Server:Buffer Manager\Page Life Expectancy

SQL Server:Buffer Manager\Free Pages

SQL Server:Buffer Manager\Free List Stalls/sec

SQL Server:Buffer Manager\Lazy Writes/sec

SQL Server:Memory Manager\Target Server Memory (KB)

SQL Server:Memory Manager\Total Server Memory (KB)

SQL Server:Memory Manager\Memory Grants Outstanding

SQL Server:Memory Manager\Memory Grants Pending


在上面的计数器中,纠正一个传言:Page Life Expectancy一般不要超过300.

300这个值是微软在很多年前的文档中给出的,现在的很多的书籍和资料,也是这样写的。但是这是不对的。因为在很多年之前,也就是sql server2000的时代,4G的物理内存就认为是非常的大了,而且sql server可用的数据缓冲池最大也只有1.6GB。但是,现在,服务器以及数据库的可用的内存已经发生了很大的变换,SQL Server使用非常多的内存。所以这个数据300也要发生变换,现在这个值计算公式为:(服务器的物理内存总数/4)*300,如果一个32GB的服务器,那么这个值就是2400



关于DMV呢,基本都是见名知意的:

sys.dm_exec_query_memory_grants:可以用来找出哪些查询在等待着分配内存。

sys.dm_os_memory_cache_counters:给出现在使用内存的一个快照。

sys.dm_os_sys_memory:给出了现在操作系统使用内存的情况

sys.dm_os_memory_clerks:给出了与SQL Server中每个组件使用的内存的情况。



下面我们来看看“内存分页”的问题以及解决办法。

自从SQL Server 2005 SP2开始,SQL Server就会发生工作区裁剪的问题,就是说:SQL Server使用的内存在操作系统发出了内存紧急命令之后,SQL Server占用的会被一下子收回去一部分。不用说,这对SQL Server性能影响是非常的大的。


同时,也会在Window Log中也会记录“a significant part of SQL Server process memory has been paged out”。


我们可以查看得出这个问题。


发生这个问题的原因主要如下:

1. 不正确的设置:max server memory 这个选项。

2. Lock Pages in Memory没有被使用。

3.大量的操作系统缓存被用来非缓冲的I/O操作,例如,在操作系统上面copy文件之类的。

4.硬件驱动问题。


解决这个问题最快的办法就是:强制SQL Server锁住它使用的内存,这通过设置Lock Pages in Memory为true来搞定。



索引碎片,主要就是在有索引的表上面不断的进行数据操作(增,删,改)

从而导致索引页上面出现很多的碎片空间,这一点和我们磁盘的碎片产生的道理类似,

索引碎片 找起来也简单,我也收集了一些查询,可以很快的找出碎片问题:


开始重建碎片率大于80%的索引,语句如下:

ALTER INDEX IX_gdid ON t_product REBUILD


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


有些可以Fragmentation 超过30%就rebuild 了,利用sys.dm_db_index_physical_stats

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


--这个语句去查询哪些sql的逻辑读很高,之后进行优化

SELECT SS.SUM_EXECUTION_COUNT,

               T.TEXT,

               SS.SUM_TOTAL_ELAPSED_TIME,

               SS.SUM_TOTAL_WORKER_TIME,

               SS.SUM_TOTAL_LOGICAL_READS,

               SS.SUM_TOTAL_LOGICAL_WRITES

FROM (SELECT S.PLAN_HANDLE,

                        SUM(S.EXECUTION_COUNT) SUM_EXECUTION_COUNT,

                        SUM(S.TOTAL_ELAPSED_TIME) SUM_TOTAL_ELAPSED_TIME,

                        SUM(S.TOTAL_WORKER_TIME) SUM_TOTAL_WORKER_TIME,

                        SUM(S.TOTAL_LOGICAL_READS) SUM_TOTAL_LOGICAL_READS,

                        SUM(S.TOTAL_LOGICAL_WRITES) SUM_TOTAL_LOGICAL_WRITES

         FROM SYS.DM_EXEC_QUERY_STATS S

         GROUP BY S.PLAN_HANDLE

         ) AS SS

         CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SS.PLAN_HANDLE) T

ORDER BY SUM_TOTAL_LOGICAL_READS DESC