June 21, 2010 at 12:44 am
Hi experts,
how can i recognize unused tables from a DB. I have refered to the script available in "script" section which uses sys.dm_db_index_usage_stats. But the support team regularly restarts SQL services on the box so results using above query may be inaccurate. Hence I need an alternative and more accurate approach.
Any pointers?
Cheers
June 21, 2010 at 6:12 am
Unfortunately this is a really challenging task.
I had to achieve the same thing and I had to code an application to search the app sources and object text for table names.
App sources means a lot of things:
+ VB sources
+ Java sources
+ DTS packages
+ Jobs
+ SSIS packages
+ Reports
+ .....
Long story short, it's a pain.
-- Gianluca Sartori
June 21, 2010 at 6:25 am
This was removed by the editor as SPAM
June 21, 2010 at 6:34 am
stewartc-708166 (6/21/2010)
try this:
select o.name, o.type_desc, si.name, rowcnt, rowmodctr
from sys.objects o
join sys.sysindexes si on si.id = o.object_id
join sys.indexes i on i.index_id = si.indid
where rowcnt = 0 and rowmodctr = 0
I don't see how this query will return unused objects.
Can you explain, please?
-- Gianluca Sartori
June 21, 2010 at 6:36 am
This was removed by the editor as SPAM
June 21, 2010 at 6:40 am
What about staging type tables that are populated, used then wiped?
p.s. that's 0 record modifications since the last stats update/index rebuild.
p.p.s. Why join in sys.indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 6:40 am
Well, sort of.
Some of my databases have tables to list exceptions to a rule. Sometimes they're empty and stay so for years, but this doesn't mean they're unused at all.
Thanks for your feedback, anyway.
-- Gianluca Sartori
June 21, 2010 at 6:44 am
Ankur
I think you have two options:
(1) Set up a profiler trace, run for a period of time, then analyse.
(2) Periodically dump the contents of the dynamic management views to permanent tables, then analyse them.
I think I prefer option 2, since option 1 won't cover the occasions where a table is referenced via a view, a function or a stored procedure. I think I'm right in saying that option 2 depends upn your having at least one index on each table.
John
June 21, 2010 at 6:48 am
output the results of sys.dm_db_index_usage_stats to a table so you have persistent, historical information.
---------------------------------------------------------------------
June 21, 2010 at 6:49 am
It could be a starter to find unused tables during the time of observation.
Finding totally unused objects, unfortunately, is a bit harder than this.
Simple example: you don't see activity on a view for three months, so you decide to delete it. The next day your CEO runs a report he needs to see every six months and the view isn't there. Be prepared with a good explanation.
-- Gianluca Sartori
June 21, 2010 at 7:03 am
To be safe, any check for unused objects should cover an entire business cycle. If you have reports/queries/jobs that run only on month ends, the check much cover a month end. Same for year end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 11:09 am
In short my condition is all worst now... B'coz:(
1) Primarily this Database is for reporting purpose . There are customized user specific report too. Now the user may run a report in 6 months or may not.
2) We have a number of ETLs pulling daily data to this database and most of these ETLs rely on stagging tables (which do not have indexes and data of'course)
3) We have a set of master tables for lookup. These tables may get updated once in a quarter.
4) Support team restarts SQL Services whenever they feel like ( y? its a long story, cant be explained here)
stewartc-708166--- #2
John Mitchell-245523--- Periodically i would have dumped the DMV data to table but this is a fixed duration project and i dont have enough time to sample the full business cycle (though no business cycle exist here)
Anyways i have got enough option to project to my client now(thankx to you people) Will do as they say, after all its the client who owns the DB:-)
Cheers
June 22, 2010 at 1:41 am
Ankur
You're probably in a better position than you think. You know what your reports are, you know what your master tables are, and you know what your ETL process is. If you analyse your reports and ETL process you will be able to find out what tables they use. As I said before, beware of views, stored procedures and functions that reference underlying tables. Add all those tables to your master tables, and how many tables do you have left in your database that aren't included?
John
June 22, 2010 at 1:55 am
I know the ETLs and Reports that are used but i do not have any list of tables which are being referenced by them. So i was looking for any system tables/ information which can help me in recognizing unused tables.
Cheers
June 22, 2010 at 2:02 am
Both ETL and Reports should have some sort of API that allows you to list referenced tables.
I used them with DTS and Crystal Reports.
It's a bit tricky because you have to go through all possible scenarios (reports, subreports, table aliases...) but now I feel more confident when I move objects to my "recycle bin".
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply