sp_executesql not using index?

Question

I'm using nHibnerate in my web application and I have a problem using indexes in generated sp_execute. My table has 210 millions records and the query is very slow.

Firstly, there was a problem with generated column 'kolumna1' type. In database I have a column of varchar but nHibernate generated nvarchar. I workarounded this by putting special attribute in the code which forced using varchar. After that trick sp_executed started using indexes and everything was correct. Now the problem is back sp_executesql takes 10 minutes to finish. When i checked normal query(without sp_executesql) it took only 1s. I checked execution plans for both: sp_executesql wasn't using index and normal query was using index. Without changing index i modified back varchar to nvarchar and sp_execute finished in 1s (used index). Anyone got any idea where did i make a mistake ? why the execution plan is diffrent for such small changes? And how to fix it?

Here i attached more code. Just in case if someone need it.

sp_executesql with varchar(8000)

exec  sp_executesql N'SELECT count(*) as y0_  FROM tabela1 this_ WHERE ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1)) and  
    (this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 varchar(8000)',
    @p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%'

sp_executesql with nvarchar(4000)

exec  sp_executesql N'SELECT count(*) as y0_  FROM tabela1 this_ WHERE ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1)) and  
    (this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 nvarchar(4000)',
    @p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%'

The funny part is that in sql profiler both query gives same reuslt:

exec sp_executesql N'SELECT count(*) as y0_ FROM tabela1 this_  
WHERE this_.kolumna3 in (@p2, @p3) and ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1))  
and ( this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 varchar(8000)',  
@p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%'  
--Declare @p0 datetime  
--set @p0 = '2013-01-08 14:38:00'  
--Declare @p1 datetime  
--set @p1 = '2013-02-08 14:38:00'  
--Declare @p2 int  
--set @p2 = 341  
--Declare @p3 int  
--set @p3 = 342  
--Declare @p4 varchar(8000)  
--set @p4 = '%501096109%'  
--SELECT count(*) as y0_  
 --FROM tabela1 this_  
 --WHERE ((this_.kolumna2 >= @p0 and  
 --this_.kolumna2 <= @p1)) and  
 --(this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4)

Here are indexes:

CREATE TABLE [dbo].[tabela1](
[id] [bigint] NOT NULL,
[kolumna1] [varchar](128) NOT NULL,
[kolumna2] [datetime] NOT NULL,
[kolumna3] [int] NOT NULL,
CONSTRAINT [PK__tabela1__4F7CD00D] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ind_tabela1_ kolumna2] ON [dbo].[tabela1] 
(
    [kolumna2] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ind_ tabela1_ kolumna3] ON [dbo].[ tabela1] 
(
    [kolumna3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna1] ON [dbo].[ tabela1] 
(
    [kolumna1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna2_ kolumna3] ON [dbo].[ tabela1] 
(
    [kolumna2] ASC,
    [kolumna3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna3_ kolumna2_id_ kolumna1] ON [dbo].[ tabela1] 
(
    [kolumna3] ASC,
    [kolumna2] ASC,
    [id] ASC,
    [kolumna1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Below execution plan for query: select count(*) from [dbo].[tabela1] where [kolumna1] like N'%501096109%'

Answer 1

Sql Server query optimizer can choose to use index seek when:

1. There are another filter predicates besides LIKE. It should be a precise search or at least SARGable predicate
2. Table is very large (millions of rows)

But seek operation cannot be done when explicit type conversion is used - different collation/datatype. Another thing that you cannot control this behavior and query plans can be vary for different predicate sets. To do this, you need to use hint FORCESEEK (version 2008+). You can find information here: http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.100%29.aspx