SQL Server Performance

Index?

Discussion in 'Performance Tuning for DBAs' started by tariq745, Oct 25, 2007.

  1. tariq745 Member

    I have 2 questions1) One big index performs better then 2 small indexes or the opposite?2) I have table with 17242772 rows and I have index1 and index2 on that table, index1 has 14 columns and index2 has 3 columns. Developer created a new report in crystal it takes 2:47 min. I analyze the query through index tuning wizard which recommend me to create some non-cluster indexes but all those columns are already indexed in index1 and index2. So my question is do I have to create index3 as tuning advisor recommended. Why can’t SQL Server see all those columns are index for other reports? Thanks
  2. FrankKalis Moderator

    I think we think more information, such as the table structure including the indices and the execution plan to provide good suggestions.
  3. tariq745 Member

    Execution plans shows sorting takes the longest. Can you or anyone else please answer my question#2
  4. Greg Larsen New Member

    I would say it really depend. What columns are returned, what columns are used to constrain your query, and order do you want your data returned and grouped. Potientially a new covering index that has only a few indexed columns and a number of include columns would perform better.
  5. satya Moderator

    If you doubt on the indexes then you could take help of PROFILER and INDEX TUNING WIZARD, also try to wrap up the process with a stored procedure for maximum efficiency during the execution, as you will observe difference in processing the query within QA and from reporting application.

Share This Page