locked
DTA tunning wizard RRS feed

  • Question

  • Can you analyze stored procedure using DTA?

    I would like to run thru SPs in database using DTA to get the best index recomentation .

    what is the best way to do this?

     

     

     

     

    • Moved by Tom Phillips Wednesday, October 20, 2010 1:56 PM Tools question (From:SQL Server Database Engine)
    Tuesday, October 19, 2010 10:26 PM

Answers

  • Can you analyze stored procedure using DTA?
    I would like to run thru SPs in database using DTA to get the best index recomentation .
    what is the best way to do this?  
    You can use DTA to tune the stored procedure but not the way you are looking for.
    DTA needs workload as input. So you have feed either profiler trace or sample execution of stored procedure to tune it.
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, October 20, 2010 12:58 AM
  • The best way is capture busy hour transaction data through profiler. Now on test environment re-run the profiler trace along with DTA to narrow down long running SP or query.

    Also you can use dynamic view to find out long running query or missing indexes
    e.g. sys.dm_db_missing_index_columns, sys.dm_exec_query_stats etc.

    http://msdn.microsoft.com/en-us/library/ms189741(SQL.90).aspx

    Also as stated above do not jump and create indexes unless and untill youdo proper testing.

    HTH

    Wednesday, October 20, 2010 10:39 AM

All replies

  • Can you analyze stored procedure using DTA?
    I would like to run thru SPs in database using DTA to get the best index recomentation .
    what is the best way to do this?  
    You can use DTA to tune the stored procedure but not the way you are looking for.
    DTA needs workload as input. So you have feed either profiler trace or sample execution of stored procedure to tune it.
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, October 20, 2010 12:58 AM
  • See what DTA suggested but  be in no hurry to implement them. People write books for the subject....Make sure that putting index on specific column you do not break another query,means you are about to do lots of testing
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 8:24 AM
  • See what DTA suggested but  be in no hurry to implement them. People write books for the subject....Make sure that putting index on specific column you do not break another query,means you are about to do lots of testing
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 8:24 AM
  • The best way is capture busy hour transaction data through profiler. Now on test environment re-run the profiler trace along with DTA to narrow down long running SP or query.

    Also you can use dynamic view to find out long running query or missing indexes
    e.g. sys.dm_db_missing_index_columns, sys.dm_exec_query_stats etc.

    http://msdn.microsoft.com/en-us/library/ms189741(SQL.90).aspx

    Also as stated above do not jump and create indexes unless and untill youdo proper testing.

    HTH

    Wednesday, October 20, 2010 10:39 AM