Skip to main content
Bounty Ended with 50 reputation awarded by SqlWorldWide
edited body
Source Link
Zikato
  • 5.5k
  • 1
  • 16
  • 34

You can disable the parameter sniffing. When there is no sniffing, there is no compiled value (since it wouldn't be used anyway)

For example on the database level with:

ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

Or on query level with a Query Hint

Example procedure:

CREATE OR ALTER PROCEDURE dbo.ProcWithParam
(
    @objectName sysname
)
AS
    SELECT * FROM sys.objects AS o
    WHERE o.name = @objectName
    OPTION (OPTIMIZE FOR UNKNOWN)
go
EXEC dbo.ProcWithParam @objectName = N'sysclsobjs'

```

But the query hint would most likely show up in the SQL text portion of the query plan ```

You can disable the parameter sniffing. When there is no sniffing, there is no compiled value (since it wouldn't be used anyway)

For example on the database level with:

ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

Or on query level with a Query Hint

Example procedure:

CREATE OR ALTER PROCEDURE dbo.ProcWithParam
(
    @objectName sysname
)
AS
    SELECT * FROM sys.objects AS o
    WHERE o.name = @objectName
    OPTION (OPTIMIZE FOR UNKNOWN)
go
EXEC dbo.ProcWithParam @objectName = N'sysclsobjs'

But the query hint would most likely show up in the SQL text portion of the query plan ```

You can disable the parameter sniffing. When there is no sniffing, there is no compiled value (since it wouldn't be used anyway)

For example on the database level with:

ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

Or on query level with a Query Hint

Example procedure:

CREATE OR ALTER PROCEDURE dbo.ProcWithParam
(
    @objectName sysname
)
AS
    SELECT * FROM sys.objects AS o
    WHERE o.name = @objectName
    OPTION (OPTIMIZE FOR UNKNOWN)
go
EXEC dbo.ProcWithParam @objectName = N'sysclsobjs'

```

But the query hint would most likely show up in the SQL text portion of the query plan

Source Link
Zikato
  • 5.5k
  • 1
  • 16
  • 34

You can disable the parameter sniffing. When there is no sniffing, there is no compiled value (since it wouldn't be used anyway)

For example on the database level with:

ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

Or on query level with a Query Hint

Example procedure:

CREATE OR ALTER PROCEDURE dbo.ProcWithParam
(
    @objectName sysname
)
AS
    SELECT * FROM sys.objects AS o
    WHERE o.name = @objectName
    OPTION (OPTIMIZE FOR UNKNOWN)
go
EXEC dbo.ProcWithParam @objectName = N'sysclsobjs'

But the query hint would most likely show up in the SQL text portion of the query plan ```