SQL Server Performance

Creating a baseline performance benchmark

Discussion in 'Performance Tuning for DBAs' started by Chappy, Nov 6, 2002.

  1. Chappy New Member

    Hi. My SQL server is under heavy constant use, and no given time period generally experiences the same workload as any other (at least not in such a way that cab be predicted). If I am making changes to my server in an attempt to increase performance, how can I tell that a change I make doesnt impact something else detrimentally?<br />If I were to save a workload from profiler, if I retrace the workload will it actually execute the queries, or just simulate them ?<br /><br />Hope this isnt too dumb <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thanks<br /><br />Chappy
  2. satya Moderator

    Initially you should check for the fine tuning query to get optimum performance, this is the only place where resources get used mostly.

    As you're going to use profiler submit the trace to index tuning wizard for better recommendation.

    Coming to hardware aspect, check with PERFMON where you're lacking.


    Satya SKJ
  3. bradmcgehee New Member

    In many cases, a decision to help boost the performance in one area can hurt the performance in another area. For example, adding additional indexes to a table to speed up SELECTs can hurt the performance of INSERTs on the same table (because of the overhead of the additional indexes).

    In addition, a query run under one server load and then run under a different server load (higher or lower) can sometimes result in slightly different execution plans, which can affect performance over time.

    Now that I said all of this, let me try to answer your question, which is essentially, "if I am making changes to my server to increase performance, how can I tell that a change I make doesn't hurt performance elsewhere?"

    This is no always easy to do, but to help you out, the first thing is to only make one change at a time, and see what happens. Now, if the change is a simple change to a single query, the best way to identify potential problems is to view the execution plan before and after the change and see if the workload has increased or descreased. Yes, the results you get might vary with the workload, but this generally isn't enough to be concerned about.

    If the change is more server-wide, like reducing the amount of memory given to SQL Server, then comparing Performance Monitor baselines before and after the change can be helpful.

    Unfortunately, if the changes are small, you won't be able to easily identify them.

    In regard to your question about Profiler traces, if you do replay them, it does actually execute the queries, which is something you don't want to do on a production server, only on a test server. But the problem with this is that your test server won't be like your production server (in most cases) and this kind of performance test most likely won't be too fruitful.

    Brad M. McGehee
  4. Uri Margalit New Member

    I would recommend you to look at Indepth for SQL Server from Precise Software Solutions.
    The product keeps track on changes in the execution plan and can correlate statements to the tables / indexes.
    It can also show you statements statistics over time. So if you know when yuo made the change, you can see if it make something worst or better.

  5. satya Moderator

    To be precise, as you're in this site lots of goodies on performance are available to follow. Its worth to look'em.

    Satya SKJ

Share This Page