Identifying unused tables and stored procedures since last six month

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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?

  • 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

  • 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()))

  • 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

  • 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]

  • 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.

  • 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.

  • 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 🙂

  • 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

  • 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