rarely used procedures in db

  • Is there any script which returns the stored procedure names in db which are never or rarely used?

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • This has given some direction but not serving my need exactly...

    can any one please post any query( may be using dmv) to get unused objects list directly..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • There isn't a way to get the list of unused objects directly. You can only try to determine the objects that are in use. Depending on the method you use, that's only going to be over a varied length of time. You can capture all calls using a server-side trace for some period of time and then determine which objects are never called. Or you can pull objects out of the cache using DMV's and put them into a table over a period of time and then determine which objects are not called, but there isn't a maintained list of things that are called an not called built into SQL Server.

    "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 Grant....am clear now.....

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • The following query returns the name, execution count, min/max execution time, etc. for all stored procedures. It can also be extended to include CPU utilization and also performance metrics simply by adding additional columns from sys.dm_exec_query_stats.

    Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it. However, it was re-started a few days ago, then it probably hasn't had time to collect data on all the procedures your application execs during a typical usage cycle (ex: month end reports or quarterly re-partitioning of data).

    select

    so.object_idas ObjectID,

    schema_name(so.schema_id) + '.' + so.name as ObjectName,

    so.create_date as ObjectCreated,

    so.modify_date as ObjectModified,

    sum(qs.execution_count) as total_execution_count,

    sum(qs.total_worker_time) as total_cpu_time,

    min(last_execution_time)as min_execution_time,

    max(last_execution_time)as max_execution_time

    from sys.dm_exec_query_stats AS qs

    cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st

    full outer join sys.objects as so

    on so.object_id = st.objectid

    where

    so.type = 'P' and

    so.is_ms_shipped = 0

    group by

    so.object_id,

    schema_name(so.schema_id),

    so.name,

    so.create_date,

    so.modify_date

    order by

    sum(qs.execution_count) desc,

    so.create_date desc;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • eric_russell 13013 (5/6/2010)


    The following query returns the name, execution count, min/max execution time, etc. for all stored procedures. It can also be extended to include CPU utilization and also performance metrics simply by adding additional columns from sys.dm_exec_query_stats.

    Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it. However, it was re-started a few days ago, then it probably hasn't had time to collect data on all the procedures your application execs during a typical usage cycle (ex: month end reports or quarterly re-partitioning of data).

    select

    so.object_idas ObjectID,

    schema_name(so.schema_id) + '.' + so.name as ObjectName,

    so.create_date as ObjectCreated,

    so.modify_date as ObjectModified,

    sum(qs.execution_count) as total_execution_count,

    sum(qs.total_worker_time) as total_cpu_time,

    min(last_execution_time)as min_execution_time,

    max(last_execution_time)as max_execution_time

    from sys.dm_exec_query_stats AS qs

    cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st

    full outer join sys.objects as so

    on so.object_id = st.objectid

    where

    so.type = 'P' and

    so.is_ms_shipped = 0

    group by

    so.object_id,

    schema_name(so.schema_id),

    so.name,

    so.create_date,

    so.modify_date

    order by

    sum(qs.execution_count) desc,

    so.create_date desc;

    A word of caution, this query may not return all stored procedures. As explained by Adam Machanic here, sys.dm_exec_query_stats will ignore procs or scripts that contain an alter table.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • eric_russell 13013 (5/6/2010)


    Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it

    In addition to not all entries being cached, on more active systems entries may be purged from the cache on a more frequent basis; because the cache is full, because the table statistics changed causing the entry to be re-compiled, etc. Check the column creation_time in sys.dm_exec_query_stats to get a feel for the cache lifetime, and record used procs on a more frequent basis

  • tony.turner (5/7/2010)


    eric_russell 13013 (5/6/2010)


    Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it

    In addition to not all entries being cached, on more active systems entries may be purged from the cache on a more frequent basis; because the cache is full, because the table statistics changed causing the entry to be re-compiled, etc. Check the column creation_time in sys.dm_exec_query_stats to get a feel for the cache lifetime, and record used procs on a more frequent basis

    Or worese yet, since sys.dm_exec_query_stats, and other DMV's, is dependent on the cache, if someone were to runn DBCC FREEPROCCACHE, all that information is gone.

    This data doesn't go back to the time that the server was started.

    "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

  • How appropriate my previous query is depends on how someone intends to use the information. I wouldn't use this info exclusively to decide which stored procedures can be identified as obsolete and dropped, but it is useful when working familiarizing myself with a new database application, to know what are the main procedures that get called and basically how frequently in the last 24 hours.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • A solution that I have implemented is to run a trigger that inserts the data for all running stored procedures into a log table. The trigger was a logon trigger and worked because we had a million users a day.

    An alternative is to query the DMV on a regular basis via a SQL Agent job and to dump the results into a table. You still may miss any procedures that have table alters in them, but it will preserve most of the data.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanking all who responded...i got very useful info...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply