Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: Performance Meassurement with SQL Server Profiler

    Hi,

    i am currently developing a new database design and try to compare two designs.

    I am using SQL Server Profiler to meassure the time a query takes. I am using the same data (amount and values), query the same data, same indexes etc. but the query time for the same query over the same data vary way to much (e.g. the same query today is 10 times faster then yesterday)

    I am using a standalone server (no one else works on them), SQL Server 2005, log the queries with Server Profiler into a table and compare the duration time.

    Any ideas whats the problem? It drives me crazy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Query time is typically considered a poor measure for tuning. It is obviously the main criterion you use to identify queries that require tuning but that doesn't make it a terribly good metric to see how your tuning is going.

    Why? Because the time taken is affected by so many other factors, in particular load on the server and network.

    Logical IO is a commonly used metric - this is because it is utterly hardware independent (the same query applied to exactly the same data (including structure and fragmentation) will always return the same value).

    Personally, I would be looking at the output of SET STATISTICS IO, SET STATISTICS TIME and the query plans rather than looking at profiler. I would only use profiler if there were references to multi statement functions that are retrieving their own data.
    Googling about SQL Server query tuning might help you get more information.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    Thanks for your comment, but i am not looking for tuning tips of my database (e.g. indexes). I want to compare two database designs to decide which offer better query time for different selects.

    Is there any way to do this?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly as I put in post #2.
    I would concentrate on the plans and the logical IO.

    If you really want to use profiler, the logical IO is one of the metrics it can gather.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    Ok,

    thanks for help i will try it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •