忽然间检索变慢 TRUNCATE表之后正常 求解决方法

Question

如题。
3个表20万左右数据的一个检索。以前都是几秒后完事。 最近突然变得很慢。
暂时的解决方案是
1）把表1数据备份
2）TRUNCATE TABLE 表1
3）把备份数据倒回到表1
暂时正常了。请教各位大神这种情况是什么原因造成的以及解决方法。
多谢。解决方案检索变慢

Answer 1

谢谢您的回答。
如果是碎片过多的话，除了现在的TRUNCATE TABLE，要怎样去做出力可以解决呢。
因为客户不认可TRUNCATE TABLE，一定要我提出另外的解决方法。
烦恼中…………

Answer 2

谢谢您的回答。
如果是碎片过多的话，除了现在的TRUNCATE TABLE，要怎样去做出力可以解决呢。
因为客户不认可TRUNCATE TABLE，一定要我提出另外的解决方法。
烦恼中…………重建表上的聚集索引就可以了

Answer 3

谢谢您的回答。
如果是碎片过多的话，除了现在的TRUNCATE TABLE，要怎样去做出力可以解决呢。
因为客户不认可TRUNCATE TABLE，一定要我提出另外的解决方法。
烦恼中…………重建表上的聚集索引就可以了
是把表的索引都删掉再重做聚集索引的意思吧？

Answer 4

可以删了重建，也可以联机重建，视乎你的sqlserver版本

Answer 5

我是sql2008. 联机重建是用SQLManagementStudio来做的意思吗

Answer 6

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
      ON Production.WorkOrder(ProductID)
      WITH (FILLFACTOR = 80,
          PAD_INDEX = ON,
          DROP_EXISTING = ON);
  GO
索引名、表名、列名改一下就可以了

Answer 7

主要是有2个问题：1.一个是表的碎片，不建议你随便把表进行truncate ，也不建议随便 把聚集索引删掉，然后重建，而是要在进行操作之前，先看一下，表的碎片是否很大，大了才需要，重建索引：
--如果avg_fragmentation_in_percent 大于25，那么可以考虑重建索引
select object_name(object_id),
       avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(
db_id('数据库'),                    --数据库id
object_id('数据库.dbo.表名'),   --对象id
null,                               --索引id
null,                               --分区号
'detailed'
);--重建索引
alter index 索引名称on 表名
rebuild2.更新统计信息，由于表中的数据会有增删改，所以数据变化了，但表的各种统计信息可能不变，那么运行sql语句时，sql server 的优化器就会根据错误的统计信息，来生成执行计划，而这个执行计划会导致你的sql语句，慢很多：update statistics 表名称;

Answer 8

如果表的碎片不大，而运行速度不快，就更新统计信息把。在之前公司就遇到了这个问题，表很大，单表有1.5亿条数据，基本上每隔一周，就发现原来运行只需要20秒左右的语句，现在运行5分钟也没反应。而且也建了相应的索引，以前也很快的，怎么就突然慢了呢？再看看表的碎片，也不大，因为这个表大量的操作都是insert，update和delete都不多，其实关键就是数据的统计信息有没反应，表的实际数据量的变化，所以update statistics 表名 就好了。

Answer 9

谢谢各位大神。因为数据库暂时不能用还不能试。
刚才确认了一下表的设计书，没有设计index。 测试用的数据库的那个表也没有创建index。
这样的话是否和现在说的索引碎片有关系呢。

Answer 10

还有yupeigu大神提到的那个查询【avg_fragmentation_in_percent】的sql我执行过了
结果是2条数据表1 66.6666666666667
表1 0

Answer 11

堆表也会有碎片的可能，比如一个表100万数据，刚开始的时候，sqlserver分配了N个区，但是由于后续的增删改操作，导致数据分布越来越混乱（堆表没有什么组织可言），到最后，一个表用了N+M个区，甚至用了2N个区，那么由于堆表只有表扫描这个操作（或者是索引扫描），扫描全表来找数据，需要扫描的区多了，IO开销自然多。当你重新导入之后，区的分配就重组了，IO也相应降低，如果是这个可能，那直接：
alter table 表名 rebuild一下就可以了，没必要truncate那些

Answer 12

感觉是碎片的问题，我以前也遇到过，开始的一张表有4G，之后重新生成之后只有100MB。在2008之后可以用alter table 表名 rebuild，之前的要创建聚集索引重建或者用你的方法重新生成数据，但是时间久了还是这个问题。所以建议用集聚索引。

Answer 13

是的，这个不是很明白，有碎片我也很理解，就是不知道何时要清理，怎么才能断定是碎片引起的效率问题~我也遇到过碎片引起的效率问题，是别人处理的，他也没经过什么细致的检查，不过确实通过重建达到了优化目的，这感觉很不专业啊~索引方面我也还在研究，非常大的范围啊，没500页的书说不清楚，碎片你可以理解为原来没碎片时，只需要N个区就可以存放数据，但是有碎片，原来的数据就分布在大于N个区上，找同样的数据时，需要查找的范围就广了，重建是很粗暴的手段，就好像见到阻塞就kill掉，这是非常不专业的

Answer 14

呵呵，学习了。其实仔细想想，这个碎片率确实不能作为一个绝对的指标来用。之前也试过重新整理表，但说实话，效果不是很大，慢的还是慢，确实是一个需要考虑的因素，但也可能只是作为一个指引两位大神，话说这个值要怎么确认呢？我记得05的技术内幕给的参考值是30，超过重建，低于重组，有没有更具大众化的参考值呢？或者从哪里可以分析出一个有效值呢？
30 is a reference, actually you can adjust based on your sql server performance.  

Answer 15

是的，这个不是很明白，有碎片我也很理解，就是不知道何时要清理，怎么才能断定是碎片引起的效率问题~我也遇到过碎片引起的效率问题，是别人处理的，他也没经过什么细致的检查，不过确实通过重建达到了优化目的，这感觉很不专业啊~这个不一定就是重建索引使得，效率问题得到解决。可能是因为表中的数据有变化，导致表中的索引的统计信息不准确，那么生成的执行计划就不够优化，导致性能问题。而通过重建索引，会重新生成最新的统计信息，那么这样就变相的更新了统计信息，效率问题自然就得到解决，所以一般建议你update statistics 表 来更新统计信息，速度也比较快，不像重建索引，比较耗时费力。

Answer 16

是的，这个不是很明白，有碎片我也很理解，就是不知道何时要清理，怎么才能断定是碎片引起的效率问题~我也遇到过碎片引起的效率问题，是别人处理的，他也没经过什么细致的检查，不过确实通过重建达到了优化目的，这感觉很不专业啊~这个不一定就是重建索引使得，效率问题得到解决。可能是因为表中的数据有变化，导致表中的索引的统计信息不准确，那么生成的执行计划就不够优化，导致性能问题。而通过重建索引，会重新生成最新的统计信息，那么这样就变相的更新了统计信息，效率问题自然就得到解决，所以一般建议你update statistics 表 来更新统计信息，速度也比较快，不像重建索引，比较耗时费力。
嗯，再遇到试试更新统计信息先
不过有没有比较准确的检测方法呢？

Answer 17

是的，这个不是很明白，有碎片我也很理解，就是不知道何时要清理，怎么才能断定是碎片引起的效率问题~我也遇到过碎片引起的效率问题，是别人处理的，他也没经过什么细致的检查，不过确实通过重建达到了优化目的，这感觉很不专业啊~这个不一定就是重建索引使得，效率问题得到解决。可能是因为表中的数据有变化，导致表中的索引的统计信息不准确，那么生成的执行计划就不够优化，导致性能问题。而通过重建索引，会重新生成最新的统计信息，那么这样就变相的更新了统计信息，效率问题自然就得到解决，所以一般建议你update statistics 表 来更新统计信息，速度也比较快，不像重建索引，比较耗时费力。
嗯，再遇到试试更新统计信息先
不过有没有比较准确的检测方法呢？没有什么太好的办法，比如你说查询变慢了，到底是什么原因导致的呢？这个很难说，所以一般的做法比较，比如上个月的时候这个语句运行只需要10秒，这个月怎么就需要10分钟呢，而且查询条件一样，这种一般都是由于产生了比较差的执行计划导致的，而这个执行计划是由优化器产生的，而优化器主要是根据，表的基本信息，比如你的表的记录数，你的表的索引，另外，根据你的where查询条件，或者on的关联条件，来估计你的结果集会有多少条记录，然后计算开销是多少。那么在这个估计的过程中，如果统计信息不准确，就会导致产生错误的执行计划，你可以通过下面的命令来了解，统计信息是否正确：dbcc show_statistics(表,索引名称)返回结果中，包含了详细的统计信息，比方说,你的索引包含了name字段，那么下面的结果就会详细返回在表中的name字段的，所有可能的值，以及每个值的分布，比如值xxx的个数是1000，值yyy的个数是20000，而你可以直接通过语句来验证，这个信息是否正确，比如：select count(*) from tb where name = xxx如果这个语句返回的也是100000，或者相近，那么说明统计信息基本上是正确的，如果相差比较大，比如原来可能是1000，但最近可能删除了一部分，变成了 10，那么就可能导致产生不准确的执行计划，就应该考虑更新统计信息

Answer 18

是的，这个不是很明白，有碎片我也很理解，就是不知道何时要清理，怎么才能断定是碎片引起的效率问题~我也遇到过碎片引起的效率问题，是别人处理的，他也没经过什么细致的检查，不过确实通过重建达到了优化目的，这感觉很不专业啊~这个不一定就是重建索引使得，效率问题得到解决。可能是因为表中的数据有变化，导致表中的索引的统计信息不准确，那么生成的执行计划就不够优化，导致性能问题。而通过重建索引，会重新生成最新的统计信息，那么这样就变相的更新了统计信息，效率问题自然就得到解决，所以一般建议你update statistics 表 来更新统计信息，速度也比较快，不像重建索引，比较耗时费力。
嗯，再遇到试试更新统计信息先
不过有没有比较准确的检测方法呢？上面写了错了一点：
没有什么太好的办法，比如你说查询变慢了，到底是什么原因导致的呢？这个很难说，所以一般的做法比较，比如上个月的时候这个语句运行只需要10秒，这个月怎么就需要10分钟呢，而且查询条件一样，这种一般都是由于产生了比较差的执行计划导致的，而这个执行计划是由优化器产生的，而优化器主要是根据，表的基本信息，比如你的表的记录数，你的表的索引，另外，根据你的where查询条件，或者on的关联条件，来估计你的结果集会有多少条记录，然后计算开销是多少。那么在这个估计的过程中，如果统计信息不准确，就会导致产生错误的执行计划，你可以通过下面的命令来了解，统计信息是否正确：
SQL code
?
1
dbcc show_statistics(表,索引名称)返回结果中，包含了详细的统计信息，比方说,你的索引包含了name字段，那么下面的结果就会详细返回在表中的name字段的，所有可能的值，以及每个值的分布，比如值xxx的个数是100000，值yyy的个数是20000，而你可以直接通过语句来验证，这个信息是否正确，比如：select count(*) from tb where name = xxx如果这个语句返回的也是100000，或者相近，那么说明统计信息基本上是正确的，如果相差比较大，比如原来可能是1000，但最近可能删除了一部分，变成了 10，那么就可能导致产生不准确的执行计划，就应该考虑更新统计信息