anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen

answer: each update statement update only 1 row
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a crit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======
additional condition
ORDER BY i.rowmodctr DESC
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>