February 10, 2009 at 4:40 am
How to know when the data of tables were viewed in mssql 2000 and mssql 2005? and also when the stored procedures were executed?
Basically I want to know since when tables and stored procedures are not used. Based on this I am going to drop the table and procedure which are not used since 6 months.
Identifying unused tables and stored procedures since last six month.
Please help me.
February 10, 2009 at 5:02 am
You can't automatically do it, some decisions have to be made. Also remember a functionality may exist that would use a table in the database, but just hasn't been used by your users YET. arbitrarily dropping tables without reviewing the code of your application is not a responsible way to tackle this kind of issue.
To at least identify potential tables to drop, though,
Here's how i would do it:
restore a database from 6 months ago.
compare the count(*) of all rows[/url] in OldDB to CurrentDB. and tables that have the same count potentially are not used (or are lookup tables that never change, like a list statuses,counties or states)
of those tables that had the same count, and also are not lookup tables,
I would examine their results from one of the many "Compare Two Databases" scripts from here on SSC.
those that don't have changes might meet the drop criteria after the application code is reviewed.
Lowell
February 10, 2009 at 7:27 am
This won't help you now, but another option available is to collect, and aggregate over time, the procedure calls by running a very lean server-side trace to capture queries run on the database in question. Then you can figure out which procedures are called, which queries are called, and by inference from those procs & queries, which tables are accessed.
But the data isn't built into the system, so you have to find a way to create it for yourself.
Another option for building the data would be regularly capture the information available in the cache by querying the DMV's such as sys.dm_exec_requests or sys.dm_exec_query_stats. You can then aggregate that information into a table somewhere and build up reports over time similar to what you would get from trace. However, since this information is only what is currently in the cache, you may miss queries or procedure calls depending on the frequency of your requests to the DMV and the volatility of the data in your cache.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2009 at 12:00 am
Thanks for reply.
I don't want to use profiler and create job for getting list of unused tables and stored
procedures.
Is there any other way to get list of tables and sps which are not being used since 6 months in mssql 2000 and mssql 2005?
February 11, 2009 at 5:21 am
As Lowell and I both said... No.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2009 at 9:50 pm
I got below mentioned query which will give list of unused table since the SQL services started.It is for MSSQL 2005. Is there any way to get list of unused stored procedures?
SELECT DISTINCT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),I.OBJECT_ID
FROM SYS.INDEXES AS I
INNER JOIN SYS.OBJECTS AS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.OBJECT_ID
NOT IN (SELECT DISTINCT I.OBJECT_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S ,SYS.INDEXES AS I
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID(db_name()))
February 17, 2009 at 4:42 am
You can query the cache to see what procedures are in it and compare that against the list of procedures. However, just because a procedure is not currently in cache doesn't mean it doesn't get used. It just means it hasn't been used recently, but may have just aged out of the cache prior to your query.
If you think it'll help, with very serious caveats in place, I'd try going against sys.dm_exec_procedure_stats. This will give you the object ID and it marks whether or not it's a stored procedure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2009 at 8:47 am
One time I was faced with an old database system and had to figure out which SPs are not being used.
There is no easy and nice way of getting this information so I decided to create my own way of gathering this information.
I created log table which held the names of the stored procedures in my system, number of times used and the time last used. I also created the SP that updated this table whenever called. This SP took one parameter - the name of the SP it was called from and updated the proper record in the log table.
Then the messy part kicks in. Each SP in the database has to be modified to call the log SP. You can do it by hand if your database does not have many SPs or script all SPs and write simple code in VB or C# to modify the sripts to include the call to the log SP.
I don't know if this is going to work for you but I figured a suggestion can not hurt.
The database access was limited to SP calls only so I did not have to deal with the table usage directly.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 18, 2009 at 9:08 am
A trace is the best way to figure out what's being used, and that won't tell you what isn't being used, but it will help. You do need to run it over time to make sure that you get most procs.
Even then, what's the point? Are you looking to remove procedures? They don't take up much space and don't impact the system if they're not being used.
If it's cleanup, I think you would be better off spending time doing something else. If you have other reasons, you can always rename some procs and see if anything breaks. That way you'll have the code around and can "restore" them if there's an issue.
February 18, 2009 at 11:02 am
I totally agree with Steve on that one. The best way is to set up a trace. About the information about the last six months, I don't think there is a way of getting this information either.
Maybe something that could be interesting for you is the "Enable Trace" option. It by default is set up to obtain DDL statements but you could add some specific events, like sp's execution.
The catch with this option is that you would have to see if you suffer any performance issues. It's not supposed to be as heavy as profiler, it's actually supposed to be a lot lighter but you would have to try it out.
Hope it helped in any way.
May 27, 2009 at 4:41 am
Grant Fritchey (2/17/2009)
If you think it'll help, with very serious caveats in place, I'd try going against sys.dm_exec_procedure_stats. This will give you the object ID and it marks whether or not it's a stored procedure.
All good stuff in this thread, but I just want to mention that sys.dm_exec_procedure_stats is new for SQL 2008, in case anyone thinks it's missing on their system 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 2, 2009 at 5:32 am
June 3, 2009 at 6:05 pm
Payal Bansal (6/2/2009)
http://www.sqlservercentral.com/articles/Administration/2582/
To save people clicking, that is the approach outlined by Grant in post 3 on this thread.
It's a reasonably old article and could stand updating for 2K5 and 2K8, but still works as far as I can tell.
There are obvious drawbacks to any mechanism that relies on checking the system caches. A sensible approach might be to use something like this to suggest objects which may not be in use - as long as more reliable checks were done before deciding to remove anything.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 15, 2010 at 7:56 am
using the Dynamic Management View (DMV) in SQL server 2008 and without using the profiler, here's a start to find unsused Stored procedures (you can modify it to use the columns that you want.:
Select *
from sysobjects S
left join sys.dm_exec_procedure_stats X on X.object_id = S.id
where S.xtype in ('P' ,'X')
and X.object_id is null
I hope this helps.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply