SIMPLE parameterization not working.
-
Sunday, October 14, 2012 7:05 PM
why simple param not working for below queries...
DBCC FREEPROCCACHE;
GO
SELECT [id],[descr] FROM [dbo].[t1] WHERE id=1;
GO
SELECT [id],[descr] FROM [dbo].[t1] WHERE id=2;
GO
SELECT usecounts, cacheobjtype, objtype, [text], p.size_in_bytes as [size]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE --cacheobjtype like 'Compiled Plan%' AND
[text] NOT LIKE '%dm_exec_cached_plans%';
--output======
usecounts cacheobjtype objtype text size
1 Compiled Plan Adhoc SELECT [id],[descr] FROM [dbo].[t1] WHERE id=2; 24576
1 Compiled Plan Adhoc SELECT [id],[descr] FROM [dbo].[t1] WHERE id=1; 24576text of
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
t1 dbo user table 2012-10-15 00:30:35.817
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
id int no 4 10 0 yes (n/a) (n/a) NULL
descr varchar no 200 yes no yes Latin1_General_CI_AI
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
ps1
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
i1 nonclustered located on ps1 id
No constraints are defined on object 't1', or you do not have permissions.
No foreign keys reference table 't1', or you do not have permissions on referencing tables.
No views with schema binding reference table 't1'.
Manish
- Changed Type LearnerSql Sunday, October 14, 2012 7:07 PM
All Replies
-
Monday, October 15, 2012 3:50 AMModerator
Hello Manish,
You have to change PARAMETERIZATION from simple to forced for the database.
Run the below query and do the test again.
{
USE [master]
GO
ALTER DATABASE [yourdbname] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO}
Thank you,
Karthick P.K |My Facebook Group |My Site| Blog Space |Twitter
Please click the Mark as answer button and vote as helpful if this reply solves your problem
- Marked As Answer by LearnerSql Wednesday, October 17, 2012 9:01 AM
- Unmarked As Answer by TiborKMVP, Moderator Wednesday, October 17, 2012 9:03 AM
-
Monday, October 15, 2012 9:16 AMThis is the normal behaviour for adhoc queries. If you try with stored procedure, you will see only one plan created for n number of execution. If the memory is your concern, you have an option to set "Optimize for adhoc" to true. Once you set this, for the first time execution, it would only store the Plan stub. If with the same parmeter is executing the second time only, it will create the compiled plan for the query. Hence you can restrict the number of plans created. This would be helpful for patterns that executes only once by creating only stub not the compiled plan.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Marked As Answer by LearnerSql Wednesday, October 17, 2012 9:01 AM
- Unmarked As Answer by TiborKMVP, Moderator Wednesday, October 17, 2012 9:03 AM
-
Monday, October 15, 2012 11:46 AMModerator
Simple parameterization requires that the plan selection doesn't depend on selectivity. It doesn't seem like you have aprimary key (unique index) on the c1 column, so SQL Server can't say how many rows to be returned by just looking at the query. Create a unique index on that column and you might get simple parameterization (and please also post the CREATE TABLE command henceforth, makes it much easier for us to guess).
- Marked As Answer by LearnerSql Wednesday, October 17, 2012 8:57 AM
-
Wednesday, October 17, 2012 9:05 AMModerator
-
Wednesday, October 17, 2012 9:07 AMModerator
.png)
