sp_executesql n'utilise pas l'index ?

Question

J'utilise nHibnerate dans mon application web et j'ai un problème avec l'utilisation des index dans le sp_execute généré. Ma table contient 210 millions d'enregistrements et la requête est très lente.

Tout d'abord, il y a eu un problème avec le type de colonne générée "kolumna1". Dans la base de données, j'ai une colonne de type varchar, mais nHibernate a généré un type nvarchar. J'ai contourné le problème en mettant un attribut spécial dans le code qui a forcé l'utilisation de varchar. Après cette astuce, sp_executed a commencé à utiliser les index et tout était correct. Maintenant le problème est de retour, sp_executesql prend 10 minutes pour se terminer. Lorsque je vérifie une requête normale (sans sp_executesql), cela ne prend que 1s. J'ai vérifié les plans d'exécution pour les deux : sp_executesql n'utilisait pas l'index et la requête normale utilisait l'index. Sans changer l'index, j'ai modifié varchar en nvarchar et sp_executesql a terminé en 1s (utilisation de l'index). Je ne sais pas si j'ai une idée de l'endroit où j'ai fait une erreur, pourquoi le plan d'exécution est différent pour des changements aussi minimes ? Et comment résoudre ce problème ?

J'ai joint plus de code. Juste au cas où quelqu'un en aurait besoin.

sp_executesql avec 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 avec 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%'

Ce qui est amusant, c'est que dans sql profiler les deux requêtes donnent le même résultat :

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)

Voici les index :

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

Voici le plan d'exécution de la requête : select count(*) from [dbo].[tabela1] where [kolumna1] like N'%501096109%'

Answer 1

Pourriez-vous essayer ceci ?

(1) Exécutez le code SQL suivant :

select * from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) t

(2) Utiliser la dernière colonne pour trouver le SQL de la première requête. Elle ne contiendra pas sp_executesql, mais commencera par votre liste de paramètres, le dernier étant un varchar. Obtenez le plan_handle et l'utiliser dans l'énoncé suivant :

dbcc freeproccache (<your_plan_handle>)

Réessayez ensuite l'interrogation 1.

Answer 2

L'optimiseur de requêtes de Sql Server peut choisir d'utiliser la recherche d'index lorsque :

1. Il existe d'autres prédicats de filtrage que LIKE. Il devrait s'agir d'une recherche précise ou au moins d'un prédicat SARGable.
2. La table est très grande (des millions de lignes)

Mais l'opération de recherche ne peut pas être effectuée lorsqu'une conversion de type explicite est utilisée - collation/type de données différents. De plus, vous ne pouvez pas contrôler ce comportement et les plans de requête peuvent varier pour différents ensembles de prédicats. Pour ce faire, vous devez utiliser l'indice FORCESEEK (version 2008+). Vous pouvez trouver des informations ici : http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.100%29.aspx