2011年8月 的存档
2011八月28

Sql Server2005性能诊断

引用:http://technet.microsoft.com/zh-cn/library/cc966540(en-us).aspx
Sql Server速度变慢问题主要由三个方面引起,可从这三个方面入手分析问题
  1,资源瓶颈:CPU、I/O、内存等。
  2,临时表瓶颈:tempdb各个数据库共用,资源紧张时会引起性能降低。
  3,用户比较耗时的sql查询。

一,CPU瓶颈分析。瓶颈症状:打开perfmon, 监视 processor:% Processor Time若长期>80%,或者任务管理器,说明可能是CPU原因。
1,分析是否是查询语句引起
 这里涉及一个动态视图:sys.dm_exec_query_stats,返回每条查询语句运行的时间
关键栏位有:
 1)sql_handle:表示包含查询的批查询或存储过程的标记,若sql_handle相同,代表查询在同个批处理或同一个存储过程内。可将此值传入    sys.dm_exec_sql_text中获取查询语句。同个存储过程或批处理语句的sql_handle相同,如何分辨是哪个查询语句得到了执行,
可用statement_start_offset、statement_end_offset来查出此查询语句的真容。
2)statement_start_offset:查询在其批查询或持久化对象文本中的开始位置
3)statement_end_offset:查询在其批查询或持久化对象文本中的结束位置
4)execution_count: 计划自上次编译以来所执行的次数。
5)total_worker_time:此计划自编译以来执行所用的 CPU 时间总量(以微秒为单位报告,但仅精确到毫秒)。
6)last_execution_time:上次开始执行计划的时间。
故分析耗CPU语句方法可得出:

--若返回的dbname,objectname为空,代表是动态sql语句,不是存储过程
SELECT
    dbname,ObjectName,Sql,
    SUM([总消耗CPU 时间(ms)]) [总消耗CPU 时间(ms)],
    SUM([运行次数])[运行次数],[查询语句],[所在存储过程],
    SUM([平均消耗CPU 时间(ms)])[平均消耗CPU 时间(ms)]    
    FROM (
    SELECT TOP (50)
        qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
        total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
       
        dbo.SQLSig(SUBSTRING(qt.text,qs.statement_start_offset/2+1, --利用sqlsig函数进行参数化,以屏蔽传来的参数,使之标准化
            (case when qs.statement_end_offset = -1
            then DATALENGTH(qt.text)
            else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1),4000)
        as [查询语句], qt.text [所在存储过程],
        qt.dbid, dbname=db_name(qt.dbid),
        qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    ORDER BY [平均消耗CPU 时间(ms)] DESC
    )M
    GROUP BY [查询语句],[所在存储过程],dbid, dbname,objectid,ObjectName
    ORDER BY [平均消耗CPU 时间(ms)] DESC

注:测试sys.dm_exec_query_stats 时可先清除缓存DBCC FREEPROCCACHE 再执行查询语句分析。

2,分析是否是重新编译引起,重新编译比较费时。
1)perfmon:

    SQL Server: SQL Statistics: Batch Requests/sec :每秒钟接收的请求数

    SQL Server: SQL Statistics: SQL Compilations/sec:每秒钟的编译数

    SQL Server: SQL Statistics: SQL Recompilations/sec:每秒钟的重新编译数

2)profile:SP:Recompile / SQL:StmtRecompile.
3)sys.dm_exec_query_stats 有一个栏位plan_generation_num,计划编译次数,可用此来分析最常编译的计划。
select top 25
    plan_generation_num,
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
        as stmt_executing,
    qt.text,
    execution_count,
    sql_handle,
    dbid,
    db_name(dbid) DBName,
    objectid,
    object_name(objectid,dbid) ObjectName
from sys.dm_exec_query_stats as qs
    Cross apply sys.dm_exec_sql_text(sql_handle) qt
where plan_generation_num >1
--AND qs.last_execution_time >='2011-08-28 10:00' 限定时间
order by plan_generation_num desc

二,内存瓶颈分析。瓶颈症状:打开perfmon, 监视 Paging File: %Usage长期>80%。Paging File:Usage: 分页空间使用百分率

三,I/O瓶颈分析:症状:计数器PhysicalDisk:%Disk Time >80% , Avg.Disk Queue Length:>2
1,%Disk Time :所选磁盘驱动器忙于为读或写入请求提供服务所用的时间的百分比。
Avg. Disk Queue Length 指读取和写入请求(为所选磁盘在实例间隔中列队的)的平均数。
不过,若出现上述情况,也可以是内存不足引起。

2,sys.dm_exec_query_stats 有栏位total_logical_writes:此计划自编译后在执行期间所执行的逻辑写入总次数。total_logical_reads:此计划自编译后在执行期间所执行的逻辑写入总次数。
故查询最耗I/O的语句为:

select top 50
(total_logical_reads/execution_count) as [平均逻辑读取次数],
(total_logical_writes/execution_count) as [平均逻辑写入次数],
(total_physical_reads/execution_count) as [平均对象读取次数],
 Execution_count 运行次数,
substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法]
from sys.dm_exec_query_stats  as r
    cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
--WHERE r.last_execution_time >='2011-08-28 10:00' 限定时间
order by
 (total_logical_reads + total_logical_writes) Desc

四,tempdb瓶颈。空间资源耗尽引起。
查询:
Select
    SUM (user_object_reserved_page_count)*8 as user_objects_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
    SUM (version_store_reserved_page_count)*8  as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
其中:freespace_kb  要足够大才可。
2011八月28

SQL SERVER2005性能调校

sql server 评论关闭

1,Query Notification:当用户访问的数据改变时,会主动通知前台程序,避免了以往的轮询机制。
2,Event Notification:在用户执行DDL或DML语言后,需要立刻处理的商业逻辑可以通过触发器来完成,但由于触发器执行时会与上述语句绑在一个事务中,因此会拖慢系统响应时间,利用Event Notification可以借由队列积累需求,在系统不忙碌时执行这些需求。
3,当CPU,内存,I/O,带宽等使用率持续超过80%时,表示此系统已达临界门槛,此后系统整体性能必定急速下滑。
4,用户端硬件差也有可能拖慢整个系统,原因:当用户要了一大堆数据,但它没有能力立刻处理完这些服务器源源不断传来的网络包,因此Sql Server对该用户的网络输出缓存区也就塞满了数据,此时并不影响其他用户,但当缓存区满时,扫描数据的工作也将暂停,这时扫描数据所持有的锁不能释放,导致其它用户可能被锁定。
5,因为磁盘是通过磁性和机械臂的运行来访问数据,因此效率远低于CPU和RAM等通过电子运行的速度。
6,学习资料:http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

2011八月13

winserver2003负载均衡架设

windows server 评论关闭

方案:双网卡+单播+VLAN
多播很多路由设备不运行,会配置不成功!

双网卡的好处:
双网卡单播时,因为主机之间不能互相通信,将设置内网通讯的网卡,也就是群集设置中的心跳。同时,为群集子网上的每个主机使用两个网络适配器有助于让网络通信通过群集主机。传入客户端通信流过集线器,以便将其同时传递给所有主机,而传出通信则直接流到交换机端口,故在某些情况下,第二个网络适配器可以提高总体网络性能。

建议将两台web服务器单独划出到一个Vlan里,这样可以限制NLB单播模式时的广播流量

负载均衡,NLB:Network Load Balancing。

是提供相同服务的一系列服务器同时监听服务请求,并允许在同一时间运行多个应用程序实例。NLB的核心是位于网络适配器驱动和网络层之间的 WLBS.SYS的筛选器驱动。NLB把每个IP数据包分发到所有群集节点,并根据数据包的源地址、目标地址、传输层协议、端口、群集的配置参数以及算法做出由某个节点处理而其他节点丢弃此数据包的统一决定。

单个群集的最大节点数为32,如果还不能满足需要,可以使用Round-Robin Domain Name Service把请求映射到多个群集上(但也因此引入了单点故障DNS,除非DNS冗余)。

windowsserver2003的负载均衡是在端口级进行操作,如端口选择80代表只针对web服务实现负载均衡

命令:nlbmgr nlbmgr /help

参考:http://support.microsoft.com/kb/323437/zh-cn

网络负载平衡常见问题

http://www.microsoft.com/china/technet/prodtechnol/windowsserver2003/technologies/clustering/nlbfaq.mspx

http://blog.csdn.net/coofucoo/article/details/5296632

操作步骤:
准备:两台服务器,双网卡,一网卡对外,另一对内,若在局域网中可通过一网络直线两服务器,形式内部网


在实现网络负载平衡的每一台计算机上,只能安装TCP/IP协议,不要安装任何其他的协议(如IPX协议或者NetBEUI协议),在网络属性中,”网络负载平衡”也不能被选择,这可以从”网络连接属性”中查看。

  ①先进入第一台计算机,以管理员身份登录,从”管理工具”中运行”网络负载平衡管理器”,用鼠标右键单击”网络负载平衡群集”,从出现的菜单中选择”新建群集”,进入”群集参数”界面

  在”IP地址”后面输入规划的群集参数地址202.206.197.195,在子网掩码处使用默认值,在”完整Internet名称”后面输入cluster.heinfo.edu.cn(也可以是其他的名称,但输入的DNS名称必须与输入的IP地址相符)。

  如果允许远程控制,请选中”允许远程控制”,并在”远程密码”和”确认密码”处输入可以进行远程控制的密码

 ②点击”下一步”按钮,进入群集IP地址页面后再进入”端口规则”界面,点击”下一步”按钮,进入”连接”

 ③在”连接”界面的”主机”栏中输入当前计算机的名称w2003-1,然后点击”连接”按钮,将在”对配置一个新的群集可用的接口”框中显示出连接的计算机的网卡及IP地址。选择与群集IP地址同一网段的地址(用于对外提供网络应用的网卡),然后点击”下一步”按钮,进入设置”主机参数”界面,点击”完成”按钮,系统将自动开始网络负载平衡群集的配置。几分钟后,网络负载平衡群集配置完成。

④ 然后,在第一台计算机上,用鼠标右键单击新创建的群集,从出现的菜单中选择”添加主机到群集”。将出现”连接”界面,在”主机”中输入第二台计算机的计算机名称,点击”连接”按钮,将会在”对配置群集可用的接口”下面显示出连接的计算机上的网络配置。选择202.206.197.191的网卡,进入主机参数界面,点击”完成”按钮,即可返回网络负载平衡管理器

 

注:1,加节点两种方式:1,在有群集的机上进行“添加主机到群集”。2,在待入群集的机上,进行链接操作。

  2,在网络负载平衡中的每个节点上,管理员账号的用户名和密码最好一致。

   3,信息保存,使用该工具进行管理是首选的方法。依次单击“开始/所有程序/管理工具/网络负载平衡管理器”,可以打开“网络负载平衡管理器”窗口。遗憾的是,用户所连接到的群集和主机的名称不能在会话之间保留,因此每次启动“网络负载平衡管理器”时,左窗格中的群集列表均为空。不过用户可以将列表保存到一个文本文件中,并在以后启动该工具时加载这个文本文件。在“网络负载平衡管理器”窗口中依次执行“文件/保存主机列表/加载主机列表”命令来实现

注:操作过程中一些参数解释:

虚拟出的群集IP与群集MAC地址对应关系:02-bf-虚拟IP16进制(单播),03-bf-虚拟IP16进制(多播),01 – 00 – 5E -7F -虚拟IP后两位16进制(IGMP多播)

1,群集操作模式:

1)单网卡单播 —两台服务器不能互访,单播模式是指各节点的网络适配器被重新指定了一个虚拟MAC,由于所有绑定群集的网络适配器的MAC都相同,所以在单网卡的情况下,各节点之间是不能通讯的。

2)多播模式下,网络适配器在保留原有的MAC地址不变的同时,还分配了一个各节点共享的多播MAC地址。

所以,即使单网卡的节点之间也可以正常通讯,不过在多播模式中,NLB节点发送的针对群集IP地址MAC地址ARP请求的

ARP回复会将群集IP地址映射到多播MAC地址,而许多路由器或交换机会拒绝这一行为,当出现这种情况时,必须在路由器和交换机上手动

添加静态映射,将群集IP地址映射到群集的多播MAC地址上。

3)IGMP多播(只有在选中多播时,才可以选择此项),

在继承多播的优点之外,NLB每隔60秒发送一次IGMP信息,使多播数据包只能发送到这个正确的交换机端口,避免了交换机数据洪水的产生。不过要求交换机支持IGMP侦听,并且要求群集工作在多播模式下。

如果启用 IGMP 支持,则允许的多址广播 IP 地址将被限制为标准的 D 类范围,即 224.0.0.0 到 239.255.255.255。

2,端口规则筛选模式:多个主机:多主机同时工作,单一主机:一个主机工作,此主机挂掉后,下一个主机才开始工作,可设置优先级。

1):多个主机:

a)无相似性: 平均分配,客户端的服务请求会平均分配到群集内的每一部服务器。假设NLB群集内有2部服务器。当接到客户端的请求时,NLB会将第1个请求交由第1部服务器来处理,第2个请求交由第2部服务器来处理,第3个请求交由第1部服务器来处理,…依此类推。因为所有客户端联机会平均分配到每一部服务器,因此可以达到最佳的负载平衡。如果需要执行交易处理,为了能够共享session状态,则必须将session状态集中储存在state( 用一台机存session即可,web.config改下)或database server中,这种方式适用于大部分的应用程序。

注:为了使网络负载平衡可以正确处理 IP 片段,当为协议设置选择“UDP”或“二者”时应避免使用“无”。

b)单一相似性:相同IP固定同一服务器,客户机的服务请求会固定分配到群集内的某一部服务器。当接到客户机的请求时,NLB会根据客户机 的IP来决定交由哪一部服务器来处理,也就是一部服务器只会处理来自某些IP的请求。因为一个IP的服务请求只会固定由一个服务器来处理,因此没有session状态共享的问题,但可能会导致负载不平衡。这种方式适用于联机需支持 SSL 集多重联机的通讯协议 ( 例如FTP与PPTP等)

网络(类C):相同网段固定同一服务器,根据IP的Class C屏蔽来决定交由哪一部服务器来处理,也就是一部服务器只会处理来自某些网段C的

c)请求。这种方式可确保使用多重 Proxy 的客户端能导向到相同的服务器。

注:在NLB建立完成后,在集雄属性的端口规则中即可设置每台机负载量。

2):.单一主机:若选择此选项,该端口范围内的所有请求都将由一台主机来进行处理,此选项将配合后面的主机优先级来进行主机判定。

注:禁用此端口范围:一般这个选项会在端口例外中进行设置,也就是说,当我们指定了一个比较大的范围端口时,其中有一个或几个端口我们不需要 客户端用户访问到,这时我们将利用这个规则来进行设定,防止用户访问此端口请求。

3,优先级(单一主机标识符),在端口规则选择为单一主机时有效,在一单主机模式下,所有通讯由一台机完成,只有当此机故障时,

才会转由下一个优先级高的接管。它的范围在1-32之间。

注:维护, 使用“nlb display”命令,可以显示网络负载平衡最近产生的事件日志记录

2011八月4

数据一致性错误: 尝试在数据库 5 中提取逻辑页 (1:1640) 失败。该逻辑页属于分配单元785421321 ,而非 9854555445。

sql server 评论关闭

尝试在数据库 5 中提取逻辑页 (1:1640) 失败。该逻辑页属于分配单元785421321 ,而非 9854555445。
1,利用DBCC CHECKDB检查一致性

发现
bdOrder的 DBCC 结果。
消息 2575,级别 16,状态 1,第 1 行
索引分配映射(IAM)页 (0:0) (位于对象 ID 1785109450,索引 ID 1,分区 ID 72057594326417408,分配单元 ID 72057594346405888 (类型为 In-row data))的下一个指针指向了 IAM 页 (3:6209),但扫描过程中检测不到它。
消息 7965,级别 16,状态 2,第 1 行

需要修复
保留一个查询页面,执行如下:
use 需要修复的数据库实体的名称
declare @dbname varchar(255)
set @dbname=’需要修复的数据库的名称’
exec sp_dboption @dbname,’single user’,'true’
dbcc checktable(‘需要修复的数据表的名称’,REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(‘需要修复的数据表的名称’,REPAIR_REBUILD)
exec sp_dboption @databasename, N’single’, N’false’?将目标数据库置为多用户状态
然后执行 SQL Server数据修复命令DBCC CHECKDB(’需要修复的数据库实体的名称’) 检查数据库是否仍旧存在错误。注意:修复后可能会造成部分数据的丢失。