I have created an index on a table, but when I see the execution plan, it is not using this index

How can force the query to use this index

Regards

Forcing the sql to use an index is not a good option. Did you recompile the stored proc. Can you share the query, table structure , indexes and

Hi satheesh

How to recompile the stored proc. I have a query which is taking around 35 sec, so I created a non clustered index but the query is not using it

Regards

read about sp_recompile here

sp_recompil

The optimizer is very efficient in choosing the best plan. By following some guidelines we could help optimizer choose the best plan. However forcing the optimizer to use an index is a bad idea(use it as last option)

With out looking at the code, execution plan and table structure etc no-one could explain why the query is not choosing appropriate indexes. So please share the code( at least  some sample query) 

Hi sateesh,

I am little confused, as I told my query was taking 35 seconds, after creating the index on columns in group by clause,it was using this index  and time was reduced by 10 seconds. then I droped the index to cross check.

But now when I created again this index, it is not using this index as it did earlier

please suggest

You need to provide table definitions, index definitions, the query used etc. so that this will help to analyse this further without guessing..or a generic answer.

Did you recompile the stored proc after creating the index?

As the table definition, index definition and query are big enough, it is difficult to post them all.

Since i created and droped this index multiple times, may be it is fragmented,

how can check whether this particular index is fragmented?

Regards

You can find the fragmentation using the following query..

SELECT 
OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'yourDBName'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

An index fragmentation will not make the index unusable by optimizer, I believe. 

Here is the query to use fragmentation 

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Check Fragmentation using,

DBCC SHOWCONTIG

Update statistics also.

The following article is on optimization:

http://www.sqlusa.com/articles/query-optimization/

For additional assistance you need to post data/index DDL.