SQL Server Performance

Performance Issue during sort

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by scud71, Aug 16, 2009.

  1. scud71 New Member

    Hi all... I am new to this forum and to the SQL 2005 world.
    I have a database on a Windows 2003 Enterprise platform with SQL 2005 SP2 Enterprise with 8GB RAM configured on the server. SQL is configured to utilise 7GB RAM for itself and PAE switches are enabled to that effect.
    We have noticed that when we perform searches on a table that contains 2 million rows, the results are extremely slow to come back. We have applied optimise stats on that table and it contains a valid index as well. But still for some reason, the searches are slow to process.
    The memory utilisation during that time is between 80 - 90% during the course of the process. Sometimes, the searches fail with timeouts. Can you please advise whether there is anything that I can check to confirm what may be going on? I ran Profiler trace as well to gather some statistics but cannot see where the problem lies.
    Any advice would be highly appreciated.
  2. madhuottapalam New Member

    If the problem is because of ORDERY BY , proper index may solve the problem. If you see the execution plan , becase of sort , there may be extra working table being created which can be avoided if you have proper index on which covers ORDER BY too. How many rows are being returned by the query? check the exectuion plan that should be the starting point. You may also want to check the memory counter to find if there is any memory issue.
    Madhu

Share This Page