SQL Server Performance

Apps are slow to open

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Tam, May 8, 2005.

  1. Tam New Member

    Hello

    We recently convert all of our databases to SQL server 2000 from Access 2000.
    Things are running but quite slow. One of our applications has about 20,000 records and when it's slow, it takes about 10 seconds to a search for a customer number. However it's not always this slow, occasionally it takes only 2 seconds to do the search. We are not sure as to why sometimes it's super fast other time it's just so slow. We even try when only 1 user on the network, and it's the same behaviour occurs.

    So far, I can safely say it's not hardware issue, not network issue, not data issue, not the query issue...and what exactly is the issue, well I don't know

    Please anyone, any suggestions would be greatly appreciated!

    Thanks
    Tam
  2. Madhivanan Moderator


    Did you use index for the key column? Post your table structure


    Madhivanan

    Failing to plan is Planning to fail
  3. ranjitjain New Member

    i feel u can track this with general recommendations like:
    1> installing sql server sp3 if not installed.
    2>installing mdac 2.8 on client terminals.
    3>OS latest service pack
    4>indexing on column included in where clause.
    5>optimise select query with different lock options and nocount options.

    i feel u can go for different options as its simply R&D to get to the mark.
  4. dineshasanka Moderator

    How did you convert those data to sql server.
    There is a SQL Server Upgrading Wizard in Access 2000. SOme times I have found that when converting to SQL Server some indexes can be lost. So give a cole look on to the SQL Server indexes


    quote:Originally posted by Tam


    We recently convert all of our databases to SQL server 2000 from Access 2000.
    Things are running but quite slow. Tam
  5. ghemant Moderator

  6. surendrakalekar New Member

    True and also do reindexing on that table and check the fillfactor as well.
    But, I think problem will be with indexing & query optimization.



    quote:Originally posted by ranjitjain

    i feel u can track this with general recommendations like:
    1> installing sql server sp3 if not installed.
    2>installing mdac 2.8 on client terminals.
    3>OS latest service pack
    4>indexing on column included in where clause.
    5>optimise select query with different lock options and nocount options.

    i feel u can go for different options as its simply R&D to get to the mark.


    Surendra Kalekar

  7. Chappy New Member

    Also review your data types. From the few times Ive upgraded from access to SQL server I seem to recall a lot of data types were left being far larger than they needed to be, ater the upgrade. Smaller data types means smaller indexes, less disk activity and better performance
  8. satya Moderator

    PROFILER and PERFMON will come handy to assess the system usage and suggest the index recommendation for optimum performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Akthar New Member

Share This Page