SQL Server Performance

same query taking different execution times

Discussion in 'Performance Tuning for DBAs' started by vikvin74, Oct 9, 2003.

  1. vikvin74 New Member

    Hi,

    I am facing some strange problem with the SQL Server 2000. I have a table consisting of almost 6 milions records. This table doesn't have any primary key or indexes or clustering. Basically it's a materialized view.

    I was executin some queries since last one months and noting down the time. One such query I am mentioning in here.

    I executed following query:

    SET STATISTICS IO ON
    SET STATISTICS TIME ON
    GO
    (!-- this one is to just collect the statistics)

    SELECT Part,[Cust Nation],Month,SUM([Sales ($)])
    FROM BaseCuboid_1_PCM
    GROUP BYPart,[Cust Nation],Month

    (!-- I got following results)
    ==================================================
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 44 ms.

    (5710004 row(s) affected)

    Table 'BaseCuboid_1_PCM'. Scan count 1, logical reads 103578, physical reads 0, read-ahead reads 103577.

    SQL Server Execution Times:
    CPU time = 222360 ms, elapsed time = 602961 ms.
    ==================================================



    Then again I ran the same query like after half an hour, but to my surprise, this query who was taking 10 minutes to execute, is started taking 20 to 21 minutes. I did some server settings like fixing memory to 185 MB. But latter on I reverted it back to Dynamic memory management. I rebooted the machine and restarted the SQL Server to make sure the changes will put into effect.

    ==============================================
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    (5710004 row(s) affected)

    Table 'BaseCuboid_1_PCM'. Scan count 1, logical reads 103578, physical reads 0, read-ahead reads 101953.

    SQL Server Execution Times:
    CPU time = 282656 ms, elapsed time = 1161715 ms.

    Why this time it took 19:20 minutes that earlier took 10.04 minutes
    ==================================================================

    Third time (Again, I executed the same query second day, and before executing it, I rebooted the machine and restarted the machine). But this time it took 22 minutes

    ==================================================================
    SQL Server parse and compile time:
    CPU time = 30 ms, elapsed time = 62 ms.

    (5710004 row(s) affected)

    Table 'BaseCuboid_1_PCM'. Scan count 1, logical reads 103578, physical reads 0, read-ahead reads 103577.

    SQL Server Execution Times:
    CPU time = 261376 ms, elapsed time = 1444823 ms.
    ==================================================================

    I also have seen the performance monitor, like Process/workingset/sqlser, Processor/page/sec, Memory available in KB. SQL Server/Buffer Management, SQL Server/Target Memory and SQL Server/Total Memory. I didn't see anything wrong in there. I thing I notted is that, process utilization after say 3 minutes of executing the query was down to 25% and never went up. THis was not the case earlier.

    Computer Specifications:
    OS: Windows 2K server Family
    SQL Server: Version 2000
    RAM: 392 MB
    Processor:398 MHz

    And executed query from the same machine. I didn't use any remote client.

    I am doing some research on Data Warehouse. I need query execution times for developing some heuristics and performing some statistical calculations. So I am kind of stuck in here. Though of taking help online as I see many useful resources on your website.

    Any help would be greatly appriciated.

    Vikas Agrawal

  2. Luis Martin Moderator

    What RAID do you have?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. vikvin74 New Member

    Thanks for your reply Luis. I have disk drive of 150 GB. And it's a dedicated sQL server.
  4. Luis Martin Moderator

    Vik:

    No RAID configuration, I mean is only IDE?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. vikvin74 New Member

    Yes, It's just the IDE hard drive, no RAID configuration.
  6. Luis Martin Moderator

    Run Execution Plan with Query Analyzer and see what is goin on.
    Also run Profiler and Index Optimizer, to find out if an Idex is neccesary.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  7. Luis Martin Moderator

    Also, previus before, tell me Physical Disk Queue Lengt when you run the query.
    If is >2, then do post before.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  8. vikvin74 New Member

    Luis,

    Thanks for the reply. However, how can I run Index Optimizer or Profiler? I have never used those.

    Secondly, How can I know about physical disk queue length?

    Sorry, I am not familiar with this technologies. Can you please help.

    Vikas
  9. Luis Martin Moderator

    1) Average Disk Queue Length:

    Open Performance Monitor (Administrative tools in W2K or XP).(Also PerformSQL)

    Chose add (+), select sql server name. In counters you find Physical Disk, one event is Average Disk Queue Length.

    2) In Microsoft SQL Server you can find Profiler.
    Run new trace, after get connection with Server, select Template Name SQL Profiler Tunnig.
    You can save trace when query finish.

    3) Still in Profiler chose: Tools, Index Tuning Wizard, follow the wizard given Trace you save before. Wait to finish and see what recomendations are.

    OR

    1) Open Query Analizer (Microsoft SQL Server)
    2) Select correct Data Base.
    3) Put your Query.
    4) In Query select Index Tuning Wizard and follow same above.

    Hope this help.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  10. vikvin74 New Member

    Avg Disk Queue Length - 1.8 to 2.1

    Profiler Info: CPU-275286, Reads-144965, Writes-12

    I couldn't interpret the Query Execution Plan though

    What should I do next?
  11. Luis Martin Moderator

    Avg. Disk Queue Length is close to poor performance.

    So, try what I said from OR.

    At end of Index Tuning Wizard may be there is a Index to improve perfomance in your Query.
    Whe Index finish, I suggest no apply changes, simple see what will be the improvement.

    We follow after that.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  12. vikvin74 New Member

    But I never had any kind of index on it before either. Reason I am telling this is that table is created by ROLAP operation using OLAP services. It's an aggregation table, with no primary key, no indexes and no clusturing.
  13. Luis Martin Moderator

    Ok. Try this:
    Run sp_updatestats, to update statistics (try after production time). And may be will performance improve.





    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  14. vikvin74 New Member

    I am executing this command. I will let you know the results. I really thankfull to you for your cooperation and time.
  15. vikvin74 New Member

    wow...it worked.....

    this time it just took 12 minutes to execute. But I didn't get the reason though. Where was the problem...and still it's 12 minutes...why not 10 minutes....

    I am very thankfull to you for your kind help. I have been struggling since two days to figure out the solution. But you need to explain it to me though.
  16. Luis Martin Moderator

    SQL Server, create statistics acording tables are used.
    If statistics are no maintenance, SQL lost them.
    Is a good practice to run sp_up... time to time.
    Adicional see in Data Base Propoperities if Auto Update Statistics is ON.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  17. vikvin74 New Member

    Thanks alot for your help.
  18. vikvin74 New Member

    Luis I have one more question. Probably you can help

    Suppose you have 5 queries lined up for almost simultaneous execution. Each query takes say 20 to 30 minutes to execute. Is there any provisions in SQL Server 2000 to prioritize query exectuion sequence. Like eventhough we say it's simultaneous queries, there will be some fraction of second difference between the two queries. I want SQL Server to execute fist query and put the outher in queue till the first one finishes. Then take the second one and execute it and put the other in queue till that is finished and so on...

    Rational behind this is, suppose each single query takes 10 minutes to execute, but if you process simultaneously, each takes 30 to 40 minutes. So there is no point to put on hold all the four different customers. Better approach will be to adopt the First Come First Serve basis. Well this will be higly senario specific though.

    There should be some way to prioritize the query execution in SQL Server 2000.
  19. vbkenya New Member

    There is a thread with this question by the same author. Please do not cross-post. It makes it difficult to track responses or even help.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  20. vikvin74 New Member

    I am really sorry for that. I didn't know this
  21. vikvin74 New Member

    I further imporved the query execution from 12 minutes to almost 9 minutes by setting the "read only" property to true in hte database properties option...just a note
  22. ftine New Member

    Guys, you're great!
    I've been working with a sp for 2 weeks. It was taking 20s on a small computer and 6 minutes on our much better server.
    Solution: sp_updatestats - found here!
    Moderator, sorry for posting here - I had to thank you!
  23. satya Moderator

    Glad to know your solution found without posting a question, don't be sorry.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page