SQLServer查询速度慢的原因

admin管理员组

文章数量:1441619

SQLServer查询速度慢的原因

查询速度慢的原因很多，常见如下几种：  1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。  3、没有创建计算列导致查询不优化。  4、内存不足  5、网络速度慢  6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）  7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列  10、查询语句不好，没有优化可以通过如下方法来优化查询 :  二、相应解决方法建议  1、把数据、日志、索引放到不同的I/O设备上， RAID0上，SQL2000不在支持。数据量（尺寸） 2、纵向、横向分割表，减少表的尺寸(sp_space 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个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还 是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例 将表锁住，禁止其他的联接访问表,后果严重。  14、SQL的注释申明对执行没有任何影响  15、尽可能不使用光标，它占用大量的资源。如果需要 row-by-row 地执行，尽量采用非光 标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可 以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。 FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性 可以在游标中任何地方随机 提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发 选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用 于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。 当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用 户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何 值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的， 服务器就执行修改。 选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控 制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用 它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数 据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全 局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先 在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的 时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列 即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默 认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控 制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游 标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁 将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标， 则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事 务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。 然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求 带更新锁的读取。滚动锁根据在游标定义的 SELECT 语句中指定的锁提示，这些游标并发选 项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先 发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动 锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的 选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维 护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 SELECT 语句中的 锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新 重要的手段。例如医院的住院费计算。 26、MIN() 和 MAX()能使用到合适的索引。  27、数据库有一个原则是代码离数据越近越好，所以优先选择 Default,依次为 Rules,Triggers, Constraint（约束如外健主健 CheckUNIQUE……,数据类型的最大长度等 等都是约束）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。  28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入(不 知 JAVA 是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务 器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传 入二进制参数，这样处理速度明显改善。  29、Between 在某些时候比 IN 速度更快,Between能够更快地根据索引找到范围。用查询优 化器可见到差别。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所 以有时会慢些。  30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因 为索引也耗费大量的资源。他的创建同是实际表一样。  31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。  32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是 否使用索引有关,如果查询需要用到联合索引，用 UNION all 执行的效率更高.多个 OR的字 句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。  33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure 来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的 本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据 时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加 了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。  34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了 额外的开销。这同UNION 和UNION ALL一样的道理。  select  top  20  adpanyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120)  as  postDate1,workyear,degreedescription FROM  jobcn_query.dbo.COMPANYAD_query  ad  where  referenceID 果返回大的结果采用存储过程  42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快  43、SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的 返回值是不同的！！！  44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否 则使用 配制线程数量<最大连接数启用 SQL SERVER 的线程池来解决,如果还是数量 = 最大 连接数+5，严重的损害服务器的性能。 45、按照一定的次序来访问你的表。如果你先锁住表 A，再锁住表 B，那么在所有的存储过 程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁 定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发 现  46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec 计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。 Process:  a、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分 比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间 百分比的一部分。这些时间单独计算并且不属于间隔计算总数的 一部 分。这个总数显示了作为实例时间百分比的平均忙时。    b、%Processor Time计数器  如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增 加一个处理器或换一个更快的处理器。    c、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作 系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另 一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理 模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。 特权时间的 % 包 括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间 隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。       d、% User Time 表示耗费 CPU 的数据库操作，如排序，执行 aggregate functions 等。 如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该 值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。 要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio 计数器该值越高越好。如果持续 低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所 以运行经过一段时间后，该值将不能反映系统当前值。    select * from @local_variable where Fid > 40 and fid <= 60  end 的不同    begin     create table #temp (FID int identity(1,1),ReferenceID varchar(20))     insert into #temp (ReferenceID)     select top 100000 ReferenceID from chineseresume order by updatedate     select * from #temp where Fid > 40 and fid <= 60 drop table #temp  end  三、、安全方面的建议安全方面的建议        1.安装最新的服务包      为了提高服务器安全性，最有效的一个方法就是升级到 SQL Server 200 3a (SP3a)。       另外，您还应该安装所有已发布的安全更新。      2.使用 Microsoft 基线安全性分析器（MBSA）来评估服务器的安全性      MBSA 是一个扫描多种 Microsoft 产品的不安全配置的工具，包括 Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)。它可以在本地 过网络运行。该工具针对下面问题对 SQL Server 安装进行检测：      1) 过多的sysadmin固定服务器角色成员。       2) 授予sysadmin以外的其他角色创建 CmdExec 作业的权利。       3) 空的或简单的密码。       4) 脆弱的身份验证模式。  5) 授予管理员组过多的权利。  6) SQL Server数据目录中不正确的访问控制表 7) 安装文件中使用纯文本的sa密码。  sa 帐户应该总拥有一个强健的密码，即使在配置为要求 Windows 身份验证的服务器 该如此。这将保证在以后服务器被重新配置为混合模式身份验证时，不会出现空白或脆 sa。 要分配sa密码，请按下列步骤操作： 1) 展开服务器组，然后展开服务器。  2) 展开安全性，然后点击登录。  3) 在细节窗格中，右键点击SA，然后点击属性。  4) 在密码方框中，输入新的密码。   6.限制 SQL Server服务的权限      SQL Server 2000 和 SQL Server Agent 是作为 Windows 服务运行的。每个服务必须 与一个 Windows 帐户相关联，并从这个帐户中衍生出安全性上下文。SQL Server允许sa 登 录的用户（有时也包括其他用户）来访问操作系统特性。这些操作系统调用是由拥有服务器 进程的帐户的安全性上下文来创建的。如果服务器被攻破了，那么这些操作系统调用可能被 利用来向其他资源进行攻击，只要所拥有的过程（SQL Server 服务帐户）可以对其进行访 问。因此，为 SQL Server 服务仅授予必要的权限是十分重要的。     我们推荐您采用下列设置：     1) SQL Server Engine/MSSQLServer     如果拥有指定实例，那么它们应该被命名为MSSQL$InstanceName。作为具有一般用户 权限的Windows 域用户帐户运行。不要作为本地系统、本地管理员或域管理员帐户来运行。     2) SQL Server Agent Service/SQLServerAgent      如果您的环境中不需要，请禁用该服务；否则请作为具有一般用户权限的 Windows 域 用户帐户运行。不要作为本地系统、本地管理员或域管理员帐户来运行。  重点： 如果下列条件之一成立，那么 SQL Server Agent 将需要本地 Windows 管理 员权限：  2000 中，下列文件可能受到影响：默认安装时:\Program Files\Microsoft SQL Server\MSSQL\Install文件夹中，以及指定实例的:\Program Files\Microsoft SQL Server\ MSSQL$\Install文件夹中的sqlstp.log, sqlsp.log和setup.iss      如果当前的系统是从 SQL Server 7.0 安装升级而来的，那么还应该检查下列文件： %Windir% 文件夹中的setup.iss以及Windows Temp文件夹中的sqlsp.log。      Microsoft 发布了一个免费的实用工具 Killpwd，它将从您的系统中找到并删除这些 密码。      10.审核指向 SQL Server 的连接      SQL Server 可以记录事件信息，用于系统管理员的审查。至少您应该记录失败的 SQL Server 连接尝试，并定期地查看这个日志。在可能的情况下，不要将这些日志和数据文件 保存在同一个硬盘上。      要在 SQL Server 的 Enterprise Manager 中审核失败连接，请按下列步骤操作：      1) 展开服务器组。       2) 右键点击服务器，然后点击属性。       3) 在安全性选项卡的审核等级中，点击失败。      4) 要使这个设置生效，您必须停止并重新启动服务器。

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2014-09-18，如有侵权请联系 cloudcommunity@tencent 删除前往查看索引并发服务服务器内存

本文标签： SQLServer查询速度慢的原因