FIX: A DML Operation on a Large Table Can Cause Performance Problems

This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 363863 (SHILOH_BUGS)
SYMPTOMS
Under the following conditions, a data manipulation language (DML) operation that affects either the whole table or a significant portion of it can be much slower than necessary:
  • An UPDATE or DELETE operation is performed on a large table using a join to another table.
  • The large table that is modified has a clustered index.
  • The join is performed by using a non-clustered index on the large table.
RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack

Hotfix information

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

SQL Server 2000 Service Pack 2

This hotfix build is designed to be applied to a server that is running SQL Server 2000 Service Pack 2:
   Date         Time   Version         Size             File name   ----------------------------------------------------------------------------   24-Jan-2003  18:53  2000.80.732.0      29,244 bytes  Dbmslpcn.dll        18-Jan-2003  02:23                    786,432 bytes  Distmdl.ldf   18-Jan-2003  02:23                  2,359,296 bytes  Distmdl.mdf   11-Dec-2002  17:50                        180 bytes  Drop_repl_hotfix.sql   02-Jul-2002  15:35  2000.80.650.0     107,088 bytes  Impprov.dll         17-Jan-2003  20:33                    774,409 bytes  Instdist.sql   11-Dec-2002  17:50                      1,402 bytes  Inst_repl_hotfix.sql   20-Aug-2002  16:24  2000.80.679.0     111,172 bytes  Logread.exe         15-Dec-2002  23:21  2000.80.717.0      90,692 bytes  Msgprox.dll         07-Apr-2002  02:08  2000.80.606.0      62,024 bytes  Odsole70.dll        03-Jan-2002  01:59                     18,185 bytes  Qfe356326.sql   20-Aug-2002  16:47  2000.80.679.0     135,748 bytes  Qrdrsvc.exe         26-Aug-2002  22:49  2000.80.679.0     406,088 bytes  Rdistcom.dll        04-Oct-2001  01:36                    437,302 bytes  Replcom.sql   20-Aug-2002  16:33  2000.80.679.0     152,136 bytes  Replmerg.exe        25-Nov-2002  21:43                  1,004,990 bytes  Replmerg.sql   15-Dec-2002  23:21  2000.80.717.0     221,768 bytes  Replprov.dll        15-Dec-2002  23:21  2000.80.717.0     303,688 bytes  Replrec.dll         22-Jan-2003  19:27                  1,002,101 bytes  Replsys.sql   04-Oct-2001  01:36                    881,228 bytes  Repltran.sql   26-Aug-2002  22:49  2000.80.679.0     283,208 bytes  Rinitcom.dll        16-Sep-2002  22:31                    390,045 bytes  Securityhotfix.sql   26-Aug-2002  22:49  2000.80.679.0      28,672 bytes  Sqlagent.dll        26-Aug-2002  18:39  2000.80.679.0     311,872 bytes  Sqlagent.exe        28-Aug-2002  16:32  2000.80.679.0      49,152 bytes  Sqlagent.rll   26-Aug-2002  22:49  2000.80.679.0      53,824 bytes  Sqlcmdss.dll        28-Aug-2002  16:40  2000.80.679.0      12,288 bytes  Sqlcmdss.rll   14-Jan-2003  21:39  2000.80.725.0     172,600 bytes  Sqlmap70.dll        01-Feb-2003  02:30  2000.80.733.0   7,479,377 bytes  Sqlservr.exe        01-Nov-2002  18:56  2000.80.698.0      45,644 bytes  Sqlvdi.dll          24-Jan-2003  18:53  2000.80.732.0      29,244 bytes  Ssmslpcn.dll        24-Jan-2003  18:53  2000.80.732.0      82,492 bytes  Ssnetlib.dll        02-Jan-2003  20:12  2000.80.722.0      25,148 bytes  Ssnmpn70.dll        07-Apr-2002  02:08  2000.80.606.0      70,208 bytes  Xplog70.dll         07-Apr-2002  02:08  2000.80.606.0      53,828 bytes  Xpqueue.dll         07-Apr-2002  02:08  2000.80.606.0     156,228 bytes  Xprepl.dll          12-Jul-2002  01:00  2000.80.658.0     279,104 bytes  Xpstar.dll          16-Sep-2002  23:12  2000.80.686.0      98,872 bytes  Xpweb70.dll 		

SQL Server 2000 Service Pack 3

This hotfix build is designed to be applied to a server that is running SQL Server 2000 Service Pack 3:
   Date         Time   Version         Size             File name   ------------------------------------------------------------------------   08-Feb-2003  05:43                    786,432 bytes  Distmdl.ldf   08-Feb-2003  05:43                  2,359,296 bytes  Distmdl.mdf   30-Jan-2003  01:55                        180 bytes  Drop_repl_hotfix.sql   01-Feb-2003  00:32  2000.80.763.0   1,557,052 bytes  Dtsui.dll           30-Jan-2003  05:18                    746,470 bytes  Instdist.sql   30-Jan-2003  01:55                      1,402 bytes  Inst_repl_hotfix.sql   08-Feb-2003  06:40  2000.80.765.0      90,692 bytes  Msgprox.dll         08-Feb-2003  03:53                  1,065,895 bytes  Replmerg.sql   08-Feb-2003  06:40  2000.80.765.0     221,768 bytes  Replprov.dll        08-Feb-2003  06:40  2000.80.765.0     307,784 bytes  Replrec.dll         30-Jan-2003  05:18                  1,084,318 bytes  Replsys.sql   08-Feb-2003  06:40  2000.80.765.0     176,696 bytes  Sqlmap70.dll        08-Feb-2003  06:40  2000.80.765.0      57,920 bytes  Sqlrepss.dll        08-Feb-2003  06:41  2000.80.765.0   7,528,529 bytes  Sqlservr.exe        08-Feb-2003  06:40  2000.80.765.0      45,644 bytes  Sqlvdi.dll          08-Feb-2003  06:40  2000.80.765.0      82,492 bytes  Ssnetlib.dll     	

Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

This fix alone does not correct this performance problem. Microsoft has added a new trace flag, 9275, to sort the updates so that they are performed more efficiently when a clustered index exists.

To add trace flag 9275 as a SQL Server startup parameter, follow these steps:
  1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
  2. On the General tab, click Startup Parameters.
  3. Add a new parameter as "-T9275" (without the quotation marks).

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
MORE INFORMATION
Under the conditions described in the "Symptoms" section of this article, the modification may perform slowly because the updates or deletes are performed in the same order as the rows are returned from the join. If the join type is a nested loop or a merge join (both preserve the ordering of the rows), and the non-clustered index key is not correlated with the clustered index key or a hash join (that is not order preserving) is used, the result is that the modification performs random IOs to read the page that will be modified. If there are multiple rows per page, and the table is much larger than the data cache, it may have to read a page multiple times. When you enable trace flag 9275, the set of qualifying rows are sorted based on the clustered key value before the modification so that fewer IOs are required.
Properties

Article ID: 813779 - Last Review: 01/17/2015 06:25:24 - Revision: 11.4

  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000, Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
  • kbnosurvey kbarchive kbhotfixserver kbqfe kbqfe kbfix kbbug KB813779
Feedback