Hi

I have two same sql databases on different servers but one one of the server database a stored procedure is executing with index scan for particular select statement in side the procedure. But other server the same select statement using the index seek. On that particular column the unique non clustered index created.

Due to the index scan the procedure execution is taking long time. I am not able figure out why the same procedure behaving differently on different servers.

The select query inside the procedure also very simple and its just the count of the row for example

select @a = count(1) from dbo.table_name where alternate_id = @a_key

can you please help me what is the wrong on other sever? is there any settings need to be modify in SQL Server?

Thanks in advance!

Thanks

Surendra

Are the indexes in both servers the same?

If not, it's a good idea to adjust the indexes. An index seek is in most cases the better choice.

Maybe the statistics on the slower server are not actual or the indexes are fragmented.

Hi Surendra,

May be on one of the databases database statistics or table statistics is not updated. So the SQL engine might be choosing different indexes.

There is specific setting for this in Sql server.

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

Make sure the following things:

1. Both servers have the same data. If not, then optimizer chooses the best plan for the execution hence index scan.

2. confirm the index is present on the tables in both databases.

3. Update stats for both tables

1. Both the servers have the same data.

2. Table structure - index is created on both the database

id - int(Primary key column)

alternate_id - nvarchar(126) - unique non clustered index

data - int

3. after updating the stats also i am getting the index scan on execution plans

Could you please provide the execution plans for both cases?

BTB, What are you doing with @a variable. If its only for existence check, you may change your query with if exits.

if Exists(Select 1 From Table where alternate_id=@a _key)

--do your code.

Hi Latheesh,

Thanks for your support!!

I am unable to add the execution plan files? is there any alternate way to attach the execution plans?

Thanks

Surendra

Hey dude, since u wanna use index seek, you could do :

1, use table hints, force plan use index seek

http://msdn.microsoft.com/en-us/library/ms187373.aspx

2, use dynamic scripts instead of alternate_id = @a_key, with your case, u have to sp_executesql

Is @a_key a variable or a parameter (for a stored procedure)? Big difference.

@a_key is a parameter for a stored procedure.

Can you try executing the SP with "WITH RECOMPILE" option in both the servers?

It should be doing same scan type.

Ok, what happens when you run the SQL query instead of whole SP in both the servers.

Did you verify that same indexes & stats are there and updated in both the servers?

What is @@version for both?

Follow the same optimization steps for both:

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

OK. The value for a parameter is sniffed at execution time, when the plan is produced and selectivity (etc) is determined from that value ("Parameter Sniffing"). In this case, it seems like SQL Server estimated selectivity too low in order to do an index seek, so it decided on some alternative strategy instead. Consider using OPTION(RECOMPILE) or (OPTIMIZE FOR ...) to get better plan stability.

Hi Surendra

Can you please run  DBCC SHOW_STATISTICS  for table and check column  "row sampled" . I feel row sampled is not equal to no. of rows in table, causing in effective plan.

Please update statistics with full scan. this should fix the issue.

select @a = count(1) from dbo.table_name where alternate_id = @a_key

If you have a single column index on "alternate_id", and variable (or parameter) @a_key has the same data type as "alternate_id", then this index is a covering index and the most efficient (narrowest) index possible for this query, so the optimizer will definitely use it.

Therefore, if this query is not seeking the index, the most likely reasons are:

• there is no (single column) index on this column
• @a_key is a variable/parameter with a higher data type precedence than "alternate_id". E.g. alternate_id is a varchar while @a_key is a nvarchar.
• this is not your actual query. E.g. your query selects other columns
  
• you are not querying the server that you think you are querying

Thanks a lot for your support.

The issue is the alternate_id column is varchar type and in procedure parameter is nvarchar type.

After changing the alternate_id column from varchar to nvarchar the index seek occuring for procedure executions.

Once again thanks for all your support.

• Marked as answer by surendrakavali Monday, March 18, 2013 10:34 AM

Thanks a lot for your support.

The issue is the alternate_id column is varchar type and in procedure parameter is nvarchar type.

After changing the alternate_id column from varchar to nvarchar the index seek occuring for procedure executions.

Once again thanks for all your support.

• Marked as answer by surendrakavali Monday, March 18, 2013 10:34 AM

Hua Min,

Did you copy your post from somewhere? If so why don't you give reference link?  Thanks.

Hua Min,

Did you copy your post from somewhere? If so why don't you give reference link?  T