Post reply

SET STATISTICS IO setting increases CPU time

  • August 17, 2011 at 8:35 pm

    #390213

    Greetings People!

    I am seeing a wierd thing in my SQL Server. I was doing some performance optimization of a code and i was collecting metrics by setting STATISTIC IO, TIME ON and OFF. I noticed something weird there.

    When i had SET STATISTICS IO, TIME ON (basically getting stats of both IO and TIME), i was getting these metrics:

    When i had only SET STATISTICS TIME ON (getting just TIME information), i was getting these metrics:

    Why would my CPU time increase by just changing IO ? I swear i did nothing but removing IO between each runs :w00t:. Have you guys faced this error? Any resolutions ?

    Anyways, the good news is, i am successful in optimizing the code 🙂 Got 7000 ms SP to run under 43 ms 🙂 😎

    Oh btw, i am running SQL Server 2008 R2 RTM ( Build : 10.50.1617.0 ) on Windows 7.

    Thanks in advance for any help.

  • August 18, 2011 at 2:13 am

    #1369971

    Don't draw conclusions from one execution.

    Run either one a few times, you may well see the CPU time vary, it happens.

    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

  • August 18, 2011 at 10:51 am

    #1370252

    Gail, i ran the tests for around 25 times.. i still see that behavior.. any weird bug in the system?

  • August 18, 2011 at 10:59 am

    #1370258

    I went on and ran the tests , one at a time, around 50 times. Now i see a difference. Both tests are yeilding more or less the same metrics. Thanks Gail.

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

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