SQL Server Performance

why index scan was not changed to index seek in execution plan?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Lynn2013, Jan 14, 2013.

  1. Lynn2013 New Member

    I have a stored procedure. I have added indices to all fields that in where clause, join, order by, and select. Then I ran the stored procedure with "with recompile", but the index scan was not changed to index seek in the execution plan.

    Any help will be appreciated.

    Lynn
  2. Shehap MVP, MCTS, MCITP SQL Server

    First welcome to Forums..

    Please share the T-SQL Code of your SP to be able to figure out the mostly probable bottleneck then we can identify the appropriate solutions
  3. Lynn2013 New Member

    SELECT

    distinct DISTRICT_DIM.DISTRICT_NUMBER, DISTRICT_DIM.DISTRICT_DESCRIPTION

    FROM

    ORGANIZATION_HIERARCHY_DIM INNER
    JOIN

    DISTRICT_DIM
    ON ORGANIZATION_HIERARCHY_DIM.DISTRICT_NUMBER = DISTRICT_DIM.DISTRICT_NUMBER
    WHERE

    (ORGANIZATION_HIERARCHY_DIM.CHAIN_NUMBER = @chain_number)AND(DISTRICT_DIM.REGION_ID = @region_number)AND(DISTRICT_DIM.STATUS='A')AND
    (ORGANIZATION_HIERARCHY_DIM.STATUS='A'
    )

    ORDER

    BY DISTRICT_DIM.DISTRICT_DESCRIPTION
  4. Shehap MVP, MCTS, MCITP SQL Server

    You can try the 2 indexes below and let me know the performance benchmarks for both criterions execution Time and logical reads before and after applying indexes by just adding set statistics io on prior to the start of query

    create nonclustered index DISTRICT_DIM_IX on DISTRICT_DIM(DISTRICT_NUMBER,REGION_ID,[STATUS])

    include ( DISTRICT_NUMBER, DISTRICT_DESCRIPTION) with (data_compression=page,fillfactor=80)

    Create nonclustered index ORGANIZATION_HIERARCHY_DIM_IX on ORGANIZATION_HIERARCHY_DIM(DISTRICT_NUMBER ,CHAIN_NUMBER,[STATUS])

    with (data_compression=page,fillfactor=80)
  5. Lynn2013 New Member

    Shehap,

    Thank you very much for your replies.

    Before running your queries to create indexes, I added "set statistics io on" at the beginning of the query, and executed the query again. When I checked the execution plan, the index scan was changed to index seek on both tables already. I don't know why adding "set statistics io on" has made the difference.

    However, I have a new question. When I compare your indexes on District_Dim with mine, the difference is in my "include" clause, I only include District_Description since District_Number is already in the previous list. So my question is why you want to include Disctrict_Number in "include" clause as well.

    Again I appreciate your help!

    Lynn
  6. Shehap MVP, MCTS, MCITP SQL Server

    It is inapplicable surely to add DISTRICT_NUMBER twice and it is enough to be @ Key Columns part,it is just a typo issue ..:)

    Let me know if any further help is needed
  7. Lynn2013 New Member

    Thanks. Here is another question. One table is used in two different select queries with overlap fields. Should I create one index including all columns or should I create two indexes? With two indexes, some fields would be in both indexes.
  8. Shehap MVP, MCTS, MCITP SQL Server

    By that case, It should be 2 indexes to assure of the concept covering compound indexing for each select query that in other words your index should be compound of Include columns and key columns and in addition it covers the entire of table columns exists within the select statement either at select column list or where condition , inner join ,order by or group by.

    However, you have to look well at the indexes sizes otherwise they might impact on the OLTP performance (Performance of DML transactions such as insert /update /delete)

    You can learn more about indexing techniques and concepts at my blog http://www.sql-server-performance.com/2013/sql-server-index-tuning/
  9. Lynn2013 New Member

    Shehap,

    I read your article and it is very helpful. Now I have a new question: how can I improve a query that has expression like @variable = '' or @varial is null.

    I have many stored procedures that pass parameters as filters in where clause to get a list of something. I allow user to pass null or empty string to get a full list without being filtered.

    In my where clause, you will see something like: fieldName = @variable or @variable = ''

    With expression like this, index scan cannot be changed to index seek.

    How can I improve the performance of this kind of stored procedures?

    All your help is highly appreciated!

    Lynn
  10. Shehap MVP, MCTS, MCITP SQL Server

    As per my understanding , All of where conditions will be filtered out due to passing Null or Empty string values to parameters …..If so you have to check basically your table if it has clustered index coz it is likelihood Query Analyzer will seek this clustered index but even though , it is much preferable to assure one parameter at least one has a value to be able to filter data according to it otherwise you are going to scan all data and I get scary that query analyzer can’t use seek for clustered index even

    In addition , you can use Dynamic Queries based on SP_EXECUTESQL which has lots of rich powers and can help much in getting then optimal query execution plan …You can read more about it http://msdn.microsoft.com/en-us/library/ms188001.aspx

Share This Page