October 19, 2009 at 10:21 am
Guys:
Recently i was given a task to Identify what tables are being used by the application mostly (In terms of transactions) and identify the growth of those tables.
is there any tool / mechanism / script to find out wat tables are mostly used for data insertion by the users?
any relavant info would be highly appreciated
Regards
Jus
October 19, 2009 at 12:00 pm
Use the ROWMODCTR Column in the sysindexes.
It maintains the number of records that have been changed (Inserted, Updated or deleted)
Use the Following Code to get a list of tables with most # if changes
SELECT B.NAME,A.NAME IND_OR_STATS_NAME,A.ROWMODCTR
FROM SYSINDEXES A
INNER JOIN SYSOBJECTS B
ON A.ID = B.ID
WHERE A.ROWMODCTR > 10000 --- IF YOU NEED TO FIND THE TABLES WITH MORE THAN 10,000 CHANGES
ORDER BY A.ROWMODCTR DESC
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 19, 2009 at 12:30 pm
-- list most used tables
-- SQLServermagazine instantDoc 53878
--
SELECT
t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups) AS 'Total_accesses',
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
, min(s.login_time) as StartUp_DateTime
, min(s.Years) as ServerUp_Years
, min(s.Months) as ServerUp_Months
, min(s.Days) as ServerUp_Days
, min(s.Timepart) as ServerUp_Timepart
FROM sys.tables t
LEFT OUTER JOIN sys.dm_db_index_usage_stats i
ON t.[object_id] = i.[object_id]
inner join (SELECT session_id, login_time
, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from ( SELECT session_id, login_time , getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM sys.dm_exec_sessions
WHERE session_id = 1
) a
) s
on s.session_id = 1
GROUP BY
i.[object_id],
t.[name]
ORDER BY [Total_accesses] DESC ;
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
October 19, 2009 at 12:51 pm
Thanks AlzDBA ..
Can you please explain "how the result can be anayzed by looking at Total_accesses, Seeks,Scans and lookups"?
I gues..This is going to be big learning for me...
Regards
Jus
October 19, 2009 at 1:09 pm
Remember that the values returned from sys.dm_db_index_usage_stats are only accurate since the last SQL restart because they get reset to 0:
From BOL
"... The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed."
October 19, 2009 at 7:31 pm
Some infor regarding rowmodctr:
It counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions.
http://msdn.microsoft.com/en-us/library/ms190283.aspx
MJ
October 20, 2009 at 7:48 am
BOL has all info I have 😉
http://msdn.microsoft.com/en-us/library/ms188755.aspx
Keep in mind a stop/restart of your sqlserver instance will reset these data !!
If you want this info long term, you'll have to persist it yourself by copying that data into your own table.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply