none
sql server2000插入数据慢 RRS feed

  • 问题

  •    我使用SQL SERVER2000企业版为我们公司构架一个大型数据库。
       其中有一个表有数千万条记录。今天,我插入(insert)一条数据的时候,居然消耗41秒之多,我试着插入第二条记录,就非常快。 据不完全统计,偶尔会出现插入数据消耗时间较多,这次41秒是记录到的,插入时间最长的一次。
    请问SQL SERVER 2000这样子的表现是否正确?是否有优化的可能?

    2008年6月15日 3:06

答案

  • 索引的优化,直接影响数据库的性能,通常情况下一个表的索引不要超过6个。 数据库性能的优化要从数据库设计、硬件开始,楼主可查看联机的性化说明和事项

    Code Snippet

     

    设计索引时,应考虑以下数据库准则:

    • 一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能,因为在表中的数据更改时,所有索引都须进行适当的调整。

      • 避免对经常更新的表进行过多的索引,并且索引应保持较窄,就是说,列要尽可能少。

      • 使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询(例如 SELECT 语句)的性能,因为查询优化器有更多的索引可供选择,从而可以确定最快的访问方法。

    • 对小表进行索引可能不会产生优化效果,因为查询优化器在遍历用于搜索数据的索引时,花费的时间可能比执行简单的表扫描还长。因此,小表的索引可能从来不用,但仍必须在表中的数据更改时进行维护。

    • 视图包含聚合、表联接或聚合和联接的组合时,视图的索引可以显著地提升性能。若要使查询优化器使用视图,并不一定非要在查询中显式引用该视图。有关详细信息,请参阅设计索引视图

    • 使用数据库引擎优化顾问来分析数据库并生成索引建议。有关详细信息,请参阅数据库引擎优化顾问概述

     

     

     

    Code Snippet

    在设计数据库时,必须确保数据库快速而正确地执行所有重要的函数。某些性能问题可以在数据库投入使用之后进行解决。但是,其他性能问题则可能是由于数据库设计较差导致的,并且只能通过更改数据库的结构和设计来解决。

    在设计和实施数据库时,应该标识出数据库中较大的表和数据库将要执行的较为复杂的进程。在设计这些表时,您还应专门考虑其性能问题。此外,还应考虑能访问数据库的用户数的增加对性能的影响。

    更改设计使性能提高的示例如下:

    • 如果必须对有成千上万行的表编制摘要作为日常报表,可以向(包含预先聚合的仅供报表使用的数据的)表添加一列或多列。

    • 数据库可能被过度规范化。这意味着数据库是由若干较小的相关的表定义的。当数据库处理这些表中的数据时,它必须执行大量的工作来组合相关的数据。这种额外的处理会降低数据库性能。在这种情况下,适当降低数据库规范化程度以简化复杂处理可以提高性能。

    硬件考虑事项

    通常,数据库越大,硬件要求越高。但是,还有其他决定因素。这些因素包括并发用户和并发会话的数量、事务吞吐量和数据库中的操作类型。例如,某数据库包含很少更新的学校图书馆数据,而另外一个 1 TB 数据仓库包含某大公司经常分析的销售、产品和客户信息,则前者比后者的硬件要求低得多。除了磁盘存储要求,数据仓库还需要更多的内存和更快的处理器,以便能够在内存中缓存更多数据,使引用大量数据的查询处理得更快。

    I/O 子系统(或存储引擎)是所有关系数据库的关键组件。一个数据库的成功实施往往需要在项目初期精心计划。关系数据库的存储引擎特别需要这种计划,计划需要确定的内容有:

    • 使用哪种类型的磁盘硬件,如 RAID(独立磁盘冗余阵列)设备。有关详细信息,请参阅关于基于硬件的解决方案

    • 如何将数据放到磁盘上。有关详细信息,请参阅使用文件和文件组

      tempdb 的大小会影响系统性能。例如,如果为 tempdb 定义的大小太小,则每次重新启动 SQL Server (MSSQLSERVER) 服务时,部分系统处理负荷可能就被用于将数据库自动增长到支持工作负荷所需的大小。可以通过增加 tempdb 的大小来避免此开销。

      针对 tempdb 数据库的物理位置和数据库选项设置的一般建议有:

      • 允许 tempdb 数据库根据需要自动扩展。这样可以确保在生成意外的大型中间结果集(存储在 tempdb 数据库中)的查询操作完成前,不会终止这些查询。

      • 将 tempdb 数据库文件的原始大小设置为一个合理大小以避免当需要更多空间时文件自动扩展。如果 tempdb 数据库扩展得过于频繁,性能会受到影响。

      • 将文件增长增量百分比设置一个合理大小以避免 tempdb 数据库文件按太小的值增长。如果文件增长远小于写入 tempdb 数据库的数据量,则 tempdb 可能需要一直扩展。这将影响性能。

      • 将 tempdb 数据库置于快速 I/O 子系统上以确保好的性能。在多个磁盘上条带化 tempdb 数据库以获得更好的性能。将 tempdb 数据库置于用户数据库使用的磁盘之外的磁盘上。有关详细信息,请参阅移动数据库文件

     

     

     

    2008年6月15日 6:23
    版主

全部回复

  • 索引的优化,直接影响数据库的性能,通常情况下一个表的索引不要超过6个。 数据库性能的优化要从数据库设计、硬件开始,楼主可查看联机的性化说明和事项

    Code Snippet

     

    设计索引时,应考虑以下数据库准则:

    • 一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能,因为在表中的数据更改时,所有索引都须进行适当的调整。

      • 避免对经常更新的表进行过多的索引,并且索引应保持较窄,就是说,列要尽可能少。

      • 使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询(例如 SELECT 语句)的性能,因为查询优化器有更多的索引可供选择,从而可以确定最快的访问方法。

    • 对小表进行索引可能不会产生优化效果,因为查询优化器在遍历用于搜索数据的索引时,花费的时间可能比执行简单的表扫描还长。因此,小表的索引可能从来不用,但仍必须在表中的数据更改时进行维护。

    • 视图包含聚合、表联接或聚合和联接的组合时,视图的索引可以显著地提升性能。若要使查询优化器使用视图,并不一定非要在查询中显式引用该视图。有关详细信息,请参阅设计索引视图

    • 使用数据库引擎优化顾问来分析数据库并生成索引建议。有关详细信息,请参阅数据库引擎优化顾问概述

     

     

     

    Code Snippet

    在设计数据库时,必须确保数据库快速而正确地执行所有重要的函数。某些性能问题可以在数据库投入使用之后进行解决。但是,其他性能问题则可能是由于数据库设计较差导致的,并且只能通过更改数据库的结构和设计来解决。

    在设计和实施数据库时,应该标识出数据库中较大的表和数据库将要执行的较为复杂的进程。在设计这些表时,您还应专门考虑其性能问题。此外,还应考虑能访问数据库的用户数的增加对性能的影响。

    更改设计使性能提高的示例如下:

    • 如果必须对有成千上万行的表编制摘要作为日常报表,可以向(包含预先聚合的仅供报表使用的数据的)表添加一列或多列。

    • 数据库可能被过度规范化。这意味着数据库是由若干较小的相关的表定义的。当数据库处理这些表中的数据时,它必须执行大量的工作来组合相关的数据。这种额外的处理会降低数据库性能。在这种情况下,适当降低数据库规范化程度以简化复杂处理可以提高性能。

    硬件考虑事项

    通常,数据库越大,硬件要求越高。但是,还有其他决定因素。这些因素包括并发用户和并发会话的数量、事务吞吐量和数据库中的操作类型。例如,某数据库包含很少更新的学校图书馆数据,而另外一个 1 TB 数据仓库包含某大公司经常分析的销售、产品和客户信息,则前者比后者的硬件要求低得多。除了磁盘存储要求,数据仓库还需要更多的内存和更快的处理器,以便能够在内存中缓存更多数据,使引用大量数据的查询处理得更快。

    I/O 子系统(或存储引擎)是所有关系数据库的关键组件。一个数据库的成功实施往往需要在项目初期精心计划。关系数据库的存储引擎特别需要这种计划,计划需要确定的内容有:

    • 使用哪种类型的磁盘硬件,如 RAID(独立磁盘冗余阵列)设备。有关详细信息,请参阅关于基于硬件的解决方案

    • 如何将数据放到磁盘上。有关详细信息,请参阅使用文件和文件组

      tempdb 的大小会影响系统性能。例如,如果为 tempdb 定义的大小太小,则每次重新启动 SQL Server (MSSQLSERVER) 服务时,部分系统处理负荷可能就被用于将数据库自动增长到支持工作负荷所需的大小。可以通过增加 tempdb 的大小来避免此开销。

      针对 tempdb 数据库的物理位置和数据库选项设置的一般建议有:

      • 允许 tempdb 数据库根据需要自动扩展。这样可以确保在生成意外的大型中间结果集(存储在 tempdb 数据库中)的查询操作完成前,不会终止这些查询。

      • 将 tempdb 数据库文件的原始大小设置为一个合理大小以避免当需要更多空间时文件自动扩展。如果 tempdb 数据库扩展得过于频繁,性能会受到影响。

      • 将文件增长增量百分比设置一个合理大小以避免 tempdb 数据库文件按太小的值增长。如果文件增长远小于写入 tempdb 数据库的数据量,则 tempdb 可能需要一直扩展。这将影响性能。

      • 将 tempdb 数据库置于快速 I/O 子系统上以确保好的性能。在多个磁盘上条带化 tempdb 数据库以获得更好的性能。将 tempdb 数据库置于用户数据库使用的磁盘之外的磁盘上。有关详细信息,请参阅移动数据库文件

     

     

     

    2008年6月15日 6:23
    版主
  •  

    感谢 中國風的回复,我不理解的还有,为什么我碰到的数据库表,插入一行数据到表中,需要41秒, 而平时都是非常快,只是偶尔会慢。  如果SQL SERVER是这么不稳定,我很难使用他用于实时数据库。因为我不知道他什么时候会性能突然下降。
    2008年6月15日 6:39
  • 应该是由于数据阻塞造成的,用事件探查器和以下语句分析

    2000查死锁方法

    Code Snippet

     

    --查死鎖
    declare Roy_lock cursor for
    select
        db_name(dbid),0,blocked
    from
        master..sysprocesses a
    where
        Blocked>0 and
        not exists(select 1 from Master..Sysprocesses where blocked=a.spid)
    union
    select
        db_name(dbid),spid,blocked
    from
        master..sysprocesses a
    where
        Blocked>0
    declare @DBName sysname,@spid bigint,@Blocked bigint
    open Roy_lock
    fetch next from Roy_lock into @DBName,@spid,@Blocked
    while @@fetch_status=0
    begin
        if @spid=0
            print N'鎖定數據庫:'+@DBName+'    語句:'
        else
            print N'鎖定數據庫:'+@DBName+'    進程SPID:'+rtrim(@spid)+' 語句:'
        dbcc inputbuffer(@Blocked)
    fetch next from Roy_lock into @DBName,@spid,@Blocked
    end
    close Roy_lock
    deallocate Roy_lock

     

     

     

    2008年6月15日 6:50
    版主
  • 使用了多种方式确认不是堵塞引起的

     

    1.使用了如上述的方法,查询是否有堵塞

    2、改变了SQL 1433端口,确认SQL没有任何业务在运行,手工执行一个插入语句,执行41秒。再执行一个插入语句,时间忽略不计。之后一个小时内,插入1万条记录,数据库性能表现都很好

     

    一直没有使用SQL SERVER进行大数据量存储,现在有些担心SQL SERVER是否能稳定并且高效的运行。我们的业务,对实效性要求还是比较高的。无法接受插入一条数据等待10秒以上。

     

    2008年6月15日 7:13
  • 建议有条件的情况下,用SQL05的分表区功能实现大容量操作。。。

    2000在数据库设计时就要考虑到,现在只硬件方面考虑和写的代码方面。。

    在2000里实现分区表功能可通过和历史数据单独放在一个表,一段时间内把旧数据搬到历史表,查询时用视图的方式

    数据库建多个文件组放在不同的磁盘上,有利于提高I/O性能

    Code Snippet

     select * from T_history union all select * from T

     

     

    2008年6月15日 7:29
    版主
  • 因为这个只是简单的数据插入,即使使用SQL 05,也担心单个单位数据量多,也同样存在这样子的问题,某一个瞬间,SQL SERVER 2000就会运行很慢。

     

    一种理解是,数据多到一个程度,数据库对索引的维护会是不小的开销。因为数据库使用优化的二叉树(平衡二叉树?),数据达到一个状态,需要对这棵树进行比较大的调整。  现在正测试,是否oracle就不会有这类问题

    2008年6月15日 7:34
  • 不多讲了,以上已回复在2000/2005处理方法,oracle处理大数据量表同样用分区表\分区索引..

    Code Snippet

    因为这个只是简单的数据插入,即使使用SQL 05,也担心单个单位数据量多,也同样存在这样子的问题,某一个瞬间,SQL SERVER 2000就会运行很慢。

     

    自己看联机有简单说明,这不是打几个字就可以让你明白的

    2008年6月15日 8:50
    版主
  •  

    如果联机帮助有简单的说明,能否告诉一个地址或者检索词

     

    看了很多解释,还是没有能说明白,为什么SQL SERVER 在同样的执行条件下,会出现偶尔插入数据很慢的原因以及如何解决。 这不是简单的硬件问题,我们使用的硬件很好,raid5,双核双CPU.

     

    再补充一下,通过I/O观察,再插入一条数据很慢的时候,I/O非常大,插入一条数据很快的后,I/O很小

     

     

     

    2008年6月15日 8:55
  •  

    找到一种可能了。 数据库文件用完了,会默认10%递增,这个时候,会有I/O操作,插入数据也会跟着慢,所以出现插入一条数据要40多秒。   同样的环境测试,插入一条数据要1秒以上的概率小于1%,但是总会有,最长出现过6秒,可能是外部因素干扰了吧

     

    2008年6月15日 12:31
  • Another case is page spliting if inserted row doesn't align with clustered index. 

     

    2008年6月15日 21:56
  • 不知道你的索引的建法和插入的方式。但是如果你偶尔插入慢的话,rmiao 的说法应该是正确的,就是插入引起的page split的问题。你可以在维护的时候考虑用以较小的填充因子重建你的clustered index。但请注意,较小的填充因子将会占用较多的存储空间。

    下面是bol的一篇文章供你参考

     

    Page Splits and Performance Considerations

    When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table.

    When frequent page splits occur, the index can be rebuilt by using a new or existing fill factor value to redistribute the data. For more information, see Reorganizing and Rebuilding Indexes.

    Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

    2008年6月16日 1:52