Post reply

DTA - Database tuning advisour?

  • August 22, 2012 at 5:12 am

    #280047

    Hi,

    DTA - will detect only database side index and statistic recommendation. but it is not suggest to re-writing Query, SP etc..

    For creating workload analysis file for server process data such as RPC-completed, SP-Stmtcompleted etc.. could you suggestion me can we create trace file at server side or client side?

  • August 22, 2012 at 6:03 am

    #1528570

    You should always create your trace files server-side, since this places least load on the server. SQL Profiler is a client-side wrapper for a server-side process, as such the sheer overhead of maintaining the rowset provider can hurt performance.

    Don't use Profiler for the trace, instead export the trace definition (File menu), play with it in a new query window and run it. You can see the event count and size by using:

    SELECT file_position, event_count

    FROM sys.traces

    WHERE id = 2

    If the trace gets too large too quickly, stop it by running:

    EXEC sp_trace_setstatus @traceid = 2, @status = 0

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • August 22, 2012 at 7:29 am

    #1528624

    DTA will give you index suggestions. Don't create them all without checking. Test them out, implement the ones that help, ignore any that don't. The tool is not infallible.

    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

Viewing 3 posts - 1 through 2 (of 2 total)

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