Post reply

How to know what are the most used store procedures and functions in a Data Base

  • August 26, 2010 at 2:59 pm

    #223118

    Hi,

    Hi, I'm using SQL Server 2005, I was wondering if there is a command which result is a list of the most used store procedures and functions in a system.

    I need this information in order to create new indexes because the database does not have good ones, they're asking me for a research on this.

  • August 26, 2010 at 6:15 pm

    #1212672

    You should start with sys.dm_db_missing_index_details. This can possibly provide you with some information to begin with.

    You can also use SQL Profiler to capture the queries and from there find out the costliest queries and trying to add appropriate indexes.

    You can also refer to cache plans to see and explore how your database is being used and even the query plans.

  • August 26, 2010 at 6:21 pm

    #1212675

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 26, 2010 at 6:28 pm

    #1212677

    To the question of finding which procedures or functions are most commonly used:

    Some will go to the length of altering all procs and adding a piece of code that inserts audit (proc name and execution start time) into a table. This code would be executed every time the proc executes.

    This of course would take some time to get to know what has actually run or not as well as the frequency.

    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

  • August 27, 2010 at 4:49 pm

    #1213331

    Thanks !!

    That links help me a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

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