sql server - sp_executesql not using index? -

sql server - sp_executesql not using index? -

i'm using nhibnerate in web application , have problem using indexes in generated sp_execute. table has 210 millions records , query slow.

firstly, there problem generated column 'kolumna1' type. in database have column of varchar nhibernate generated nvarchar. workarounded putting special attribute in code forced using varchar. after trick sp_executed started using indexes , correct. problem sp_executesql takes 10 minutes finish. when checked normal query(without sp_executesql) took 1s. checked execution plans both: sp_executesql wasn't using index , normal query using index. without changing index modified varchar nvarchar , sp_execute finished in 1s (used index). got thought did create error ? why execution plan diffrent such little changes? , how prepare it?

here attached more code. in case if need it.

sp_executesql varchar(8000)

exec sp_executesql n'select count(*) y0_ tabela1 this_ ((this_.kolumna2 >= @p0 , this_.kolumna2 <= @p1)) , (this_.kolumna3 in (@p2, @p3) , this_.kolumna1 @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 nvarchar(4000)

exec sp_executesql n'select count(*) y0_ tabela1 this_ ((this_.kolumna2 >= @p0 , this_.kolumna2 <= @p1)) , (this_.kolumna3 in (@p2, @p3) , this_.kolumna1 @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 in sql profiler both query gives same reuslt:

exec sp_executesql n'select count(*) y0_ tabela1 this_ this_.kolumna3 in (@p2, @p3) , ((this_.kolumna2 >= @p0 , this_.kolumna2 <= @p1)) , ( this_.kolumna1 @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(*) y0_ --from tabela1 this_ --where ((this_.kolumna2 >= @p0 , --this_.kolumna2 <= @p1)) , --(this_.kolumna3 in (@p2, @p3) , this_.kolumna1 @p4)

here 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 query: select count(*) [dbo].[tabela1] [kolumna1] n'%501096109%'

sql server query optimizer can take utilize index seek when:

there filter predicates besides like. should precise search or @ to the lowest degree sargable predicate table big (millions of rows)

but seek operation cannot done when explicit type conversion used - different collation/datatype. thing cannot command behavior , query plans can vary different predicate sets. this, need utilize hint forceseek (version 2008+). can find info here: http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.100%29.aspx

sql-server nhibernate count