SQL Server Performance

Index Searches / Sec high!

Discussion in 'Performance Tuning for DBAs' started by RoyalSher, May 17, 2003.

  1. RoyalSher New Member

    Hi,

    I have a query and runs against a large table having half a million records. Can ne body tell me how to troubleshoot index searches/sec which is the highest when checked with performance counters ?

    i am putting down my query if ne body could suggest.. way to write it or index the columns.

    Query:
    SELECT C.USERNAME, RIGHT(C.PLAN_TYPE, 2) AS PTYPE, C.TOTAL_SESSION, C.IPADDRESS AS IPADDRESS
    FROM DBO.USERS_AUTHENTICATION C WHERE C.BACKUPACCTSTATUS = CASE C.PLAN_TYPE WHEN 6 THEN 1 ELSE 0 END
    AND C.USERNAME ='xxx'

    Thanks in advance.



    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  2. satya Moderator

  3. joechang New Member

    it will depend on the selectivity of the the columns
    i am assuming USERNAME is reasonable selective
    if only a few rows for a given USERNAME exists, then the minimum index is

    CREATE INDEX IX_USERS_AUTHENTICATION ON USERS_AUTHENTICATION(USERNAME)

    otherwise, if BACKUPACCTSTATUS increases the selectivity, following will do:
    CREATE INDEX IX_USERS_AUTHENTICATION_1 ON USERS_AUTHENTICATION(USERNAME, BACKUPACCTSTATUS, PLAN_TYPE)

    if there are still many rows that meet the criteria, then do a full covered index

    CREATE INDEX IX_USERS_AUTHENTICATION_2 ON USERS_AUTHENTICATION(USERNAME, BACKUPACCTSTATUS, PLAN_TYPE, TOTAL_SESSION, IPADDRESS)



  4. Luis Martin Moderator

    Did you try using Query Analyzer with Index Tunig Wizard?

    Luis Martin
  5. rushmada New Member

    U can use Show Execution plan in Query Analyzer to find out the index analysis
    and
    Try to optimize the query by creating indexes on the columns which u were using
    for Where clause in the query

    and Run the execution plan and check the time taking for the query.





    Rushendra

Share This Page