SQL Server Performance

set statistics time ON increase query run time???Expected????

Discussion in 'General DBA Questions' started by amu_27, Aug 29, 2007.

  1. amu_27 New Member

    if I run the query without set statistics time and set statistics io than it ran in 5mins 6secs. but if I turn on set statistics time and set statistics io than query runs for 33mins 12secs. Is this expected behaviour?
  2. Luis Martin Moderator

    As far I know no.
    No to that time. It has to run slower but not from 5 to 33 minutes.
  3. amu_27 New Member

    I was surprise too. The query that I have I can see the biggest cost is aggregration and it done out of big table that holds about 6M rows. But having said that I still see this huge difference and it wasn't belivable. I will try to upload some statstics info as well as show text execution plan for my query.
    any one else has experienced this before?
  4. ghemant Moderator

    Hi,
    Ensure statistics are updated, regular index maintenance and you are on latest sp ,mdac level.
    Ensure nother processes is eating up your resource, is this the only query that take this much time!!!
  5. satya Moderator

    It could be another problem, but using these statements doesn't harm the performance.
    Can you try to reproduce the same from another machine?
  6. amu_27 New Member

    Ok this is the execution plan
    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Compute Scalar(DEFINE:([Expr1007]=Convert([Expr1016])))
    |--Hash Match(Aggregate, HASH:([Expr1004], [Expr1005], [Expr1006]), RESIDUAL:(([Expr1004]=[Expr1004] AND [Expr1005]=[Expr1005]) AND [Expr1006]=[Expr1006]) DEFINE:([Expr1016]=COUNT(*), [Tbl_Rats].[Leg_Number]=ANY([Tbl_Rats].[Leg_Number])))
    |--Compute Scalar(DEFINE:([Expr1004]=[dbo].[fnmmyyyy](Convert([Tbl_Rats].[Deal_Date])), [Expr1005]=If ([Tbl_Rats].[Deal_Type]='F') then 'Forwards' else If ([Tbl_Rats].[Deal_Type]='S') then 'Spots' else 'Swaps', [Expr1006]=If ([Tbl_Rats].[Deal_S
    |--Index Seek(OBJECT:([DXAX0].[dbo].[Tbl_Rats].[RBC_Tbl_Rats2]), SEEK:([Tbl_Rats].[Leg_Number]=1) ORDERED FORWARD)
    (4 row(s) affected)
    is this help?
  7. satya Moderator

    Check as referred by Hemant, also ensure to check what has been changed in between those 2 operations. Say if you are executed update stats then it will do good or if there are changes to the data then it will take time to execute next time unless you perform update stats.
  8. amu_27 New Member

    Thanks.
    But I tried running DBReindex and update stats on all the tables and still huge difference. I also have this DBreindex and update stats as weekly process on my all databases. still huge difference Confuse?
  9. amu_27 New Member

    One more piece of information. To my surprise, if I use only statistics IO on than query runs in 5mins but as soon as I turned statistics time on takes 35mins.
    io and time on: 33min 42secs
    (238 row(s) affected)

    SQL Server Execution Times:
    CPU time = 1932391 ms, elapsed time = 2021948 ms.
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.
    only io on: 5mins 22secs
    (238 row(s) affected)
    Table 'Tbl_Rats'. Scan count 1, logical reads 20586, physical reads 0, read-ahead reads 0.
  10. Luis Martin Moderator

    Did you try with other query and/or other PC?
  11. amu_27 New Member

    in process of doing it. seems to be same as query is running more than 25mins right now with statistics time on.
  12. amu_27 New Member

    Confirmation. I tried from 3 different machine and same thing. I don't think this is machine related. Its only statstics time on on query caused long running time (if I refer the execution plan biggest cost is hash aggregation so I am not sure this is specific to issue with this kind of queries?).
  13. Luis Martin Moderator

    I found this in MS:
    When SET STATISTICS TIME is ON, the timestatistics for a statement are displayed. When OFF, the time statisticsare not displayed.
    The setting of SET STATISTICS TIME is set at execute or run time and not at parse time.
    Microsoft SQL Server is unable to provide accurate statistics in fiber mode, which is activated when you enable the lightweight pooling configuration option.
    Could be your case?
  14. thomas New Member

    Long shot, are you using AMD Athlon processors by any chance?
  15. amu_27 New Member

    Nope, I am on intel chip with IBM hardware.
  16. satya Moderator

    Microsoft SQL Server is unable to provide accurate statistics in fiber mode, which is activated when you enable the lightweight pooling configuration option.
    How about Luis's reference, have you enabled it?
    As per your note above the hash aggregate requires memory. Before executing a query with a hash aggregate, SQL Server uses cardinality estimates to estimate how much memory we need to execute the query. So what is the setting of memory in this case?
  17. amu_27 New Member

    This is what I have for lightweight pooling.
    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    lightweight pooling 0 1 0 0
    Now this is on cluster enviornment with 64GB memmory on the server with 16CPUs (physical) and those are dual so virually 32CPUs. We have AWE and PAE enabled. Now having said the memory usage and everything this server is under 30% utilized in total resources as we have cluster on 3 nodes with active/active.
    Now memory usage when you use statistics time on should not be increased so much that your total qurey run time from 5mins to 33mins. This is way off because beyond all that you still doing the same aggregation and calculation as well as data retrival. Isn't it?
  18. amu_27 New Member

    Any suggestion?????
  19. Adriaan New Member

    You probably set this option when you want to monitor some performance issue, right?
    Look at your other options.

Share This Page