SQL Server Performance

index scan

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ashwin, Dec 26, 2007.

  1. ashwin New Member

    Hi all,
    While going through the excecution plan i observed that index scan on clustered index involves lot of cost i.e primary key of one of the table. Please suggest
    how to minimize this cost. May be this is what adds to the performance degradation.
    thanks
  2. Luis Martin Moderator

    Did you try DTA?
  3. ashwin New Member

    Can you please tell me how to use DTA? Iam using sql server 2005 desktop version.
    thanks
  4. ghemant Moderator

    Hi Ashwin,
    It is Database Tuning Advisor Refer www.microsoft.com/technet/prodtechnol/sql/2005/sql2005dta.mspx
    Regards
  5. satya Moderator

    What kind of data type is used on that primary key?
    Also what kind of columns are involved in the index?
    http://sqlserver-qa.net/blogs/perft...-optimizer-reuses-for-better-performance.aspx fyi.
    if you are using SQL 2005 then take help of plan guides - Using a feature called plan guides, you can inject query hints into a stored procedure without modifying the code. You can also exploit a new query hint USE PLAN to specify a full XML query plan for a particular query. With these two options, you can tune an application even when you don't have access to its code base.
  6. Jack Vamvas Member

    Also , could you post the query you are analysing plus the query plan
  7. satya Moderator

    Further I would like to know whether this is a SQL 2005 or 2000 version including the service pack level.
  8. dineshasanka Moderator

Share This Page