Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

High logical reads for insert? ASE 15.5

8 posts in Performance and Tuning Last posting was on 2013-02-01 13:17:49.0Z
rick_806 Posted on 2013-01-30 19:22:24.0Z
Sender: 22cc.51096caa.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: High logical reads for insert? ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510972f0.2498.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Jan 2013 11:22:24 -0800
X-Trace: forums-1-dub 1359573744 172.20.134.41 (30 Jan 2013 11:22:24 -0800)
X-Original-Trace: 30 Jan 2013 11:22:24 -0800, 172.20.134.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13339
Article PK: 1307383

I have a single insert statement that is resulting in over
1300 logical page reads? There is a primary key constraint
on the table but no other index. How can a single insert
cause this much IO? Even assuming I get a page split every
time I insert, I still don't know how it can result in
reading this many pages? The record contains mostly
tinyint's and primary key is numeric(9,0) and no char or
varchar columns. Table is APL and page size is 2K.

thx,
rick_806


Bret Halford Posted on 2013-01-30 22:08:42.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
References: <510972f0.2498.1681692777@sybase.com>
In-Reply-To: <510972f0.2498.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <510999ea$1@forums-1-dub>
Date: 30 Jan 2013 14:08:42 -0800
X-Trace: forums-1-dub 1359583722 172.20.134.152 (30 Jan 2013 14:08:42 -0800)
X-Original-Trace: 30 Jan 2013 14:08:42 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13340
Article PK: 1307384


On 1/30/2013 12:22 PM, rick_806 wrote:
> I have a single insert statement that is resulting in over
> 1300 logical page reads? There is a primary key constraint
> on the table but no other index. How can a single insert
> cause this much IO? Even assuming I get a page split every
> time I insert, I still don't know how it can result in
> reading this many pages? The record contains mostly
> tinyint's and primary key is numeric(9,0) and no char or
> varchar columns. Table is APL and page size is 2K.
>
> thx,
> rick_806
>

Could you post the output of

sp_help <tablename>

and

sp_spaceused <tablename>

?

-bret


--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


rick_806 Posted on 2013-01-30 22:33:11.0Z
Sender: 22cc.51096caa.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <51099fa7.313d.1681692777@sybase.com>
References: <510999ea$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Jan 2013 14:33:11 -0800
X-Trace: forums-1-dub 1359585191 172.20.134.41 (30 Jan 2013 14:33:11 -0800)
X-Original-Trace: 30 Jan 2013 14:33:11 -0800, 172.20.134.41
Lines: 135
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13341
Article PK: 1307385


> On 1/30/2013 12:22 PM, rick_806 wrote:
> > I have a single insert statement that is resulting in
> > over 1300 logical page reads? There is a primary key
> > constraint on the table but no other index. How can a
> > single insert cause this much IO? Even assuming I get a
> > page split every time I insert, I still don't know how
> > it can result in reading this many pages? The record
> > contains mostly tinyint's and primary key is
> > numeric(9,0) and no char or varchar columns. Table is
> APL and page size is 2K. >
> > thx,
> > rick_806
> >
> Could you post the output of
>
> sp_help <tablename>
>
> and
>
> sp_spaceused <tablename>
>
> ?
>
> -bret
>
>
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary
> Support Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
> 80021

Name Owner Object_type Object_status Create_date

MediaFileStreamingVideoindbo user table -- none -- May 15
2010 6:16PM

Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
mf_id numeric 5 9 0 0 0
hasWindowsVideo tinyint 1 NULL NULL 0
0
hasVideoThumbnail tinyint 1 NULL NULL 0
0
hasFlash tinyint 1 NULL NULL 0 0
hasQuicktime tinyint 1 NULL NULL 0 0
hasWinVideoLow tinyint 1 NULL NULL 0
0
hasWinVideoHigh tinyint 1 NULL NULL 0
0
hasPresentation tinyint 1 NULL NULL 0
0
galleryThumbCount smallint 2 NULL NULL 0
0
hasSlateThumbnail tinyint 1 NULL NULL 0
0
hasMP4 tinyint 1 NULL NULL
1 MediaFileS_hasMP4_753801825 0


Object has the following indexes



index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local

XPK_MFStreamingVideoin mf_id clustered, unique
0 0 0 May 15 2010
6:16PM Global Index

index_ptn_name index_ptn_seg

XPK_MFStreamingVideoin_420312332 default



No defined keys for this object.


name type partition_type partitions partition_keys

MediaFileStreamingVideoinbase table roundrobin 1





partition_name partition_id pages row_count segment create_date

MediaFileStreamingVideoin_420312332 420312332 21555
1149211 default May 15 2010 6:16PM




Partition_Conditions






Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)

21555 21555 21555 1 1


Lock scheme Allpages

The attribute 'exp_row_size' is not applicable to tables
with allpages lock scheme.

The attribute 'concurrency_opt_threshold' is not applicable
to tables with allpages lock scheme.



exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts

0 0 0 0 0 0

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg

0 0 0

index_name size reserved unused
XPK_MFStreamingVideoin 368 KB 45468 KB 1990 KB


name rowtotal reserved data index_size unused

MediaFileStreamingVideoin1149211 45468 KB 43110 KB 368
KB 1990 KB


"Mark A. Parsons" <iron_horse Posted on 2013-01-30 22:49:32.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
References: <510972f0.2498.1681692777@sybase.com>
In-Reply-To: <510972f0.2498.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 130112-1, 01/12/2013), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5109a37c$1@forums-1-dub>
Date: 30 Jan 2013 14:49:32 -0800
X-Trace: forums-1-dub 1359586172 172.20.134.152 (30 Jan 2013 14:49:32 -0800)
X-Original-Trace: 30 Jan 2013 14:49:32 -0800, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13342
Article PK: 1307386

The table uses allpages locking but has a unique clustered index ... so that rules out large overflow page chains.

How did you measure the logical IOs, ie, where did you get 1300 from?

How many constraints does the table have? (sp_helpconstraint <table_name>)

Does the table have an insert trigger? (select object_name(instrig) from sysobjects where name = '<table_name>')

What do you get if you perform the insert with 'set showplan on' enabled? (this should show more than just your table if
you have RI constraints and/or a trigger firing for the insert)

What does the actual INSERT statement look like? You haven't stated if this is a simple insert/values statement ... or
an insert/select which is pulling rows from one more more tables joined in a SELECT statement ... ??

On 01/30/2013 12:22, rick_806 wrote:
> I have a single insert statement that is resulting in over
> 1300 logical page reads? There is a primary key constraint
> on the table but no other index. How can a single insert
> cause this much IO? Even assuming I get a page split every
> time I insert, I still don't know how it can result in
> reading this many pages? The record contains mostly
> tinyint's and primary key is numeric(9,0) and no char or
> varchar columns. Table is APL and page size is 2K.
>
> thx,
> rick_806


rick_806 Posted on 2013-01-31 13:25:32.0Z
Sender: 606a.510a6fa3.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510a70cc.60da.1681692777@sybase.com>
References: <5109a37c$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Jan 2013 05:25:32 -0800
X-Trace: forums-1-dub 1359638732 172.20.134.41 (31 Jan 2013 05:25:32 -0800)
X-Original-Trace: 31 Jan 2013 05:25:32 -0800, 172.20.134.41
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13343
Article PK: 1307398


> The table uses allpages locking but has a unique clustered
> index ... so that rules out large overflow page chains.
>
> How did you measure the logical IOs, ie, where did you get
> 1300 from? It came from monSysStatement
>
> How many constraints does the table have?
> (sp_helpconstraint <table_name>) No constraints
>
> Does the table have an insert trigger? (select
> object_name(instrig) from sysobjects where name =
> '<table_name>') No triggers
>
> What do you get if you perform the insert with 'set
> showplan on' enabled? (this should show more than just
> your table if you have RI constraints and/or a trigger
> firing for the insert) Canlt do that, this is Production
>
> What does the actual INSERT statement look like? You
> haven't stated if this is a simple insert/values statement
> .. or an insert/select which is pulling rows from one
> more more tables joined in a SELECT statement ... ?? It is
a insert .. values statement nothing more
>
> On 01/30/2013 12:22, rick_806 wrote:
> > I have a single insert statement that is resulting in
> > over 1300 logical page reads? There is a primary key
> > constraint on the table but no other index. How can a
> > single insert cause this much IO? Even assuming I get a
> > page split every time I insert, I still don't know how
> > it can result in reading this many pages? The record
> > contains mostly tinyint's and primary key is
> > numeric(9,0) and no char or varchar columns. Table is
> APL and page size is 2K. >
> > thx,
> > rick_806


"Mark A. Parsons" <iron_horse Posted on 2013-01-31 13:57:23.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
References: <5109a37c$1@forums-1-dub> <510a70cc.60da.1681692777@sybase.com>
In-Reply-To: <510a70cc.60da.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 130112-1, 01/12/2013), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <510a7843$1@forums-1-dub>
Date: 31 Jan 2013 05:57:23 -0800
X-Trace: forums-1-dub 1359640643 172.20.134.152 (31 Jan 2013 05:57:23 -0800)
X-Original-Trace: 31 Jan 2013 05:57:23 -0800, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13344
Article PK: 1307399

ASE version?

If ASE 15.x, is the dataserver configured to use statement cache? [sp_configure statement; sp_configure literal]

Is the insert a standalone query or part of a stored proc or trigger?

If a standalone query ... do you see the same performance hit if it you the insert in a stored proc and run the stored
proc a few times? [want to see performance on re-using the proc]

Does the login performing the insert get routed through a login trigger? [sp_displaylogin <login>; Wondering if any
settings are being made that could affect performance, eg, disabling statement cache.]

Does the insert show poor performance for all logins that issue the INSERT or just a certain logins?

Do you see this same performance hit on the same table in other dataservers?

Do you see this performance hit for insert/value statements against other tables?

On 01/31/2013 06:25, rick_806 wrote:
>> The table uses allpages locking but has a unique clustered
>> index ... so that rules out large overflow page chains.
>>
>> How did you measure the logical IOs, ie, where did you get
>> 1300 from? It came from monSysStatement
>>
>> How many constraints does the table have?
>> (sp_helpconstraint<table_name>) No constraints
>>
>> Does the table have an insert trigger? (select
>> object_name(instrig) from sysobjects where name =
>> '<table_name>') No triggers
>>
>> What do you get if you perform the insert with 'set
>> showplan on' enabled? (this should show more than just
>> your table if you have RI constraints and/or a trigger
>> firing for the insert) Canlt do that, this is Production
>>
>> What does the actual INSERT statement look like? You
>> haven't stated if this is a simple insert/values statement
>> .. or an insert/select which is pulling rows from one
>> more more tables joined in a SELECT statement ... ?? It is
> a insert .. values statement nothing more
>>
>> On 01/30/2013 12:22, rick_806 wrote:
>>> I have a single insert statement that is resulting in
>>> over 1300 logical page reads? There is a primary key
>>> constraint on the table but no other index. How can a
>>> single insert cause this much IO? Even assuming I get a
>>> page split every time I insert, I still don't know how
>>> it can result in reading this many pages? The record
>>> contains mostly tinyint's and primary key is
>>> numeric(9,0) and no char or varchar columns. Table is
>> APL and page size is 2K.>
>>> thx,
>>> rick_806


rick_806 Posted on 2013-02-01 01:51:06.0Z
Sender: 606a.510a6fa3.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510b1f8a.338c.1681692777@sybase.com>
References: <510a7843$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Jan 2013 17:51:06 -0800
X-Trace: forums-1-dub 1359683466 172.20.134.41 (31 Jan 2013 17:51:06 -0800)
X-Original-Trace: 31 Jan 2013 17:51:06 -0800, 172.20.134.41
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13345
Article PK: 1307420


> ASE version?
>
> If ASE 15.x, is the dataserver configured to use statement
> cache? [sp_configure statement; sp_configure
literal]statement cache-yes, literal-no
>
> Is the insert a standalone query or part of a stored proc
> or trigger?part of a proc that either does a insert or
update. The update results in almost as many logical reads
>
> If a standalone query ... do you see the same performance
> hit if it you the insert in a stored proc and run the
> stored proc a few times? [want to see performance on
> re-using the proc]
>
> Does the login performing the insert get routed through a
> login trigger? [sp_displaylogin <login>; Wondering if any
> settings are being made that could affect performance, eg,
> disabling statement cache.] No
>
> Does the insert show poor performance for all logins that
> issue the INSERT or just a certain logins? app, only one
login
>
> Do you see this same performance hit on the same table in
> other dataservers? no other server
>
> Do you see this performance hit for insert/value
> statements against other tables? Don;t know, I wasn't

looking for this issue when I wrote the scripts to capture
mda data. I was looking for the cause of long waits for a
specific sproc.
>
>
> On 01/31/2013 06:25, rick_806 wrote:
> >> The table uses allpages locking but has a unique
> clustered >> index ... so that rules out large overflow
> page chains. >>
> >> How did you measure the logical IOs, ie, where did you
> get >> 1300 from? It came from monSysStatement
> >>
> >> How many constraints does the table have?
> >> (sp_helpconstraint<table_name>) No constraints
> >>
> >> Does the table have an insert trigger? (select
> >> object_name(instrig) from sysobjects where name =
> >> '<table_name>') No triggers
> >>
> >> What do you get if you perform the insert with 'set
> >> showplan on' enabled? (this should show more than just
> >> your table if you have RI constraints and/or a trigger
> >> firing for the insert) Canlt do that, this is
> Production >>
> >> What does the actual INSERT statement look like? You
> >> haven't stated if this is a simple insert/values
> statement >> .. or an insert/select which is pulling rows
> from one >> more more tables joined in a SELECT statement
> > ... ?? It is a insert .. values statement nothing more
> >>
> >> On 01/30/2013 12:22, rick_806 wrote:
> >>> I have a single insert statement that is resulting in
> >>> over 1300 logical page reads? There is a primary key
> >>> constraint on the table but no other index. How can a
> >>> single insert cause this much IO? Even assuming I get
> a >>> page split every time I insert, I still don't know
> how >>> it can result in reading this many pages? The
> record >>> contains mostly tinyint's and primary key is
> >>> numeric(9,0) and no char or varchar columns. Table is
> >> APL and page size is 2K.>
> >>> thx,
> >>> rick_806


"Mark A. Parsons" <iron_horse Posted on 2013-02-01 13:17:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: High logical reads for insert? ASE 15.5
References: <510a7843$1@forums-1-dub> <510b1f8a.338c.1681692777@sybase.com>
In-Reply-To: <510b1f8a.338c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 130112-1, 01/12/2013), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <510bc07d$1@forums-1-dub>
Date: 1 Feb 2013 05:17:49 -0800
X-Trace: forums-1-dub 1359724669 172.20.134.152 (1 Feb 2013 05:17:49 -0800)
X-Original-Trace: 1 Feb 2013 05:17:49 -0800, vip152.sybase.com
Lines: 75
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13346
Article PK: 1307422

Without running some tests (eg, other logins, other servers, other tables, 'set' options enabled) I'm leaning towards
recompilation overhead. Probably wouldn't hurt to verify auditing and capturing of query metrics/plans is off.

On 01/31/2013 18:51, rick_806 wrote:
>> ASE version?
>>
>> If ASE 15.x, is the dataserver configured to use statement
>> cache? [sp_configure statement; sp_configure
> literal]statement cache-yes, literal-no
>>
>> Is the insert a standalone query or part of a stored proc
>> or trigger?part of a proc that either does a insert or
> update. The update results in almost as many logical reads
>>
>> If a standalone query ... do you see the same performance
>> hit if it you the insert in a stored proc and run the
>> stored proc a few times? [want to see performance on
>> re-using the proc]
>>
>> Does the login performing the insert get routed through a
>> login trigger? [sp_displaylogin<login>; Wondering if any
>> settings are being made that could affect performance, eg,
>> disabling statement cache.] No
>>
>> Does the insert show poor performance for all logins that
>> issue the INSERT or just a certain logins? app, only one
> login
>>
>> Do you see this same performance hit on the same table in
>> other dataservers? no other server
>>
>> Do you see this performance hit for insert/value
>> statements against other tables? Don;t know, I wasn't
> looking for this issue when I wrote the scripts to capture
> mda data. I was looking for the cause of long waits for a
> specific sproc.
>>
>>
>> On 01/31/2013 06:25, rick_806 wrote:
>>>> The table uses allpages locking but has a unique
>> clustered>> index ... so that rules out large overflow
>> page chains.>>
>>>> How did you measure the logical IOs, ie, where did you
>> get>> 1300 from? It came from monSysStatement
>>>>
>>>> How many constraints does the table have?
>>>> (sp_helpconstraint<table_name>) No constraints
>>>>
>>>> Does the table have an insert trigger? (select
>>>> object_name(instrig) from sysobjects where name =
>>>> '<table_name>') No triggers
>>>>
>>>> What do you get if you perform the insert with 'set
>>>> showplan on' enabled? (this should show more than just
>>>> your table if you have RI constraints and/or a trigger
>>>> firing for the insert) Canlt do that, this is
>> Production>>
>>>> What does the actual INSERT statement look like? You
>>>> haven't stated if this is a simple insert/values
>> statement>> .. or an insert/select which is pulling rows
>> from one>> more more tables joined in a SELECT statement
>>> ... ?? It is a insert .. values statement nothing more
>>>>
>>>> On 01/30/2013 12:22, rick_806 wrote:
>>>>> I have a single insert statement that is resulting in
>>>>> over 1300 logical page reads? There is a primary key
>>>>> constraint on the table but no other index. How can a
>>>>> single insert cause this much IO? Even assuming I get
>> a>>> page split every time I insert, I still don't know
>> how>>> it can result in reading this many pages? The
>> record>>> contains mostly tinyint's and primary key is
>>>>> numeric(9,0) and no char or varchar columns. Table is
>>>> APL and page size is 2K.>
>>>>> thx,
>>>>> rick_806