执行计划统计信息索引天善智能一家之言

1、天善智能致全体 BI 同仁的各位 BI 同仁们：大家好！如果有一天，您有机会看到这封信，说明很有缘。天善智能是一个小团队，是由几位 BI 技术实战者组织建立的草根组织，或者某一天会变得很强大，如果您支持和同意的话。天善智能专注于商业智能和数据库性能优化，2011 年 9 月由（Robay）组织成立，前期以接外包项目和顾问培训服务为主，以及一些小范围的独家知识。在 2012 年 9 月，天善开始通过网络来天善多年来在商业智能和数据库方面的实战经验。目前天善智能总共已经制作了 23 份技术文档，录制了 25 部（陆续增加中.），开展了六期 BI 技术公开课，并且把这资料放在天善智能的博客，优酷，各

2、种网络媒介上免费让大家，和学习。最难得的是，这些资料是网络上最独有，最全，最有含金量的，并整理的井井有序，免费开放了给大家。这样做的目的，完全是因为天善体谅一些大学生、初学者想学习 BI 技术，但是又苦于学习无门的心情，因为天善的所有成员也经历过一段这样痛苦的时间，因此想通过这些小小的行为帮助到那些 BI 的初学者。俗话说：“授之以鱼不如授之以渔”，天善智能虽然提供了这么多的资料供大家免费学习，但是天善认为仅仅做到这一点是不够的，更好的方式应该是让大家掌握一种无形的本领。这种本领可以描述成“如何思考、如何学习、如何解决问题、如果沉淀、如何成长”等。也许您会觉得有点虚无缥缈，但是请相信天善智能，

3、和天善一起前行成长，终有一天会实现各位心中的理想。最后，天善智能成立时间短，也还年轻，更急需成长，因此天善智能诚恳的希望您能提出具有建设性的建议，助天善团队壮大，天善团队，使天善迈向成阶段。写于 2012 年 12 月 22 日重生后的第一天如何找到天善？博客：(订阅本博客随时掌握天善动态，文档工具。目前已经 600 人订阅，还不订阅更待何时？)QA： HYPERLINK http:/q/ http:/q(任何技术问题，只要您认真提，天善一定认真答。)5 群：236899666 群：237979203前 4 群基本满员，多达 2000 人，加入也是必须的。（加入时请注明：天善智能）天善优酷：/

4、tianshansoft天善智能博客：第 1 页 共 21 页且SQL 统计信息更新1统计信息统计信息基础统计信息SQL Server 2005 允许创建有关列中值的分布情况的统计信息。查询优化器使用这些统计信息并通过估计使用索引评估查询的开销来确定最佳查询计划。按照默认设置，如果表中的某列没有索引，则 SQL Server 会自动为该列创建统计。然后，查询优化器评估该列中数据分布范围的统计信息，以选择一个更为有效的查询处理方案。创建统计信息后，数据库引擎 对列值（根据这些值创建统计信息）进行排序，并根据这些值（最多 200 个，按间隔分隔开）创建一个“直方图”。直方图指定有多

5、少行精确匹配每个间隔值，有多少行在间隔范围内，以及间隔中值的密度大小或重复值的发生率。1.1.2统计信息的作用1）index 建立后，优化器是否使用该 index，优化器需要借助一些统计信息来做判断根据统计信息，预估采用嵌套循环连接，合并连接，哈希连接等哪根据统计信息判断表的估计最佳的成本（最佳的执行顺序）接1.1.3统计信息的创建如果列上创建了索引，会自动生成一个跟索引同名的统计信息；没有索引的列上，如果用它来关联表或者查询数据，这时，系统会在评估最佳查询计划前，生成一个该列的的统计信息，其前缀为_WA_Sys_。1.1.4统计信息内容显示指定表上的指定目标的当前分发统计信息.(用户必须是表

6、所有者，或者是 sysadmin 固定服务器角色、db_owner固定数据库角色或 db_ddladmin 固定数据库角色的成员。)DBCC SHOW_SISTICS ( table_name | view_name ,) WITH NO_INFOMSGS , n : =S_HEADER | DENSITY_VECTOR | HISTOGRAM参数：table_name | view_name是要显示其统计信息的表或索引视图的名称。表名和视图名称必须符合标识符规则。是要显示其统计信息的对象名称（索引名称、统计信称或列名）。目标名称必须符合标识符规则。如果是表的现有索引或统计信息的名称，则返回有

7、关此目标的统计信息。如果容，则返回有关该自动创建统计内容的信息。NO_INFOMSGS取消严重级别从 0 到 10 的所有信息性消息。是现有列的名称，且此列中存在自动创建的统计内S_HEADER | DENSITY_VECTOR | HISTOGRAM , n 如果指定以上一个或多个选项，可限制该语句返回的结果集。天善智能博客：第 2 页 共 21 页1.1.5统计信息创建1）可以使用以下语句创建统计信息：CREATE S参数：table_nameISTICS ON . WITH FULLSCAN ,PUTE 指定要创建的统计信息所基于的表的名称。index_name要创建的统计信息所基于的索

8、引。如果未指定索引，则为表中的所有索引创建统计信息。FULLSCAN指定收集统计信息时应PUTE表或视图中的所有行。指定应禁用统计信息的自动重新计算功能。如果指定此选项，即使数据发生更改，数据库引擎也将仍然继续使用旧的统计信息。数据库引擎不自动更新和统计信息，这可能生成不理想的统计计划。（不建议使用此参数）2）通过系统过程 sp_createss，可以为当前数据库中所有用户表的所有合格列和表创建单列统计信息。新的统计信息与创建该统计信息所在列具有相同的名称。（需要 db_owner 固定数据库角色成员资格。）语法如下：sp_creates , , 参数：s indexonly = indexo

9、nly fullscan = fullscan pute = pute indexonly = indexonly指定创建统计信息时只应考虑参与索引的列。indexonly 的数据类型为 char(9)。默认值为 NO。 fullscan = fullscan指定将 FULLSCAN 选项用于 CREATE SISTICS。如果忽略 fullscan，SQL Server 2005 Database Engine 将执行默认示例扫描。fullscan 的数据类型为char(9)。默认值为 NO。pute = pute指定对新创建的统计信息禁用统计信息自动重新计算功能。pute 的数据类型为ch

10、ar(12)。默认值为NO。已经包含统计信息的列不会受影响，如索引的第一列或包含显式创建的统计信息的列。对于每个满足上述限制的列，将执行CREATE SISTICS 语句。如果指定了 fullscan 则执行 FULLSCAN。对于被禁用的索引的前导列，将不会对其创建统计信息。如果指定了 indexonly，那么，除非其他启用的索引也使用了已禁用的非索引中的列，否则不会对该列创建统计信息。sp_createss 会忽略包含已禁用索引的表。1.1.6统计信息的更新1）为指定的表或索引视图中的一个或多个统计信息组（集合）更新键值分布信息。UPDATE SISTICS ON . WITH FULLS

11、CAN ,PUTE 2）如果希望在当前数据库中更新所有的统计信息，可以使用系统系统过程 sp_updatess。该过程在SQL Server2005 中进行了改善，只更新必要的（当数据发生改变时）统计信息。不会更新未改变数据的统计信息。示例：Exec sp_updatess;执行后的结果：天善智能博客：第 3 页 共 21 页.（省略部分结果）正在更新dbo.AC_AR_WA_Sys_00000001_7E2D9D55已更新. _WA_Sys_00000002_7E2D9D55已更新._WA_Sys_00000005_7E2D9D55，不需要更新. _WA_Sys_00000007_7E2D9

12、D55已更新. _WA_Sys_00000003_7E2D9D55已更新. _WA_Sys_0000000E_7E2D9D55已更新.已更新 5 個索引/統計資料，個不需要更新。正在更新dbo.ESERVICE_DW_K_TBL_WA_Sys_00000001_7EECB764已更新. _WA_Sys_00000002_7EECB764已更新.已更新 2 個索引/統計資料，個不需要更新。正在更新dbo.JOB_TYPE PK_JOB_TYPE，不需要更新.已更新 0 個索引/統計資料，個不需要更新。正在更新dbo.ESERCICE_DW_M_DESIGN_RELATION_TBL已更新 0 個

13、索引/統計資料，個不需要更新。所有資料表的統計資料都已更新。1.1.7索引信息删除删除指定的表和索引的统计信息。Drop sistics on .1.1.8更新指定表索引统计索引，更新指定表单个索引信息，对表进行全表扫描，更新索引信息update supdate s update sistics 表名istics 表名 索引名istics 表名(列名) with fullscan2浅谈 SQLSERVER 统计对于查询的影响2.1简介SQL Server 查询分析器是基于开销的。通常来讲，查询分析器会根据谓词来确定该如何选择高效的查询路线,比如该选择哪个索引。而每次查询分析器寻找路径时，并不会

14、每一次都去统计索引中包含的行数，值的范围等，而是根据一定条件创建和更新这些信息后保存到数据库中，这也就是所谓的统计信息。2.1.1如何查看统计信息查看 SQL Server 的统计信息非常简单，使用如下指令：DBCC SHOW_SISTICS(表名,索引名)所得到的结果如图 1 所示。天善智能博客：第 4 页 共 21 页2.1.2统计信息如何影响查询下面通过一个简单的例子来看统计信息是如何影响查询分析器。我建立一个测试表，有两个值的列，其中id为自增，ref 上建立非例数据的统计信息。索引，100 条数据，从 1 到 100，再9900 条等于 100 的数据。图 1 中的统计信息就是示此时

15、，我 where 后使用 ref 值作为查询条件，但是给定不同的值，的选择，如图 2 所示。可以看出根据统计信息，查询分析器做出了不同天善智能博客：第 5 页 共 21 页图 2.根据不同的谓词，查询优化器做了不同的选择其实，对于查询分析器来说，柱状图对于直接可以确定的谓词非常管用，这些谓词比如:where date = getdate() where id= 12345where monthly_sales (select sum(qty) from sales) where a.id =b.ref_idwhere col1 =1 and col2=2这类在运行时才能知道值的查询，采样步长就

16、明显不是那么好用了。另外，上面第四行如果谓词是两个查询条件，使用采样步长也并不好用。因为无论索引有多少列，采样步长仅仅密度来确定最佳的查询路线。索引的第一列。当柱状图不再好用时，SQL Server 使用密度的公式是：1/表中唯一值的 个数。当密度越小时，索引越容易被选中。比如图 1 中的第二个表，下公式来计算一下密度:可以通过如天善智能博客：第 6 页 共 21 页图 3.某一列的密度根据公式可以推断，当表中的数据量逐渐增大时，密度会越来越小。对于那些不能根据采样步长做出选择的查询，查询分析器使用密度来估计行数,这个公式为:估计的行数=表中的行数*密度那么，根据这个公式，如果我做查询时，估计

17、的行数就会为如图 4 所示的数字。图 4.估计的行数来验证一下这个结论,如图 5 所示。天善智能博客：第 7 页 共 21 页图 5.估计的行数因此，可以看出，估计的行数是和实际的行数有出入的，当数据分布均匀时，或者数据量大时，这个误差将会变的非常小。2.1.3统计信息更新由上面的例子可以看到，查询分析器由于依赖于统计信息进行查询，那么过时的统计信息则可能导致低效率的查询。统计信息既可以由 SQL Server 来进行管理，也可以手动进行更新，也可以由 SQL Server 管理更新时手动更新。当开启了自动更新后，SQL Server表中的数据更改，当达到临界值时则会自动更新数据。这个标准是:

18、向空表数据时少于 500 行的表增加 500 行或者当表中行多于 500 行时，数据的变化量大于 20%时上述条件的满足均会导致统计被更新。当然，也可以使用如下语句手动更新统计信息。UPDATE SISTICS 表名索引名天善智能博客：第 8 页 共 21 页2.1.4列级统计信息SQL Server 还可以针对不属于任何索引的列创建统计信息来帮助查询分析器获取”估计的行数“.当级别的选项“自动创建统计信息”如图 6 所示。开启数据库图 6.自动创建统计信息当这个选项设置为 True 时，当下两种情况例外:where 谓词指定了不在任何索引上的列时，列的统计信息会被创建，但是会有以创建统计信息

19、的成本超过生成查询计划的成本当SQL Server 忙时不会自动生成统计信息可以通过系统视图 sys.ss 来查看这些统计信息,如图 7 所示。天善智能博客：第 9 页 共 21 页图 7.通过系统视图查看统计信息当然，也可以通过如下语句手动创建统计信息:CREATE S2.1.5总结ISTICS 统计名称 ON 表名 (列名 ,.n)本文简单谈了统计信息对于查询路径选择的影响。过时的统计信息很容易造成查询性能的降低。因此，定期更新统计信息是 DBA 重要的工作之一。3非常重要附加查询计划(/fish-li/archive/2011/06/06/2073626.html)对于 SqlServe

20、r 的优化来说，可能优化查询是很常见的事情。关于数据库的优化，本身也是一个涉及面比较的广的话题，本文只谈优化查询时如何看懂 SqlServer 查询计划。由于我对 SqlServer 的认识有限，时批评指正。错误，也恳请您在发现后及首先，打开【SQL Server Management Studio】，输入一个查询语句看看 SqlServer 是如何显示查询计划的吧。说明：本文所演示的数据库，是我写的一个演示程序的数据库， 可以在此网页中。select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Fin

21、ished fromOrdersView as vwhere v.OrderDate = 2010-12-1 and v.OrderDate 2011-12-1;其中，OrdersView 是一个视图，其定义如下：SELECTdbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,dbo.OrdermMoney, dbo.Orders.Finished,ISNULL(dbo.Customers.CustomerName, N) AS CustomerName dbo.Orders LEFT OUTER JOINdbo.Cu

22、stomers ON dbo.Orders.CustomerID = dbo.Customers.CustomerIDFROM对于前一句查询，SqlServer 给出的查询计划如下（点击上的【显示估计的执行计划】按钮）：天善智能博客：第 10 页 共 21 页从这个图，至少可以得到 3 个有用的信息：哪些执行步骤花费的成本比较高。显然，最右边的二个步骤的成本是比较高的。哪些执行步骤产生的数据量比较多。对于每个步骤所产生的数据量， SqlServer 的执行计划是用【线条粗细】来表示的，因此也很容易地从分辨出来。每一步执行了什么样的动作。对于一个比较慢的查询来说，通常首先要知道哪些步骤的成本比较

23、高，进而，可以尝试一些改进的方法。一般来说，如果您不能通过：提高硬件性能或者调整 OS,SqlServer 的设置之类的方式来解决问题，那么剩下的可选方法通常也只有以下这些了：为【scan】这类操作增加相应字段的索引。有时重建索引或许也是有效的，具体情形请参考后文。调整语句结构，引导 SqlServer 采用其它的查询方案去执行。调整表结构（分表或者分区）。下面再来说说一些很重要的理论知识，这些内容对于执行计划的理解是很有帮助的。3.1Sql Server 查找的方法说到这里，不得不说 SqlServer 的索引了。SqlServer 有二种索引：索引和非。【非索引。二者的差别在于：【聚索引】

24、保存了二个信息：1.相集索引】直接决定了应索引字段的值，2.的存放位置，或者说：根据索引可以直接获取到对应索引的位置（如果表没有索引则保存指针）。因此，如果能通过【索引】来查找，显然也是最快的。Sql Server 会有以下方法来查找您需要的数据1. 【Table Scan】：遍历整个表，查找所匹配的：行。这个操作将会一行一行的检查，当然，效率也是的。2. 【Index Scan】：根据索引，从表中过滤出来一部分因此比【Table Scan】要快。，再查找所匹配的行，显示比第式的查找范围要小，3. 【Index Seek】：根据索引，定位（获取）的存放位置，然后取得，因此，比起前二种方式会更快

25、。4. 【Clustered Index Scan】：和【Table Scan】一样。注意：不要以为这里有个 Index，就认为不一样了。其实它的意思是说：按没有索引来逐行扫描每一行，因为就是按索引来顺序存放的。而【Table Scan】只是说：要扫描的表索引而已，因此这二个操作本质上也是一样的。5. 【Clustered Index Seek】：直接根据索引获取，最快！所以，当发现某个查询比较慢时，可以首先检查哪些操作的成本比较高，再看看那些操作是查找时，是不是【Table Scan】或者【Clustered Index Scan】，如果确实和这二种操作类型有关，则要考虑增加索引来解决了。不

26、过，增加索引后，也会影响数据表的修改动作，因为修改数据表时，要更新相应字段的索引。所以索引过多，也会影响性能。还有一种情况是不适天善智能博客：第 11 页 共 21 页合增加索引的：某个字段用 0 或 1 表示的状态。例如可能有绝大多数是 1，那么此时加索引根本就没有意义。这时只能考虑为 0 或者 1 这二种情况分开来保存了，分表或者分区都是不错的选择。如果不能通过增加索引和调整表来解决，那么可以试试调整语句结构，引导 SqlServer 采用其它的查询方案去执行。这种方法要求： 1.对语句所要完成的功能很清楚， 2.对要查询的数据表结构很清楚， 3.对相关的业务背景知识很清楚。如果能通过这种

27、方法去解决，当然也是很好的解决方法了。不过，有时 SqlServer 比较智能，即使你调整语句结构，也不会影响它的执行计划。如何比较二个同样功能的语句的性能好坏呢，我建议采用二种方法： 1. 直接把二个查询语句放在【SQL Server ManagementStudio】，然后去看它们的【执行计划】，SqlServer 会以百分比的方式告诉你二个查询的【查询开销】。这种方法简单，通常也是可以参考的，不过，有时也会，具体原因请接着往下看(可能索引统计信息过旧)。2. 根据真实的程序调用，写相应的测试代码去调用：这种方法就麻烦一些，但是它更能代表现实调用情况，得到的结果也是更具有参考价值的，因此也

28、是值得的。3.2索引统计信息：查询计划的选择依据前面一直说到【执行计划】，既然是计划，就表示要在具体执行前就能确定下来的操作方案。那么 SqlServer 是如何选择一种执行计划的呢？ SqlServer 怎么知道什么时候该用索引或者用哪个索引？对于 SqlServer 来说，每当要执行一个查询时，都要首先检查有没有这个查询的执行计划是否存在缓存中，如果没有，则要生成一个执行计划，具体在产生执行计划时，并不是看有哪些索引可用（随机选择），而是会参考一种被称为【索引统计信息】的数据。 如果您仔细地看一下前面的执行计划或者执行过程表格，会发现 SqlServer 能预估每个步骤所产生的数据量，正是

29、因为 SqlServer 能预估这些数据量，SqlServer 才能选择一个它认为最合适的方法去执行查询过程，此时【索引统计信息】就能告诉 SqlServer 这些数据。说到这里，您是不是有点好奇呢，为了对【索引统计信息】有个感性的认识，来看看【索引统计信息】是个什么样子的。请在【SQL Server Management Studio】，输入以下语句，然后执行。3.3统计信息，指导条件选择不同查询计划（重点）再来看看同一个查询，但因为查询参数值不同时，SqlServer 选择的执行计划：天善智能博客：第 12 页 共 21 页从上图可以看出，由于 CategoryId 的参数值不同，SqlS

30、erver 会选择完全不同的执行计划。统计信息重要性在这里体现地很清楚吧。创建统计信息后，数据库引擎对列值（根据这些值创建统计信息）进行排序，并根据这些值（最多 200 个，按间隔分隔开）创建一个“直方图”。直方图指定有多少行精确匹配每个间隔值，有多少行在间隔范围内，以及间隔中值的密度大小或重复值的发生率。SQL Server 2005 引入了对 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 和 ntext 列创建的统计信息收集的其他信息。这些信息称为“字符串摘要”，可以帮助查询优化器估计字符串模式中查询谓词的选择性。查

31、询中有 LIKE 条件时，使用字符串摘要可以更准确地估计结果集大小，并不断优化查询计划。这些条件包括诸如 WHERE ProductName LIKE %Bike 和 WHERE Name LIKE CSheryl 之类的条件。既然【索引统计信息】这么重要，那么它会在什么时候生成或者更新呢？事实上，【索引统计信息】是不用手工去的， SqlServer 会自动去它们。而且在 SqlServer 中也有个参数来控制这个更新方式：天善智能博客：第 13 页 共 21 页创建索引时，查询优化器自动有关索引列的统计信息。另外，当 AUTO_CREATE_SISTICS 数据库选项设置为 ON（默认值）时

32、，数据库引擎自动为没有用于谓词的索引的列创建统计信息。随着列中数据发生变化，索引和列的统计信息可能会过时，从而导致查询优化器选择的查询处理方法不是最佳的。例如，如果创建一个包含一个索引列和 1,000 行数据的表，每一行在索引列中的值都是唯一的，则查询优化器将把该索引列视为收集查询数据的好方法。如果更新列中的数据后存在许多重复值，则该列不再是用于查询的理想候选列。但是，查询优化器仍然根据索引的过时分布统计信息（基于更新前的数据），将其视为好的候选列。当 AUTO_UPDATE_SISTICS 数据库选项设置为 ON（默认值）时，查询优化器会在表中的数据发生变化时自动定期更新这些统计信息。每当查

33、询执行计划中使用的统计信息没有通过针对当前统计信息的测试时就会启动统计信息更新。采样是在各个数据页上随机进行的，取自表或统计信息所需列的最小非索引。从磁盘一个数据页后，该数据页上的所有行都被用来更新统计信息。常规情况是：在大约有 20% 的数据行发生变化时更新统计信息。但是，查询优化器始终确保采样的行数尽量少。对于小于 8 MB 的表，则始终进行完整扫描来收集统计信息。采样数据（而不是分析所有数据）可以将统计信息自动更新的开销降至最低。在某些情况下，统计采样无法获得表中数据的精确特征。可以使用 UPDATE SISTICS 语句的 SLE 子句和 FULLSCAN 子句，控制按逐个表的方式手动

34、更新统计信息时采样的数据量。FULLSCAN 子句指定扫描表中的所有数据来收集统计信息，而 S比或采样的行数LE 子句用来指定采样的行数百分在 SQL Server 2005 中，数据库选项 AUTO_UPDATE_SISTICS_ASYNC 提供了统计信息异步更新功能。当此选项设置为 ON 时，查询不等待统计信息更新，即可进行编译。而过期的统计信息置于队列中，由进程中的工作线程来更新。查询和任何其他并发查询都通过使用现有的过期统计信息立即编译。由于不存在等待更新后的统计信息的延迟，因此查询响应时间可；但是过期的统计信息可能导致查询优化器选择低效的查询计划。在更新后的统计信息就绪后启动的查询将

35、使用那些统计信息。这可能会导致重新编译缓存的计划（取决于较旧的统计信息版本）。如果在同一个显式用户事务中出现某些数据定义语言 (DDL) 语句（例如，CREATE、ALTER 和 DROP 语句），则无法更新异步统计信息。AUTO_UPDATE_SISTICS_ASYNC 选项设置于数据库级别，并确定用于数据库中所有统计信息的更新方法。它只适用于统计信息更新，而无法用于以异步方式创建统计信息。只有将 AUTO_UPDATE_SISTICS 设置为 ON 时，将此选项设置为 ON才有效。默认情况下，AUTO_UPDATE_SISTICS_ASYNC 选项设置为 OFF。天善智能博客：第 14 页

36、 共 21 页从以上说明中，器的判断了。可以看出，对于大表，还是有可能存在统计信息更新不及时的时候，这时，就可能会影响查询优化有些人可能有个经验：对于一些慢的查询，他们会想到重建索引来尝试解决。其实这样做是有道理的。因为，在某些时候一个查询突然变慢了，可能和统计信息更新不及时有关，进而会影响查询优化器的判断。如果此时重建索引，就可以让查询优化器知道的数据分布，自然就可以避开这个问题。 还记得我前面用【set sistics profile on】显示的执行过程表格吗？注意哦，那个表格就显示每个步骤的实际数据量和预估的数据量。要不要重建索引，其实可以用【setsistics profile on

37、】来看一下，如果实际数据量和预估的数据量的差值比较大，那么可以考虑手工去更新统计信息，然后再去试试。4高 CPU 数据库问题如何和解决 CPU 高度消耗(100%)的数据库问题高性能 CPU 测试总结很多时候的服务器可能会经历 CPU 消耗 100%的性能问题.排除系统的异常，这类问题通常都是因为系统中存在性能低下甚至存在错误的 SQL 语句, 消耗了大量的 CPU 所致.本文通过一个案例就如何捕获这样的 SQL 给出一个通用的方法.问题描述:系统 CPU 高度消耗,系统运行缓慢On Solaris8Oracle:Oracle9203天善智能博客：第 15 页 共 21 页天善

38、智能博客：第 16 页 共 21 页总结: 很多时候，高CPU 消耗都是由于问题 SQL 导致的,所以找到这些 SQL 通常也就找到了问题所在，通过优化调整通常就可以解决问题。但是有时候你可能会发现，这些最消耗 CPU 的进程是导致的，需要具体问题具体分析了.进程，这一般是由于异常、BUG 或者恢复后的异常4.1.2一条 SELECT 语句导致瓶颈情况描述上周，公司一项目新上线，刚上线的第 2 天，在发现数据库服务器与 IIS 服务器的网络 IO 出现瓶颈，1GB 的网络带宽，天善智能博客：第 17 页 共 21 页占用了 70%-100%，也就是每秒传输数据 700MB-1GB，数据库使用内

39、存高达 21GB。IIS 服务器 CPU 使用率时常爆至 80%-90%，导致频频出现连接超时。原因:晚上只好暂时关闭Select * From Table1，进行服务器，作全面的检查，发现是一句 Select 语句导致：这条语句，语法是没问题的，但在应用上出了问题。Table1的是 10 多万行数据，表数据每天都会上万的增长。为了统计总行数，频频调用这语句，每秒刷新不低于 1000 次。也因此导致网络出现瓶颈。解决Select Count(*) from Table1即可解决问题，网络 IO 数据马上降至 10MB 以下，数据库使用内存也保持在预计范围 12GB。看似非常简单，其实不然。解决

40、这问题，所花的时间周期是 6 小时，检查问题使用 1 小时，修改代码使用 5 小时。小结做事要细心，不要犯低级错误，有时候成功取决于细节。使用 SQL 语句查询耗时最长，CPU 消耗最高，IO 阻塞查询耗时最长的 SQL 语句/*功能说明：查询耗时最长的SQL语句*/SELECT TOP 5 Sum(qs.total_worker_time) / 1000 AS total_cpu_time, Sum(qs.execution_count) / 1000AS total_execution_count,Count(*) qs.plan_handle, qs.sql_handlesys.dm_e

41、xec_query_s BY qs.plan_handle,qs.sql_handleAS number_of_sements,FROMGROUPs qsORDERBY Sum(qs.total_worker_time) DESCselect * fromsys.dm_exec_sql_text(0 x06002400D04F2410B8408B00000000)5.2/*查询进程消耗 CPU 时间功能说明:线程ID、消耗CPU时间、等待资源类型*/SELECT ses_id,cpu_time, wait_TypeFROMsys.dm_exec_requestsORDER BY cpu_tim

42、e DESC-dbcc inputbuffer(62)-kill 641天善智能博客：第 18 页 共 21 页5.3/*查询 SQL IO 阻塞查询功能说明:SQL的IO阻塞者的查询*/SELECT blocking_seswait_duration_ms,_id,ses_idFROMsys.dm_os_waiting_tasksWHERE blocking_ses-dbcc INPUTBUFFER(60)_id IS NOT NULL5.4/*查询 SQL 当前执行最多的语句功能说明:SQL当前执行的最多的语句*/SELECT execution_count,( total_elapsed

43、_time / execution_count / 1000 ) avg_time, textFROMsys.dm_exec_query_ss qsCROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS stBY execution_count DESCORDER5.5/*功能说明：数据查询数据库各个表的过程空间空间的查询逻辑说明：数据库的大小，表空间的大小*/IF NOT EXISTS (SELECTFROMd WHERE id*ysobjects= Object_id(Ndbo.tablespaceinfo)AND Objectproperty(id,CREATE TABLE tablespaceinfo -创建结果 (NIsUserTable) = 1)表nameinforowsinfoVARCHAR(50), VARCHAR(20), VARCHAR(20),datainfoindex_size VARCHAR(20),unusedVARCH