下载文档原格式
基于M s s Q L s E R V E R数据库的性能优化刘畅(沈阳职业技术学院辽宁沈阳110045)【摘要】通过对E—ST A R T股票交易系统的调整和优化，实现缩短查询响应时间，系统的客户端查询速度也得到明显的提高。
[关键词]数据库性能优化s Q L Ser ver中围分类号：TP3文献标识码：^文章编号：167卜7597(2009)0110051-01。
随着现代计算机科学技术的发展和信息应用系统信息量的增大，数据库已逐步成为计算机信息系统和计算机应用系统的基础和核心，而数据库性能问题的重要性也越来越引起人们的关注。
这样，提出了基于s Q L ser ve r数据库应用系统如何进行性能调整的问题。
性能调整的步骤如下：1．优化业务逻辑；2．调整数据结构设计：3．调整应用程序结构；4．优化数据库的逻辑结构；5．优化数据库的操作；6．数据库服务器性能调整。
一、基于E—ST AFr r股票交易系统的数据库性能调整分析针对在数据库使用过程中出现的问题，结合现有的数据库调整措施和优化技术，对原E-ST A R T系统数据库制定了以下调整步骤：1．优化数据库设计调整。
针对出现的系统运行效率低的问题，结合已有的业务逻辑，对原系统的相应数据库逻辑设计和物理结构做出合理的调整。
2．客户端应用程序调整。
针对客户的遇到的查询等待问题。
对应用程序中影响查询效率的不合理的设计进行调整，包括：sQ L查询语句的优化、应用程序的调整、合理地增加存储过程。
3．服务器性能调整。
针对使用过程中系统资源消耗大、运行成本高的问题，通过对数据库服务器硬件性能瓶颈的分析和调整及对s Q L ser ver与操作系统参数的合理配置优化服务器性能。
二、敦据库设计调整(一)逻辑数据库设计调整可以从以下几个方面来精练数据库的逻辑设计：1．在适当的地方采用反规范形式；2．在适当的地方分割表；3．在适当的地方把表进行分组放入数据库；4．执行完整性约束。
SQL Server 2005SQL 查询优化目录SQL Server 2005:SQL查询优化 .............................................................................................. 错误！未定义书签。
实验安装 (44)练习一：使用SQL Server Profiler工具解决死锁问题 (5)练习二：使用SQL Server Profiler工具隔离运行速度慢的查询语句 (9)练习三：检查执行计划 (11)练习四：使用数据库引擎优化顾问工具(Database Tuning Advisor) (12)SQL Server 2005 SQL查询优化目标注释:本实验侧重于这个模块中的概念，因此不必遵循微软的安全建议。
注释:SQL Server 2005的最新详细资料, 请访问/sql/.完成本实验之后, 你可以实现以下目标:▪使用SQL Server Profiler工具解决死锁问题▪为一个低性能查询制定一个查询计划，并将它以XML格式的文档保存。
▪使用数据库引擎优化顾问工具(Database Tuning Advisor)场景假设你是AdventureWorks数据库的数据库管理员.你的数据库用户经常遇到死锁问题而且你很关心死锁是不是导致系统性能低的一个原因。
你已经隔离了一个经常与死锁有关的查询。
你将使用SQL Server Profiler工具来跟踪导致死锁的事件并详细记录死锁的信息。
追踪到死锁的原因之后，你发现这个原因并不是导致系统性能下降的主要原因，所以你决定检查那些关键的查询。
通过检测为那些关键查询制定的查询计划来分析它们，然后你可以使用索引优化顾问工具来提出最适当的索引。
前提条件▪SQL Server 2000管理任务的基本经验▪熟悉T-SQＬ语言▪完成SQL Server Management Studio 的动手实验。
sql2000查询速度慢的原因2009年10月13日星期二 10:53查询速度慢的原因很多，常见如下几种：1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)2、I/O吞吐量小，形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足5、网络速度慢6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列10、查询语句不好，没有优化●可以通过如下方法来优化查询 :1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb 应放在RAID0上，SQL2000不在支持。
数据量（尺寸）越大，提高I/O越重要.2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)3、升级硬件4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。
注意填充因子要适当（最好是使用默认值0）。
索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段5、提高网速;6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。
配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。
运行Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。
如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。
将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。
7、增加服务器CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。
SQL数据库系统语句优化问题研究[摘要]sql语句的优化是将性能低下的sql语句转换成目的相同的性能优异的sql语句。
人工智能自动sql优化就是使用人工智能技术，自动对sql语句进行重写，从而找到性能最好的等效sql语句。
[关键词]人工智能自动优化 sql语句优化中图分类号：tp311.138 文献标识码：a 文章编号：1009-914x （2013）10-0042-011、一个数据库系统的生命周期可以分成设计、开发和成品三个阶段。
在设计阶段进行数据库性能优化的成本最低，收益最大。
在成品阶段进行数据库性能优化的成本最高，收益最小。
数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。
最常见的优化手段就是对硬件的升级。
根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。
许多优化专家认为，对应用程序的优化可以得到80%的系统性能的提升。
应用程序的优化通常可分为两个方面：源代码和sql语句。
由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高，而对数据库系统性能的提升收效有限。
1.1 为什么要优化sql语句优化sql语句的传统方法是通过手工重写来对sql语句进行优化。
dba或资深程序员通过对sql语句执行计划的分析，依靠经验，尝试重写sql语句，然后对结果和性能进行比较，以试图找到性能较佳的sql语句。
这种传统上的作法无法找出sql语句的所有可能写法，且依赖于人的经验，非常耗费时间。
1.2 sql优化技术的发展历程第一代sql优化工具是执行计划分析工具。
这类工具针对输入的sql语句，从数据库提取执行计划，并解释执行计划中关键字的含义。
第二代sql优化工具只能提供增加索引的建议，它通过对输入的sql语句的执行计划的分析，来产生是否要增加索引的建议。
第三代sql优化工具不仅分析输入sql语句的执行计划，还对输入的sql语句本身进行语法分析，经过分析产生写法上的改进建议。
sqlserver 数据库实例SQL Server 数据库实例SQL Server 是由微软公司开发的关系型数据库管理系统（DBMS），它是一种可靠、安全、高效的数据库解决方案。
在SQL Server中，一个数据库实例（Database Instance）是指在一个计算机上运行的SQL Server程序，它包含了一个或多个数据库。
一个SQL Server数据库实例由以下几个主要组件组成：1. SQL Server引擎（SQL Server Engine）：SQL Server引擎是核心组件，负责处理数据库的管理和查询。
它包括查询优化器、查询执行引擎和事务管理器等功能，提供了高效的数据存储和检索机制。
2. 数据库文件（Database Files）：数据库文件是SQL Server中存储数据的基本单元。
一个数据库实例可以包含多个数据库文件，包括主要数据文件（Data Files）、事务日志文件（Transaction Log Files）和辅助文件组（Filegroup）。
主要数据文件用于存储表、索引和存储过程等数据，事务日志文件用于记录数据库的变更操作，辅助文件组用于存储其他辅助数据。
3. 实例配置（Instance Configuration）：实例配置包括数据库实例的名称、端口号、身份验证方式、内存限制和CPU限制等参数设置。
通过实例配置，可以对数据库实例进行灵活的管理和优化。
4. 安全性和权限（Security and Permissions）：SQL Server提供了丰富的安全性和权限控制机制，可以对数据库实例、数据库和对象进行细粒度的访问控制。
管理员可以通过角色、用户和权限设置来管理用户的访问权限，保证数据的安全性和完整性。
5. 备份和恢复（Backup and Recovery）：SQL Server提供了强大的备份和恢复功能，可以对数据库进行定期备份，以防止数据丢失。
管理员可以通过备份和恢复操作来保护数据库的可用性和持久性。
1.列出数据库服务器、Web服务器的基本的硬件配置，如CPU、内存等。
2.检查数据库服务器是否真正启用了AWE内存。
(1) 启用AWE：数据库服务器检查C:\boot.ini文件,需要配置"/PAE"(*重启电脑才能生效),如下：
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise"
/noexecute=optout /fastdetect /PAE
(2) 开启sql server 服务用户的，内存中锁定页面权限 (*重启电脑才能生效)在“服务管理”中查看 SQL SERVER 服务登录账户，默认是本地系统帐户（System）。
然后在运行 gpedit.msc ，选择计算机配置->windows 设置->安全设置->本地策略->用户权限分配->内存中锁定页面。
添加SQL SERVER服务的登录用户到里面去。
(3)启用数据库AWE内存，以服务器8G内存为例，一般设置如下，最小2G，最大6G(重启SQL SERVER服务即可)：
(4)跟踪数据库性能“Total Server Memory ”的使用情况，看看数据库真正使
用的内存，越接近为数据库分配的最大内存越好。
或使用如下语句，查询数据库的内存使用情况：
use master
go
select * from sysperfinfo where counter_name like '%Total Server Memory(KB)%'
go
3.Web服务器监控项：
4.数据库服务器监控项：
5.系统运行时，检查数据库的内存、cpu、页面错误情况。
（1）查看服务器“性能”，跟踪“Total Server Memory ”的使用情况，看看数据库真正使用的内存，越接近为数据库分配的最大内存越好。
（2）从任务管理器中查看“sqlservr.exe”进程的“内存使用”和“页面错误”情况，一般此处的内存使用一般比（1）中使用的内存要小，页面错误也是越少越好。
（3）CPU的使用情况：如果发现CPU一直比较高，经常达到90%以上，此时需考虑可能是某些存储过程中逻辑有问题，数据库需要不停的运算，导致CPU较高。
6.优化数据库索引。
（1）系统原来每个索引的索引项很多，而且存在和其他索引有交叉的现象。
可将主表如： Transfer_TransactInfo的非聚集索引全部删除，重新建立。
（2）跟踪数据库缺失的索引，参考实际的存储过程逻辑，确定可能需要建立哪些索引，索引项一定不要交叉。
参考查找缺失的索引脚本如下：
select * from(
select [Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
,db_name(d.database_id) dbname,object_name(d.object_id)
tablename,d.index_handle,
d.equality_columns,d.inequality_columns,d.included_columns,d.statement as
fully_qualified_object,gs.*
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs on
gs.group_handle=g.index_group_handle
join sys.dm_db_missing_index_details d on g.index_handle=d.index_handle
--where d.database_id=d.database_id and d.object_id=d.object_id
) as tt
where dbname='whOA' ORDER BY [Total Cost] desc ,tablename DESC
（3）在用户访问量较大时，使用SQL Server Profile跟踪一段时间（半小时左右），找出执行时间超过3秒以上的脚本，开启“执行计划”，查看每个语句的索引使用情况（能否有效缩小数据量），逐个优化。
（4）如果还有问题，可查找耗CPU时间较多的SQL语句，然后优化。
参考脚本如下：
SELECT TOP 50
sql_handle,total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 as [平均消耗CPU 时间(ms)]
FROM sys.dm_exec_query_stats qs
ORDER BY [平均消耗CPU 时间(ms)] DESC ,[运行次数] desc
select * from
sys.dm_exec_sql_text(0x0200000065400C20DC86E9D045BD6AFA56960F0B05A71657)
经验：
1.一般公文处理只要优化“流转表（Transfer_TransactInfo）”的索引就会达到很
好的效果，附带在优化几个其他表的索引。
2.存储过程优化：
（1）存储过程中未进行锁降级的，可以先导出所有的主要的存储过程，将锁降级的语句加上，如with(nolock)、with(rowlock)。
（2）一般需要重点优化的存储过程有：发件执行情况列表、来件执行情况列表、已签收列表、未签收列表、待办来文列表
（3）优化思路：有的存储过程可去除查询两次来获取列表总数和当前页数据的情况，改用@@rowcount来获取。
可参考马鞍山的优化成果。
3.各项目的优化成果脚本都放在各自的项目ST的07.Test\ DailyBuild下,命名以“日期_数据库性能优化_优化描述_项目简称.txt”，可以参考一下。
