February 27, 2014 at 12:15 pm
While trying to find an alternative to usage of coalesce in the where clause I ran into an issue that the execution plan will only choose an index seek if the RECOMPILE hint is given.
It's a one column table containing commandlines of all processes on a Windows server. Clustered index is on this nam column. I'm trying to filter on wmi.
Of the 3 queries only the 3rd one will use and index seek where I expect that since query plans are compiled against the parameters with which they were first executed, that 2 and 3 would result in the same query plans. With RECOMPILE the plan is not cached.
See attachment for a screenshot of the 3 actual execution plans.
CREATE TABLE [dbo].[test]([name] [nvarchar](400) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX ci_test ON [dbo].[test]
([name] ASC)
GO
dbcc freeproccache
exec sp_executesql N'select * from test where coalesce(name,N'''') like @name',N'@name nvarchar(400)',@name = N'wmi%'
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name or @name = ''All'' ) ',N'@name nvarchar(400)',@name = N'wmi%'
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name or @name = ''All'' ) OPTION(RECOMPILE)',N'@name nvarchar(400)',@name = N'wmi%'
February 27, 2014 at 1:24 pm
I got interested in your question and make some tests:
dbcc freeproccache
exec sp_executesql N'select * from test where coalesce(name,N'''') like @name',N'@name nvarchar(400)',@name = N'wmi%'
/* You have compiled the statement with the following compiled and runtime values:
<ParameterList>
<ColumnReference Column="@name" ParameterCompiledValue="N'wmi%'" ParameterRuntimeValue="N'wmi%'" />
</ParameterList>
*/
dbcc freeproccache
exec sp_executesql N'select * from test where name like @name',N'@name nvarchar(400)',@name = N'wmi%'
--index seek, because coalesce was removed
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name or @name = ''All'' ) ',N'@name nvarchar(400)',@name = N'wmi%'
/* You have compiled the statement with the following compiled and runtime values:
<ParameterList>
<ColumnReference Column="@name" ParameterCompiledValue="N'wmi%'" ParameterRuntimeValue="N'wmi%'" />
</ParameterList>
*/
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name) ',N'@name nvarchar(400)',@name = N'wmi%'
--index seek because @name = ''All'' was removed, you will have index scan if you try remove name and leave @name in the where()
dbcc freeproccache
exec sp_executesql N'select * from test where (@name = ''All'' ) ',N'@name nvarchar(400)',@name = N'wmi%'
--index scan, because @name is sniffed with value 'wmi%'
dbcc freeproccache
exec sp_executesql N'select * from test where (name = ''All'' )'
--index seek, and will always be (under updated statistics)
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name or @name = ''All'' ) OPTION(RECOMPILE)',N'@name nvarchar(400)',@name = N'wmi%'
--index seek because the sp was recompiled, and sniffed parameter disabled
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 27, 2014 at 1:55 pm
Because - http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 2:13 pm
dbcc freeproccache
exec sp_executesql N'select * from test where (name like @name or @name = ''All'' ) ',N'@name nvarchar(400)',@name = N'wmi%'
--index scan
vs.
dbcc freeproccache
declare @name nvarchar(400)
set @name = N'wmi%'
declare @dyn_sql nvarchar(1000)
set @dyn_sql = N'select * from test where (name like '''+@name+''' or '''+ @name +'''= ''All'' )'
exec sp_executesql @dyn_sql
--index seek
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 27, 2014 at 2:31 pm
Igor,
An index seek in your second example has been chosen by the optimizer because the query is not parametrized. You simply concatenated the query text and the resolution about the value of the variable @name is resolved before the query execution. Therefore you got an optimal execution plan. The price for it is that your statement is prone to SQL injection.
In the first case the query is parametrized and SQL Server tends to create a "safe" plan which would work for both values of the parameter @name (All or something else). This is always true until you specify OPTION (RECOMPILE). In this case the expression is first evaluated and then the execution plan is generated (similar to dynamic SQL, but without SQL injection).
The same would happen if you even declare a stored procedure with recompile hint:
CREATE PROCEDURE dbo.GetPerson
@Name NVARCHAR(50)
WITH RECOMPILE
AS
SELECT * from Person.Person WHERE LastName LIKE @name OR @name = 'All'
GO
When you execute the stored procedure you can see Index Scan instead of index seek. For inde seek you would need:
SELECT * from Person.Person WHERE LastName LIKE @name OR @name = 'All' OPTION (RECOMPILE)
___________________________
Do Not Optimize for Exceptions!
February 27, 2014 at 2:34 pm
milos.radivojevic (2/27/2014)
Igor,An index seek in your second example has been chosen by the optimizer because the query is not parametrized. You simply concatenated the query text and the resolution about the value of the variable @name is resolved before the query execution. Therefore you got an optimal execution plan. The price for it is that your statement is prone to SQL injection.
In the first case the query is parametrized and SQL Server tends to create a "safe" plan which would work for both values of the parameter @name (All or something else). This is always true until you specify OPTION (RECOMPILE). In this case the expression is first evaluated and then the execution plan is generated (similar to dynamic SQL, but without SQL injection).
The same would happen if you even declare a stored procedure with recompile hint:
CREATE PROCEDURE dbo.GetPerson@Name NVARCHAR(50)
WITH RECOMPILE
AS
SELECT * from Person.Person WHERE LastName LIKE @name OR @name = 'All'
GO
When you execute the stored procedure you can see Index Scan instead of index seek. For inde seek you would need:
SELECT * from Person.Person WHERE LastName LIKE @name OR @name = 'All' OPTION (RECOMPILE)
Yeah, just agree. Added that as a plus.
Igor Micev,My blog: www.igormicev.com
February 28, 2014 at 1:32 am
Thank you for your replies.
@Gail: your link supplies the complete answer. Indeed the issue is catch all queries. One of our tools has customizable search forms with search fields. If these are empty then '%%' is passed on in the query and [font="Courier New"]coalesce(column_name,'''') like @searchfield[/font] is used in the query. We see that replacing the coalesce improves performance, at least it never degrades it.
Unfortunately there is no option of dynamic sql. The queries of the search form are fixed. I have considered the option of dynamic sql via a function or stored procedure but that has not yet led to an acceptable method.
February 28, 2014 at 1:37 am
If you're using SQL 2012, use the 'catch-all' form (where Column=@Parameter or @Parameter IS NULL) and add the OPTION (RECOMPILE) to the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply