Prepared vs AdHoc - Plan Cache ObjType

Question

Prepared vs AdHoc - Plan Cache ObjType

Jared

SSC Guru

Points: 61793
More actions
April 8, 2013 at 10:45 am

#291922

I would think that this would be an easy find with google... However, I have not found anything useful. So, gurus... What is the definition of a Prepared ObjType for a plan and what is the definition of an AdHoc ObjType for a plan; what distinguishes one from another and why is it important to me?
Jared
CE - Microsoft

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Parameterised vs not parameterised. (though you can prepare a statement with hardcoded values in it 🙂 )

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 2

GilaMonster (4/8/2013)
Parameterised vs not parameterised.

Excellent! Thanks Gail.

Jared
CE - Microsoft

Tac11 SSCertifiable Points: 6976 More actions · Answer 3

Hi Gila,

Don't know if I can add on this thread. let me know for future reference. Anyway just have a question:

my 'Prepared' size is more than 'adHoc'. should I run dbcc freesystemcache ('sql plans') to minimize size of the 'Prepared' plan Cache Object? Is 'Prepared' as bad as 'adHoc'?

thank you