SQL Server Performance

Indexes ... I m confused.... :(

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by harneetsingh, Sep 6, 2007.

  1. harneetsingh New Member

    Hi All,We are writing an application and are using Sql Server as backend database to store the metadata.
    I am not a great user/dba on Sql server so If my post seems silly please let me know [:)]
    We have hundreds of queries fired against the database and while analyzing the queries and checking the queries with database tuning advisor, i came across the recommendations of DTA which recommends building of indexes on same table with same columns but in different order.
    For eg.
    TABLE EMP is
    id bigint,
    name varchar,
    salary bigint,
    address
    it recommends creating an index as (id,name,salary) and other as (salary,name,id)

    Why are these recommendations different and why cant it use a single index for all its scans.
    Any suggestions are highly appreciated.
    thanks
    Harneet

  2. satya Moderator

    Welcome to the forum!
    As you are beginner to SQL Server it is better you understand the concepts of indexing and their usage.
    Fundamentally all the index operations performed are offline operations. And this has been the default. In SQL Server when an index updation/creation/deletion happens it is an offline operation. What this means is that, all queries and modifications to the underlying data and associated nonclustered indexes are blocked while the process finishes the request.
    Within SQL Server the usage of Indexes is very important. Understanding the usage being one side, optimizing and using this is the other aspect. Your key focus should be on introducing some of the basic enhancements brought into SQL Server 2005 for indexes, refer to the books online for SQL 2005(updated).
    http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx
  3. harneetsingh New Member

    Satya,
    Thanks a lot for the document it really helps.
    But my questions are still unanswered, I am well known with the functioning of indexes, I have been working with Oracle Database for some time now and only things i need to know are specific to Sql Server.
    1) see my orignal post why are the recommendations different for same table and same columns just order changed.
    2) Consider the following example
    Table TAB
    a bigint,
    b bigint,
    c bigint,
    d bigint,
    e bigint
    Inex TAB_IDX on TAB (a,b,c,d)
    now I fire a query which is supposed to use index (a,b,c) will it use the TAB_IDX already created on (a,b,c,d) or do i need to create another (a,b,c) index.
    Thanks in the anticipation.
  4. ghemant Moderator

  5. Adriaan New Member

    An index on (a, b, c, d) is perfectly good for a query that needs (a, b, c).
    But that same index would be bad if your query needs (b, c, d) and not the first column, which is the most critical one in any index.
  6. harneetsingh New Member

    Thanks Hemant,
    that clarifies reasons for my point 1, but is there any way, i can force my queries to use a specified index
    say i have a index created with columns (a,b,c) and DTA recommends creating an index (b,c,a) should I force the query to use (a,b,c) index.
    What are the implications and what drawbacks if any?
    also my 2nd point is still unanswered ?
    Sorry for being so pushy, but I need these answers for my understanding and better using the Sql Server :) Thanks again
  7. Adriaan New Member

    You seem to have missed my post after ghemant's ... Most of the time, an index on (a, b, c) will be a poor choice if you actually need one on (b, c, a).
    Check out Books Online for SELECT and then the FROM clause. If SQL is not using an index that you would like it to use, then add WITH INDEX (index_name) after the table name.
    Most of the time, however, SQL will get things right without hints - but this depends completely on the indexes that it has at its disposal.
  8. harneetsingh New Member

    Thanks mate, so that means I have to create indexes in the exact order the Sql server wants me to , i cannot use composite indexes otherwise like Oracle Skip Scan feature :(
    and index usage for all these types is equal for most of my queries, that is almost equal number of queries need all the indexes.
    I will end up over indexing my system not good.
    My exact need is that I need to evaluate over 200-250 queries per second and till now I am able to do so only for 100-150, and I still need some more pushing, indexing was one of those methods I was thinking of, any other ideas most welcome.
    Regards
    harry
  9. Adriaan New Member

    Not sure about the details of the "skip scan" feature of Oracle. Anyway, you can have any number of composite indexes on any table, so you can have one on (a, b, c) and another on (b, c, a) - no problem.
  10. satya Moderator

    As explained by Adrian you can make use of COMPOSITE INDEXES in SQL server, fyi tips on this http://www.sql-server-performance.com/tips/composite_indexes_p1.aspx features and use is only if it is required.
    As you are using SQL 2005 you can take help of few DMVs in order to correct those missing indexes with the help of Profiler & DTA too.
  11. FrankKalis Moderator

    The recommendations by the DTA are at best just indications. You shouldn't take them as ultimative correct advise. Build your tables, indices, app and then test extensively different scenarios and how everything behaves under these conditions. If you are satisfied with performance, then fine. If not, come back and we'll try to help.
    Btw, if every employee gets a BIGINT salary, may I ask if you are hiring?
    It occurs to me that your EMP table need a little bit of fine-modeling. [;)]

Share This Page