Skip to main content
Database Administrators

Return to Question

deleted 19 characters in body; edited title
Source Link
marc_s
marc_s
  • 9k
  • 6
  • 45
  • 52

Sql server SQL Server changes execution plan

I have SQL code that is executing slow

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'
 

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode
 OPTION (recompile)

Sql server changes execution plan

I have SQL code that is executing slow

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'
 

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode
 OPTION (recompile)

SQL Server changes execution plan

I have SQL code that is executing slow

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (recompile)
formatting, spelling, grammar, tagging
Source Link
Andriy M
Andriy M
  • 23.2k
  • 6
  • 59
  • 103

I have SQL code that is executing slow 

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why sql server uses Nestedis SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

Slow querryThe slow query is executing 5.5 seconds, without the parameter isit's 1.8 secondseconds.

This querryquery is also working fine SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode OPTION (recompile):

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode
 OPTION (recompile)

I have SQL code that is executing slowenter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why sql server uses Nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

Slow querry is executing 5.5 seconds, without parameter is 1.8 second

This querry is also working fine SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode OPTION (recompile)

I have SQL code that is executing slow 

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode
 OPTION (recompile)
I added execution time
Source Link
edit approved Mar 11, 2016 at 13:37
Matej
edit approved Mar 11, 2016 at 13:37
Matej
  • 107
  • 6

I have SQL code that is executing slowenter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why sql server uses Nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

Slow querry is executing 5.5 seconds, without parameter is 1.8 second

This querry is also working fine SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode OPTION (recompile)

I have SQL code that is executing slowenter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why sql server uses Nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

I have SQL code that is executing slowenter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'


SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE @ccode

Why sql server uses Nested loops?

This code is working fine, it uses only index scan:

SELECT  [CVN_UID]
  FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
 WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

Slow querry is executing 5.5 seconds, without parameter is 1.8 second

This querry is also working fine SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode OPTION (recompile)

added 36 characters in body
Source Link
Mikael Eriksson
Mikael Eriksson
  • 22.2k
  • 5
  • 61
  • 104
Source Link
Matej
Matej
  • 107
  • 6