Re: Performance issue with row size and order by

From: wei xiao [MSFT] (weix_at_online.microsoft.com)
Date: 10/06/04

Next message: Jonathan Yong: "Re: schedule sql file"
Previous message: \: "schedule sql file"
In reply to: BGK: "Re: Performance issue with row size and order by"
Next in thread: BGK: "Re: Performance issue with row size and order by"
Reply: BGK: "Re: Performance issue with row size and order by"
Messages sorted by: [ date ] [ thread ]

Date: Tue, 5 Oct 2004 18:38:33 -0700

You are probably observing in-memory sort versus on-disk sort. 800 bytes
should not have any particular significance. You can to change the number
of rows you are sorting and see if 800 is still an interesting data point.

You have 500K rows with average size of 1K, and how much memory does your
machine have? It seems you are in the boundary of in-memory versus on-disk.
Please turn on windows performance monitor and see if IO pattern is
different.

The amount of memory sort uses is dynamically tuned. you can play with
sp_configure "min memory per query Option" to see if it makes a difference.
But as BOL says:

"Increasing the value of min memory per query may improve performance for
some small to medium sized queries, but could lead to increased contention
for memory resources. min memory per query includes memory allocated for
sorting and replaces the sort pages option in SQL Server version 7.0 or
earlier."

So please be careful if you intend to increase this configuration parameter
in a production system.

-- 
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"BGK" <BGK@discussions.microsoft.com> wrote in message 
news:27C8128D-6A86-40A5-BDD9-E1FF1E71235D@microsoft.com...
> Great thx for the help!
>
> The table does not contain primary or cluster index and no primary key. 
> The
> table is as follows (SQL 2000 SP3a) (no relation to other tables):
>
> CREATE TABLE [CUSTINVOICEJOUR_Test] (
> [CUSTGROUP] [varchar] (10) DEFAULT (''),
> [REFNUM] [int] NOT NULL DEFAULT (0),
> [SALESID] [varchar] (20) DEFAULT (''),
> [ORDERACCOUNT] [varchar] (12) DEFAULT (''),
> [INVOICEACCOUNT] [varchar] (12) DEFAULT (''),
> [INVOICEDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
> [DUEDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
> [CASHDISC] [numeric](28, 12) NOT NULL DEFAULT (0),
> [CASHDISCDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
> [QTY] [numeric](28, 12) NOT NULL DEFAULT (0),
> [VOLUME] [numeric](28, 12) NOT NULL DEFAULT (0),
> [WEIGHT] [numeric](28, 12) NOT NULL DEFAULT (0),
> [COSTVALUE] [numeric](28, 12) NOT NULL DEFAULT (0),
> [SUMLINEDISC] [numeric](28, 12) NOT NULL DEFAULT (0),
> [SALESBALANCE] [numeric](28, 12) NOT NULL DEFAULT (0),
> [ENDDISC] [numeric](28, 12) NOT NULL  DEFAULT (0),
> [INVOICEAMOUNT] [numeric](28, 12) NOT NULL DEFAULT (0),
> [CURRENCYCODE] [varchar] (3) DEFAULT (''),
> [EXCHRATE] [numeric](28, 12) NOT NULL DEFAULT (0),
> [SALESADMINISTRATOR] [varchar] (10) DEFAULT (''),
> [INVOICEID] [varchar] (20) DEFAULT (''),
> [LEDGERVOUCHER] [varchar] (20) DEFAULT (''),
> [UPDATED] [int] NOT NULL DEFAULT (0),
> [DIMENSION] [varchar] (10) DEFAULT (''),
> [DIMENSION2_] [varchar] (10) DEFAULT (''),
> [DIMENSION3_] [varchar] (10) DEFAULT (''),
> [ONACCOUNTAMOUNT] [numeric](28, 12) NOT NULL DEFAULT (0),
> [TAXPRINTONINVOICE] [int] NOT NULL DEFAULT (0),
> [LISTCODE] [int] NOT NULL DEFAULT (0),
> [PRINTED] [int] NOT NULL DEFAULT (0),
> [DOCUMENTNUM] [varchar] (20) DEFAULT (''),
> [DOCUMENTDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
> [INTRASTATDISPATCH] [varchar] (20) DEFAULT (''),
> [DELIVERYNAME] [varchar] (40) DEFAULT (''),
> [DELIVERYADDRESS] [varchar] (250) DEFAULT (''),
> [PURCHASEORDER] [varchar] (20) DEFAULT (''),
> [DLVTERM] [varchar] (10) DEFAULT (''),
> [DLVMODE] [varchar] (10) DEFAULT (''),
> [PAYMENT] [varchar] (10) DEFAULT (''),
> [CASHDISCCODE] [varchar] (10) DEFAULT (''),
> [INVOICEROUNDOFF] [numeric](28, 12) NOT NULL  DEFAULT (0),
> [SUMMARKUP] [numeric](28, 12) NOT NULL DEFAULT (0),
> [COVSTATUS] [int] NOT NULL DEFAULT (0),
> [RETURNITEMNUM] [varchar] (10) DEFAULT (''),
> [POSTINGPROFILE] [varchar] (10) DEFAULT (''),
> [BACKORDER] [int] NOT NULL DEFAULT (0),
> [DLVZIPCODE] [varchar] (10) DEFAULT (''),
> [DLVCOUNTY] [varchar] (10) DEFAULT (''),
> [DLVCOUNTRY] [varchar] (10) DEFAULT (''),
> [DLVSTATE] [varchar] (10) DEFAULT (''),
> [TAXGROUP] [varchar] (10) DEFAULT (''),
> [TAXITEMGROUP] [varchar] (10) DEFAULT (''),
> [TAXSPECIFYTOTAL] [int] NOT NULL DEFAULT (0),
> [TAXSPECIFYBYLINE] [int] NOT NULL DEFAULT (0),
> [ONETIMECUSTOMER] [int] NOT NULL DEFAULT (0),
> [SUMTAX] [numeric](28, 12) NOT NULL DEFAULT (0),
> [SALESTYPE] [int] NOT NULL DEFAULT (0),
> [PARMID] [varchar] (20) DEFAULT (''),
> [EUSALESLIST] [varchar] (20) DEFAULT (''),
> [EXCHRATESECONDARY] [numeric](28, 12) NOT NULL DEFAULT (0),
> [TRIANGULATION] [int] NOT NULL DEFAULT (0),
> [CUSTOMERREF] [varchar] (40) DEFAULT (''),
> [PORT] [varchar] (10) DEFAULT (''),
> [NUMBERSEQUENCEGROUP] [varchar] (10) DEFAULT (''),
> [LANGUAGEID] [varchar] (5) DEFAULT (''),
> [INCLTAX] [int] NOT NULL DEFAULT (0),
> [LOG] [varchar] (255) DEFAULT (''),
> [PAYMDAYID] [varchar] (10) DEFAULT (''),
> [INVOICINGNAME] [varchar] (40) DEFAULT (''),
> [INVOICINGADDRESS] [varchar] (250) DEFAULT (''),
> [INVZIPCODE] [varchar] (10) DEFAULT (''),
> [INVCOUNTY] [varchar] (10) DEFAULT (''),
> [INVCOUNTRY] [varchar] (10) DEFAULT (''),
> [INVSTATE] [varchar] (10) DEFAULT (''),
> [GIROTYPE] [int] NOT NULL DEFAULT (0),
> [GIROACCOUNTID] [varchar] (10) DEFAULT (''),
> [CONTACTPERSONID] [varchar] (10) DEFAULT (''),
> [SALESORIGINID] [varchar] (10) DEFAULT (''),
> [BILLOFLADINGID] [varchar] (17) DEFAULT (''),
> [INVENTLOCATIONID] [varchar] (10) DEFAULT (''),
> [IKREFTYPE] [int] NOT NULL DEFAULT (0),
> [IKREFNUMBER] [varchar] (12) DEFAULT (''),
> [EDIINVOICEMSGID] [varchar] (10) DEFAULT (''),
> [MYLORDPRINTWHEN] [int] NOT NULL DEFAULT (0),
> [MYLCUSSUMMONTHLYDISC] [numeric](28, 12) NOT NULL DEFAULT (0),
> [CREATEDDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
> [CREATEDTIME] [int] NOT NULL DEFAULT (0),
> [CREATEDBY] [varchar] (5) DEFAULT ('?'),
> [DATAAREAID] [varchar] (3) DEFAULT ('dat'),
> [RECID] [int] NOT NULL ,
> [MYLCUSDISCORDER] [int] NOT NULL DEFAULT (0),
> [MYLDISTRICT] [varchar] (3) DEFAULT ('')
> ) ON [PRIMARY]
> GO
>
>
> Indexes:
> CREATE  INDEX [I_062SALESIDDATEIDX] ON
> [dbo].[CUSTINVOICEJOUR]([DATAAREAID], [REFNUM], [SALESID], [INVOICEDATE]) 
> ON
> [PRIMARY]
> GO
>
> CREATE  INDEX [I_062INVOICENUMIDX] ON 
> [dbo].[CUSTINVOICEJOUR]([DATAAREAID],
> [INVOICEID], [INVOICEDATE], [NUMBERSEQUENCEGROUP]) ON [PRIMARY]
> GO
>
> CREATE  INDEX [I_062INVOICEACCOUNTIDX] ON
> [dbo].[CUSTINVOICEJOUR]([DATAAREAID], [INVOICEACCOUNT], [INVOICEDATE],
> [EDIINVOICEMSGID]) ON [PRIMARY]
> GO
>
> CREATE  INDEX [I_062ORDERACCOUNTIDX] ON
> [dbo].[CUSTINVOICEJOUR]([DATAAREAID], [ORDERACCOUNT], [INVOICEDATE]) ON
> [PRIMARY]
> GO
>
> CREATE  UNIQUE  INDEX [I_062RECID] ON 
> [dbo].[CUSTINVOICEJOUR]([DATAAREAID],
> [RECID]) ON [PRIMARY]
> GO
>
>
> "wei xiao [MSFT]" wrote:
>
>> Can you post the schema of your talbe? Does it contain a primary key?
>>
>> -- 
>> Wei Xiao [MSFT]
>> SQL Server Storage Engine Development
>>
>> This posting is provided "AS IS" with no warranties, and confers no 
>> rights.
>>
>>
>> "BGK" <BGK@discussions.microsoft.com> wrote in message
>> news:F5D1C066-62FD-417E-92F3-02803DDF2F4F@microsoft.com...
>> > 1. No, the execution plan is the same
>> > 2. Yes I need all the rows
>> >
>> > The question is place is not if I need all the rows or not. I want to 
>> > know
>> > WHY this happens and if there are some settings in SQL that can affect
>> > this.
>> > Is this a bug in SQL, does SQL change sorting algorithm when row size
>> > increases or ...
>> >
>> > Regards,
>> > BGK
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> IS there a different execution plan? Also, do you really need the all
>> >> columns (SELECT *`)?
>> >>
>> >> -- 
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "BGK" <BGK@discussions.microsoft.com> wrote in message
>> >> news:64C44095-89F7-4EED-BCF2-74A5F6688CB8@microsoft.com...
>> >> >I have a table with approx. 90 columns and 500.000 records. The row 
>> >> >size
>> >> >is
>> >> > approx. 1100 bytes.
>> >> >
>> >> > If I select rows from this table with "order by" on a non-indexed 
>> >> > field
>> >> > it
>> >> > takes a looooooong time. If I, on the other hand, reduce the number 
>> >> > of
>> >> > columns unitl the row size is slightly less than 800 bytes the 
>> >> > result
>> >> > is
>> >> > returned in a short time. The difference is 50 fold!!!
>> >> >
>> >> > "select top 100 * from myTable order by nonIndexedField" takes 420 
>> >> > sec.
>> >> > "select top 100 <fieldList> from myTable order by nonIndexedField"
>> >> > takes 10
>> >> > sec.
>> >> >
>> >> > I have tried this on several different SQL databases with different
>> >> > processor power, different SQL versions (Enterprise, Standard),
>> >> > different
>> >> > memory configuration etc.
>> >> >
>> >> > What I'm extremely interested in, is what happens around the 800 
>> >> > byte
>> >> > row
>> >> > size threshold that causes this and if there are some workarounds.
>> >> >
>> >> > The profile for the select looks like this:
>> >> >
>> >> >    Time sec
>> >> >    ^
>> >> >    |
>> >> >    |                                           --------
>> >> > 400 -                                  ---------
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                                 |
>> >> >    |                      -----------
>> >> >    |           -----------
>> >> > 10 - ----------
>> >> >    |
>> >> >    |
>> >> >    |-----------------|---------------|---------> Row size
>> >> >
>> >> > 0 byte            400 bytes       800 bytes
>> >> >
>> >> > The time is almost linear until the c.a. 800 byte threshold is 
>> >> > reached.
>> >> >
>> >> > Any suggestions?
>> >> >
>> >> > Regards,
>> >> > BGK
>> >>
>> >>
>> >>
>>
>>
>>

Next message: Jonathan Yong: "Re: schedule sql file"
Previous message: \: "schedule sql file"
In reply to: BGK: "Re: Performance issue with row size and order by"
Next in thread: BGK: "Re: Performance issue with row size and order by"
Reply: BGK: "Re: Performance issue with row size and order by"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: FTS Performance in SQL 2005
... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
(microsoft.public.sqlserver.fulltext)
Re: FTS Performance in SQL 2005
... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
(microsoft.public.sqlserver.fulltext)
RE: Consolidating instances of SQL server
... SQL Server is designed to use memory. ... The maximum amount of memory SQL Server can use varies depending on your ... This newsgroup only focuses on SBS technical issues. ...
(microsoft.public.windows.server.sbs)
RE: The DBMS returned an unspecified error.
... | We are using Microsoft's JDBC driver for Java database access to SQL ... | Insufficient memory available. ... even though SQL Server 2000 is configured to use up to the ... the problem is most likely in the MemToLeave ...
(microsoft.public.sqlserver.jdbcdriver)
RE: multiple instances of sql - 2 Gb memory limitation?
... The 2GB Memory limitation is per instance and the SQL Server can be ... Configure memory for more than 2 GB in SQL Server ... Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter ...
(microsoft.public.sqlserver.server)