SQL Server Performance

very Strange problem on SQL 2000 - help

Discussion in 'Performance Tuning for DBAs' started by ravism, Sep 29, 2003.

  1. ravism New Member

    Our production database is 10 GB in size. 14 months since live. The server by itself is a very prowerful server with 4 processers and 2gb ram and the works.

    And since last week the server freazes for about an hour and simple select statements take for ever to execute. And then the system runs as if there was nothing wrong at all. The last time it happened was this evening at 8pm and I was the only user in the system. I have checked for batch jobs and the rest and there was nothing running.

    I know I am being very vague about the problem but dont know where to start looking.

    All/any help would be appreciated.

    Regards
    Ravi
    ravism, Sep 29, 2003
    #2

  2. satya Moderator

    Best option to start with using PERFMON/SYSMON and capture counters for Memory, SQL Server :buffer, CPU, Physical disk and other SQL server related counters.

    For the slow running queries use PROFILER and see the activity, take help of index tuning wizard with the trace for index recommendations.

    BTW< what is the level of version & service pack for sQL server & O/s?
    Are there any other applications running parallely?
    How about the DBCC checks and optimization jobs?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Sep 29, 2003
    #2

  3. ravism New Member

    well unfortunately during this one hour all queries are running slow. And I cant add the overhead of running the PROFILER.

    SQL 2000 Service pack 3
    Win 2000 Server & service pack 3.

    And there are now other applications that run parallel.
    And I also rebuild the indexes regularly.

    I did try DBCC CheckDB but didnt help

    Could you please suggest a few steps for analyzing or guide me to articles that would help

    Regards,
    Ravi


    ravism, Sep 29, 2003
    #2

  4. satya Moderator

    To monitor the activity for the slow running queries PROFILER must be used though it adds up bit of overhead.

    Other than artciles in this website, I don't see any additional references to fine tune the performance. For instancehttp://www.sql-server-performance.com/transact_sql.asp TSQL performance tuning,http://www.sql-server-performance.com/statistics_io_time.asp to tune SQL queries,http://www.sql-server-performance.com/sql_2000_tools_tutorial.asp performance tuning tools andhttp://www.sql-server-performance.com/query_execution_plan_analysis.asp query execution plan analysis.

    Can take help of Technet pagehttp://www.microsoft.com/technet/tr...=/technet/tcevents/itevents/sql2000/tnt04.asp about sessioning performance in depth.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Sep 29, 2003
    #2

  5. gaurav_bindlish New Member

    Have you checked for any errors in the event log?

    What about the performance of SQL Server when queries are executed locally? How does memory utilization and processor utilization look in task manager? How about disk I/O? Do you see any issued with that? What is the space utilization on the disks? How about Log file size? How much is it used?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Sep 29, 2003
    #2

  6. Luis Martin Moderator

    You can't use Profiler because add overhead, but SQL take all the time, that is a Russell Paradox.
    Can you duplicate Database in other server (not neccesary same configuration) and find, via profiler whats goin 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
    Luis Martin, Sep 29, 2003
    #2

  7. joechang New Member

    ditto with Luis
    you can run profiler and solve your problem
    or live with your problem without adding the overhead of profiler
    joechang, Sep 29, 2003
    #2

  8. satya Moderator

    Still if you can take pain of running PROFILER during this slow trend, you will be able to know the activity. It helped me earlier... no pain no gain...

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Sep 29, 2003
    #2

  9. Twan New Member

    If you really don't want to impact the server at all , then you could purchase a tool such as ZeroImpact. This listens on the network so does not impact the SQL server.

    I'd use profiler to capture any statements taking longer than 1 second in duration (RPC complete, Batch complete)

    and I'd run performance monitor to check CPU, paging, disk load, network utilisation

    They don't add much overhead (small percentage) and can give you a very quick insight as to what is going on.

    If CPU is low, no long queries are seen, then check blocking (but seems unlikely from what you've said)

    Cheers
    Twan
    Twan, Sep 29, 2003
    #2

  10. Luis Martin Moderator

    Try to run Upadate Statistics after hours. If SQL where ok during 14 month, and now are slow, may be some statistics where missing.

    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
    Luis Martin, Sep 29, 2003
    #2

  11. ravism New Member

    Hi all,

    I should have mentioned this earlier. I did try and run the profiler and the trace showed sql batches were taking few hundred miliseconds which would normally take 20 or 30 miliseconds. But this kind of behaviour is only for about an hour and then everything returns to normal.

    Regards,
    Ravi
    ravism, Sep 29, 2003
    #2

  12. satya Moderator

    Well then check thru the SQL server during that one hour's time for all kinds of activity, for instance any scheduled jobs, long running queries and as you said other applications running parallely, I would like to ask what kind of applications running. Go thru the links provided above which helps to fine tune the performance.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Sep 29, 2003
    #2

  13. Twan New Member

    Also check anything else running on the server, e.g tape backups, NT scheduled jobs, etc.

    You can use perfmon to show a histogram of the cpu usage of each process. Then during the time of problems check which process is chewing up CPU (suspected bottleneck by your comment of batches taking 100s ms rather then 10s)

    Cheers
    Twan
    Twan, Sep 29, 2003
    #2

  14. wolffy New Member

    I have had this problem before. Adding more RAM and more swap drive fixed it for me. But I did have errors in the event logs pointing me in that direction. Don't know if yours is the same, but it is worth a look.

    Wolffy.
    wolffy, Sep 30, 2003
    #2

  15. ravism New Member

    Hi All,

    I thank you for all the help. I have finally managed to resolve the problem.

    It was an index issue.

    I will start a new thread for this.

    Thanks again
    Regards,
    Ravi
    ravism, Oct 1, 2003
    #2

Share This Page