SQL Server Performance

Performance Issue on my Query Execution

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by kumaravel v, Oct 5, 2008.

  1. kumaravel v New Member

    as per your suggestion, i have removed parkunitIdSource from WMS_IX_TParkEvent_ParkUnitIdSource_parkEventStatusCode.
    As you said, the ParkEventStatus column is covered by WMS_IX_ParkEventLastModified index is very low. Shall change the
    order ?
  2. Adriaan New Member

    No need to change the order on WMS_IX_ParkEventLastModified, since the other index is now covering the EventStatus column exclusively.
    Also, by changing the column order on that first index, you will probably ruin the selectivity for lots of other queries. Don't forget that multi-column indexes are searched first column first, then second column, etc.
  3. kumaravel v New Member

    ok Thanks.
    I am just waiting for the completion of process after i dropped the ParkUnitIdSpource column from WMS_IX_TParkEvent_ParkUnitIdSource_ParkEventStatusCode. Becuase, it is still running.
    once it is over, then i will try to run the query again.
  4. kumaravel v New Member

    hi
    I ran the query again after i removed parkunitIDSource, but Still it is giving performance problem. It takes more time.
    Is it advisable to go for reindex, reorganizing the table ?
  5. Adriaan New Member

    Did the execution plan change in any way?
  6. kumaravel v New Member

    it shows the same percentage what i sent you last time.
  7. Adriaan New Member

    Execute the stored procedure with the WITH RECOMPILE option:
    EXEC stored_procedure parameters WITH RECOMPILE
  8. kumaravel v New Member

    hey
    I am really thankful to you that the way you gave me good tips and tops.
    At last i got through the fast execution my query after i removed parkunitIdSource from WMS_IX_ParkEventLastModified, WMS_IX_ParkEventTimeDetected and WMS_IX_TParkEvent_ParkUnitIdSource_ParkEventStatusCode INDEX Keys.
    It is running fast like anything. Great Job!!
    Thanks man.
    -- Kumar
  9. Adriaan New Member

    Glad to have been of help. Good to see you apply the new-found knowledge by yourself.
    There appears to have been some confusion about indexes in the design of this database. If there is a clustered index on a table, this serves as the basis for the non-clustered indexes. There is absolutely no need to repeat the clustered index in a non-clustered index.
  10. kumaravel v New Member

    hi
    | want to change my server collation from "Latin1_General_CI_AS" to "Latin1_General_CI_AI"
    can you please advice me !
    thanks in advance!
  11. Adriaan New Member

  12. kumaravel v New Member

    sorry.
    thanks.
  13. Adriaan New Member

    Inside your derived table (TT1) move the DATEPART bits to and the WHERE clause for the date filtering to a derived table. This often helps SQL doing first things first, instead doing everything in aggregate.

Share This Page