Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: how to create performance statistics (was "A Challenging Question")

    How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Add this code before ur query.
    Code:
    set statistics io on
    go
    set statistics time on
    go
    run ur query before making any changes,note down Execution Time, IO (specially logical read).
    Make changes to ur query or add index,
    remove data from cache and force sp to recomplie if u are using sp, by running below code
    Code:
    DBCC DROPCLEANBUFFERS
    go
    DBCC FREEPROCCACHE
    go
    add the first code I mentioned,run the sql statement again , note down execution time,IO.

    compare the result.show to ur manager if it is improved,otherwise take holiday!
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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