SQL Server Performance

Forced Parameterization

Discussion in 'ALL SQL SERVER QUESTIONS' started by Hrishi_nk, Jul 30, 2012.

  1. Hrishi_nk New Member

    I have table say table_A having many columns and millions of rows.There are two columns customerid,date on which index is created.I have a procedure which takes a lot of time to execute.
    On investigating the query plan i found that the below query is taking maximum cost and its a scanning enitre index(index scan).I am just posting the relevant query which is required.@start_date and @end_date are input params of the proc.

    declare @start_date datetime
    declare @end_date datetime

    select @start_date='20120601',@end_date='20120630'

    select date,min(status) as status from table_a with(nolock)
    where date between @start_date and @end_date
    and customerid in(select customerid from sometable)
    group by date

    But when i execute the same query as below

    select date,min(status) as status from table_a with(nolock)
    where date between '20120601' and '20120630'
    and customerid in(select customerid from sometable)
    group by date

    It ends up with index seek.
    Do i need to enable forced parameterization.What are the considerations to enable Forced Parameterization.Is there any other way the above query could be written so that uses the appropriate index.
  2. Shehap MVP, MCTS, MCITP SQL Server

    For such circumstances , you can work out the below assumptions be the same sequence :

    1. Update statistics + Rebuild indexes in particular more these 2 tables "Sometable" and "Table_a" and check again…

    2. If still persist , you can use the 2 below indexes and check again :

    Createnonclusteredindextable_a_IX1ontable_a (date,customerid)
    include (status)with (fillfactor=80,data_compression=page)

    Createnonclusteredindex Sometable_IX1onSometable(customerid)
    with (fillfactor=80,data_compression=page)

    3. If still persist , you can work out the below query and check these table hints of Index Forceseek can be helpful here or not :

    selectdate,min(status)asstatusfromtable_awith(index (table_a_IX1),forceseek,nolock)
    wheredatebetween'20120601'and'20120630'
    andcustomeridin(selectcustomeridfromsometablewith (index (Sometable_IX1),forceseek,nolock)
    groupbydate

    4. If not applicable and it posted an error for you , you might change the order of key columns of the above index to be something like below and check again:

    Createnonclusteredindextable_a_IX1ontable_a (customerid , date)
    include (status)with (fillfactor=80,data_compression=page)

    Kindly work out them and let me know your feedback
  3. Hrishi_nk New Member

    Thanks For the reply
    1.
    Update statistics + Rebuild indexes in particular more these 2 tables "Sometable" and "Table_a" and check again…

    I tried this but its of no use still scanning the index

    2.
    If still persist , you can use the 2 below indexes and check again :

    Create
    nonclusteredindextable_a_IX1ontable_a (date,customerid)

    include (status)with (fillfactor=80,data_compression=page)

    Createnonclusteredindex Sometable_IX1onSometable(customerid)
    with (fillfactor=80,data_compression=page)
    I already have a index on date,customerid including status.Its still scanning.But SQL server is suggesting me to create
    index on date include(customerid,status)

    3. If still persist , you can work out the below query and check these table hints of Index Forceseek can be helpful here or not :
    selectdate,min(status)asstatusfromtable_awith(index (table_a_IX1),forceseek,nolock)
    wheredatebetween'20120601'and'20120630'
    andcustomeridin(selectcustomeridfromsometablewith (index (Sometable_IX1),forceseek,nolock)
    groupbydate
    This query is working for me.Its using seek.
    But I am still in a state of doubt.How come the SQL server is not using the index inspite of providing the proper where clause on the indexed columns.




  4. Shehap MVP, MCTS, MCITP SQL Server

    If you have such millions of records, it worth much thinking about schema partitioning of table and use the storage aligned technique which means both indexes and table are aligned on the same partitioning schema which can boost IO+ CPU Parallel processing and thus perhaps this help to find index seek…

    All what you have to do to is just :

    · Create a partitioning functions with an appropriate range according to your data entity and then create a partitioning schema based on different File groups of files exists in different disks (If possible)

    · Partition your table on this partitioning but keep in mind to select the partitioning key the same as clustered index column ( PK by default)

    · Change the storage attribute of your index to be aligned on this schema partitioning instead of File groups

    This generally is helpful for the performance of tables having huge data entity like our case ..

    Kindly let me know if any further help is needed

Share This Page