February 4, 2009 at 7:59 am
Hi,
A few weeks back I found a script on the web that would list all tables in a database and the number of times that had been updated since the last time sp_updatestats had been run on the database - output was as follows:
table_name index_name rows_modified
tblSearchLog_ArchivetblSearchLog_Archive4092428
tblStoredPCSectortblStoredPCSector604738
tblCircRead tblCircRead 234689
Of course I didnt bookmark where I found it and need it again - does anyone have a copy of this script or know how to create it.
Basically I just need to know for every table in a database the number of rows that have been modified since last sp_updatestats.
I am using SQL SERVER 7 (I know!!)
Thanks in advance,
JK
February 12, 2009 at 4:55 am
Hi,
Still searching for this one - any help really appreciated,
Cheers,
Jack
February 12, 2009 at 5:11 am
I searched SSC for
stats_date for all indexes
and it came up with:
www.sqlservercentral.com/scripts/Index+Management/30041/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2009 at 10:13 am
Hi Alzdba - thanks for the reply - not quite what I was looking however....
I did manage to get this query that can run against a database:
SELECT distinct sysobjects.name AS TableName,
sysindexes.rowmodctr AS RowsModified
FROM sysobjects JOIN sysindexes ON
sysobjects.id = sysindexes.id
ORDER BY RowsModified DESC
This will return all table names and the value for rowmodctr - what I would like to know is what exactly is the figure returned in rowmodctr telling me - is it showing the number of rows in the table that have been updated since the last sp_updatestats command was executed?
any help greatly appreciated - I am a bit confused.....
February 12, 2009 at 3:25 pm
Yes, use the sys.indexes catalog view instead.
http://msdn.microsoft.com/en-us/library/ms190283.aspx
MJ
February 12, 2009 at 4:01 pm
Jack - Query is giving you the number of rows updated after the stats are last updated.
--Sudhie
February 13, 2009 at 12:13 am
Jack Kennedy (2/12/2009)
..
I managed to copy / past the wrong hyperlink :blush:
Combine your query with this one :
SELECT object_name(i.id)table_name
, i.rows
, i.rowmodctr
, i.name as ixname
, STATS_DATE(i.id, i.indid) as ix_Statistics_Date
FROM sysindexes i
WHERE i.name not like'sys%'
I tested it on our last SQL7 :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2009 at 4:39 am
Hi everyone - thanks so much for the replies - starting to make sense now but as I read this definition of rowmodctr from the microsoft site:
Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
it shows that it would not give me the figures I need. I am looking for the number of rows inserted and updated in a table since a given date.
I guess I could write a script to run nightly - get the number of rows in the table since yesterday and subtract it from updated rows to give me number of updated rows - problem then is that if rows are deleted in that time period then it makes the figures fairly useless....
Any ideas anyone? I am thinking that it cant be that uncommon a task to track the updates made to rows in a table - remembering of course that I am still using SQL Server 7 - I am sure there is probably an easier way in 2005/2008
I know I could use triggers but there is no many tables (over 200) it would be incredibly time consuming and I am afraid the overhead would be too great....
Cheers,
Jack
February 13, 2009 at 4:59 am
My guess is , if you want to know exact figures, you'll have to implement a trigger for insert, update, delete and have a counter per PK value in your trigger receiving table.
Statistics may get updated more than once during the day .... depending on the load of the object
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2009 at 5:51 am
Hi Alzdba - yes you are right - might have to use triggers in that case - but as there are over 200 tables in the database I was hoping not too - I might be able to get the requester to narrow down the number of tables to monitor - thanks for your help.....
February 13, 2009 at 6:11 am
If this is a single shot operation , just to get a little insight, maybe a log-analyser tool can help out. (at least if you have full recovery model).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2009 at 6:17 am
No - its a simple recovery model on the database and it is not a one off. Basically marketing want to know the number of Inserts and Updates made to their data on a monthly basis - I think I should be able to get them to narrow it down to a few tables so Triggers might do the trick...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply